Core:archive has "Serialization failure: 1213 Deadlock" when running with more than --concurrent-archivers 1

Support & Bugs
1

Hi,
I’m not sure if this is a bug or a performance issue so here are our specs:

Matomo installation

  • over a 1000 sites (most of them tiny)
  • DB size ~42GB.
  • Matomo in Docker containers (one for PHP-FPM one for the webserver)
  • MariaDB 10.6 on same machine connected via local socket

Our Machine:

  • 8 CPU cores (VMWare Guest)
  • 20GB RAM. (16GB RAM was a bit tight with Matomo 4.x, so we gave 5.1 20GB)

Since migrating to the new machine and upgrading to Matomo 5.0 (later 5.1) we get the error message below several times a day. Our cron job runs every 15 minutes, but is usually restricted to --concurrent-archivers 8. In the past we had no problems but now we get these deadlocks even with a maximum of 2 concurrent archivers.

ERROR     [2024-07-01 02:38:55] 1418677  Uncaught exception: /var/www/html/libs/Zend/Db/Statement/Pdo.php(236): SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction [Query: , CLI mode: 1]
 
In Pdo.php line 236:
                                                                               
  SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to   
  get lock; try restarting transaction                                         
                                                                               
 
In Pdo.php line 233:
                                                                               
  SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to   
  get lock; try restarting transaction                                         
                                                                               
 
core:archive [--url URL] [--skip-idsites [SKIP-IDSITES]] [--skip-all-segments] [--force-idsites [FORCE-IDSITES]] [--skip-segments-today] [--force-periods [FORCE-PERIODS]] [--force-date-last-n [FORCE-DATE-LAST-N]] [--force-date-range [FORCE-DATE-RANGE]] [--force-idsegments FORCE-IDSEGMENTS] [--concurrent-requests-per-website [CONCURRENT-REQUESTS-PER-WEBSITE]] [--concurrent-archivers [CONCURRENT-ARCHIVERS]] [--max-websites-to-process MAX-WEBSITES-TO-PROCESS] [--max-archives-to-process MAX-ARCHIVES-TO-PROCESS] [--disable-scheduled-tasks] [--accept-invalid-ssl-certificate] [--php-cli-options [PHP-CLI-OPTIONS]] [--force-all-websites] [--force-report [FORCE-REPORT]]

We migrated the VM to faster hardware. We migrated the database to SSD-based storrage. We upped the Caching of MariaDB… but now we are running out of ideas, what else we could try.
One single archiver seems to be not fast enough to keep up with the incoming data, so we relied on the cron starting additional jobs utilizing more CPU cores when they were needed.

Any Ideas what goes wrong?

Best
TOM

1 Like
2

Hi @Thomas_Oliver_Moll
I think this is related to:

Can you confirm?
If so, you may add some comments in the GitHub thread in order to help the bug fix.

3

Yes thank you for making the connection, I did not find anything while googeling it. I’ll continue over at git hub

1 Like
4

Since yesterday we’ve got the same problem. We’ve got errors like:

Error: Got invalid response from API request: ?module=API&method=CoreAdminHome.archiveReports&idSite=147&period=month&date=2024-12-01&format=json&segment=pageUrl%3D%5Ehttps%25253A%25252F%25252Fwww.xyz.de%25252F2-handlungsfelder%25252F02-nachhaltigkeitsberichterstattung.html&trigger=archivephp. Response was '{"result":"error","message":"SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; when running the piwik-archive cron-job. This doesn’t always affect the same segments.

Our setting is:
Ubuntu 22.04.5 LTS
Matomo: 5.2.0
MariaDB: 10.6.18-MariaDB-0ubuntu0.22.04.1
PHP versions tested: from 8.1 to 8.3.14
213 web sites
227 segments
Piwik-DB: 109 GB