We are getting the below mentioned error on regular basis :
[error] [client 172.28.14.138] Error in Piwik (tracker): Error query: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction In query:
When this happens, it impacts the application which the Piwik is monitoring and business of our clients gets affected. Our application fails to loads as Piwik continuously tries to throw this error. We have to restart Piwik and our applications to get it back up.
We are using piwik 2.16.0 version. Our application send around 25,000 requests to Piwik on a daily basis and we have around 200gb of space alloted to Piwik Database.
Could you please suggest why this issue occurs and if this known issue with piwik could you let me know the version in which this would be fixed.
Can you show us the output of
SHOW FULL PROCESSLIST when this happens?
What's the value of your
There's a similar issue here https://github.com/piwik/piwik/issues/8010 but it was not tackled.
Currently I don't have the output of
SHOW FULL PROCESSLIST.
But when this issue had happened there were around 300-400 entries.
Thx for letting us know. The output of process list would be really helpful to investigate further.
This the process list output when the issue had occurred.
Thx for this! Can you check whether the table is using InnoDB engine?
Thank you, that looks all good.
I would recommend you have eg a read here http://stackoverflow.com/questions/13234290/lots-of-query-end-states-in-mysql-all-connections-used-in-a-matter-of-minutes This seems more like a server issue to me than an issue in Piwik. I would maybe also recommend to try asking the same in our forums https://forum.piwik.org/ as some more people will see it there and they maybe had similar problems.
If you get this error while importing server logs, reducing the
--max-payload parameter value may help. See forum post: https://forum.piwik.org/t/parsing-logs-very-slow-when-using-multithread-import-script/19182/3
Should not be a Piwik issue