Hi,

I'm using Piwik 2.10.0 and experiencing slow website response of about 1+ minute for 6+ million records in monthly archive table. I trace it down to the daily archive blob being update/invalidate every time the UI try to read from archive.

Background

• My Piwik instance track 8 Sites with about ~100K visits per day across all sites.
• My original setup was 2ecu + 2gb VPS, but it was unbearably slow. I have since throw a ton of hardware at it and done a lot of optimization. The current setup is:
• pi.mysite.com: 8cpu + 8gb of ram to handle tracking from client
• report.mysite.com: 10cpu + 10gb of ram + 100GB RAID10 for MySQL and cron archiving

Most of the time, CPU on report.mysite.com is only at 10% and memory at 60%. High memory usage is the result of TokuDB engine my MySQL setup.

With such beefy setup, I still get timeouts with cron archive and website even though my CPU and Memory usage stay the same. I'm almost at the point of giving up, but then I notice a pattern. It usually happen toward the end of the month and at the end of the day.

Debug

As it is reproducible, I did some SQL troubleshooting and identify that the issue has to do with the SQL executed from this module:
https://github.com/noogen/piwik/blob/master/core/DataAccess/Model.php - line 75: updateArchiveAsInvalidated

Issue was identified that the website always call the "get" method of report archiving causing slow UI response. Somehow, this SQL cause a full table read resulting in 1 second for every 1 million record in the monthly archive table. I move this invalidation in "Archive.php" from "get" method to "prepareArchive" method. This allow the cron job to continue to work while reducing the issue in the UI.

I have also prepared a pull request. The change is simple but please review because I do not know the inner working of Piwik as I am not a regular Piwik Developer.