MySQL process using 97% CPU

[skymedia]

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:

 

[jphilipson]

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:

 

[skymedia]

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:

 

[Solokron]

Did you find a resolution for this?

I have a user that is driving the cpu up to 120% with mysql.

 

[casey]

Originally posted by Solokron
Did you find a resolution for this?

I have a user that is driving the cpu up to 120% with mysql.

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.

 

[Solokron]

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.

Originally posted by casey
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.