I have a piwik 2.17.1 installation with 3 websites (1 for js tracking, 1 for apache logs, 1 for varnish logs) of average traffic. It has been running for the last 2 years and the mysqldump is 20GB. On that piwik every midnight a script is run to:
- import apache logs for the day using
misc/log-analytics/import_logs.py
- import varnish logs for the day using
misc/log-analytics/import_logs.py
- archive all sites using
console core:archive --url=http://.../index.php
Two weeks ago archiving suddenly started taking a lot of hours and then it failed. In order to debug the problem I copied the piwik files and the database to another machine and started experimenting.
It turns out that the process is always stuck at the period=year
of any of the 3 websites. It takes 60 minutes and then fails with:
Response was 'PHP Fatal error: Allowed memory size of 7516192768 bytes exhausted (tried to allocate 469110 bytes)`
Yes, that is 7GBs allocated via memory_limit
of /etc/php/5.6/cli/php.ini
. Note that the production machine just has 2GB allocated and it has been running well so far (until 2 weeks ago).
Do you have any idea why it is stuck? Is there a possibility that I can run some custom sql queries to do some “cleaning” in order for the archiving to continue working? I think that something is wrong because it used to work with 2GBs memory_limit
2 weeks ago but now it can’t get past even with 7GBs.
When it’s stuck the php process is at 100%. Mysql isn’t doing anything important (checked show processlist
, the general_log
and the slow_query_log
). The last query executed before the intense PHP work looks like the following and regards 2016-12:
SELECT value, name, idsite, date1, date2, ts_archived
FROM piwik_archive_blob_2016_12
WHERE idarchive IN (8869)
AND (name = 'Actions_actions_url' OR
(name LIKE 'Actions_actions_url%' AND
(SUBSTRING(name, 20, 7) = '_chunk_' OR
(SUBSTRING(name, 21, 1) >= '0' AND SUBSTRING(name, 21, 1) <= '9')
)
)
);
If I kill the inner php process doing the period=year
work, the exception is:
Exception trace:
() at /web-pub/piwik/core/CronArchive.php:496
Piwik\CronArchive->logFatalError() at /web-pub/piwik/core/CronArchive.php:489
Piwik\CronArchive->end() at /web-pub/piwik/core/CronArchive.php:291
Piwik\CronArchive->Piwik\{closure}() at /web-pub/piwik/core/Access.php:469
Piwik\Access::doAsSuperUser() at /web-pub/piwik/core/CronArchive.php:292
Piwik\CronArchive->main() at /web-pub/piwik/plugins/CoreConsole/Commands/CoreArchiver.php:27
Piwik\Plugins\CoreConsole\Commands\CoreArchiver->execute() at /web-pub/piwik/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:257
Symfony\Component\Console\Command\Command->run() at /web-pub/piwik/vendor/symfony/console/Symfony/Component/Console/Application.php:874
Symfony\Component\Console\Application->doRunCommand() at /web-pub/piwik/vendor/symfony/console/Symfony/Component/Console/Application.php:195
Symfony\Component\Console\Application->doRun() at n/a:n/a
call_user_func() at /web-pub/piwik/core/Console.php:79
Piwik\Console->Piwik\{closure}() at /web-pub/piwik/core/Access.php:469
Piwik\Access::doAsSuperUser() at /web-pub/piwik/core/Console.php:80
Piwik\Console->doRun() at /web-pub/piwik/vendor/symfony/console/Symfony/Component/Console/Application.php:126
Symfony\Component\Console\Application->run() at /web-pub/piwik/console:27