Size of piwik_archive_blob files is astronomical

Hello, I’m running into a very big problem with the size of my Piwik_archive_blob files. I’ve searched around and can’t find a solution that fixes the issue. My server is completely out of disk space because of this and I need help badly.

This is what the file sizes look like:

18.8GiB [##########]  piwik_archive_blob_2019_01.ibd
   15.2GiB [########  ]  piwik_archive_blob_2018_12.ibd
   11.8GiB [######    ]  piwik_archive_blob_2018_11.ibd
    7.4GiB [###       ]  piwik_archive_numeric_2018_12.ibd
    7.1GiB [###       ]  piwik_archive_numeric_2019_01.ibd
    6.5GiB [###       ]  piwik_archive_blob_2019_02.ibd
    6.2GiB [###       ]  piwik_archive_numeric_2018_11.ibd
    5.9GiB [###       ]  piwik_archive_blob_2018_10.ibd
    3.6GiB [#         ]  piwik_archive_numeric_2018_10.ibd
    3.1GiB [#         ]  piwik_archive_blob_2018_01.ibd
    2.6GiB [#         ]  piwik_archive_numeric_2019_02.ibd

Can someone please help point me in the right direction?

Hello, we encountered the same problem after updating to Matomo 3.12.0. From 30.10.19 until today our piwik_archive_blob_2019_01.sql.gz grew from 1,5 GB to 27 GB.

@piwik2 Could you solve your problem?

We’ve searched around too, but couldn’t find any hint to solve this problem or hints about changing the configuration to stop the daily growth of this file.

Any help is appreciated!

I also had this problem several months ago and am having the problem again now. We saw one archive table go from under a GB to over 20GB. It did seem to eventually simply resolve itself but since it seems to cause major spikes in resource usage and is making our backups take up far more space than usual, I’d like to better understand how to prevent and fix it. It does seem to have happened a little bit after we upgraded to 3.12.0. Any help would be appreciated. Thank you!

Thanks all the report. We also would really like to fix this issue.
Can you please try to run the console command:
./console core:purge-old-archive-data all

and confirm if this helps?

feel free to also join discussion at Run core:purge-old-archive-data regularly to prevent archive tables gaining size · Issue #10439 · matomo-org/matomo · GitHub
Thanks,

Hello,
we ran the command
./console core:purge-old-archive-data all

Unfortunately there was no effect. In the meantime our piwik_archive_blob_2019_01 got 51 GB.

We ran the suggested command a few times and it reduced the size of the two largest archive blobs by about 10% but unfortunately this means they are still collectively taking up about 27GB. Possibly coincidentally our largest blob is also piwik_archive_blob_2019_01 at ~25GB

18 days after updating to 3.12.0 our piwik_archive_blob_2019_01 got 72 GB and our November File got 56 GB.

2 days ago we tried the solution mentioned in github by user “diosmosis”

This worked very good. November and January file had ~ 450 MB after this.
Thanks to “diosmosis”!

We still work with the changed model.php and put up a cronjob every night with
./console core:purge-old-archive-data all

Otherwise files still are getting bigger every night. With the next update we will check the new model.php.

Hello everyone,
We just published 3.13.0-b1 which should fix the issue, could you try upgrade to latest beta with I would like to test early beta and RC releases, how do I enable automatic updates to use these development versions? FAQ - Analytics Platform - Matomo and let us know if the issue is fixed after 24 hours?

Thanks

Our server went down because this bug ate up available space.
Does the update also cleans up the faulty data from from the 2019-01 tables?
How can this be properly cleaned?

Yes, the faulty data should get cleaned up within a day or so, let us know if not?

updated to 3.13.0 last week and looks like everything is working fine. Database size reduced from 35GB to ~7GB. Archiving is now back to speedy and less resource intensive. Thank you for the help!

1 Like