Cleaning Up / Reducing Piwik Database Size?

Hello,

Presently running a 60GB piwik db with records over 2 years.

The cost of hosting it is getting exorbitant and for my usage I really only require recent data say 3-6months.

The two tables that take up the most space and date back the furthest appear to be piwik_log_visit & piwik_log_link_visit_action.

Are these the right DB’s to truncate in order to trim the Piwik DB? On a mirror of my production DB, I’m presently testing deleting all records older than 90days from both tables and as expected is taking a huge amount of time.

Is this the ideal way to go about trimming piwik to hold records only for the last x days?

Into the future, is it better to a run a db cleanup script to do this every month? Or utilize piwik’s own settings? Anybody have any tricks, methods or scripts that would achieve what i’m after?

Cheers!

Hi there,

The ideal way to purge old piwik data is to use the built-in feature that does it, see the user guide here: Managing your database’s size - Analytics Platform - Matomo

@matt

there is not yet a console command to purge this data? For me it takes mutiple days to purge the data. I do it now scheduled, but if I want to it manually, I can only do it from the website.

It would be nice to have a console command to purge the data. The command ./console core:purge-old-archive-data does not seem to do it. Is there a way to purge the old data from the visitlog tables through cli?

Yes since Piwik 2.14.0 there is a new console command to delete Old log data. please check: How do I delete old visitors statistics for a given website and/or date? - Analytics Platform - Matomo

I see that this could help, but is that doing the same as purgin old data?

What I am now doing is: Managing your database’s size - Analytics Platform - Matomo
That does not involve dates to be entered.

I did see that the code for cleaning the database (LogDataPurger.php) is changed. Is this because of the new console command? Than I can see that it should do the same.
Is that correct?

We ran the console command to delete old reports, etc. However, the size of the DB has not changed.
Backups are smaller, and there does not seem to be a correlation between table sizes and the disk.
what I mean is - the tables have gotten smaller, but the disk is still almost full…

Is something not getting purged?

I have the same problem. I already tried to use console tool and the purge by web interface. In both case, my database is with more that 1 Tb.

Now, Mysql is killing the connection in 2/3 of archiving process.

Any other suggestion?