Too many active concurrent transactions when archiving

We are hosting Matomo on our k8s instance on prem.
I have 3 pods running:

  • matomo itself
  • mariadb database
  • archive cron (every 30 minutes)

On the 17th of August, the archive process suddenly stopped working… I noticed only a couple of days later and found the following error for many days:

Error: Got invalid response from API request: ?module=API&method=CoreAdminHome.archiveReports&idSite=2&period=year&date=2022-01-01&format=json&segment=entryPageUrl%3D%40https%25253A%25252F%
25252Fxxx.com%25252Forganization%25252F&trigger=archivephp. Response was '{"result":"error","message":"Mysqli statement execute error : Too many active concurrent transactions #0 \/var\/www\/html\/libs\/Zend\/D
b\/Statement.php(300): Zend_Db_Statement_Mysqli->_execute(Array) #1 \/var\/www\/html\/libs\/Zend\/Db\/Adapter\/Abstract.php(479): Zend_Db_Statement->execute(Array) #2 \/var\/www\/html\/core\/DataAccess\/LogAggregator.p
hp(325): Zend_Db_Adapter_Abstract->query('INSERT INTO mat...', Array) #3 \/var\/www\/html\/core\/DataAccess\/LogAggregator.php(351): Piwik\\DataAccess\\LogAggregator->createTemporaryTable('logtmpsegment94...', 'SELECT
\/* trigg...', Array) #4 \/var\/www\/html\/core\/DataAccess\/LogAggregator.php(561): Piwik\\DataAccess\\LogAggregator->generateQuery('count(distinct ...', Array, 'log_visit.visit...', '', '') #5 \/var\/www\/html\/core\/Ar
chiveProcessor\/PluginsArchiver.php(285): Piwik\\DataAccess\\LogAggregator->queryVisitsByDimension() #6 \/var\/www\/html\/core\/ArchiveProcessor\/PluginsArchiver.php(102): Piwik\\ArchiveProcessor\\PluginsArchiver->aggr
egateDayVisitsMetrics() #7 \/var\/www\/html\/core\/ArchiveProcessor\/Loader.php(278): Piwik\\ArchiveProcessor\\PluginsArchiver->callAggregateCoreMetrics() #8 \/var\/www\/html\/core\/ArchiveProcessor\/Loader.php(176): Piwi
k\\ArchiveProcessor\\Loader->prepareAllPluginsArchive(1, 0) #9 \/var\/www\/html\/core\/ArchiveProcessor\/Loader.php(159): Piwik\\ArchiveProcessor\\Loader->insertArchiveData(1, 0) #10 \/var\/www\/html\/core\/ArchiveProc
essor\/Loader.php(99): Piwik\\ArchiveProcessor\\Loader->prepareArchiveImpl('VisitsSummary') #11 \/var\/www\/html\/core\/Context.php(75): Piwik\\ArchiveProcessor\\Loader->Piwik\\ArchiveProcessor\\{closure}() #12 \/var\/
www\/html\/core\/ArchiveProcessor\/Loader.php(103): Piwik\\Context::changeIdSite(2, Object(Closure)) #13 \/var\/www\/html\/plugins\/CoreAdminHome\/API.php(277): Piwik\\ArchiveProcessor\\Loader->prepareArchive('VisitsSumma
ry') #14 \/var\/www\/html\/core\/Archive.php(894): Piwik\\Plugins\\CoreAdminHome\\API->archiveReports(2, Object(Piwik\\Period\\Day), '2022-08-24', 'entryPageUrl%3D...', 'VisitsSummary', '') #15 \/var\/www\/html\/core\/Arc
hive.php(686): Piwik\\Archive->prepareArchive(Array, Object(Piwik\\Site), Object(Piwik\\Period\\Day)) #16 \/var\/www\/html\/core\/Archive.php(624): Piwik\\Archive->cacheArchiveIdsAfterLaunching(Array, Array) #17 \/var\
/www\/html\/core\/Archive.php(549): Piwik\\Archive->getArchiveIds(Array) #18 \/var\/www\/html\/core\/Archive.php(347): Piwik\\Archive->get(Array, 'numeric') #19 \/var\/www\/html\/core\/ArchiveProcessor.php(630): Piwik\
\Archive->getDataTableFromNumeric(Array) #20 \/var\/www\/html\/core\/ArchiveProcessor.php(265): Piwik\\ArchiveProcessor->getAggregatedNumericMetrics(Array, false) #21 \/var\/www\/html\/core\/ArchiveProcessor\/PluginsAr
chiver.php(306): Piwik\\ArchiveProcessor->aggregateNumericMetrics(Array) #22 \/var\/www\/html\/core\/ArchiveProcessor\/PluginsArchiver.php(104): Piwik\\ArchiveProcessor\\PluginsArchiver->aggregateMultipleVisitsMetrics(
) #23 \/var\/www\/html\/core\/ArchiveProcessor\/Loader.php(278): Piwik\\ArchiveProcessor\\PluginsArchiver->callAggregateCoreMetrics() #24 \/var\/www\/html\/core\/ArchiveProcessor\/Loader.php(176): Piwik\\ArchiveProcessor\
\Loader->prepareAllPluginsArchive(0, 0) #25 \/var\/www\/html\/core\/ArchiveProcessor\/Loader.php(159): Piwik\\ArchiveProcessor\\Loader->insertArchiveData(0, 0) #26 \/var\/www\/html\/core\/ArchiveProcessor\/Loader.php(9
9): Piwik\\ArchiveProcessor\\Loader->prepareArchiveImpl('VisitsSummary') #27 \/var\/www\/html\/core\/Context.php(75): Piwik\\ArchiveProcessor\\Loader->Piwik\\ArchiveProcessor\\{closure}() #28 \/var\/www\/html\/core\/Ar
chiveProcessor\/Loader.php(103): Piwik\\Context::changeIdSite(2, Object(Closure)) #29 \/var\/www\/html\/plugins\/CoreAdminHome\/API.php(277): Piwik\\ArchiveProcessor\\Loader->prepareArchive('VisitsSummary') #30 \/var\/www
\/html\/core\/Archive.php(894): Piwik\\Plugins\\CoreAdminHome\\API->archiveReports(2, Object(Piwik\\Period\\Week), '2022-08-22', 'entryPageUrl%3D...', 'VisitsSummary', '') #31 \/var\/www\/html\/core\/Archive.php(686): Piw
ik\\Archive->prepareArchive(Array, Object(Piwik\\Site), Object(Piwik\\Period\\Week)) #32 \/var\/www\/html\/core\/Archive.php(624): Piwik\\Archive->cacheArchiveIdsAfterLaunching(Array, Array) #33 \/var\/www\/html\/core\
/Archive.php(549): Piwik\\Archive->getArchiveIds(Array) #34 \/var\/www\/html\/core\/Archive.php(347): Piwik\\Archive->get(Array, 'numeric') #35 \/var\/www\/html\/core\/ArchiveProcessor.php(630): Piwik\\Archive->getD
ataTableFromNumeric(Array) #36 \/var\/www\/html\/core\/ArchiveProcessor.php(265): Piwik\\ArchiveProcessor->getAggregatedNumericMetrics(Array, false) #37 \/var\/www\/html\/core\/ArchiveProcessor\/PluginsArchiver.php(306):
Piwik\\ArchiveProcessor->aggregateNumericMetrics(Array) #38 \/var\/www\/html\/core\/ArchiveProcessor\/PluginsArchiver.php(104): Piwik\\ArchiveProcessor\\PluginsArchiver->aggregateMultipleVisitsMetrics() #39 \/var\/www\
/html\/core\/ArchiveProcessor\/Loader.php(278): Piwik\\ArchiveProcessor\\PluginsArchiver->callAggregateCoreMetrics() #40 \/var\/www\/html\/core\/ArchiveProcessor\/Loader.php(176): Piwik\\ArchiveProcessor\\Loader->prepa
reAllPluginsArchive(134, 0) #41 \/var\/www\/html\/core\/ArchiveProcessor\/Loader.php(159): Piwik\\ArchiveProcessor\\Loader->insertArchiveData(134, 0) #42 \/var\/www\/html\/core\/ArchiveProcessor\/Loader.php(99): Piwik\\Ar
chiveProcessor\\Loader->prepareArchiveImpl('VisitsSummary') #43 \/var\/www\/html\/core\/Context.php(75): Piwik\\Arc ... )\n#83 \/var\/www\/html\/console(32): Symfony\\Component\\Console\\Application->run()\n#84 {main}"}'

Currently it shows:

ERROR [2022-09-15 14:28:24] 125110 482 total errors during this script execution, please investigate and try and fix these errors.

Can you give me any hint what I can do to fix this?
Thanks

Maybe your site has too much traffic. (Then the tracking too)
You may:

  • Archive less often and at low traffic time (in our configuration it is twice a day)
  • Use the Queued Tracking plugin in order to send less requests to MySQL during tracking…

Our site has more than 200k per month… so yes, maybe…

Actually, I was thinking about increasing the archive frequency, but maybe I got that wrong?
Is it better to run it less frequent?

Using the Q’ed tracking makes only sense if I use redis instead of mysql right? Otherwise its the same DB that has the concurrent transaction issue…
Or since its delayed, its better anyways, since they are not happening straight away?

Will that also help fixing the past weeks since it failed?

Do you need the compiled data of “today”, considering this day is not finished?
If not, you may archive data the less you can (eg. once or twice a day, during low traffic)

No, the use of Queue tracking allows Matomo to send batches of updated instead of several database updates, this will lighten servers workload…

Don’t you have good (past) archived data anymore? Didn’t any archiving during low traffic work without error?

Do you need the compiled data of “today”

Probably not… So yes, once or twice a day should be enough.

Don’t you have good (past) archived data anymore?

  • Archiving was last executed without error 28 days 13 hours ago.

Since the archive is running every 30 minutes, on weekends, when there is low traffic, I still have the same error…

Interesting is actually this table:

TABELLE DATENMENGE INDEXGRÖSS ZEILENANZAHL
matomo_archive_blob_2022_10 16 K 16 K -
matomo_archive_blob_2022_09 16 K 16 K 3,09
matomo_archive_blob_2022_08 431.7 M 4.5 M 82,63
matomo_archive_blob_2022_07 54.5 M 1.5 M 19,82
matomo_archive_blob_2022_06 56.5 M 1.5 M 14,01
matomo_archive_blob_2022_05 57.5 M 1.5 M 18,99
matomo_archive_blob_2022_04 47.5 M 1.5 M 17,23
matomo_archive_blob_2022_03 59.5 M 1.5 M 12,84
matomo_archive_blob_2022_02 52.5 M 1.5 M 21,15
matomo_archive_blob_2022_01 62.5 M 1.5 M 22,04

As you can see, there was a sudden increase in amount from between 50-60mb to over 400mb in August. This somehow seems logical when looking at the last run 18. August 2022 22:30:14
So there might be some connection between both.

Ah… And I just noticed, since we have more than one website, the archive seems to not work only in one of them… the other shows no error…?

Also, I disabled Goals a couple of days ago to check if that helps… Today I re-enabled it… And the error now includes this: Too many active concurrent transactions - in plugin Goals

Very strange…
Maybe @innocraft has a clue on such behaviour?

So… I changed the frequency to once a day, and re-enabled all plugins that I previously disabled… the result looks like this:

https://zerobin.net/?793d713b2e6d7858#SiUD2+DXm+sNkoFFLhoCuIAB/WvipUR2wkDMExGzyl8=

So, still no fix, even though I also enabled Q’ed tracking and set the archive job to 00:00, where we might only have 3-4 active users not doing much.

Just to be sure: Do you confirm there is only one single server hosting your Matomo instance (Apache/PHP)?

Yes:

I have no really ideas…
Maybe have a look at the MySQL:

Hi @cronventis

There could be several reasons for this to be happening. Could you perhaps check the following for us:

  1. Are you executing the core:archive cron on all hosts or only one host?
    The core:archive task should only be executed on one of the servers connected to the Matomo database. Running it on several servers can cause issues

  2. Did you make any recent changes to the config.ini.php file? For example increasing datatable row limits?
    This is explained in this FAQ: https://matomo.org/faq/how-to/faq_54/

Increasing these limits too high can cause very slow archiving and can lead to quite large increases in the archive table sizes.

  1. Do you limit the number of concurrently running archivers? If not and if the archive task starts to get stuck on a specific report then you could have a situation where several archivers are all running at the same time. The worst I’ve seen here was an archiving issue caused by a third party plugin that resulted in over 70 concurrently running core:archive tasks (Because the crontab command used did not limit the number of concurrently running archivers)

To check this you can run for example a top -c command and check how many climulti or core:archive commands are currently running.

In order to limit the number of concurrently running archivers your server also needs to support the “Managing processes via CLI” as explained in this FAQ: https://matomo.org/faq/troubleshooting/how-to-make-the-diagnostic-managing-processes-via-cli-to-display-ok/

To limit the number of concurrent archivers add the following to your crontab:
--concurrent-archivers=2

  1. Finally, installing and setting up the Queued Tracking plugin will reduce the number of active connections to the database. This can be used with MySQL as @heurteph-ei mentioned.
1 Like

Hey Richard…

  1. tried both, only one and all of them. for now, If I disable goals and VisitFrequency, the archive is running for all sites, no issues at all
  2. no, it just stopped working “without any reason”… At least not from config side.
  3. no, I dont limit them, but there is only one container, scheduled at midnight once a day that does the archive, so there should not be any issue with that.
  4. done that, but as soon as I enable VisitFrequency, the archive does not work. But these are numbers we would like to have. Same with Goals, but we dont use goals… so at least that can stay deactivated.