Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Tracking unavailable when data rotation happens #13636

Closed
kaplun opened this issue Oct 22, 2018 · 2 comments
Closed

Tracking unavailable when data rotation happens #13636

kaplun opened this issue Oct 22, 2018 · 2 comments

Comments

@kaplun
Copy link

kaplun commented Oct 22, 2018

On a large site, when the PrivacyManager\Tasks.deleteLogData task is executed, the tables can get locked for a very long time, thus blocking receiving new data.

MariaDB [(none)]> show processlist;
+--------+-------------+-----------+--------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id     | User        | Host      | db     | Command | Time  | State                           | Info                                                                                                 | Progress |
+--------+-------------+-----------+--------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+----------+
|      1 | system user |           | NULL   | Daemon  |  NULL | InnoDB purge worker             | NULL                                                                                                 |    0.000 |
|      2 | system user |           | NULL   | Daemon  |  NULL | InnoDB purge worker             | NULL                                                                                                 |    0.000 |
|      3 | system user |           | NULL   | Daemon  |  NULL | InnoDB purge worker             | NULL                                                                                                 |    0.000 |
|      4 | system user |           | NULL   | Daemon  |  NULL | InnoDB purge coordinator        | NULL                                                                                                 |    0.000 |
|      5 | system user |           | NULL   | Daemon  |  NULL | InnoDB shutdown handler         | NULL                                                                                                 |    0.000 |
| 881641 | matomo      | localhost | matomo | Sleep   | 27088 |                                 | NULL                                                                                                 |    0.000 |
| 881644 | matomo      | localhost | matomo | Query   |  1707 | Sending data                    | /* trigger = CronArchive, idSegments = [7] */

                        SELECT
                                count(distinct log_inner.idvisitor) AS  |    0.000 |
| 883625 | matomo      | localhost | matomo | Query   |   828 | Waiting for table metadata lock | LOCK TABLES matomo_log_action WRITE, matomo_log_conversion READ, matomo_log_conversion_item READ, ma |    0.000 |
| 901550 | matomo      | localhost | matomo | Query   |   280 | Waiting for table metadata lock | SELECT MIN(idaction) as idaction, type, name FROM matomo_log_action WHERE ( hash = CRC32('secure.pro |    0.000 |
| 901551 | matomo      | localhost | matomo | Query   |   280 | Waiting for table metadata lock | SELECT MIN(idaction) as idaction, type, name FROM matomo_log_action WHERE ( hash = CRC32('secure.pro |    0.000 |
| 901549 | matomo      | localhost | matomo | Query   |   280 | Waiting for table metadata lock | SELECT MIN(idaction) as idaction, type, name FROM matomo_log_action WHERE ( hash = CRC32('secure.pro |    0.000 |
| 901552 | matomo      | localhost | matomo | Query   |   280 | Waiting for table metadata lock | SELECT MIN(idaction) as idaction, type, name FROM matomo_log_action WHERE ( hash = CRC32('secure.pro |    0.000 |
| 901574 | matomo      | localhost | matomo | Sleep   |   237 |                                 | NULL                                                                                                 |    0.000 |
| 901575 | matomo      | localhost | matomo | Query   |   234 | Waiting for table metadata lock | SELECT
                                        log_link_visit_action.idvisit,
                                        COALESCE(log_action.type, log_action_title.type) AS  |    0.000 |
| 901648 | root        | localhost | NULL   | Query   |     0 | init                            | show processlist                                                                                     |    0.000 |
+--------+-------------+-----------+--------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+----------+
@kaplun
Copy link
Author

kaplun commented Oct 22, 2018

The particular query that is locking up tracking is:

/* trigger = CronArchive, idSegments = [7] */ SELECT count(distinct log_inner.idvisitor) AS `1`, count(distinct log_inner.user_id) AS `39` FROM ( SELECT log_visit.idvisitor, log_visit.user_id FROM matomo_log_visit AS log_visit LEFT JOIN matomo_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit WHERE ( log_visit.visit_last_action_time >= '2018-09-30 22:00:00' AND log_visit.visit_last_action_time <= '2018-10-31 22:59:59' AND log_visit.idsite IN ('1') ) AND ( log_link_visit_action.custom_dimension_1 = '400' AND (( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'mail.protonmail.com/api/users', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'mail.protonmail.com/api/users', '%') AND type = 3 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'mail.protonmail.com/api/users', '%') AND type = 2 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'mail.protonmail.com/api/users', '%') AND type = 10 )) ) )) GROUP BY log_visit.idvisit ORDER BY NULL ) AS log_inner

@kaplun kaplun changed the title System unusable when data rotation happens Tracking unavailable when data rotation happens Oct 22, 2018
@kaplun kaplun mentioned this issue Oct 22, 2018
Open
@mattab
Copy link
Member

mattab commented Nov 13, 2018 •
edited

Hi @kaplun

We can recommend you get full professional paid support for your Matomo to ensure best possible support for you 👍

Contact us via https://matomo.org/support/

Otherwise for this particular problem, try following the steps in this FAQ: https://matomo.org/faq/how-to/faq_113/ and maybe set all these 5 settings to 0

@mattab mattab closed this as completed Nov 13, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@kaplun @mattab