Navigation Menu

Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Performance degradation after update from 2.9.2 -> 2.11.2 #7416

Closed
sdosg opened this issue Mar 12, 2015 · 7 comments
Closed

Performance degradation after update from 2.9.2 -> 2.11.2 #7416

sdosg opened this issue Mar 12, 2015 · 7 comments
Labels
answered For when a question was asked and we referred to forum or answered it.

Comments

@sdosg
Copy link

sdosg commented Mar 12, 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
Copy link

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
Copy link
Contributor

+1 on nginx / CentOS 6 / php 5.3.3

@diosmosis
Copy link
Member

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

@GreenReaper
Copy link

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
Copy link
Member

mattab commented Mar 16, 2015

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

@mattab mattab closed this as completed Mar 16, 2015
@mattab mattab added the answered For when a question was asked and we referred to forum or answered it. label Mar 16, 2015
@sdosg sdosg mentioned this issue Mar 16, 2015
Closed
@diosmosis
Copy link
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
Copy link
Member

mattab commented Mar 16, 2015

Let's continue discussion in #7440

@GreenReaper GreenReaper mentioned this issue Mar 17, 2015
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
answered For when a question was asked and we referred to forum or answered it.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@diosmosis @cybernet @GreenReaper @mattab @sdosg