Hello,
I am having some difficulty with some errors happening on our Piwik Installation.
Before I continue, here are some basic facts:
- We get roughly 80000 visits per month mostly midweek during normal business hours. All together it’s about 1 000 000 visits per year. This is all for just one website.
- We have a dev server dedicated just to running Piwik tests which has 4 GB Ram. We have another Piwik server used for production with 16 GB RAM that is very fast. Other servers perform all the other work.
- We have PHP 5.5.9 running in Nginx 1.4.6 using FPM and Mysql 5.5.43
- We switched to Piwik a year ago from Google Analytics, and had imported GA data without any problems.
- We use cron archiving as per the Piwik documentation (How to Set up Auto-Archiving of Your Reports - Analytics Platform - Matomo). It now takes 2+ hours to execute, so we have to run the cron every 2 - 4 hours instead of hourly.
When we first start our PHP & Mysql services, it loads the admin dashboard correctly and quickly and everything seems to run well. However, after about 15 minutes of use of the dashboard, 4 of the widgets (Pages, Site Search Keywords, Downloads, Outlinks) stop working with a “Oops… there was a problem during the request…” message regardless of what date range is used. Chrome Console indicates that each of the 4 ajax requests times out after exactly 1 minute with a 504 error. When we check the running processes on the server, there are 4 related PHP-FPM processes that together hog 99.99% cpu and 20% ram.
We have tested various configurations such as changing the RAM limit, and various timeout settings, but regardless of what happens the 4 processes run at 99% cpu and don’t successfully complete.
When we run the ajax requests using PHP-CLI from the command line (with simulated headers/cookies), they each successfully complete in less than a second with the data that we requested. Our PHP-CLI and PHP-FPM settings are almost identical except for max RAM (CLI is set to unlimited), but neither PHP-CLI or PHP-FPM use up more than 1GB anyways. When we restart the PHP and Mysql services, everything works again for a short period of time before the 4 widgets start 504’ing again.
Our PHP slow log shows the following trace for one of those 504’ing processes:
script_filename = /piwik/index.php
fetch() /piwik/libs/Zend/Db/Statement/Mysqli.php:276
fetch() /piwik/libs/Zend/Db/Statement.php:341
fetchAll() /piwik/libs/Zend/Db/Adapter/Abstract.php:737
fetchAll() /piwik/core/Db.php:198
fetchAll() /piwik/core/DataAccess/ArchiveSelector.php:294
getArchiveData() /piwik/core/Archive.php:604
get() /piwik/core/Archive.php:397
getDataTableExpanded() /piwik/core/ArchiveProcessor.php:348
aggregateDataTableRecord() /piwik/core/ArchiveProcessor.php:217
aggregateDataTableRecords() /piwik/plugins/Actions/Archiver.php:502
aggregateMultipleReports() /piwik/core/ArchiveProcessor/PluginsArchiver.php:112
callAggregateAllPlugins() /piwik/core/ArchiveProcessor/Loader.php:122
prepareAllPluginsArchive() /piwik/core/ArchiveProcessor/Loader.php:72
prepareArchive() /piwik/core/Archive.php:894
prepareArchive() /piwik/core/Archive.php:708
cacheArchiveIdsAfterLaunching() /piwik/core/Archive.php:660
getArchiveIds() /piwik/core/Archive.php:598
get() /piwik/core/Archive.php:369
getDataTable() /piwik/core/Archive.php:460
getDataTableFromArchive() /piwik/core/Archive.php:489
Any help is much appreciated!