Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

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:
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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:
    850
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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
    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:
    850
    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.

     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #6 Solokron, Nov 10, 2003
(You must log in or sign up to post here.)
Loading...
Similar Threads - MySQL process using
  1. oreo240

    MySQL processes using high memory

    oreo240, Jun 12, 2018, in forum: Database Discussion
    Replies:
    3
    Views:
    154
    cPanelLauren
    Jun 13, 2018
  2. Sunlander

    MySQL Error in WHM Service Status Daily Process Log

    Sunlander, Jun 28, 2018, in forum: Database Discussion
    Replies:
    2
    Views:
    155
    cPanelMichael
    Jun 29, 2018
  3. Mayc

    Null Mysql Daemon Innodb Process

    Mayc, Feb 7, 2018, in forum: Database Discussion
    Replies:
    5
    Views:
    372
    cPanelMichael
    Feb 12, 2018
  4. Nirjonadda

    MySQL 5.7 upgrade process failed

    Nirjonadda, Feb 6, 2018, in forum: Database Discussion
    Replies:
    2
    Views:
    786
    cPanelMichael
    Feb 14, 2018
  5. electric

    Possible for end-user to see all their active mysql processes?

    electric, Sep 12, 2017, in forum: Database Discussion
    Replies:
    5
    Views:
    561
    cPanelMichael
    Sep 13, 2017

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice