Mysql – Speeding SQL queries for a large(?) WordPress database

MySQLoptimization

I am using a WordPress plugin called feedwordpress in order to run a planet like website (it takes content from other websites, with their permission) on WordPress (See it here).

The plugin is great except for one thing – it hogs down my (VPS) server into submission once every week or so.

In a recent e-mail exchange with the webadmin he wrote the following:

It does look like the increased mysql
resource usage is being caused by slow
queries being run by r-bloggers.com.
Here is a copy of some of the logs
that are being produced. You would
need to optimize this site and
database further to have it running as
efficiently as possible. If these
changes have already been made, your
best option would be to look into a
large upgrade for your VPS due to the
high level or resources and traffic
that your site needs and sees.

Here are the logs:

# Time: 110614 16:11:35
# User@Host: rblogger_rblogr[rblogger_rblogr] @ localhost []
# Query_time: 104 Lock_time: 0 Rows_sent: 0 Rows_examined: 54616
SELECT SQL_CALC_FOUND_ROWS wp_rb_posts.* 
  FROM wp_rb_posts 
  WHERE 1=1 AND (
   (guid = '235cbefa4424d0cdb7b6213f15a95ded') OR 
   (guid = 'http://www.r-bloggers.com/?guid=235cbefa4424d0cdb7b6213f15a95ded') OR 
   (guid = 'http://www.r-bloggers.com/?guid=235cbefa4424d0cdb7b6213f15a95ded') OR 
   (MD5(guid) = '235cbefa4424d0cdb7b6213f15a95ded') ) AND 
  wp_rb_posts.post_type IN 
      ('post', 'page', 'attachment', 'revision', 'nav_menu_item') AND 
  (wp_rb_posts.post_status = 'publish' OR 
   wp_rb_posts.post_status = 'future' OR 
   wp_rb_posts.post_status = 'draft' OR 
   wp_rb_posts.post_status = 'pending' OR 
   wp_rb_posts.post_status = 'trash' OR 
   wp_rb_posts.post_status = 'auto-draft' OR 
   wp_rb_posts.post_status = 'inherit' OR 
   wp_rb_posts.post_status = 'private'
  ) 
  ORDER BY wp_rb_posts.post_date DESC LIMIT 1570, 10;
# User@Host: rblogger_rblogr[rblogger_rblogr] @ localhost []
# Query_time: 237 Lock_time: 0 Rows_sent: 0 Rows_examined: 54616
SELECT SQL_CALC_FOUND_ROWS wp_rb_posts.* FROM wp_rb_posts WHERE 1=1 AND ((guid = '235cbefa4424d0cdb7b6213f15a95ded') OR (guid = 'http://www.r-bloggers.com/?guid=235cbefa4424d0cdb7b6213f15a95ded') OR (guid = 'http://www.r-bloggers.com/?guid=235cbefa4424d0cdb7b6213f15a95ded') OR (MD5(guid) = '235cbefa4424d0cdb7b6213f15a95ded')) AND wp_rb_posts.post_type IN ('post', 'page', 'attachment', 'revision', 'nav_menu_item') AND (wp_rb_posts.post_status = 'publish' OR wp_rb_posts.post_status = 'future' OR wp_rb_posts.post_status = 'draft' OR wp_rb_posts.post_status = 'pending' OR wp_rb_posts.post_status = 'trash' OR wp_rb_posts.post_status = 'auto-draft' OR wp_rb_posts.post_status = 'inherit' OR wp_rb_posts.post_status = 'private') ORDER BY wp_rb_posts.post_date DESC LIMIT 570, 10;
# Time: 110614 16:18:13
# User@Host: rblogger_rblogr[rblogger_rblogr] @ localhost []
# Query_time: 257 Lock_time: 0 Rows_sent: 0 Rows_examined: 54616
SELECT SQL_CALC_FOUND_ROWS wp_rb_posts.* FROM wp_rb_posts WHERE 1=1 AND ((guid = '956e208f101562f6654e88e9711276e4') OR (guid = 'http://www.r-bloggers.com/?guid=956e208f101562f6654e88e9711276e4') OR (guid = 'http://www.r-bloggers.com/?guid=956e208f101562f6654e88e9711276e4') OR (MD5(guid) = '956e208f101562f6654e88e9711276e4')) AND wp_rb_posts.post_type IN ('post', 'page', 'attachment', 'revision', 'nav_menu_item') AND (wp_rb_posts.post_status = 'publish' OR wp_rb_posts.post_status = 'future' OR wp_rb_posts.post_status = 'draft' OR wp_rb_posts.post_status = 'pending' OR wp_rb_posts.post_status = 'trash' OR wp_rb_posts.post_status = 'auto-draft' OR wp_rb_posts.post_status = 'inherit' OR wp_rb_posts.post_status = 'private') ORDER BY wp_rb_posts.post_date DESC LIMIT 570, 10;
# Time: 110614 16:19:02
# User@Host: rblogger_rblogr[rblogger_rblogr] @ localhost []
# Query_time: 83 Lock_time: 0 Rows_sent: 0 Rows_examined: 54616
SELECT SQL_CALC_FOUND_ROWS wp_rb_posts.* FROM wp_rb_posts WHERE 1=1 AND ((guid = '6c589e661f03a67b0529fab2f080bfd3') OR (guid = 'http://www.r-bloggers.com/?guid=6c589e661f03a67b0529fab2f080bfd3') OR (guid = 'http://www.r-bloggers.com/?guid=6c589e661f03a67b0529fab2f080bfd3') OR (MD5(guid) = '6c589e661f03a67b0529fab2f080bfd3')) AND wp_rb_posts.post_type IN ('post', 'page', 'attachment', 'revision', 'nav_menu_item') AND (wp_rb_posts.post_status = 'publish' OR wp_rb_posts.post_status = 'future' OR wp_rb_posts.post_status = 'draft' OR wp_rb_posts.post_status = 'pending' OR wp_rb_posts.post_status = 'trash' OR wp_rb_posts.post_status = 'auto-draft' OR wp_rb_posts.post_status = 'inherit' OR wp_rb_posts.post_status = 'private') ORDER BY wp_rb_posts.post_date DESC LIMIT 1440, 10;

Which leads me to my question – what in this logs might indicate to me what is happening (why should such queries take so long?)? is it possible to optimize these? If so, how?

Thanks,
Tal

Best Answer

The first thing I see is the MD5(guid) = '235cbefa4424d0cdb7b6213f15a95ded' in the WHERE clause. That might trigger a bypass of the MySQL Query Optimizer and issue a full table scan to accomplish locating the row.

Also, try to find out if the guid column is indexed in the wp_rb_posts table.

Also, what jumps out at me when I see 'WordPress' is this question: Is all your data MyISAM or InnoDB ??? If all your data is MyISAM (in this case, the wp_rb_posts table), ALWAYS expect full table locks upon each INSERT, UPDATE, or DELETE on a MyISAM table. You may want to consider converting all your WordPress data into InnoDB. This will alleviate table locking.

The reason I switched gears into converting MyISAM to InnoDB ? When there are a lot of INSERTs, UPDATES, or DELETEs against wp_rb_posts (if it currently MyISAM), each will create a full table lock on a first-come, first-server basis. Any SELECT query, regardless of being a good or bad performing query, simply waits its turn until all queries accessing wp_rb_posts see the wp_rb_posts table unlock and access is granted.

While such SELECT queries wait, you may realize that the running time is climbing, not because the query is necessarily bad, but because it spent most of its lifetime waiting. Thus, the running time of the query may be deceptive because of external factors such as number of DB Connections running the same query, number of DB connections running different queries involving wp_rb_posts, overall server load, and so forth. Also worth noting is the number of rows in wp_rb_posts. You need to find out if the running time of this query is bad in a standalone test environment.

On the other hand, if wp_rb_posts is already InnoDB, now you can explore the query's EXPLAIN plan and look for indexes being selected or ignored.

Here is how you can convert all MyISAM tables to InnoDB

As a MySQL DBA, I trust MySQL to do the conversion by having MySQL write the script for me.

Form the Linux command run this query 

mysql -h... -u... -p... -A --skip-column-names -e"SELECT db,tb FROM (SELECT A.db,A.tb,A.tbsize FROM (SELECT table_schema db,table_name tb,(data_length+index_length) tbsize FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A LEFT JOIN (SELECT table_schema db,table_name tb FROM information_schema.statistics WHERE index_type='FULLTEXT') B USING (db,tb) WHERE B.db IS NULL) AA ORDER BY tbsize" > /root/ConvertMyISAM2InnoDB.sql

The script will convert the smallest tables first. This script was also bypass any MyISAM tables that have FULLTEXT indexes.

Ater looking over the script, you can simply run it in MySQL as follows:

mysql -h... -u... -p... -A < /root/ConvertMyISAM2InnoDB.sql

or if you want to see the timing of each conversion, login to mysql and run this:

mysql> source /root/ConvertMyISAM2InnoDB.sql

This should not get messed up because a full table lock happens when the conversion is being executed.

Once all tables are converted you need to tune the MySQL settings for InnoDB usage and scale down the key_buffer.

Please read this for setting the InnoDB Buffer Pool : What are the main differences between InnoDB and MyISAM?

Give it a Try !!!

Related Question