100% CPU consumed if same query run 3-4 times parallel

Pawan Kumar · #1 (**permalink**) 07-19-10, 07:46

Hi All,
There is running one query then system is running fine when same query runs 3-4 times parallel..mysqld consumes 100% CPU...

What I need to do?

and Created_tmp_files is 111

Regards

dav1mo · #2 (**permalink**) 07-19-10, 08:22

you should tune the query. look at its access path is it doing needless work, scanning huge tables instead of unsing indexes?
Dave

Pawan Kumar · #3 (**permalink**) 07-19-10, 08:58

r937 · #4 (**permalink**) 07-19-10, 09:24

you have some old-style implicit joins in your query --

Code:

select count(omsorder2_.POTRFNUM) as col_0_0_ 
  from oms_itinsord omsitinsor0_ 
inner
  join oms_suborder omssuborde1_ 
    on omsitinsor0_.ITNPSTRFNUM = omssuborde1_.PSTRFNUM 
inner
  join oms_order omsorder2_ 
    on omssuborde1_.PSTPOTRFNUM = omsorder2_.POTRFNUM
     , oms_orderuser omsorderus3_
     , oms_flightofford omsflighto4_
     , oms_cnd omscnd5_
     , oms_cnd omscnd6_ 
 where omsitinsor0_.ITNNETWORKTYPECNDRFNUM = omscnd6_.CNDRFNUM 
   and omssuborde1_.PSTCNDCHANNELID = omscnd5_.CNDRFNUM 
   and omsorderus3_.OrderNumber = omsorder2_.POTRFNUM
   and omsorderus3_.UserRefNum = 10000
   and omsorderus3_.OrderNumber = omsorder2_.POTRFNUM
   and omssuborde1_.PSTSMTSTATUS = 10023
   and omscnd5_.CNDCODE = 'CHAL'
   and omssuborde1_.PSTRFNUM = omsitinsor0_.ITNPSTRFNUM
   and omscnd6_.CNDCODE = 'DOM' 
group 
    by omssuborde1_.PSTRFNUM;

first thing i would do is rewrite them as INNER JOINs

also, please use [code]code[/code] tags, ta very much

Code:

+----+-------------+--------------+--------+-----------------------------------------------------------------+------------------------+---------+-----------------------------------------+------+----------------------------------------------+
| id | select_type | table        | type   | possible_keys                                                   | key                    | key_len | ref                                     | rows | Extra                                        |
+----+-------------+--------------+--------+-----------------------------------------------------------------+------------------------+---------+-----------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | omssuborde1_ | ref    | PRIMARY,PSTCNDCHANNELID,FK_PST_OMS_Order,FK_PST_OMS_StateMaster | FK_PST_OMS_StateMaster | 9       | const                                   |  678 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | omsitinsor0_ | ref    | IDX_ITNPOT,FK_oms_itnsord_oms_cnd                               | IDX_ITNPOT             | 8       | tsi.omssuborde1_.PSTRFNUM               |    1 |                                              | 
|  1 | SIMPLE      | omscnd5_     | eq_ref | PRIMARY                                                         | PRIMARY                | 8       | tsi.omssuborde1_.PSTCNDCHANNELID        |    1 | Using where                                  | 
|  1 | SIMPLE      | omsorderus3_ | ref    | OrderNumber,UserRefNum                                          | OrderNumber            | 8       | tsi.omssuborde1_.PSTPOTRFNUM            |    2 | Using where; Using index                     | 
|  1 | SIMPLE      | omsorder2_   | eq_ref | PRIMARY                                                         | PRIMARY                | 8       | tsi.omsorderus3_.OrderNumber            |    1 | Using where; Using index                     | 
|  1 | SIMPLE      | omsflighto4_ | index  | NULL                                                            | PRIMARY                | 8       | NULL                                    |  342 | Using index                                  | 
|  1 | SIMPLE      | omscnd6_     | eq_ref | PRIMARY                                                         | PRIMARY                | 8       | tsi.omsitinsor0_.ITNNETWORKTYPECNDRFNUM |    1 | Using where                                  | 
+----+-------------+--------------+--------+-----------------------------------------------------------------+------------------------+---------+-----------------------------------------+------+----------------------------------------------+

dav1mo · #5 (**permalink**) 07-19-10, 09:25

First off, you should use code tags around your statement so that you can format into a readable query. Also, by doing that you would find that you have provided a cartesian join(take note of the line in red). So, not only would it run long, it would return incorrect results. Lastly, it doesn't seem like there would be much filtering of rows done, based on the joins, so there would be a lot of data to count eaqch time this is run. Also, for consistency sake you should stick with one style of joins instead of the mishmash, just keeps it easier to maintain and troubleshoot. Here is your SQL statement. The only thing I did was changed the implicit joins to explicit joins and moved all the conditions out of the WHERE clause into the ON clauses.
Dave Nance

Code:

select count(omsorder2_.POTRFNUM) as col_0_0_ 
   from oms_itinsord omsitinsor0_ 

inner join oms_suborder omssuborde1_
    on omsitinsor0_.ITNPSTRFNUM    = omssuborde1_.PSTRFNUM 
   and omssuborde1_.PSTSMTSTATUS   = 10023
   and omssuborde1_.PSTRFNUM       = omsitinsor0_.ITNPSTRFNUM

inner join oms_order omsorder2_
    on omssuborde1_.PSTPOTRFNUM=omsorder2_.POTRFNUM

inner join oms_orderuser omsorderus3_
    on  omsorderus3_.OrderNumber     = omsorder2_.POTRFNUM
   and omsorderus3_.UserRefNum       = 10000

inner join oms_flightofford omsflighto4_
inner join oms_cnd omscnd5_
    on  omssuborde1_.PSTCNDCHANNELID        = omscnd5_.CNDRFNUM
   and omscnd5_.CNDCODE                    = 'CHAL'

inner join oms_cnd omscnd6_
    on omsitinsor0_.ITNNETWORKTYPECNDRFNUM = omscnd6_.CNDRF NUM
   and omscnd6_.CNDCODE                    = 'DOM'

group by omssuborde1_.PSTRFNUM;

Internet Services

Web Development

Digital Marketing

Consumer Tech