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.

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

Tracking unavailable when data rotation happens #13636

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

Comments

Projects
None yet
2 participants
@kaplun

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

This comment has been minimized.

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 from System unusable when data rotation happens to Tracking unavailable when data rotation happens Oct 22, 2018

@mattab

This comment has been minimized.

Member

mattab commented Nov 13, 2018

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 Nov 13, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment