Return to Question

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 |

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

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

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

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