We use PIWIK since 2010. We have now about 10 websites with about 20 000-30 000 connections per days (not all of them are track by piwik maybe only 2 000 or less).
We have some performance issue, so i would like to use cron for auto archiving instead of ‘Browser trigger archiving’.
In the documentation it’s says : “Generally, it completes in less than one minute”
So i tried and here is the log :
INFO CoreConsole[2014-09-18 14:07:24] [0a486] - Reports for today will be processed at most every 10 seconds. You can change this value in Piwik UI > Settings > General Settings.
INFO CoreConsole[2014-09-18 14:07:24] [0a486] - Reports for the current week/month/year will be refreshed at most every 3600 seconds.
INFO CoreConsole[2014-09-18 14:07:24] [0a486] - Will process 8 websites with new visits since 7 jours 0 heures , IDs: 1, 2, 3, 6, 8, 9, 10, 14
INFO CoreConsole[2014-09-18 14:07:24] [0a486] Will ignore websites and help finish a previous started queue instead. IDs: 3, 12
INFO CoreConsole[2014-09-18 14:07:24] [0a486] ---------------------------
INFO CoreConsole[2014-09-18 14:07:24] [0a486] START
INFO CoreConsole[2014-09-18 14:07:24] [0a486] Starting Piwik reports archiving...
INFO CoreConsole[2014-09-18 14:07:39] [0a486] Archived website id = 3, period = day, 29 visits in last last52 days, 0 visits today, Time elapsed: 14.967s
INFO CoreConsole[2014-09-18 14:11:28] [0a486] Archived website id = 3, period = week, 297 visits in last last260 weeks, 1 visits this week, Time elapsed: 229.423s
ERROR CoreConsole[2014-09-18 14:11:28] [0a486] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
How can i avoid MYSQL timemout ? I thinks my mysql timeout is enought.(2 min)
To the OP: How exactly did you solve your problem, please?
To Matt: The FAQ lists two possibilities: adjusting
wait_timeout
or adjusting
max_allowed_packet
A suggested value for max_allowed_packet is given, but not for wait_timeout. I believe my problem is the wait_timeout parameter. Why? Simply because the server issues the following notice:
[Note] Aborted connection 2633581 to db: 'piwik' user: 'XXXX' host: 'HHHHH.ZZ.YY' (Got timeout reading communication packets)
and this corresponds to the errors seen on the client, very similar to the ones reported by the OP.
However, any number higher than say, 1 minute, doesn’t really make sense – not from a systems standpoint. The wait_timeout parameter in mysql refers to the amount of time a connection may be idle before the server closes it. A reasonably low wait_timeout parameter keeps the server from having too many idle, open connections. When the server closes a TCP connection, the client should recognize that it is closed (unless there is some kind of underlying networking problem). In the case of the PHP-mysql driver, it should detect this condition and note that this connection has been closed. In the worst case, the mysql driver will abort the connection and re-open a new one and try again. Or so I think it should it behave.
Let me be clear: This is a bug. Actually, it’s probably a collision of two bugs. There probably is a bug in whatever PHP driver we are both using (I am working on getting this information). The second bug is with piwik: why does piwik treat this as a failure condition? It should always retry at least once to re-establish a connection before giving up. Otherwise, things like automatic failover become moot.
So let us try to fix the underlying problem instead of band-aiding it with an unreasonable wait_timeout parameter.
I increase the wait_timeout (~5min) and max_allowed_packet 256M.
Like you mention, my wait_timeout is pretty high and I don’t like it.
It was working well since the last update (2.13.0), the error come back.
I am not sure but I think that after several iteration of piwik cron, now it takes less time (~3 min Because I have no more mysql timeout).
I didn’t search really a lot about my problem and the fact that the wait_timeout is high. Maybe increasing the number of archiving task will decrease the time spend on sql requests. I need to do a better setup but until there is no critical problem due to this specific configuration, I will stay like this.
Follow up. Marginally changing the above parameters didn’t help. We drastically changed them, and have experienced no problems since. That still seems like a bug (or two) but maybe the bug is within MySQL.
wait_Timeout ist 28800 (8 Std.).
max_allowed_packet bis 256 MB an.
net_read_timeout and net_write_timeout to 300s each.