Project

General

Profile

Actions
Copy link

Bug #2566

closed

Excessive Db queries?

Added by local admin over 11 years ago. Updated over 11 years ago.

Status:
Resolved
Priority name:
Normal
Assignee:
Boone Gorges
Category name:
-
Target version:
1.4.29
Start date:
2013-05-08
Due date:
% Done:

0%

Estimated time:
Deployment actions:

Description

I'm seeing the database get overloaded with requestes like this:

 74440457   commons       localhost commonswpm         8  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440458   commons       localhost commonswpm         8  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440459   commons       localhost commonswpm         8  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440463   commons       localhost commonswpm         8  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440464   commons       localhost commonswpm         8  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440468   commons       localhost commonswpm         8  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440469   commons       localhost commonswpm         8  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74439874   commons       localhost commonswpm         9  Query SELECT SUM(u.c) c FROM (SELECT COUNT(DISTINCT(ip)) c FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `exclu
 74440432   commons       localhost commonswpm         9  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440436   commons       localhost commonswpm         9  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440440   commons       localhost commonswpm         9  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440442   commons       localhost commonswpm         9  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440443   commons       localhost commonswpm         9  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440447   commons       localhost commonswpm         9  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440409   commons       localhost commonswpm        10  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440418   commons       localhost commonswpm        10  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440419   commons       localhost commonswpm        10  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440423   commons       localhost commonswpm        10  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440425   commons       localhost commonswpm        10  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440426   commons       localhost commonswpm        10  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440429   commons       localhost commonswpm        10  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440431   commons       localhost commonswpm        10  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440398   commons       localhost commonswpm        11  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user
 74440399   commons       localhost commonswpm        11  Query SELECT COUNT(*) FROM `wp_15_firestats_hits` h,`wp_15_firestats_useragents` u WHERE h.useragent_id=u.id AND `excluded_ip` = '0' AND `excluded_by_user` = '0' AND `excluded_user

Files

mysql_connections-day.png (32 KB) mysql_connections-day.png local admin, 2013-05-28 10:37 AM
Actions
Copy link
 #1

Updated by local admin over 11 years ago

Restarting the mysqld and httpd processes seems to have resolved the issue at least temporarily.

Actions
Copy link
 #2

Updated by local admin over 11 years ago

  • Status changed from New to Resolved

It's been a few minutes and things look normal again. Closing it as resolved.

Actions
Copy link
 #3

Updated by Matt Gold over 11 years ago

Thanks for your work on this, Andre.

Actions
Copy link
 #4

Updated by local admin over 11 years ago

This is happening again. Looking into it.

Actions
Copy link
 #5

Updated by local admin over 11 years ago

Slow queries log:

# Time: 130527  7:41:20
# User@Host: commons[commons] @ localhost []
# Query_time: 1.108230  Lock_time: 0.000084 Rows_sent: 1  Rows_examined: 745401
SET timestamp=1369654880;
SELECT SUM(u.c) c FROM (SELECT COUNT(DISTINCT(ip)) c
                                        FROM `wp_184_firestats_hits` h,`wp_184_firestats_useragents` u
                                        WHERE  h.useragent_id=u.id AND `excluded_ip` = '0'
                        AND `excluded_by_user` = '0' 
                        AND `excluded_user` = '0' 
                        AND `match_bots`='0'
                        AND `site_id` = '1' AND 1  
                                        GROUP BY SUBSTRING(`timestamp`,1,10),site_id) u;
# Time: 130527  7:41:25
# User@Host: commons[commons] @ localhost []
# Query_time: 1.925550  Lock_time: 0.000098 Rows_sent: 1  Rows_examined: 745404
SET timestamp=1369654885;
SELECT SUM(u.c) c FROM (SELECT COUNT(DISTINCT(ip)) c
                                        FROM `wp_184_firestats_hits` h,`wp_184_firestats_useragents` u
                                        WHERE  h.useragent_id=u.id AND `excluded_ip` = '0'
                        AND `excluded_by_user` = '0' 
                        AND `excluded_user` = '0' 
                        AND `match_bots`='0'
                        AND `site_id` = '1' AND 1  
                                        GROUP BY SUBSTRING(`timestamp`,1,10),site_id) u;
# Time: 130527  7:41:28
# User@Host: commons[commons] @ localhost []
# Query_time: 1.117990  Lock_time: 0.000100 Rows_sent: 1  Rows_examined: 745407
SET timestamp=1369654888;
SELECT SUM(u.c) c FROM (SELECT COUNT(DISTINCT(ip)) c
                                        FROM `wp_184_firestats_hits` h,`wp_184_firestats_useragents` u
                                        WHERE  h.useragent_id=u.id AND `excluded_ip` = '0'
                        AND `excluded_by_user` = '0' 
                        AND `excluded_user` = '0' 
                        AND `match_bots`='0'
                        AND `site_id` = '1' AND 1  
                                        GROUP BY SUBSTRING(`timestamp`,1,10),site_id) u;
Actions
Copy link
 #6

Updated by Boone Gorges over 11 years ago

  • Status changed from Resolved to Assigned
  • Assignee set to Boone Gorges

Thanks for posting this info, André.

I'd be surprised if those queries, annoying as they are, are alone responsible for bringing down the db. Seems like the overall influx of Firestats traffic is the real culprit.

I think that, as a short term solution, we might consider disabling the option to enable Firestats places where it's not already enabled. Maybe not a solution so much, but it will curb further issues. Matt, do you have any problems with this?

I can also look into Firestats to see if there's any way to cache some of this data for logged-out users. There's no reason why logged-out users have to see up-to-the-second exact visitor counts.

Actions
Copy link
 #7

Updated by Matt Gold over 11 years ago

Matt, do you have any problems with this?

Nope -- I enthusiastically support that move.

Actions
Copy link
 #8

Updated by local admin over 11 years ago

I think what happened was that the max number of connections was reached (500) then new connections (Sarah's) just failed silently.

Actions
Copy link
 #9

Updated by local admin over 11 years ago

This picture tells the story (attached).

Also we've been having some bad guys (presumably) scanning the website over the last few days (from logwatch):

A total of 3 sites probed the server
   50.56.58.47
   50.57.190.97
   50.57.68.14
Actions
Copy link
 #10

Updated by Boone Gorges over 11 years ago

  • Status changed from Assigned to Resolved
  • Target version set to 1.4.29

Firestats is disabled for new users in https://github.com/castiron/cac/commit/cbca648b600151a7049815d4be8b8fcf9ddcb2f0

I looked into the code a bit to see what was going on. I can't find an obvious way to build in a throttle, without forking the plugin and rewriting much of the javascript. So I'm going to mark this Resolved for now, and we can address in greater depth if this turns out to be a recurring problem.

Actions
Copy link

Also available in: Atom PDF