Piwik_archive_blob unnormally large?

futureweb · April 3, 2017, 1:54pm

Hey there,
I just noticed that some of the piwik_archive_blob Tables are unormally large in size?

See here:

Any idea what could cause this? And how to fix?

thx, bye from Austria
Andreas Schnederle-Wagner

futureweb · April 4, 2017, 5:01pm

@matthieu - I guess you could help me on this?!

futureweb · May 5, 2017, 9:03am

really noone who can help on this?

matthieu · May 8, 2017, 6:42pm

Try to run the command ./console core:purge-old-archive-data

futureweb · May 11, 2017, 1:03pm

@matthieu - command didn’t touch those Tables … only new ones!

php console core:purge-old-archive-data  
Purging outdated archives for 2017_05...Done. [Time elapsed: 0.447s]
Purging invalidated archives for 2017_05...Done. [Time elapsed: 0.789s]
Purging custom range archives for 2017_05...Done. [Time elapsed: 0.004s]
Optimizing archive tables...
Optimizing table piwik_archive_numeric_2017_05...Done. [Time elapsed: 9.372s]
Optimizing table piwik_archive_blob_2017_05...Done. [Time elapsed: 21.377s]

Andreas

futureweb · May 11, 2017, 1:12pm

alright …

php console core:purge-old-archive-data 2016-01-01

did the Trick … now the Table is 470MB instead of 4,6GB …

php console core:purge-old-archive-data all

should fix the Problem for all Tables, right?

matthieu · August 3, 2017, 5:57am

right. let us know if not working for you

futureweb · August 3, 2017, 7:28am

worked as expected:

futureweb · January 15, 2018, 10:00am

Hey there,
just noticed that one Table resists to get smaller - even with the purge command … no big deal - but just out of curiosity … any ideas on that?

thx, bye from Austria
Andreas

futureweb · November 14, 2018, 11:12am

Hey there,
got another Table which is significant bigger than all others … “php console core:purge-old-archive-data all” didn’t make it smaller.
Anything I can do here? Any ideas what’s causing this?

thx, bye from Austria
Andreas

fdellwing · November 14, 2018, 11:30am

github.com/matomo-org/matomo

Archive tables not cleaned up and growing uncontrollably

opened 10:42AM - 08 Nov 18 UTC

closed 07:31PM - 22 Nov 18 UTC

toebu

answered

Since about 3 weeks the archive data in one of our Matomo instances is growing a…t a much larger rate than usual. There was no change on the server or the traffic that could explain this: - Matomo version 3.5.1 was running for a few months already - No new plugins were added or other changes were made on the server - Traffic tracked by the instance stayed at around 500k pageviews per week - Archiving continued to be done by a cron job running every 15 minutes The log of the archiving showed the following error, that started at around the same time than the database started to increase in size: `Error: Got invalid response from API request: ?module=API&method=API.get&idSite=11&period=year&date=last7&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:63:"SQLSTATE[HY000]: General error: 2006 MySQL server has gone away";}'` Following the recommendations for that error from the [FAQ #183](https://matomo.org/faq/troubleshooting/faq_183/), we tried the following: - Set `max_allowed_packet` to 1GB - The `wait_timeout` was already set to 8 hours (and the archiving script only runs for a few minutes) This didn't change anything, the same error is still happening. Other measures we took: - Updated Matomo to the latest version (3.6.1) - Updated all plugins to the latest versions (HeatmapSessionRecording, MediaAnalytics, SecurityInfo, UsersFlow). Note that we also have 4 custom developed plugins running (already for several years without problems). - Installed the DBStats plugin - Based on the error, the problem comes from the yearly archive of site id 11 (the site with about 95% of the total traffic). This was confirmed by running archiving only for other sites and other time periods, which completed successfully. The DBStats plugin confirmed that the yearly archive is bloating the DB. Although also the monthly archive table seems to have way too many rows compared to previous months. Some cleanup seems to happen on some tables, because at least the blob_2018_10 table shrunk drastically in the last days. Other weird stuff seems to happen as there are big changes in tables that should no longer be touched, e.g. blob_2017_12 (if I'm understanding the archiving process correctly). Here's are two screenshots of the report tables sizes: 5th of November: ![screenshot_20181105_104648](https://user-images.githubusercontent.com/1837568/48193336-8bf56380-e34a-11e8-9700-1df61dd57296.png) 8th of November: ![screenshot_20181108_112526](https://user-images.githubusercontent.com/1837568/48193341-8e57bd80-e34a-11e8-8d05-0a3f36113a6a.png) Checking the DB directly, we can see the many duplicate archive entries (again, if I'm understanding the process correctly, this query should return no results): `SELECT idsite, date1, date2, period, name, COUNT(*) as count FROM piwik_archive_numeric_2018_01 GROUP BY idsite, date1, date2, period, name HAVING count > 1 ORDER BY count DESC;` Here's the first 10 results (the result set has 1550 rows): ``` +--------+------------+------------+--------+--------------------------------------+-------+ | idsite | date1 | date2 | period | name | count | +--------+------------+------------+--------+--------------------------------------+-------+ | 11 | 2018-01-01 | 2018-12-31 | 4 | bounce_count | 2010 | | 11 | 2018-01-01 | 2018-12-31 | 4 | max_actions | 2010 | | 11 | 2018-01-01 | 2018-12-31 | 4 | nb_actions | 2010 | | 11 | 2018-01-01 | 2018-12-31 | 4 | nb_visits | 2010 | | 11 | 2018-01-01 | 2018-12-31 | 4 | sum_visit_length | 2010 | | 11 | 2018-01-01 | 2018-12-31 | 4 | UserCountry_distinctCountries | 1959 | | 11 | 2018-01-01 | 2018-12-31 | 4 | Actions_sum_time_generation | 1925 | | 11 | 2018-01-01 | 2018-12-31 | 4 | Actions_nb_hits_with_time_generation | 1925 | | 11 | 2018-01-01 | 2018-12-31 | 4 | Actions_nb_pageviews | 1925 | | 11 | 2018-01-01 | 2018-12-31 | 4 | Actions_nb_uniq_pageviews | 1925 | +--------+------------+------------+--------+--------------------------------------+-------+ ``` Any hints what might be happening here and how we could go about fixing it? At the current rate of increased disk usage, we have about 2 weeks left before the disk is full. So first thing would be to find a way to delete duplicate archiving data manually to buy us more time. But we of course have to find a real solution that gets the yearly archiving working correctly again (which will hopefully also get the archive purging working again). (This issue is related to #11322, #10439 and #7181)