Archive performance degradation in 2.14.3


#1

Hi,

We have piwik 1.X, 2.12 and now 2.14.3,

In 2.14.3 archive crashes with MySQL server has gone away after finishing one site and switches to another. In this case one variable that needed to be changed was connect_timeout (28800).

In 2.12 we have connect_timeout set to 10 and archive is working fine.

The issue is not resolved by connect_timeout, it’s just got better (archiving more than one website during archive run), it drops after:
DEBUG Actions[2015-10-10 08:00:54] Archive site 351, week (From 2014-06-30 to 2014-07-06) skipped, archive is before the website was created.

Previously it stoped at skipped yearly archive before website was created.

We are changing all settings like max_allowed_packet (currently 512M) or timeouts now they are many times more than on the same server running 2.12.0 before upgrade.

It’s not the biggest of our servers, piwik_log_visit - 40GB

Any help?

New comment -
INFO CoreConsole[2015-10-10 08:09:45] Starting Piwik reports archiving…
INFO CoreConsole[2015-10-10 08:09:45] Will pre-process for website id = 353, period = day, date = last52
INFO CoreConsole[2015-10-10 08:09:45] - pre-processing all visits
INFO CoreConsole[2015-10-10 08:11:28] Archived website id = 353, period = day, 0 segments, 0 visits in last last52 days, 0 visits today, Time elapsed: 0.002s
INFO CoreConsole[2015-10-10 08:11:28] Will pre-process for website id = 353, period = week, date = last260
INFO CoreConsole[2015-10-10 08:11:28] - pre-processing all visits
INFO CoreConsole[2015-10-10 08:41:36] Archived website id = 353, period = week, 0 segments, 10218 visits in last last260 weeks, 65 visits this week, Time elapsed: 1807.911s
INFO CoreConsole[2015-10-10 08:41:36] Will pre-process for website id = 353, period = month, date = last52
INFO CoreConsole[2015-10-10 08:41:36] - pre-processing all visits
INFO CoreConsole[2015-10-10 08:42:37] Archived website id = 353, period = month, 0 segments, 10218 visits in last last52 months, 94 visits this month, Time elapsed: 60.506s
INFO CoreConsole[2015-10-10 08:42:37] Will pre-process for website id = 353, period = year, date = last7
INFO CoreConsole[2015-10-10 08:42:37] - pre-processing all visits
INFO CoreConsole[2015-10-10 08:42:41] Archived website id = 353, period = year, 0 segments, 10218 visits in last last7 years, 3413 visits this year, Time elapsed: 4.561s

[Zend_Db_Statement_Exception]
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

Second Comment:
Only mysql warning is: [Warning] Aborted connection 105 to db: ‘xx’ user: ‘yy’ host: ‘localhost’ (Got timeout reading communication packets)