Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Is the GUID column really either a GUID or a complete string? You're matching on some really long strings, and I have a feeling that is the problem. String matching always takes more time. Also, why not have the post_status do an "in" check instead of how you have it structured (just out of curiousity). –  jcolebrand Jun 16 '11 at 15:26
    
I added a comment to my answer. Please read. –  RolandoMySQLDBA Jun 17 '11 at 12:25
    
+1 for this question, which should remind WordPress users to give due attention to database architecture. Once due diligence has been performed in this respect, you rarely have to go over it again. –  RolandoMySQLDBA Jun 23 '11 at 14:03

1 Answer 1

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 !!!

share|improve this answer
    
Wow Roland, what an amazing answer - thank you! Question - I am running on a managed VPS hosting. Can this query be effective from within phpmyadmin? –  Tal Galili Jun 17 '11 at 6:09
    
@Tal : You just need to run EXPLAIN on the query and work on improving from there. If the query runs good or bad, phpmyadmin, WebYog, or even the mysql client, the query will be good or bad anywhere. –  RolandoMySQLDBA Jun 17 '11 at 12:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.