I'm using Piwik to track hits to my site. It's fairly popular (~4k visits/30k pageviews per day on average); until recently it was hosted on Dreamhost and barely worked because it was too big to archive without hitting the memory limit.
I recently moved it to my VPS using nginx and php5-fpm, but now I have another problem: the tracker keeps hanging at the query. Currently, it's using its own pool of php5-fpm processes, but they tend to get clogged:
[27-Aug-2013 03:20:05] WARNING: [pool stats] child 5908, script '/var/www/stats/piwik.php' (request: "GET /piwik.php") execution timed out (76.106048 sec), terminating [27-Aug-2013 03:20:05] WARNING: [pool stats] child 5905, script '/var/www/stats/piwik.php' (request: "GET /piwik.php") execution timed out (82.416656 sec), terminating [27-Aug-2013 03:20:05] WARNING: [pool stats] child 5914 exited on signal 15 (SIGTERM) after 75.932611 seconds from start [27-Aug-2013 03:20:05] NOTICE: [pool stats] child 6219 started etc etc etc ...
The slow log points to a large SELECT query in core/Tracker/Visit.php:1203
[27-Aug-2013 03:17:52] [pool stats] pid 5789 script_filename = /var/www/stats/piwik.php [0x091b21b4] xecute() /var/www/stats/core/Tracker/Db/Pdo/Mysql.php:159 [0x091b2024] query() /var/www/stats/core/Tracker/Db/Pdo/Mysql.php:126 [0x091b0bfc] fetch() /var/www/stats/core/Tracker/Visit.php:1203
The tables are optimized and as far as I know up to date. I can't find any reason why the insert query keeps hanging; I've used mysqltuner to try to adjust caching/etc to reasonable values, but for whatever reason the processes continue to lock up and clog the system.
The problem isn't always obvious, either; it can go for a while before everything suddenly goes downhill. I haven't been able to find any obvious correlation between when it starts falling apart and any particular request or log.