Debugging self-hosted Matomo slowness - stuck in MySQL query


I’m using Matomo (v4.4.1) with the A/B Test plugin (v4.1.5). When viewing the results of one of the A/B Tests, some of the conversions used for the Success Metrics fail to load. They appear to be loading for a long time (depending on how long I set timeout settings in nginx) but always eventually time out, with an error message:

“Oops… there was a problem during the request. Maybe the server had a temporary issue, or maybe you requested a report with too much data. Please try again. If this error occurs repeatedly please contact your Matomo administrator for assistance.”

I’ve run into a similar issue before on a few occasions and each time it was resolved by doing one of:

  • Increasing PHP-FPM’s memory limit
  • Increasing PHP-FPM’s max/min children processes
  • Increasing PHP-FPM’s script timeout limit
  • Increasing nginx’s fast_cgi timeout value
  • Increasing capacity of the Matomo server or MySQL server (we have them hosted in Cloud, so can scale up)

In this case, I’ve already set these values to be very high (1024M limit for PHP-FPM, no time limit for PHP-FPM script execution, 7200 sec timeout for nginx’s “fastcgi_read_timeout” setting); however, some of the Goals in question always fail to load. It is always the same Goals in the A/B Test that fail to load. Currently, it will try to load for 1+ hour before it eventually times out.

I turned on the slow logs for PHP and the request is logged; each time the stack is:

[30-Jul-2021 02:49:17]  [pool www] pid 18067
script_filename = /var/www/matomo/index.php
[0x00007f919b820680] execute() /var/www/matomo/libs/Zend/Db/Statement/Pdo.php:233
[0x00007f919b8205c0] _execute() /var/www/matomo/libs/Zend/Db/Statement.php:300
[0x00007f919b820510] execute() /var/www/matomo/core/Db/Adapter/Pdo/Mysql.php:305
[0x00007f919b820470] query() /var/www/matomo/libs/Zend/Db/Adapter/Abstract.php:736
[0x00007f919b8203c0] fetchAll() /var/www/matomo/core/DataAccess/ArchiveSelector.php:213
[0x00007f919b820140] getArchiveIds() /var/www/matomo/core/Archive.php:644
[0x00007f919b820010] cacheArchiveIdsWithoutLaunching() /var/www/matomo/core/Archive.php:578
[0x00007f919b81ff00] getArchiveIds() /var/www/matomo/core/Archive.php:502
[0x00007f919b81fdb0] get() /var/www/matomo/core/Archive.php:385
[0x00007f919b81fd00] getDataTableExpanded() /var/www/matomo/core/ArchiveProcessor.php:346
[0x00007f919b81fc00] aggregateDataTableRecord() /var/www/matomo/core/ArchiveProcessor.php:213
[0x00007f919b81fac0] aggregateDataTableRecords() /var/www/matomo/plugins/Goals/Archiver.php:470
[0x00007f919b81f970] aggregateMultipleReports() /var/www/matomo/core/Plugin/Archiver.php:103
[0x00007f919b81f900] callAggregateMultipleReports() /var/www/matomo/core/ArchiveProcessor/PluginsArchiver.php:168
[0x00007f919b81f770] callAggregateAllPlugins() /var/www/matomo/core/ArchiveProcessor/Loader.php:228
[0x00007f919b81f6a0] prepareAllPluginsArchive() /var/www/matomo/core/ArchiveProcessor/Loader.php:163
[0x00007f919b81f570] prepareArchiveImpl() /var/www/matomo/core/ArchiveProcessor/Loader.php:101
[0x00007f919b81f4e0] Piwik\ArchiveProcessor\{closure}() /var/www/matomo/core/Context.php:75
[0x00007f919b81f3e0] changeIdSite() /var/www/matomo/core/ArchiveProcessor/Loader.php:105
[0x00007f919b81f370] prepareArchive() /var/www/matomo/plugins/CoreAdminHome/API.php:278

When I use “SHOW FULL PROCESSLIST” in MySQL, I don’t see any particularly long-running query (everything listed has run for < 15 sec, even though in the browser, the A/B Test page loads for 10+ minutes.

At this point I’m a bit stuck – it seems that the A/B Test can’t load properly because it gets stuck executing some MySQL. But I’m not sure what the problematic query is or why it is slow/how to fix it. Any suggestions for how to further debug this problem?

If anyone else is interested, the workaround was to disable enforcement of disable browser archiving. I think something in Goals or AbTesting does not interact with it correctly. Opened a bug report at