Mysql Slow when join query running increaed

Question

Our Mysql dedicated server box always have around 40 - 80 active user connection , so the query speed very good and Cpu usage low . (around 4 - 8 core from 24 core) but sometimes when some wordpress "Join" query running , server speed slow and other query go in queue so server running query increased over 600 -1000 /s.

this is following query that I think cause issue :

SELECT SQL_CALC_FOUND_ROWS  WP_POSTS.ID FROM WP_POSTS  INNER JOIN
WP_TERM_RELATIONSHIPS ON (WP_POSTS.ID = W
P_TERM_RELATIONSHIPS.OBJECT_ID) WHERE 1=1  AND (  
WP_TERM_RELATIONSHIPS.TERM_TAXONOMY_ID IN ()

My question ;

• why Join query affected our mysql server performance ?

• is it config related problem ? or we need more hardware ? at this time our server 24 core + 64 GIG RAM + SAS 15K RAID 10

Update 1 :

Simple Processlist related one of user :

| 1871506 | xhamster_gdgdgd  | server6:48608 | xhamster_hehehe           | Query   | 456  | Sending data                   | SELECT SQL_CALC_FOUND_RO
WS  wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 2467509 | kordgsmc_gsm     | server6:37144 | kordgsmc_vb             | Query   | 22   | Waiting for table level lock | SELECT userid, username
                                FROM user
                               sWHERE userid IN (6295)                                   |
| 2468708 | samseria_wp      | server6:38899 | samseria_wp             | Query   | 23   | Sending data                 | SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 2469440 | samseria_wp      | server6:39953 | samseria_wp             | Query   | 6    | Sending data                 | SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 2469825 | samseria_wp      | server6:40542 | samseria_wp             | Query   | 23   | Sending data                 | SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |

Answer 1

With MyISAM, an INSERT/UPDATE/DELETE will request an exclusive lock, and have to wait for any SELECTs to finish. If another SELECT comes in after the write, then it will be blocked waiting for the write to get its lock and finish its action.

That is, one simple write can snowball into the mess you are seeing.

Changing to InnoDB is likely to avoid the problem, since locks are taken at the row level, not the table level.

Let's see the EXPLAIN for your SELECT (and provide one that does not have syntax errors). Plus tell us how big the table is. And SHOW CREATE TABLE for each relevant table.

Speeding up the query will help avoid the locks.