@kaplun opened this Issue on October 22nd 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 commented on October 22nd 2018

This is the query that it causing the tracking lock up:

/* 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 commented on October 22nd 2018

This is a duplicate of #13636. GitHub was having synchronization troubles when I submitted the issue.

@tsteur commented on December 17th 2018 Member

Reopening this one. We are planning to merge https://github.com/matomo-org/matomo/pull/11988/files which will make the process itself faster. And we also plan to make it non-locking so that there won't be any tracking issues.
We ideally also want to temporarily for few seconds stop tracking maybe while the process of deleting actions is happening (should be fast).

cc @diosmosis @mattab

@mattab commented on March 20th 2019 Member

https://github.com/matomo-org/matomo/pull/11988/files was fixed, what would be the next steps @tsteur ? Maybe we can create a separate issue for each specific idea?

Powered by GitHub Issue Mirror