I'm using Piwik to monitor my website traffic and sending events to track users' behavior. In parallel I constantly withdraw this event data from Piwik using Piwik API.
Every day at ~12:00AM Piwik dashboard and api becomes unresponsive for 3 hours. There is an endless DB sql queries running on the DB during this time and DB CPU usage surges.
How can I prevent this downtime? I've already implemented all suggested implementations for heavy load sites on Piwik Documentations.
SQL slow query log show during this time (and other shorter calls):
Query_time: 10415.327710 Lock_time: 20.380198 Rows_sent: 0 Rows_examined: 46913903
UPDATE piwik_archive_blob_2015_06 SET value = 4 WHERE ( (date1 <= '2015-06-29' AND '2015-06-29' <= date2 AND name LIKE 'done%') ) AND idsite IN ('5');
Update your Piwik instance to the latest version. This query is actually executed because of a bug in Piwik core.
Please see: https://github.com/piwik/piwik/issues/7195