I maintain a rather large piwik installation. Each month’s blob archive usually has an approximate size of 2 GB and typically contains around 3 million rows.
Since the update from 2.9.0 to 2.10.0 (which took approximately 9 hours, btw) I have massive problems with timed out transactions and performance overall. Also, the blob archive for 2015_01 now suddenly has a size of 13 GB and contains approximately 18 million rows. The archiver runs as cron job and is not triggered automatically.
Since the update, the Mysql process list is clogged with updates that look like this:
UPDATE piwik_archive_blob_2015_01 SET value = 4 WHERE ( (date1 <= '2015-02-10' AND '2015-02-10' <= date2 AND name LIKE 'done%') ) AND idsite IN ('....')
I assume that this query comes from the method “updateArchiveAsInvalidated” in core/DataAccess/Model.php. Given that there is neither an index on the date columns nor on idsite, this query is highly inefficient in a table as large as this. The query seems to be triggered by a call to the API method “doTrackPageView”.
Ok, so I was able to resolve the issue (hopefully).
Apparently the archiver was executed with the old syntax (php misc/cron/archive.php). I now switched to the new syntax (php console cron:archive) and executed the job. After that, the updates are gone from the mysql process list.
The only difference to the problem I described yesterday is that date1 and date2 in the update query is today’s date.
UPDATE piwik_archive_blob_2015_01 SET value = 4 WHERE ( (date1 <= '2015-02-11' AND '2015-02-11' <= date2 AND name LIKE 'done%') ) AND idsite IN ('....')
The update queries are blocking the table and consequently opening statistics for a specific website eventually fail with the error message “SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction”.
The archiver job started yesterday at 10 pm and has been running since more than 13 hours now.
We didnt hear of this problem before. Can you please create a bug report at: Issues · matomo-org/piwik · GitHub
ideally with the Mysql SHOW FULL PROCESSLIST showing the long running query, or the Mysql slow query log?