My server hosting company sent me this ... Any one here who can explain what's going on with my server and how to best fix it so it runs as good as possible? "Upon checking the server logs I can find that mysql process is consuming a considerable amount of server resources and hence other resources become unavailable for other services." PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 3148 mysql 15 0 884m 512m 4964 S 146.7 6.4 3757:26 /usr/sbin/mysqld --basedir=/ --datadir=/mnt/sdb1/mysql --user=mysql --pid-file=/mnt/sdb1/mysql/ 6781 nobody 16 0 617m 26m 2512 S 3.0 0.3 0:49.27 /usr/local/apache/bin/httpd -k restart -DSSL 6779 nobody 16 0 427m 24m 2512 S 2.3 0.3 0:47.88 /usr/local/apache/bin/httpd -k restart -DSSL 4893 mysite3 15 0 0 0 0 Z 2.0 0.0 0:00.06 [php] <defunct> 4899 mysite1 17 0 134m 12m 7252 R 1.7 0.2 0:00.05 /usr/bin/php /home/mysite1/public_html/details.php 4896 mysite2 15 0 135m 13m 7728 S 1.3 0.2 0:00.04 /usr/bin/php /home/mysite2/public_html/company.php 6777 nobody 16 0 427m 21m 2712 S 1.3 0.3 0:47.76 /usr/local/apache/bin/httpd -k restart -DSSL Code (markup):
Look at your mysql queries, see if they are efficient or poorly written. Unless you're running enterprise class amounts of data or visitors, that is a lot for a mysql process to be using. Maybe bad indices.
I have problem to read and understand the "Code" I posted above :-( But so far I understand it must be mysite1, mysite2 and mysite3 that use all these resources. (I do have a few more sites on the server) But which one is worst ? ... So I know where to start trying to fix it ... And no, I'm not running enterprise ...
There's no way of knowing which site is causing the mysql drag in the text you gave us. I can see your php scripts. Might not even be direct from your site - might be a cron job or similar.
mysql 146.7 CPU usage, is their total server's clients CPU usage. They did not send you particular data as you are a shared hosting user. It seems they dont have mysql user monitor or, process limit installed on their server where as we do. Otherwise it never took more than 20~35 if its properly optimized and all custom modules to provide dedicated resource protection is installed on their server.