For the past few weeks mysql queries have been taking a long time. Things worked for the previous year or so without issue. The system is not heavily loaded.

As a test, I just purchased a second Linux 2048 node, and made a clone of the system. I ran the same mysql command on that clone system and it is much faster. Anyone have any ideas or suggestions? This makes no sense.

On system having issues the following query (with query cache disabled) took 1.53seconds vs 0.24seconds on the cloned system.

mysql> SELECT   wp_posts.ID,NOW() FROM wp_posts  INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1  AND wp_posts.post_type = 'shop_order' AND (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key = '_customer_user' AND CAST(wp_postmeta.meta_value AS CHAR) = '3063') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 5;
+-------+---------------------+
| ID    | NOW()               |
+-------+---------------------+
| 24661 | 2013-09-09 12:18:12 | 
| 23870 | 2013-09-09 12:18:12 | 
| 23082 | 2013-09-09 12:18:12 | 
| 22284 | 2013-09-09 12:18:12 | 
| 21428 | 2013-09-09 12:18:12 | 
+-------+---------------------+
5 rows in set (1.53 sec)

mysql> SELECT   wp_posts.ID,NOW() FROM wp_posts  INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1  AND wp_posts.post_type = 'shop_order' AND (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key = '_customer_user' AND CAST(wp_postmeta.meta_value AS CHAR) = '3063') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 5;
+-------+---------------------+
| ID    | NOW()               |
+-------+---------------------+
| 24661 | 2013-09-09 12:17:53 | 
| 23870 | 2013-09-09 12:17:53 | 
| 23082 | 2013-09-09 12:17:53 | 
| 22284 | 2013-09-09 12:17:53 | 
| 21428 | 2013-09-09 12:17:53 | 
+-------+---------------------+
5 rows in set (0.24 sec)

Here is the SQL EXPLAIN:

mysql> explain SELECT  wp_comments.*,NOW() FROM wp_comments JOIN wp_posts ON ( wp_comments.comment_post_ID = wp_posts.ID ) WHERE post_status = 'publish' AND comment_approved = '1' AND  wp_posts.post_type NOT IN ('shop_order')   ORDER BY comment_date_gmt DESC LIMIT 10;
+----+-------------+-------------+--------+-------------------------------------------+---------------------------+---------+---------------------------------------+-------+-------------+
| id | select_type | table       | type   | possible_keys                             | key                       | key_len | ref                                   | rows  | Extra       |
+----+-------------+-------------+--------+-------------------------------------------+---------------------------+---------+---------------------------------------+-------+-------------+
|  1 | SIMPLE      | wp_comments | range  | comment_post_ID,comment_approved_date_gmt | comment_approved_date_gmt | 62      | NULL                                  | 19621 | Using where | 
|  1 | SIMPLE      | wp_posts    | eq_ref | PRIMARY,type_status_date                  | PRIMARY                   | 8       | wordpress.wp_comments.comment_post_ID |     1 | Using where | 
+----+-------------+-------------+--------+-------------------------------------------+---------------------------+---------+---------------------------------------+-------+-------------+
2 rows in set (0.01 sec)

my.cnf is:

key_buffer_size = 64M
query_cache_limit = 2M
query_cache_size = 16M
thread_cache_size = 4
tmp_table_size = 64M
max_heap_table_size = 64M

skip-innodb
skip-bdb

log-slow-queries=/var/log/mysql-slowquery.log
long_query_time=1

It's possible that the "cloned" Linode is faster because nothing else is running on it whereas your main Linode hosts your other site(s).

No, I thought of that before I ran my test. I stopped the httpd daemon so there was no traffic on the site before I ran the queries - it was a pretty close comparison.

Linode technical support has since migrated my current site to a box similar to the one I cloned to and it is now working well. They are claiming the newer boxes have better CPU's, which is true, but that would not account for a 500% performance increase, perhaps 20-30% IMO. My gut feeling is that the box I was on is going to fail (newark507). It would be interesting to know if other customers on that box are experiencing problems.