The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL process using 97% CPU

Discussion in 'General Discussion' started by skymedia, Sep 11, 2003.

  1. skymedia

    skymedia Member

    Joined:
    Jul 11, 2003
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    A customer is running the following MySQL query from a PHP script:

    SELECT viejo.ope AS opeViejo, guia.cli, guia.gui, guia.ldc, count( * ) AS NroRtos, sum( bul ) AS bul, sum( kgr ) AS kgr, guia.ope, guia.loe, guia.loc, guia.fec, guia.fre, guia.retenida, alertas, guia.rte, guia.des, usuario FROM ecargas_despachos AS guia INNER JOIN ecargas_remitos AS remito ON CONCAT( guia.gui, guia.ldc ) = CONCAT( remito.gui, remito.ldc ) , ecargas_usuario_x_transp AS usrtrans, viejo_despachos AS viejo WHERE guia.codigo = 1 AND usrtrans.alertas != '000000' AND guia.cli = usrtrans.clientedex AND guia.codigo = usrtrans.empresa AND (CONCAT( guia.gui, guia.ldc ) = CONCAT( viejo.gui, viejo.ldc ) AND guia.ope != viejo.ope) GROUP BY CONCAT( guia.gui, guia.ldc ) , CONCAT( remito.gui, remito.ldc )) UNION (SELECT viejo.ope, guia.cli, guia.gui, guia.ldc, count( * ) AS NroRemitos, sum( bul ) AS bul, sum( kgr ) AS kgr, guia.ope, guia.loe, guia.loc, guia.fec, guia.fre, guia.retenida, alertas, guia.rte, guia.des, usuario FROM ecargas_despachos AS guia INNER JOIN ecargas_remitos AS remito ON CONCAT( guia.gui, guia.ldc ) = CONCAT( remito.gui, remito.ldc ) LEFT JOIN viejo_despachos AS viejo ON CONCAT( guia.gui, guia.ldc ) = CONCAT( viejo.gui, viejo.ldc ) , ecargas_usuario_x_transp AS usrtrans WHERE guia.codigo = 1 AND usrtrans.alertas != '000000' AND guia.cli = usrtrans.clientedex AND guia.codigo = usrtrans.empresa AND CONCAT( viejo.gui, viejo.ldc ) IS NULL GROUP BY CONCAT( guia.gui, guia.ldc ) , CONCAT( remito.gui, remito.ldc )) ORDER BY usuario

    This process uses 97% of my CPU and it runs for a long time.
    Is there a way to limit the process? For example setting a timeout, max CPU usage, etc.?

    Any suggestion is welcome.

    Thank you,

    Adrian

    cPanel.net Support Ticket Number:
     
  2. jphilipson

    jphilipson Well-Known Member

    Joined:
    Jan 8, 2003
    Messages:
    80
    Likes Received:
    0
    Trophy Points:
    6
    You can tweak a few settings in your php.ini to your needs..

    you can set the max execution time, default is 60

    max_execution_time = 60

    and dissallow persistant links to mysql..by changing

    mysql.allow_persistent = On

    to

    mysql.allow_persistent = Off

    other than that, not much else, besides finding out why the script is causing the load.

    cPanel.net Support Ticket Number:
     
  3. skymedia

    skymedia Member

    Joined:
    Jul 11, 2003
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    My php.ini has:
    max_execution_time = 30

    So it seems to have nothing to do with PHP. This mysql process runs for about 30 minutes. I have to kill it all the time, but wanted to know if this can be limited in some way by adding a line to my.cnf of if there's some way to automatically kill a process when it's consuming too much resources.

    When I do "top" the process looks like this:
    PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
    28937 mysql 16 0 22424 6360 1380 R 97.6 1.2 16:40 mysqld

    Thank you,

    Adrian

    cPanel.net Support Ticket Number:
     
  4. Solokron

    Solokron Well-Known Member

    Joined:
    Aug 8, 2003
    Messages:
    849
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Seattle
    cPanel Access Level:
    DataCenter Provider
    Did you find a resolution for this?

    I have a user that is driving the cpu up to 120% with mysql.
     
    #4 Solokron, Nov 10, 2003
    Last edited: Nov 10, 2003
  5. casey

    casey Well-Known Member

    Joined:
    Jan 17, 2003
    Messages:
    2,303
    Likes Received:
    0
    Trophy Points:
    36
    Location:
    If there is trouble, it will find me
    That, in my opinion, should be the customer's responsibility. You should send an e-mail and say, hey, your script is taking up 120% of the cpu resources. Fix it, or it's going to be removed.
     
  6. Solokron

    Solokron Well-Known Member

    Joined:
    Aug 8, 2003
    Messages:
    849
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Seattle
    cPanel Access Level:
    DataCenter Provider
    Of course that is done.

    I have implemented some additional tweaks that have dropped it considerably but it is still too high for my liking. He will be correcting it or meet the dreaded suspend account button.

     
Loading...

Share This Page