@mkindred opened this Issue on May 20th 2021

This does involve the custom reports plugin, but the issue is during archiving, so I'm not sure if the culprit is custom reports, goals, or core.

Expected Behavior

Archiving processes run from hourly cron should complete.

Current Behavior

I have had several archive processes that start at each cron archive run (hourly), but never complete. They ran for hundreds of hours or until I killed the process manually. Since this first started happening, my archive tables have been growing to be quite large and full of duplicates.

The two archive processes in question were yearly archives for custom reports, 2020-01. Assuming that the requestedReport parameter in the archive command corresponds to the custom report ID, it seems that both of the custom reports in question are fairly new. My guess is that I created the new custom reports and then invalidated six months of archives, which would overlap into 2020. At that point, I'm guessing that the 2020 yearly archives would run again. But I don't understand why they wouldn't complete.

Possible Solution

To stop the archive tables from growing and prevent the server from running out of resources, I deleted the two custom reports in question. That seems to have solved the initial problem.

Steps to Reproduce (for Bugs)

I'm not sure that this is easily reproducible. I want to try to wrangle all the archive tables back down to manageable levels before I attempt it. But here is what I remember of my process.

  1. Set up a goal
  2. Create a custom report including the goal
  3. Invalidate archive for a period of time that would overlap with a year where the goal and custom report didn't exist.

Your Environment

  • Matomo Version: 4.2.1
  • PHP Version: 7.3.28, cli memory_limit => -1
  • Server Operating System: Debian 9
  • Additionally installed plugins: CustomReports (v4.0.7), DisableTracking (v1.2.0), InvalidateReports (v4.0.1), MarketingCampaignsReporting (v4.0.4)

Matomo settings

  • Never deleting old raw data
  • Never deleting old aggregated report data
  • Cron archive once per hour

FWIW site 1 gets 500k pageviews / year, and site 2 gets 2.5 million pageviews / year.

Questions

Do my steps to reproduce above point to an obvious error in my process? Ideally I'd like to create the custom reports and invalidate archives again, but I don't want to risk having the same thing happen.

Now that I've deleted the custom reports and things are running OK, I have very large archive tables:

'matomo_archive_blob_2020_01', '3,428 MB', '13,947,541'
'matomo_archive_numeric_2020_01', '1,499 MB', '7,999,953'

I seem to have millions of duplicate rows in these two tables. I ran the following to find duplicates:

SELECT
  idsite, date1, date2, period, name,
  COUNT(*) as count
FROM matomo_archive_blob_2020_01
GROUP BY idsite, date1, date2, period, name
HAVING count > 1 ORDER BY count DESC;

... resulting in the following (truncated for brevity):

'1', '2020-01-06', '2020-01-06', '1', 'Goals_ItemsCategory', '49523'
'1', '2020-01-06', '2020-01-06', '1', 'Goals_ItemsCategory_Cart', '49523'
'1', '2020-01-06', '2020-01-06', '1', 'Goals_ItemsName', '49523'
'1', '2020-01-06', '2020-01-06', '1', 'Goals_ItemsName_Cart', '49523'
'1', '2020-01-06', '2020-01-06', '1', 'Goals_ItemsSku', '49523'
'1', '2020-01-06', '2020-01-06', '1', 'Goals_ItemsSku_Cart', '49523'
'1', '2020-01-06', '2020-01-06', '1', 'Goal_days_until_conv', '49523'
'1', '2020-01-06', '2020-01-06', '1', 'Goal_visits_until_conv', '49523'
'1', '2020-01-07', '2020-01-07', '1', 'Goals_ItemsCategory', '49519'
'1', '2020-01-07', '2020-01-07', '1', 'Goals_ItemsCategory_Cart', '49519'
'1', '2020-01-07', '2020-01-07', '1', 'Goals_ItemsName', '49519'
...etc

I've brought the code and dbase down into a local docker environment to test whether I can purge. But I'm running into lots of issues with group_concat errors (GROUP_CONCAT cut off the query result, you may have to purge archives again). Based on the number of duplicate rows above, I'm assuming that maybe this is trying to create an SQL statement involving 45,000 rows?

Do I need to change my MySQL settings in order to allow the purging and optimizing to continue?

@diosmosis commented on May 20th 2021 Member

@mkindred this sounds like it would be fixed by https://github.com/matomo-org/matomo/pull/17244, which is included in 4.3.0. Can you see if applying this change then running core:purge-old-archive-data helps? (Ideally if you could, updating to 4.3.0 would be better, as it has many other fixes around archiving.)

@mkindred commented on May 21st 2021

Yep, I'll upgrade. I wanted to hold off on upgrading until I had stopped the table growth.

Just to help with my understanding, can you confirm:

The automatic purging of invalidated archives doesn't happen until the archive in question completes, correct? And I assume this probably also applies to a manual purging. Those tables aren't going to shrink if there's not a completed archive for the same site and period.

Also, will the upgrade to 4.3 help with the GROUP_CONCAT errors, or will I need to change my.conf to help these purges?

@diosmosis commented on May 21st 2021 Member

@mkindred

The automatic purging of invalidated archives doesn't happen until the archive in question completes, correct?

The purging process deletes archives that are older than the newest ones. The newest ones do not get deleted, and the newest one has to be marked as complete to be considered the newest.

Also, will the upgrade to 4.3 help with the GROUP_CONCAT errors, or will I need to change my.conf to help these purges?

It should help with it, the code will try and set the session group_concat_max_len before the query. If this works for the mysql user you are using, it will make the problem much less likely to occur. However, since your tables are already rather large, you may need to manually purge a couple times to get things under control first.

@mkindred commented on May 27th 2021

Update:

  • I upgraded Matomo to 4.3.0 in my local docker environment.
  • I deleted the two custom reports. This seems to have allowed my archiving to complete.
  • Once I saw that, I deleted the same two reports from production, and archiving is completing there, as well. That's good to see.

But I still have millions of duplicate rows in the archive tables associated with the 2020-01 yearly archives:

MariaDB [php]> SELECT
    ->   idsite, date1, date2, period, name,
    ->   COUNT(*) as count
    -> FROM matomo_archive_blob_2020_01
    -> GROUP BY idsite, date1, date2, period, name
    -> HAVING count > 1 ORDER BY count DESC
    -> LIMIT 25;
+--------+------------+------------+--------+--------------------------+-------+
| idsite | date1      | date2      | period | name                     | count |
+--------+------------+------------+--------+--------------------------+-------+
|      1 | 2020-01-06 | 2020-01-06 |      1 | Goals_ItemsCategory      | 49523 |
|      1 | 2020-01-06 | 2020-01-06 |      1 | Goals_ItemsCategory_Cart | 49523 |
|      1 | 2020-01-06 | 2020-01-06 |      1 | Goals_ItemsName          | 49523 |
|      1 | 2020-01-06 | 2020-01-06 |      1 | Goals_ItemsName_Cart     | 49523 |
|      1 | 2020-01-06 | 2020-01-06 |      1 | Goals_ItemsSku           | 49523 |
|      1 | 2020-01-06 | 2020-01-06 |      1 | Goals_ItemsSku_Cart      | 49523 |
|      1 | 2020-01-06 | 2020-01-06 |      1 | Goal_days_until_conv     | 49523 |
|      1 | 2020-01-06 | 2020-01-06 |      1 | Goal_visits_until_conv   | 49523 |
|      1 | 2020-01-07 | 2020-01-07 |      1 | Goals_ItemsCategory      | 49519 |
|      1 | 2020-01-07 | 2020-01-07 |      1 | Goals_ItemsCategory_Cart | 49519 |
|      1 | 2020-01-07 | 2020-01-07 |      1 | Goals_ItemsName          | 49519 |
|      1 | 2020-01-07 | 2020-01-07 |      1 | Goals_ItemsName_Cart     | 49519 |
|      1 | 2020-01-07 | 2020-01-07 |      1 | Goals_ItemsSku           | 49519 |
|      1 | 2020-01-07 | 2020-01-07 |      1 | Goals_ItemsSku_Cart      | 49519 |
|      1 | 2020-01-07 | 2020-01-07 |      1 | Goal_days_until_conv     | 49519 |
|      1 | 2020-01-07 | 2020-01-07 |      1 | Goal_visits_until_conv   | 49519 |
|      1 | 2020-01-08 | 2020-01-08 |      1 | Goals_ItemsCategory      | 49511 |
|      1 | 2020-01-08 | 2020-01-08 |      1 | Goals_ItemsCategory_Cart | 49511 |
|      1 | 2020-01-08 | 2020-01-08 |      1 | Goals_ItemsName          | 49511 |
|      1 | 2020-01-08 | 2020-01-08 |      1 | Goals_ItemsName_Cart     | 49511 |
|      1 | 2020-01-08 | 2020-01-08 |      1 | Goals_ItemsSku           | 49511 |
|      1 | 2020-01-08 | 2020-01-08 |      1 | Goals_ItemsSku_Cart      | 49511 |
|      1 | 2020-01-08 | 2020-01-08 |      1 | Goal_days_until_conv     | 49511 |
|      1 | 2020-01-08 | 2020-01-08 |      1 | Goal_visits_until_conv   | 49511 |
|      1 | 2020-01-09 | 2020-01-09 |      1 | Goals_ItemsCategory      | 49505 |
+--------+------------+------------+--------+--------------------------+-------+
25 rows in set (2 min 47.919 sec)

Attempting to purge still dies:

wodby<a class='mention' href='https://github.com/php'>@php</a>.container:/var/www/html $ time /usr/local/bin/php /var/www/html/public/console core:purge-old-archive-data all;
Purging outdated archives for 2008_07...Done. [Time elapsed: 0.003s]
Purging outdated archives for 2008_08...Done. [Time elapsed: 0.000s]
Purging outdated archives for 2008_09...Done. [Time elapsed: 0.000s]
...snip....
Purging invalidated archives for 2019_11...Done. [Time elapsed: 0.133s]
Purging invalidated archives for 2019_12...Done. [Time elapsed: 0.135s]
Purging invalidated archives for 2020_01...INFO [2021-05-27 18:09:56] 2184  GROUP_CONCAT cut off the query result, you may have to purge archives again.
INFO [2021-05-27 18:09:56] 2184  GROUP_CONCAT cut off the query result, you may have to purge archives again.
INFO [2021-05-27 18:09:56] 2184  GROUP_CONCAT cut off the query result, you may have to purge archives again.
INFO [2021-05-27 18:09:57] 2184  GROUP_CONCAT cut off the query result, you may have to purge archives again.
INFO [2021-05-27 18:09:57] 2184  GROUP_CONCAT cut off the query result, you may have to purge archives again.
...etc.

mariadb group_concat_max_len:

MariaDB [(none)]> SHOW VARIABLES LIKE '%group_concat_max_len%';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| group_concat_max_len | 1048576 |
+----------------------+---------+
1 row in set (0.001 sec)
@diosmosis commented on May 27th 2021 Member

@mkindred Does the count of duplicates stay the same after running the purge command? Or better, does the count of total rows in the archive table decrease at all?

@mkindred commented on May 27th 2021

The duplicate rows remain the same after each purge. The total row counts for matomo_archive_blob_2020_01 and matomo_archive_numeric_2020_01 seem to fluctuate up a bit, then back down to 12.2M rows.

@diosmosis commented on May 27th 2021 Member

@mkindred I guess there are just far too many duplicate archives for the current code to handle... Would you be able to apply this patch: https://github.com/matomo-org/matomo/compare/large-amount-of-purgable-archives?expand=1 then try and purge again?

Powered by GitHub Issue Mirror