Advice needed on process using high CPU

Portal Home > Knowledgebase > Articles Database > Advice needed on process using high CPU

Posted by Gazza-t, 09-04-2007, 06:02 PM
could some one advise me on a problem i have ? I run a dedicated server, Running CentOS 4.5 final Apache 2.0.59 php 4.3.11 MySQL 4.1.20 I have a user who has been importing large sql databases and this causes the cpu to run at 80% - 90% this in turn causes high loads and sometimes causing the server to crash. Is there a way to automatically limit or control how much the process can use or stop the process to allow the server to recover. Just trying to put some sort of safe guard in place Hope i have explained what i am trying to do right. Any help appreciated Thank you
Posted by whmcsguru, 09-04-2007, 08:26 PM
if IMPORTING a db causes this much strain on the sql server for a repeated length of time, then it's time to ask the owner to get a dedicated server. Not a semi-ded, but a dedicated. Why? Simply put, if the DB takes that much time and extracts the resources that much on import, then how much MORE resource is it going to use on a day to day basis? Trust me, that's a road you don't want to go down. No, not and keep the DB reasonably intact.
Posted by cygnusd, 09-04-2007, 08:33 PM
a couple of random tips (kindly research how these are done) - enable logging of long lived queries - selectively kill sql threads by using logging in as root and using 'SHOW PROCESSLIST', 'SHOW FULL PROCESSLIST', 'KILL' combo - talk to your user if it's possible to revise his import script to user 'INSERT .. DELAYED' syntax - tune your my.cnf Cheers!
Posted by david510, 09-04-2007, 11:51 PM
If your server has enough memory, try increasing the value max_allowed_packet to a higher value under the sections [mysqld] and [mysqldump] inside my.cnf file. It will speed up the mysql dump/restore process.
Posted by Gazza-t, 09-05-2007, 12:18 PM
Hi @linux-tech The users been on the server for 8 months with no problems but recently been importing backups after messing up his database, thats when the problem arose but yes i can see the point your making. Thank you for your reply @cygnusd logging of long lived queries is enabled and there where not that many, i also reduced long query time to 5 sec sometime back. my.cnf was optimised but taking another look to see if it can be improved. The user was importing with bigdumps and by reducing linespersession has reduced the load on the CPU Thank you for your reply @david510 [mysqld] max_allowed_packet = 16m set already at 16m could go higher as i have 2G memory. [mysqldump] that setting was not in my.cnf but have added to see what effects it has on the server after a bit of research [mysqldump] quick max_allowed_packet=16M Thank you for your reply
Posted by HostingSocial, 09-05-2007, 02:29 PM
Ask user to do d/b import in batch files. Like he can make 3-4 files of large sql files and than import one by one. It makes sense if you do that for your customer. They will appreciate and stay with you longer.
Posted by Gazza-t, 09-05-2007, 05:44 PM
thats an idea i will look into that. thanks

Add to Favourites Print this Article