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:
    151
    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:
     
    #1 skymedia, Sep 11, 2003
  2. jphilipson

    jphilipson Well-Known Member

    Joined:
    Jan 8, 2003
    Messages:
    80
    Likes Received:
    0
    Trophy Points:
    156
    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:
     
    #2 jphilipson, Sep 12, 2003
  3. skymedia

    skymedia Member

    Joined:
    Jul 11, 2003
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    151
    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:
     
    #3 skymedia, Sep 12, 2003
  4. Solokron

    Solokron Well-Known Member

    Joined:
    Aug 8, 2003
    Messages:
    849
    Likes Received:
    1
    Trophy Points:
    168
    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:
    191
    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.
     
    #5 casey, Nov 10, 2003
  6. Solokron

    Solokron Well-Known Member

    Joined:
    Aug 8, 2003
    Messages:
    849
    Likes Received:
    1
    Trophy Points:
    168
    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.

     
    #6 Solokron, Nov 10, 2003
(You must log in or sign up to post here.)
Loading...
Similar Threads - MySQL process using
  1. Leee-John

    Mysql process error log

    Leee-John, May 22, 2017, in forum: Database Discussions
    Replies:
    1
    Views:
    100
    cPanelMichael
    May 23, 2017
  2. linux.system25

    Stop MySQL Processes

    linux.system25, Mar 30, 2017, in forum: Database Discussions
    Replies:
    2
    Views:
    219
    vacancy
    Mar 30, 2017
  3. djbaxter

    SOLVED The MySQL restore process died from the “SEGV” signal.

    djbaxter, Mar 11, 2017, in forum: Data Protection
    Replies:
    8
    Views:
    1,173
    cPanelMichael
    Apr 4, 2017
  4. Buzz50

    Slow mysql process

    Buzz50, Feb 19, 2017, in forum: Database Discussions
    Replies:
    2
    Views:
    187
    cPanelMichael
    Feb 20, 2017
  5. turgay

    mysqld_safe Process Time Warning

    turgay, Jan 28, 2017, in forum: Database Discussions
    Replies:
    1
    Views:
    285
    cPanelMichael
    Jan 30, 2017

Share This Page