0

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

enter image description here

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 |
  • Please show us the entire query, plus SHOW CREATE TABLE. May need to use SHOW FULL PROCESSLIST;. – Rick James Aug 25 '15 at 21:15
  • @RickJames first question updated. – Pardis Aug 26 '15 at 8:50
  • what is the MySQL version and what engine are you using in both tables? – Masoud Aug 26 '15 at 9:09
  • @Masoud , happy to see you here , Mysql 5.6 , our JOIN related Table almost MyISAM. – Pardis Aug 26 '15 at 10:04
  • @Pardis... Can u really see me? hahah ... Table level lock is MyISAM specialty. It should not lock the table for a query but for DDL statements. Check your settings at first. but i suggest you move both tables to innodb instead. and take a look at this page – Masoud Aug 26 '15 at 10:21
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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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