Cleaning up database


(kionez) #1

Hi all!

I have a Piwik installation with 50 small websites tracked.

I disabled the browser triggered archiving, set the processing report to 30minutes (1800 seconds) and I’m running the auto-archive cron script every hour.

Thanks to new features in new version (1.5) I set the weekly automatic purge of logs older than 180 days.

My Piwik database is stll “big”, about 700 Mib. I read on that forum and some blogs around the net that I can drop the tables “piwik_archive_blob_*” and “piwik_archive_numeric_*”, because they’re rebuilt automatically when they are needed.

Is this correct? Can i purge that tables without losing old data?

I know, with log purge I delete everything but access statistics.

I can’t understand if access statistics are related to piwik_archive_blob_* or piwik_archive_numeric_*, but I have to reduce database usage :slight_smile:

Thanks in advance!

k.


(Matthieu Aubry) #2

piwik_archive_XXX contains processed reports. piwik_archive_XXX are re-created from the logs, but ONLY if you have all logs available. if you also use log purging, then you risk losing your data forever.

At this stage it’s not possible to minimize size of piwik_archive_XXX tables unless you delete old ones forever.


(kionez) #3

[quote=matt]
At this stage it’s not possible to minimize size of piwik_archive_XXX tables unless you delete old ones forever.[/quote]

Ok, many thanks!

So in my installation i can’t delete piwik_archive_* older than 180days (my purge log value) if I don’t want to lose data.

I don’t have restrictions on mysql size, because everything runs on my dedicated server but I have to tune a bit the mysql backup script, it run every hour in order to have many copies of customers databases and when it compress (gzip) Piwik db it reaches high cpu load.
I’m running it at level 19 of nice, so it shouldn’t affect other tasks, but average loads sometime reachses 4.0!

k.