Results 1 to 7 of 7
-
09-04-2007, 06:02 PM #1Junior Guru Wannabe
- Join Date
- Jun 2006
- Posts
- 35
Advice needed on process using high CPU
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
-
09-04-2007, 08:26 PM #2
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.
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.Tom Whiting, WHMCS Guru extraordinaire
Linux problems? WHMCS Problems? Give me a shout
Check out my WHMCS Addons
-
09-04-2007, 08:33 PM #3Web Hosting Guru
- Join Date
- Oct 2005
- Posts
- 292
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!
-
09-04-2007, 11:51 PM #4Web Hosting Master
- Join Date
- Oct 2004
- Location
- Kerala, India
- Posts
- 4,771
David | www.cliffsupport.com
Affordable Server Management Solutions sales AT cliffsupport DOT com
CliffWebManager | Access WHM from iPhone and Android
-
09-05-2007, 12:18 PM #5Junior Guru Wannabe
- Join Date
- Jun 2006
- Posts
- 35
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
-
09-05-2007, 02:29 PM #6Web Hosting Master
- Join Date
- Oct 2002
- Posts
- 724
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.
Dinsol.com Web Hosting Since Year 1999 Open Running Stable
Social Media Optimization & Marketing Services with World Class Quality
www.dinsol.com
-
09-05-2007, 05:44 PM #7Junior Guru Wannabe
- Join Date
- Jun 2006
- Posts
- 35