Since the upgrade from 1.8.2 to 1.8.3 the reports tables (blob and numeric) grew expotential. Total database size grew from around 800MB to 1.2 GB in not even a week.
We are using archiving via php cron with browser updates disabled and running the cron every hour.
The cron output shows no errors (stating last run was without errors and finishes without errors at the end). The daily task seems to free some MBs, but does not offset the data collected by far.
I can’t pin-point (as I’m not exactely sure what is stored where), but it looks to me that the blob table is far inflated. From a row count the blob table is roughly 4 x the number of rows of the numeric table.
The blob for the current month has over 13 x the records of the numeric and the month 01 (with I understand has the yearly figures in there) has more than 30 x more records in blob than numeric.
That’s interesting. My database grew from 25Mb to 250Mb in a short period of time after upgrading to 1.8.3, , but I thought it was just from adding 3 more websites, even though they were brand new with no traffic yet.
All who experience this issue, what value do you use in “Settings > General Settings > Allow Piwik archiving to trigger when reports are viewed from the browser” ?
Maybe you use a small value, in which case please try to increase it to 3600 or 8900 seconds for example. This will decrease the size. On the demo, the current month and January of the year is about twice the size of the other months, so not too much (and it gets lower once the month is finished).
increasing the timeout should help, also we can consider this as a bug and try to work around it. I’ll create a ticket once you confirm your settings.
My timeout was 1800 with an hourly execution of archive.php and the “Browser updates” were set to “NO”.
Increasing this timeout to 3600 helped and the size starts decreasing, but now some sites are very occasionally skipped in the hourly run if the previous run took a tiny bit longer than usual. I probably should decrease to 3000 to make sure sites with traffic are always updated and to cater for the time the cron takes to run.
I do not understand this, though: What else except archive.php would trigger archiving or at least entries in the archive tables if “Allow Piwik archiving to trigger when reports are viewed from the browser” is set to “NO”? Could it be that the use of the Apple App would trigger archiving ignoring this setting?
Same here, since the upgrade from 1.8.2 to 1.8.3. the DBs increased about 30% - 40%. This, while we expected a drastically reduce the size of DBs (as mentioned in the change log).
On this setup as well, “Allow Piwik archiving to trigger when reports are viewed from the browser” is false and a cronjob is processing the reports every 15 minutes. The reports for Today are processed at most every 3600 seconds.
Same problem here. I had switched to the archive.php because I’d started to get the PHP Memory error.
I had set the interval as high as 7200 and it made things worse, getting to the point where I could never load any of my reports at all, and could only manage to load the settings. Even going back to the manual archive trigger fails 100% of the time.
I even removed 4 of the smaller sites from my Piwik install, but I still can’t even use my Piwik install right now, and have just been watching the forums waiting to see if a fix appears… if nothing fixes it, I will have no choice but to just remove it completely and go back to Google Analytics less than satisfying blandness.
Index: core/ArchiveProcessing/Period.php
===================================================================
--- core/ArchiveProcessing/Period.php (revision 6877)
+++ core/ArchiveProcessing/Period.php (working copy)
@@ -387,7 +387,7 @@
// in case archiving is disabled or run once a day, we give it this extra time to run
// and re-process more recent records
$temporaryArchivingTimeout = self::getTodayArchiveTimeToLive();
- $purgeEveryNSeconds = max($temporaryArchivingTimeout, 24 * 3600);
+ $purgeEveryNSeconds = max($temporaryArchivingTimeout, 2 * 3600);
// we only delete archives if we are able to process them, otherwise, the browser might process reports
// when &segment= is specified (or custom date range) and would below, delete temporary archives that the