Excessive growth of an piwik_archive_blob_ table

beger · July 2, 2020, 7:44am

We upgraded our Piwik-Servers from v3.5 to v3.13.5 about a month ago. What we are now seeing is one piwik_archive_blob_table that keeps growing and growing.

The table piwik_archive_blob_2020_01 has already 170 GB while all the others are below 10 GB (see attached screenshot).

Do you have any suggestions what might be causing this table to grow so large and how we might get it down to a reasonable size again?

Lukas · July 2, 2020, 10:06am

Hi,

I think that issue was only fully solved in 3.13.6 with

github.com/matomo-org/matomo

Archive Blob Tables bloating in size

opened 09:26AM - 10 May 20 UTC

closed 08:45PM - 20 May 20 UTC

James-Oakley

Bug c: Performance

This is a follow-up to #15831. Since upgrading to 3.13.5, I was able to get m…y bloated archive tables back to their proper size by running `./console core:purge-old-archive-data all`. However, since then, the tables began to grow in size again. Things are much better since 3.13.5. I was able to shrink the tables again by running the `purge-old-archive-data` command again. My largest archive blog table then shrank from 50,456 rows and 236 MiB back to 9,910 rows and 7.0 MiB. So I now have a way to solve the problem of the bloated tables (whereas, before 3.13.5, I had no way to shrink those tables). However, they are surely not supposed to grow to the point that they need pruning with a manual command. The table referred to above is for January 2020, to which no new data is added, so I'd expect that archive table to remain the same size. It may well be that the problem is actually my end. Having read the documentation, I'm running a maintenance command once every 10 minutes: `/usr/bin/php ~/public_html/console core:archive --url=https{domain} >> {log-file}` Is there another command that, when Matomo is behaving correctly, needs running on schedule to keep things running smoothly? (I realise I _could_ run `purge-old-archive-data` with cron, but is it correct behaviour for me to need to do so?) Or are the tables still bloating when they shouldn't be?

beger · September 14, 2020, 6:13am

So it took a while for us to do the upgrade, but I wanted to document what we did and how the results are so far.

First thing we did was upgrade Matomo to 3.14 where the issue above should be fixed. As expected, that alone did not solve the problem. Then I ran the following commands:

./console core:purge-old-archive-data january
./console database:optimize-archive-tables january

That reduced the table piwik_archive_blob_2020_01 to ~ 9GB where it has roughly stayed for two weeks now. So atm. I am cautiously optimistic that the issue is solved.