CLI archiver always out of memory

Nolz · August 16, 2022, 12:04pm

Hi all,

I have the problem that the archiver (run via cron or manually on the CLI) always runs out of memory. I searched the forum and found many similar topics, but if they were solved the solutions didn’t worked for me. So here I am:

Hardware:

AMD Ryzen 5 3600 (6/12 Core)
64GB RAM
512 GB SSD
16GB Swap

Software:

Debian 11.4
Matomo 4.11.0
MariaDB 10.5.15-MariaDB-0+deb11u1
PHP 7.4.30 (CLI) 8.0.22 (Web)
Matomo DB 15.3GB

I set the memory limit via /cli/php.ini or via --php-cli-options to many different values (8, 16, 20, 24, 32, 40, 42, 48, 56GB). PHP 7.4 had no memory_limit at all and the archiving processes consumed all RAM and Swap until the OOM killer visited. I tried both PHP versions with different php.ini files in combination with those different memory_limits.

At the moment I track four websites, three very small ones without any problems (and no segments) and one website with a few thousand visitors per day (at the moment two segments). The memory issue seems to be on archiving some days. I run the archiving command right now manually with --force-periods=day --concurrent-requests-per-website=1 set (I tried with small ranges via --force-date-range too). In the error message Allowed memory size of 34359738368 bytes exhausted (tried to allocate 29105160768 bytes) the tried to allocate value is always different, doesn’t matter how high the memory_limit is.

What is using so much RAM, even if the entire database fits more than twice in the memory_limit?
Can I somehow estimate how much RAM is needed for the archiving to be successful?
Does someone has a similar setup with this problem (and maybe a solution)?

If more information is needed, I will provide that.

Hopefully someone can help me. Please

Nolz · August 17, 2022, 7:20pm

I try to archive just the more visited site in small steps with:
console core:archive --url=https://matomo.neuland.technology --skip-idsites=2,3,4 --force-periods=day --force-date-range=2022-07-08,2022-07-14 --concurrent-requests-per-website=1

The errors I get in detail while running this command manually on the CLI:

ERROR [2022-08-17 18:34:58] 21563  Got invalid response from API request: ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=day&date=2022-07-14&format=json&segment=provider%21%3DAMAZON-02&trigger=archivephp. Response was 'Matomo encountered an error: Allowed memory size of 17179869184 bytes exhausted (tried to allocate 4055908352 bytes) (which lead to: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.)Matomo encountered an error: Allowed memory size of 17179869184 bytes exhausted (tried to allocate 4055908352 bytes) (which lead to: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.)'
INFO [2022-08-17 18:34:58] 21563  'Matomo encountered an error: Allowed memory size of 17179869184 bytes exhausted (tried to allocate 4055908352 bytes) (which lead to: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.)Matomo encountered an error: Allowed memory size of 17179869184 bytes exhausted (tried to allocate 4055908352 bytes) (which lead to: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.)'
ERROR [2022-08-17 18:34:58] 21563  Error unserializing the following response from ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=day&date=2022-07-14&format=json&segment=provider%21%3DAMAZON-02&trigger=archivephp: 'Matomo encountered an error: Allowed memory size of 17179869184 bytes exhausted (tried to allocate 4055908352 bytes) (which lead to: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.)Matomo encountered an error: Allowed memory size of 17179869184 bytes exhausted (tried to allocate 4055908352 bytes) (which lead to: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.)'

ERROR [2022-08-17 18:43:12] 21563  Got invalid response from API request: ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=day&date=2022-07-14&format=json&segment=pageUrl%21%40%25252Fapi%25252F&trigger=archivephp. Response was '{"result":"error","message":"Allowed memory size of 17179869184 bytes exhausted (tried to allocate 28338792960 bytes) on \/var\/www\/web1\/htdocs\/xxx\/matomo\/libs\/Zend\/Db\/Statement\/Pdo.php(233) #0 \/var\/www\/web1\/htdocs\/xxx\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(164): Piwik\\Plugins\\Actions\\Archiver-&gt;callAggregateDayReport() ","backtrace":"Allowed memory size of 17179869184 bytes exhausted (tried to allocate 28338792960 bytes) on \/var\/www\/web1\/htdocs\/xxx\/matomo\/libs\/Zend\/Db\/Statement\/Pdo.php(233)\n#0 \/var\/www\/web1\/htdocs\/xxx\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(164): Piwik\\Plugins\\Actions\\Archiver->callAggregateDayReport()\n\n#0 [internal function]: Piwik\\Plugins\\CorePluginsAdmin\\Controller->safemode(Array)\n#1 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(631): call_user_func_array(Array, Array)\n#2 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(169): Piwik\\FrontController->doDispatch('CorePluginsAdmi...', 'safemode', Array)\n#3 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(100): Piwik\\FrontController->dispatch('CorePluginsAdmi...', 'safemode', Array)\n#4 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(275): Piwik\\FrontController::generateSafeModeOutputFromError(Array)\n#5 [internal function]: Piwik\\FrontController::triggerSafeModeWhenError()\n#6 {main}"}{"result":"error","message":"Allowed memory size of 17179869184 bytes exhausted (tried to allocate 28338792960 bytes) on \/var\/www\/web1\/htdocs\/xxx\/matomo\/libs\/Zend\/Db\/Statement\/Pdo.php(233) #0 \/var\/www\/web1\/htdocs\/xxx\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(164): Piwik\\Plugins\\Actions\\Archiver-&gt;callAggregateDayReport() ","backtrace":"Allowed memory size of 17179869184 bytes exhausted (tried to allocate 28338792960 bytes) on \/var\/www\/web1\/htdocs\/xxx\/matomo\/libs\/Zend\/Db\/Statement\/Pdo.php(233)\n#0 \/var\/www\/web1\/htdocs\/xxx\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(164): Piwik\\Plugins\\Actions\\Archiver->callAggregateDayReport()\n\n#0 [internal function]: Piwik\\Plugins\\CorePluginsAdmin\\Controller->safemode(Array)\n#1 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(631): call_user_func_array(Array, Array)\n#2 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(169): Piwik\\FrontController->doDispatch('CorePluginsAdmi...', 'safemode', Array)\n#3 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(100): Piwik\\FrontController->dispatch('CorePluginsAdmi...', 'safemode', Array)\n#4 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(275): Piwik\\FrontController::generateSafeModeOutputFromError(Array)\n#5 [internal function]: Piwik\\FrontController::triggerSafeModeWhenError()\n#6 {main}"}'
INFO [2022-08-17 18:43:12] 21563  '{"result":"error","message":"Allowed memory size of 17179869184 bytes exhausted (tried to allocate 28338792960 bytes) on \\/var\\/www\\/web1\\/htdocs\\/xxx\\/matomo\\/libs\\/Zend\\/Db\\/Statement\\/Pdo.php(233) #0 \\/var\\/www\\/web1\\/htdocs\\/xxx\\/matomo\\/core\\/ArchiveProcessor\\/PluginsArchiver.php(164): Piwik\\\\Plugins\\\\Actions\\\\Archiver-&gt;callAggregateDayReport() ","backtrace":"Allowed memory size of 17179869184 bytes exhausted (tried to allocate 28338792960 bytes) on \\/var\\/www\\/web1\\/htdocs\\/xxx\\/matomo\\/libs\\/Zend\\/Db\\/Statement\\/Pdo.php(233)\\n#0 \\/var\\/www\\/web1\\/htdocs\\/xxx\\/matomo\\/core\\/ArchiveProcessor\\/PluginsArchiver.php(164): Piwik\\\\Plugins\\\\Actions\\\\Archiver->callAggregateDayReport()\\n\\n#0 [internal function]: Piwik\\\\Plugins\\\\CorePluginsAdmin\\\\Controller->safemode(Array)\\n#1 \\/var\\/www\\/web1\\/htdocs\\/xxx\\/dbuser\\/core\\/FrontController.php(631): call_user_func_array(Array, Array)\\n#2 \\/var\\/www\\/web1\\/htdocs\\/xxx\\/dbuser\\/core\\/FrontController.php(169): Piwik\\\\FrontController->doDispatch(\'CorePluginsAdmi...\', \'safemode\', Array)\\n#3 \\/var\\/www\\/web1\\/htdocs\\/xxx\\/dbuser\\/core\\/FrontController.php(100): Piwik\\\\FrontController->dispatch(\'CorePluginsAdmi...\', \'safemode\', Array)\\n#4 \\/var\\/www\\/web1\\/htdocs\\/xxx\\/dbuser\\/core\\/FrontController.php(275): Piwik\\\\FrontController::generateSafeModeOutputFromError(Array)\\n#5 [internal function]: Piwik\\\\FrontController::triggerSafeModeWhenError()\\n#6 {main}"}{"result":"error","message":"Allowed memory size of 17179869184 bytes exhausted (tried to allocate 28338792960 bytes) on \\/var\\/www\\/web1\\/htdocs\\/xxx\\/matomo\\/libs\\/Zend\\/Db\\/Statement\\/Pdo.php(233) #0 \\/var\\/www\\/web1\\/htdocs\\/xxx\\/matomo\\/core\\/ArchiveProcessor\\/PluginsArchiver.php(164): Piwik\\\\Plugins\\\\Actions\\\\Archiver-&gt;callAggregateDayReport() ","backtrace":"Allowed memory size of 17179869184 bytes exhausted (tried to allocate 28338792960 bytes) on \\/var\\/www\\/web1\\/htdocs\\/xxx\\/matomo\\/libs\\/Zend\\/Db\\/Statement\\/Pdo.php(233)\\n#0 \\/var\\/www\\/web1\\/htdocs\\/xxx\\/matomo\\/core\\/ArchiveProcessor\\/PluginsArchiver.php(164): Piwik\\\\Plugins\\\\Actions\\\\Archiver->callAggregateDayReport()\\n\\n#0 [internal function]: Piwik\\\\Plugins\\\\CorePluginsAdmin\\\\Controller->safemode(Array)\\n#1 \\/var\\/www\\/web1\\/htdocs\\/xxx\\/dbuser\\/core\\/FrontController.php(631): call_user_func_array(Array, Array)\\n#2 \\/var\\/www\\/web1\\/htdocs\\/xxx\\/dbuser\\/core\\/FrontController.php(169): Piwik\\\\FrontController->doDispatch(\'CorePluginsAdmi...\', \'safemode\', Array)\\n#3 \\/var\\/www\\/web1\\/htdocs\\/xxx\\/dbuser\\/core\\/FrontController.php(100): Piwik\\\\FrontController->dispatch(\'CorePluginsAdmi...\', \'safemode\', Array)\\n#4 \\/var\\/www\\/web1\\/htdocs\\/xxx\\/dbuser\\/core\\/FrontController.php(275): Piwik\\\\FrontController::generateSafeModeOutputFromError(Array)\\n#5 [internal function]: Piwik\\\\FrontController::triggerSafeModeWhenError()\\n#6 {main}"}'
ERROR [2022-08-17 18:43:12] 21563  Error unserializing the following response from ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=day&date=2022-07-14&format=json&segment=pageUrl%21%40%25252Fapi%25252F&trigger=archivephp: '{"result":"error","message":"Allowed memory size of 17179869184 bytes exhausted (tried to allocate 28338792960 bytes) on \/var\/www\/web1\/htdocs\/xxx\/matomo\/libs\/Zend\/Db\/Statement\/Pdo.php(233) #0 \/var\/www\/web1\/htdocs\/xxx\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(164): Piwik\\Plugins\\Actions\\Archiver-&gt;callAggregateDayReport() ","backtrace":"Allowed memory size of 17179869184 bytes exhausted (tried to allocate 28338792960 bytes) on \/var\/www\/web1\/htdocs\/xxx\/matomo\/libs\/Zend\/Db\/Statement\/Pdo.php(233)\n#0 \/var\/www\/web1\/htdocs\/xxx\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(164): Piwik\\Plugins\\Actions\\Archiver->callAggregateDayReport()\n\n#0 [internal function]: Piwik\\Plugins\\CorePluginsAdmin\\Controller->safemode(Array)\n#1 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(631): call_user_func_array(Array, Array)\n#2 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(169): Piwik\\FrontController->doDispatch('CorePluginsAdmi...', 'safemode', Array)\n#3 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(100): Piwik\\FrontController->dispatch('CorePluginsAdmi...', 'safemode', Array)\n#4 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(275): Piwik\\FrontController::generateSafeModeOutputFromError(Array)\n#5 [internal function]: Piwik\\FrontController::triggerSafeModeWhenError()\n#6 {main}"}{"result":"error","message":"Allowed memory size of 17179869184 bytes exhausted (tried to allocate 28338792960 bytes) on \/var\/www\/web1\/htdocs\/xxx\/matomo\/libs\/Zend\/Db\/Statement\/Pdo.php(233) #0 \/var\/www\/web1\/htdocs\/xxx\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(164): Piwik\\Plugins\\Actions\\Archiver-&gt;callAggregateDayReport() ","backtrace":"Allowed memory size of 17179869184 bytes exhausted (tried to allocate 28338792960 bytes) on \/var\/www\/web1\/htdocs\/xxx\/matomo\/libs\/Zend\/Db\/Statement\/Pdo.php(233)\n#0 \/var\/www\/web1\/htdocs\/xxx\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(164): Piwik\\Plugins\\Actions\\Archiver->callAggregateDayReport()\n\n#0 [internal function]: Piwik\\Plugins\\CorePluginsAdmin\\Controller->safemode(Array)\n#1 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(631): call_user_func_array(Array, Array)\n#2 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(169): Piwik\\FrontController->doDispatch('CorePluginsAdmi...', 'safemode', Array)\n#3 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(100): Piwik\\FrontController->dispatch('CorePluginsAdmi...', 'safemode', Array)\n#4 \/var\/www\/web1\/htdocs\/xxx\/dbuser\/core\/FrontController.php(275): Piwik\\FrontController::generateSafeModeOutputFromError(Array)\n#5 [internal function]: Piwik\\FrontController::triggerSafeModeWhenError()\n#6 {main}"}'

Nolz · August 22, 2022, 6:08am

I think the problem is solved now.
It seems not to be a good idea to import the same log file more than once a day (a little warning somewhere would be nice ). It kinda seems to send the archiver into an archiving loop resulting in a memory exhaustion. I purged the whole database and started fresh, now everything seems to be fine.

V.K · August 23, 2022, 8:06pm

Hi @Nolz sorry for the delay, thanks for the post and update. It is great to hear you managed to resolve this issue.

heurteph-ei · August 24, 2022, 7:47am

@V.K , is the issue reported by Nolz a known issue?
Maybe it could be a good idea to document this somewhere in the Matomo Help…

JRPonte · August 24, 2022, 8:40am

Can you be more precise? You’ve changed the archive period to 1 day?

After updating to matomo 4.11 this “Allowed memory exhausted” errors started to appear.
Matomo currently logs 35 sites and an average os 750k pageviews per day.
PHP memory limit is now 8GB, start servers are 50, max children 100, and max requests 5000.
I’ve disabled archiving through the browser, and I’ve one cron job that runs every 5 minutes.

Nolz · August 24, 2022, 6:13pm

The log importer script shouldn’t run more than once against the same log file. The archiver I run hourly.

JRPonte · August 24, 2022, 10:57pm

I’m sorry, I’m not understanding how I can configure importer script.
I’m using matomo’s tag manager has the tracker script. For what I understood, matomo is receiving all tracking data and every hour the archive cron job is aggregating the last period.

Regularly the Cron job while archiving one of the sites throws an memory exhausted errors.
Should I invalidate the past data and try to run one full archiving?