var sidebar_align = 'right';
var content_container_margin = parseInt('350px');
var sidebar_width = parseInt('330px');
SQL Taking Up Too Much Resources
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!
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.
where can I go to do this?
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.
Install MTOP (
http://mtop.sourceforge.net/) and monitor the MySQL server
You may also want to log slow mysql queries
Plug in Nginx & Plug in performance on cPanel systems
WebOps on cPanel . Deploy webapps to multiple servers and scale horizontally
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.
Chief of R&D
, WebGroup Media LLC. -
is a fast and flexible web-based platform for business collaboration and automation.
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.