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.

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 rotating logs for large site #13637

Open
kaplun opened this issue Oct 22, 2018 · 4 comments
Open

Tracking unavailable when rotating logs for large site #13637

kaplun opened this issue Oct 22, 2018 · 4 comments
Labels
c: Performance For when we could improve the performance / speed of Matomo.
Milestone
For Prioritization

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

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
Copy link
Author

kaplun commented Oct 22, 2018

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

@kaplun kaplun closed this as completed Oct 22, 2018
@Findus23 Findus23 added the duplicate For issues that already existed in our issue tracker and were reported previously. label Oct 22, 2018
@tsteur
Copy link
Member

tsteur commented Dec 17, 2018

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

@tsteur tsteur reopened this Dec 17, 2018
@tsteur tsteur added c: Performance For when we could improve the performance / speed of Matomo. and removed duplicate For issues that already existed in our issue tracker and were reported previously. labels Dec 17, 2018
@tsteur tsteur modified the milestones: 3.8.0, 3.9.0 Dec 17, 2018
@mattab mattab modified the milestones: 3.9.0, 3.10.0 Mar 18, 2019
@mattab
Copy link
Member

mattab commented Mar 20, 2019

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?

@mattab mattab removed this from the 3.10.0 milestone Mar 20, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
c: Performance For when we could improve the performance / speed of Matomo.
Projects
None yet
Milestone
For Prioritization
Development

No branches or pull requests
5 participants
@kaplun @tsteur @mattab @Findus23 @innocraft-automation