@sdosg opened this Issue on March 12th 2015

After upgrading from 2.9.2 to 2.11.2 our server can handle only about half of the requests than before the upgrade (from ~740 req/s to ~400 req/s). Due to the database upgrades, i can't go back to 2.9.2.
The bottleneck is cpu, mostly consumed by php fcgi threads.

Server Setup:
FreeBSD 10.1, lighttpd 1.35, php 5.6.6, mysql 5.6

@GreenReaper commented on March 14th 2015

Do you have munin or some similar monitoring platform? I saw what appears to be a doubling in mysql temp tables (and worse, disk tables) in 2.10 - would be interested to know if others have as well. This isn't fixed even in 2.12-b3.

@cybernet commented on March 14th 2015 Contributor

+1 on nginx / CentOS 6 / php 5.3.3

@diosmosis commented on March 14th 2015 Member

@GreenReaper Is the table size increase in the archive tables? If so, this issue is tracked here: https://github.com/piwik/piwik/issues/7181 . Can you take a look at the last comment there and see if it helps you?

@GreenReaper commented on March 15th 2015

I'm not sure that the table size has increased markedly - the issue is that it is creating temporary disk tables where it wasn't before (where before = around 2.9), seemingly on each hit - like this:
mysql_tmp_tables-month

Sometimes I catch it on "sorting result" doing a query like this:

( SELECT visit_last_action_time, visit_first_action_time, idvisitor, idvisit, user_id, visit_exit_idaction_url, visit_exit_idaction_name, visitor_returning, visitor_days_since_first, visitor_days_since_order, visitor_count_visits, visit_goal_buyer, location_country, location_region, location_city, location_latitude, location_longitude, referer_name, referer_keyword, referer_type, location_ip, location_browser_lang, visit_total_events, visit_total_time, visit_total_actions, visit_total_searches   ,
                0 as priority
                FROM piwik_log_visit
                WHERE visit_last_action_time >= '2015-03-15 19:21:45' AND visit_last_action_time <= '2015-03-15 20:21:45' AND idsite = '1'  AND config_id = '▒m▒5▒▒▒-' AND user_id IS NULL
                ORDER BY visit_last_action_time DESC
                LIMIT 1
             )
                UNION
                ( SELECT visit_last_action_time, visit_first_action_time, idvisitor, idvisit, user_id, visit_exit_idaction_url, visit_exit_idaction_name, visitor_returning, visitor_days_since_first, visitor_days_since_order, visitor_count_visits, visit_goal_buyer, location_country, location_region, location_city, location_latitude, location_longitude, referer_name, referer_keyword, referer_type, location_ip, location_browser_lang, visit_total_events, visit_total_time, visit_total_actions, visit_total_searches   ,
                1 as priority
                FROM piwik_log_visit
                WHERE visit_last_action_time >= '2015-03-15 19:21:45' AND visit_last_action_time <= '2015-03-15 20:21:45' AND idsite = '1'  AND idvisitor = 'ᑰ▒▒▒▒▒'
                ORDER BY visit_last_action_time DESC
                LIMIT 1
             )
                ORDER BY priority DESC
                LIMIT 1

or (less commonly) this

SELECT MIN(idaction) as idaction, type, name FROM piwik_log_action WHERE ( hash = CRC32('Gallery | Inkbunny, the Furry Art Community') AND name = 'Gallery | Inkbunny, the Furry Art Community' AND type = '4' )  OR ( hash = CRC32('inkbunny.net/submissionsviewall.php?mode=search&orderby=&text=&stringtype=&keywords=no&title=no&description=no&artist=&favsby=&type=4&sale=&days=&keyword_id=&user_id=&random=&md5=') AND name = 'inkbunny.net/submissionsviewall.php?mode=search&orderby=&text=&stringtype=&keywords=no&title=no&description=no&artist=&favsby=&type=4&sale=&days=&keyword_id=&user_id=&random=&md5=' AND type = '1' )  GROUP BY type, hash, name 

Installing the beta or running an archive cleanup does not appear to have affected this. I have, however, been getting significant spikes in disk read activity every two to three hours which were not previously present.
diskstats_iops-day

@mattab commented on March 16th 2015 Owner

Hi there,
Please test with latest Piwik beta: http://builds.piwik.org/ where this issue should be fixed. If you still have issue with this, please open new separate issue. Thanks

@diosmosis commented on March 16th 2015 Member

@GreenReaper Those queries use temporary tables, but I wouldn't expect them to be stored on disk, as they should not select much data. Can you see what happens when you run those queries yourself w/o the GROUP BY/MIN and ORDER BY/LIMIT? How many rows do they end up selecting?

@mattab commented on March 16th 2015 Owner

Let's continue discussion in #7440

This Issue was closed on March 16th 2015
Powered by GitHub Issue Mirror