@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

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
@mattab commented on November 13th 2018 Member

Hi @kaplun

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

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

This Issue was closed on November 13th 2018
Powered by GitHub Issue Mirror