[EventCalendar] MySql CPU usage, slow queries

Jeff Ellermeyer jeff at buck.tv
Mon May 10 23:31:52 BST 2010


We run a busy site for a live music venue.  We publish 2-3 events a day for the last 3-4 years.  Recently, we have been having CPU issues on our server.  This has been traced back to the MySql server taking up all the CPU.  In order to address this, I turned off versioning in WP, removed all the old versions for each post.  I optimized all the tables.  I installed and turned on WP super cache.  I have optimized MySql to take advantage of all of the 2Gigs of Ram on the server.  All this has helped, but does not address the issue of some database operations take up too much CPU.  I started logging all of the slow queries AFTER these optimizations and ran mysqlsla on the log http://hackmysql.com/mysqlsla to summarize what was going on.  Sure enough, most of the slow queries that hammer the CPU are taken up by one query that takes up 88% of cpu time and is 46% of the slow queries.  I have attached the information from this query below.  I am not a SQL expert, but when I see "Rows examined" averaging at 4.5 Million, that tells me there is a big problem.  From here http://hackmysql.com/documents it seems like the code must be very inefficient.  I could remove old entries in my database, there are 3,303 records in wp_ec3_schedule, 3,782 in wp_posts and 11,183 in wp_term_relationships.  This is NOT a huge database.  Is there anybody that could possible suggest some way of indexing or improving this before I remove the last two years of posts as a last resort?

Does anyone want to get paid to help figure this out?

Comments?  Suggestions?  Here are the results of the slow queries.

Report for slow logs: /var/log/mysql-slow.log 2.46k queries total, 89 unique Sorted by 't_sum'
Grand Totals: Time 1.57M s, Lock 94.66k s, Rows sent 115.24k, Rows Examined 8.06G


______________________________________________________________________ 001 ___
Count         : 1.15k  (46.79%)
Time          : 1389035 s total, 1207.856522 s avg, 2 s to 4848 s max  (88.68%)
  95% of Time : 1123936 s total, 1029.245421 s avg, 2 s to 4301 s max Lock Time (s) : 3709 s total, 3.225217 s avg, 0 to 685 s max  (3.92%)
  95% of Lock : 139 s total, 127.289 ms avg, 0 to 5 s max
Rows sent     : 73 avg, 0 to 128 max  (72.52%)
Rows examined : 4.45M avg, 0 to 6.17M max  (63.47%)
Database      : spaceland
Users         : 
	echo_wp_user at localhost  : 73.04% (840) of query, 80.23% (1972) of all users
	Spacelandwp at localhost  : 26.96% (310) of query, 19.12% (470) of all users

Query abstract:
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT JOIN wp_ec3_schedule ec3_sch ON ec3_sch.post_id=id WHERE N=N AND wp_posts.post_type = 'S' AND (wp_posts.post_status = 'S') AND ((year(wp_posts.post_date)='S' AND month(wp_posts.post_date)='S') OR ((year(start)='S' AND month(start)='S') OR (start<='S' AND end>='S'))) GROUP BY wp_posts.id ORDER BY wp_posts.post_date DESC LIMIT N, N;

Query sample:
SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  LEFT JOIN wp_ec3_schedule ec3_sch ON ec3_sch.post_id=id  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND ((YEAR(wp_posts.post_date)='2010' AND MONTH(wp_posts.post_date)='5') OR ((YEAR(start)='2010' AND MONTH(start)='5') OR (start<='2010-5-0 0:0:0' AND end>='2010-5-0 0:0:0')))  GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 200;

______________________________________________________________________ 002 ___
Count         : 321  (13.06%)
Time          : 55174 s total, 171.88162 s avg, 2 s to 1941 s max  (3.52%)
  95% of Time : 31460 s total, 103.486842 s avg, 2 s to 1112 s max Lock Time (s) : 1767 s total, 5.504673 s avg, 0 to 790 s max  (1.87%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 6 avg, 0 to 38 max  (1.54%)
Rows examined : 5.49M avg, 0 to 6.17M max  (21.88%)
Database      : echo_wp
Users         : 
	echo_wp_user at localhost  : 88.47% (284) of query, 80.23% (1972) of all users
	Spacelandwp at localhost  : 11.53% (37) of query, 19.12% (470) of all users

Query abstract:
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT JOIN wp_ec3_schedule ec3_sch ON ec3_sch.post_id=id WHERE N=N AND wp_posts.post_type = 'S' AND (wp_posts.post_status = 'S') AND ((year(wp_posts.post_date)='S' AND month(wp_posts.post_date)='S' AND dayofmonth(wp_posts.post_date)='S') OR ((year(start)='S' AND month(start)='S' AND dayofmonth(start)='S') OR (start<='S' AND end>='S'))) GROUP BY wp_posts.id ORDER BY wp_posts.post_date DESC LIMIT N, N;

Query sample:
SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  LEFT JOIN wp_ec3_schedule ec3_sch ON ec3_sch.post_id=id  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND ((YEAR(wp_posts.post_date)='2008' AND MONTH(wp_posts.post_date)='2' AND DAYOFMONTH(wp_posts.post_date)='20') OR ((YEAR(start)='2008' AND MONTH(start)='2' AND DAYOFMONTH(start)='20') OR (start<='2008-2-20 0:0:0' AND end>='2008-2-20 0:0:0')))  GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 200;

______________________________________________________________________ 003 ___
Count         : 40  (1.63%)
Time          : 30630 s total, 765.75 s avg, 3 s to 1199 s max  (1.96%)
  95% of Time : 28330 s total, 745.526316 s avg, 3 s to 1027 s max Lock Time (s) : 30494 s total, 762.35 s avg, 0 to 1193 s max  (32.21%)
  95% of Lock : 28206 s total, 742.263158 s avg, 0 to 1025 s max
Rows sent     : 1 avg, 0 to 1 max  (0.03%)
Rows examined : 2 avg, 0 to 2 max  (0.00%)
Database      : echo_wp
Users         : 
	echo_wp_user at localhost  : 97.50% (39) of query, 80.23% (1972) of all users
	Spacelandwp at localhost  : 2.50% (1) of query, 19.12% (470) of all users

Query abstract:
SELECT wp_posts.* FROM wp_posts WHERE N=N AND year(wp_posts.post_date)='S' AND month(wp_posts.post_date)='S' AND dayofmonth(wp_posts.post_date)='S' AND wp_posts.post_name = 'S' AND wp_posts.post_type = 'S' ORDER BY wp_posts.post_date DESC;

Query sample:
SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND YEAR(wp_posts.post_date)='2010' AND MONTH(wp_posts.post_date)='1' AND DAYOFMONTH(wp_posts.post_date)='19' AND wp_posts.post_name = 'friday-03-12-10-black-rebel-motorcycle-club-echoplex' AND wp_posts.post_type = 'post'  ORDER BY wp_posts.post_date DESC;

______________________________________________________________________ 004 ___
Count         : 87  (3.54%)
Time          : 25704 s total, 295.448276 s avg, 2 s to 1156 s max  (1.64%)
  95% of Time : 20169 s total, 245.963415 s avg, 2 s to 1037 s max Lock Time (s) : 25197 s total, 289.62069 s avg, 0 to 1154 s max  (26.62%)
  95% of Lock : 19699 s total, 240.231707 s avg, 0 to 1027 s max
Rows sent     : 2 avg, 1 to 3 max  (0.14%)
Rows examined : 3.18k avg, 1.74k to 3.32k max  (0.00%)
Database      : spaceland
Users         : 
	echo_wp_user at localhost  : 91.95% (80) of query, 80.23% (1972) of all users
	Spacelandwp at localhost  : 8.05% (7) of query, 19.12% (470) of all users

Query abstract:
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.id = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) LEFT JOIN wp_ec3_schedule ec3_sch ON ec3_sch.post_id=id WHERE N=N AND wp_term_taxonomy.taxonomy = 'S' AND wp_term_taxonomy.term_id IN (S1) AND wp_posts.post_type = 'S' AND (wp_posts.post_status = 'S') AND ((year(start)=N AND month(start)=N AND dayofmonth(start)=N) OR (start<='S' AND end>='S')) GROUP BY wp_posts.id ORDER BY ec3_sch.start LIMIT N, N;

Query sample:
SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)  LEFT JOIN wp_ec3_schedule ec3_sch ON ec3_sch.post_id=id  WHERE 1=1  AND wp_term_taxonomy.taxonomy = 'category'  AND wp_term_taxonomy.term_id IN ('2')  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND ((YEAR(start)=2009 AND MONTH(start)=05 AND DAYOFMONTH(start)=03) OR (start<='2009-05-03 0:0:0' AND end>='2009-05-03 0:0:0'))  GROUP BY wp_posts.ID ORDER BY ec3_sch.start LIMIT 0, 200;

-Jeff



More information about the EventCalendar mailing list