@arvindsarangi14 opened this Issue on May 5th 2016

Hi team,

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.

@tsteur commented on May 5th 2016 Owner

Can you show us the output of SHOW FULL PROCESSLIST when this happens?

What's the value of your innodb_lock_wait_timeout setting?

There's a similar issue here https://github.com/piwik/piwik/issues/8010 but it was not tackled.

@arvindsarangi14 commented on May 6th 2016

innodb_lock_wait_timeout =50
Currently I don't have the output of SHOW FULL PROCESSLIST.
But when this issue had happened there were around 300-400 entries.

@tsteur commented on May 8th 2016 Owner

It will be interesting to see the output of processlist. Feel free to email us to hello at piwik.org in case it is a problem to paste it here. Just leave a comment here so we can check the spam folder if needed. Are you using regular JavaScript tracking method? Which MySQL version are you using?

@arvindsarangi14 commented on May 9th 2016

My MYSQL version is 5.5.19 and yes i am using the regular JavaScript tracking method.

@tsteur commented on May 9th 2016 Owner

Thx for letting us know. The output of process list would be really helpful to investigate further.

@arvindsarangi14 commented on May 10th 2016

ShowProcess.txt
This the process list output when the issue had occurred.

@tsteur commented on May 10th 2016 Owner

Thx for this! Can you check whether the table is using InnoDB engine?

@arvindsarangi14 commented on May 11th 2016

SHOWTableStatus.xlsx
Show Table Status output excel.

@tsteur commented on May 12th 2016 Owner

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.

@mattab commented on May 23rd 2016 Owner

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

@mattab commented on December 11th 2017 Owner

Should not be a Piwik issue

This Issue was closed on December 11th 2017
Powered by GitHub Issue Mirror