@futureweb opened this Issue on November 15th 2018 Contributor

Hey there,
since a few weeks (not 100% sure if it started when we updated to 3.6.1 but it seems like?!) my "core:archive" Cronjob is giving me a nightly" [Zend_Db_Statement_Mysqli_Exception] Mysqli prepare error: MySQL server has gone away" ...

  • Cron is set to run hourly.
  • MariaDB isn't restarted or something (and no Errors)
  • no problems with Stats logging

From what I saw - it seems like sometimes the Cron is running into another long running "core:archive" Job which is causing troubles.

When this happens I see 2 long running SQL Threads doing:
OPTIMIZE TABLE piwik_archive_numeric_2012_01,piwik_archive_numeric_2012_02,piwik_archive_num...

Both waiting for "getting table lock" ... guess they stand out against each other ...
For now I try to extend Cron Times to 1.5 Hours and see if it helps ...

But woud it be possible to implement an "Archive Job already running" LOCK or something like that? Guess this should also help with such problems?

thx, bye from Austria
Andreas Schnederle-Wagner

@tsteur commented on November 15th 2018 Member

We have various locks to not archive the same site while another is in progress. This lock may not always work so we have another lock to not start archiving the same period/segment again while another archive is in progress (since Matomo 3.5 or so).

I don't know whether we have an Optimize Table lock but this could be actually quite helpful to prevent issues like this 👍

@tsteur commented on November 15th 2018 Member

A workaround be to disable optimize table in Matomo by setting [General] enable_sql_optimize_queries = 0 in the config/config.php and then running for example the command ./console database:optimize-archive-tables for example monthly. This is I reckon better in general anyway as it shouldn't be needed to optimize tables all that often.

@futureweb commented on November 16th 2018 Contributor

Hey @tsteur,
thank you for your fast answer!

Tonight it happened again:
grafik

Will disable optimize as advised by you and do it manually once a month - should be plenty enogh as you say! ;-)

Thank you, bye from sunny Austria
Andreas

ps) I'm a bit puzzled that both OPTIMIZE Queries are running the exact same amount of time - it seems 1 Job triggered them both the same time?!? (got definitely only 1 archive cron on server)

@tsteur commented on November 22nd 2018 Member
@futureweb commented on November 22nd 2018 Contributor

sorry for not getting back on this ... disabling OPTIMIZE immediately helped on my issue - everything working as expected again! ;-)
Problem was definitely 2 OPTIMIZE TABLE Threads which somehow locked each other out ...
(which also prevented MySQL Backups - as they were not able to lock the Tables which were already locked by OPTIMIZE)

thx, bye from Austria
Andreas

@tsteur commented on November 22nd 2018 Member

@futureweb I'll reopen the issue as we need to ideally add a lock there to not run this twice at the same time.

Powered by GitHub Issue Mirror