Auto archiving script makes mysql unusable?

Support & Bugs
1

Hello,

we use piwik on a site with more than 100k daily visits and I have discovered quite a big problem. Every night the archive.sh script is run and during the calculation whole mysql server is unreachable, so the site isn’t able to show any content. This is very odd, i’d appreciate if anyone could come with some solution.

Thank you

2

[quote=cer @ Apr 7 2010, 11:32 PM]Hello,

we use piwik on a site with more than 100k daily visits and I have discovered quite a big problem. Every night the archive.sh script is run and during the calculation whole mysql server is unreachable, so the site isn’t able to show any content. This is very odd, i’d appreciate if anyone could come with some solution.

Thank you[/quote]

How many sites do you have in piwik? Are you running the archive script on the database server?
There are some memory leaks in piwiks archiving process which is even worse with multiple sites as memory usage is accumulating which could simply be too much for the server.
I’m also seeing thousands of mysql queries per second when archiving is running.

3

[quote=Thomas Seifert @ Apr 9 2010, 10:30 AM]How many sites do you have in piwik? Are you running the archive script on the database server?
There are some memory leaks in piwiks archiving process which is even worse with multiple sites as memory usage is accumulating which could simply be too much for the server.
I’m also seeing thousands of mysql queries per second when archiving is running.[/quote]

yes, i run it on the database server. there is only one site in piwik, but that site has well over 100k unique hits/day, maybe it’s too much for the script to handle properly

4

Make sure your tables are indexed correctly. I had a similar problem that was partially caused by missing indexes. You could also set a cron script to run archiving hourly so it doesn’t have process a day’s worth of data at once.

5

Archiving every hour will not make archiving faster, as Piwik works by day increments and will process daily data the first time archiving is ran after the day is finished. see the bottom of http://piwik.org/docs/setup-auto-archiving/ for more info.