2.8.3 Archive Database size increasing dramatically

Support & Bugs
1

After upgrading from 2.8.2. to 2.8.3. the piwik_archive_blob tables are growing dramatically (see attached picture).
This already resulted in a corrupt table that had to be repaired with myisamchk.

We had no notable increase in visitors, anyway the table of the current month and january 2014 are growing like hell… I know that in the january table the overall year data is stored. This explains why only current month and january table are affected.

This happended after upgrading from 2.8.2. to 2.8.3. We have the following cronjob running:
30 * * * * piwik /usr/bin/php5 /var/www/webs/piwik/docs/misc/cron/archive.php --url=http://piwik.hirescam.de/ > /dev/null

I think this must be a serious bug - most of the piwik users wont realize this until they run out of disk space or get a corrupt database table…

Regards
Florian

UPDATE: After having a look in the backups I realised that this problem already occured after update from 2.8.1. to 2.8.2. Thus I think it still exists in 2.8.3. as i cant find anything about this issue in the chengelog…

2

it’s normal that January table is bigger than others because it contains the yearly data as well

Have you setup the automated cron script? How to Set up Auto-Archiving of Your Reports - Analytics Platform - Matomo

Every day or so it should remove the “invalid” archives from there.

3

Dear Matt,

thanks for your reply.
Yes, we have the cronjob running every hour.
I know that the january table is bigger. Anyway please have a look at the attached picture in my previous post. The october table already has 1.4 million entries. We had no increase in site traffic.
More than 8 million entries in january and 1 million entries in october happened in a few days after upgrading from 2.8.1 to 2.8.2/2.8.3.
If you consider 1 million entries in about 2-3 days you can expect 15 million entries a month comparing to 196.000 entries in september. This does not look like a regular behavior. Any idea what this could be? Reports are showing up correctly in the backend…

Any SQL query that I can run to help debugging and find the reason? No one else having similar problems?
We are running piwik for years now and always updated with the newest release…

4

Quick idea: Do you or a user created a “preprocessed segment” in October ?

If I recall right, preprocessing segments gives speed but costs MySQL space…

Dali

5

The problem seems to be solved… It turned out, that during the archive cronjob, the database temporarily increases from ~ 200.000 to ~ 1.800.000 entries and after successfull cronjob shrinks back to ~ 200.000 entries.
Because of a broken database table the cronjob missed to cleanup the old entries because the database was locked.
After having successfully executed the cronjob it cleaned up everything…

Anyway a strange behavior and I didnt know that the archive cronjob temporarily stores entries during the archive process.