Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    136

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

    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
    Regards
    Pawan Kumar

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you should tune the query. look at its access path is it doing needless work, scanning huge tables instead of unsing indexes?
    Dave

  3. #3
    Join Date
    Mar 2008
    Posts
    136
    mysql> explain 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_.CNDRF NUM 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;
    +----+-------------+--------------+--------+-----------------------------------------------------------------+------------------------+---------+-----------------------------------------+------+----------------------------------------------+
    | 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_OM S_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 |
    +----+-------------+--------------+--------+-----------------------------------------------------------------+------------------------+---------+-----------------------------------------+------+----------------------------------------------+
    7 rows in set (0.00 sec)
    Regards
    Pawan Kumar

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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                                  | 
    +----+-------------+--------------+--------+-----------------------------------------------------------------+------------------------+---------+-----------------------------------------+------+----------------------------------------------+
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •