Results 1 to 7 of 7
Thread: SQL Taking Up Too Much Resources
-
05-23-2008, 03:35 PM #1Junior Guru Wannabe
- Join Date
- Apr 2008
- Posts
- 73
SQL Taking Up Too Much Resources
Hello everyone,
One of my servers keep overloading due to a SQL process.
The process is:
/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/zeus1.forcium.net.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock
It takes up pretty much 90-95% of the cpu and memory at times if I do not kill the process. But even after I kill the process it comes back and immediately hogs up cpu load again causing it to go into loads of 8.00 or higher ( I have 8cpus ).
Any solution to this?
Thanks in advance!
-
05-23-2008, 03:45 PM #2Web Hosting Master
- Join Date
- Jan 2006
- Location
- Athens, Greece
- Posts
- 1,481
Use one of the .cnf files that came with mysql. Possibly my-medium.cnf
I would also suggest keeping mysql threads to 4.
If that doesn't do anything, then there is a script that does some heavy processing.
-
05-23-2008, 04:22 PM #3Junior Guru Wannabe
- Join Date
- Apr 2008
- Posts
- 73
where can I go to do this?
-
05-23-2008, 08:33 PM #4Junior Guru Wannabe
- Join Date
- Apr 2008
- Posts
- 73
I can't seem to spot any single process that can be taking up all that resources with SQL.
It seems like this process would build up overtime and eventually overload the server.
Currently Looking at Today's CPU Usage:
mysql 59.90 2.37 0.0
Top Process %CPU 148 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/zeus1.forcium.net.pid --skip-external-locking --socket/var/lib/mysql/mysql.sock
Top Process %CPU 125 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/zeus1.forcium.net.pid --skip-external-locking --socket/var/lib/mysql/mysql.sock
Top Process %CPU 124 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/zeus1.forcium.net.pid --skip-external-locking --socket/var/lib/mysql/mysql.sock
Then I go to: Main ]] SQL Services ]] Show MySQL Processes
I see the following:
1330 hentaifo_blu localhost hentaifo_smf Sleep 3 NULL
1407 womenfoo_footbal localhost womenfoo_football Sleep 1 NULL
1442 hentaifo_blu localhost hentaifo_smf Sleep 5 NULL
1484 hentaifo_blu localhost hentaifo_smf Sleep 1 NULL
1522 bwcbob_scftane localhost bwcbob_scftanet Sleep 5 NULL
1523 bwcbob_scftane localhost bwcbob_scftanet Sleep 5 NULL
1540 fhosting_s2k localhost fhosting_status2k Sleep 2 NULL
1544 eximstats localhost eximstats Sleep 2 NULL
1559 root localhost NULL Query 0 NULL SHOW PROCESSLIST
Any input on this would be appreciated.
Thanks
-
05-24-2008, 01:48 AM #5Web Hosting Master
- Join Date
- Oct 2007
- Location
- 9.9N 76.2E , Planet Earth
- Posts
- 1,003
Install MTOP (http://mtop.sourceforge.net/) and monitor the MySQL server
You may also want to log slow mysql queries∞A U T O M 8 N . C O M
High Available webstack for cPanel
Active-Active redundancy and High Availability plugin for cPanel
-
05-24-2008, 03:02 PM #6Web Hosting Evangelist
- Join Date
- Aug 2001
- Location
- Brea, CA
- Posts
- 541
You could also do something like:
strace -f -p <process id>
That would let you watch the system calls MySQL is making. It'll often scroll pretty fast, but you can use some of the options to log to a file and then examine it.
It's hit and miss, but it often gives some insight.Jeff Standen, Software Architect, Cerb
Cerb - web-based teamwork and workflow automation - https://cerb.ai/
-
05-24-2008, 03:24 PM #7Temporarily Suspended
- Join Date
- Feb 2004
- Location
- UK
- Posts
- 1,431
Hi
Firstly I would look at the configuration of the mysql startup as suggested earlier. There are numerous sites out there that have tuning scripts that will tell your where the bottleneck is.
if you don't know about my.cnf there is a good chance its a default one.
Thanks