Web Hosting Talk : Web Hosting Main Forums : Dedicated Server : What should I do for excessive MySQL usage?

[gamer1888]

Hello,
I'm currently on a server from Softlayer for which I'm paying $450 per month. A recent change on my website from simple HTML pages to a database driven script has made the website very slow.

The server load is 0.64 but most of my load is because of SQL. Is there any upgrade or action I can take to perhaps overcome this load? I'm a newbie to dedicated servers so I have no idea what exactly to do here.

Here is a copy/paste of the server load statistic:
mysql 27.68 3.02 0.0
Top Process %CPU 28.0 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.aaa.com.pid --skip-external-locking
Top Process %CPU 27.9 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.aaa.com.pid --skip-external-locking
abc abc.net 13.52 17.51 57.9
Top Process %CPU 12.5 httpd [www.abc.net] [/abc/mod/A/tracksearchmp3.html]
Top Process %CPU 12.0 httpd [www.abc.net] [/abc/mod/S/tracksearchmp3.html]
Top Process %CPU 8.1 httpd [www.abc.net] [/abc/genre/Comedy/titlegenre.html]
nobody 0.00 0.00 0.0
Top Process %CPU 12.5 /usr/local/apache/bin/httpd -DSSL
Top Process %CPU 12.0 /usr/local/apache/bin/httpd -DSSL
Top Process %CPU 8.1 /usr/local/apache/bin/httpd -DSSL
xyz xyz.com 0.13 0.28 0.0
Top Process %CPU 1.0 httpd [www.xyz.com] [/images/top_banner.gif]
Top Process %CPU 0.9 httpd [www.xyz.com] [/mp3/m-a/]
Top Process %CPU 0.8 httpd [www.xyz.com] [/mp3/index.php]
mailnull 0.01 0.09 0.0
Top Process %CPU 0.5 /usr/sbin/exim -bd -q60m
Top Process %CPU 0.2 /usr/sbin/exim -bd -q60m
Top Process %CPU 0.1 /usr/sbin/exim -bd -q60m
root 0.01 13.57 2.0
Top Process %CPU 1.0 /bin/bash /etc/init.d/apf stop
dbus 0.00 0.00 0.0
xfs 0.00 0.00 0.0
mailman 0.00 2.60 0.0
ntp 0.00 0.20 0.0
sshd 0.00 0.00 0.0
nails 0.00 0.00 0.0
named 0.00 0.20 0.0

In this situation, should I hire someone to look at these statistics and maybe kill the processes which are producing the load or does this seem okay and I should perhaps upgrade something in the server?

Any kind of help will be appreciated. Thank you

[PrezKennedy]

Not knowing what specs you have for that server... I'll take a wild guess here and say you probably need to move your MySQL data to a separate disk drive if it isn't already. If it is, I would suggest upgrading it to a SCSI drive. Since it sounds like your CPU isn't doing much work at all, your hard disk is probably the thing slowing you down here.

[mainlink]

Maybe there is something wrong with your my.cnf? Can you please post it and provide further information regarding OS and httpd.conf?

[MountedWeb]

I think there is a script for mysql caching that can help you.

[Axel]

stop paying attention to the cpu load - look into io load instead.

once your mem is not enough to cache the
"hot" part of db the sever becomes slow.

i suggest to work with db specialist directly
there are many things to be checked actually.
if you a noob to this - will be the best chice.

[gamer1888]

If anyone of you know any database specialist that can look into the server and perhaps take steps to lower down the load, or suggest a fix of any kind, please tell him to get in touch with me. I need help and I've had bad experiences with hired people. Some are too arrogant and the others can't haul their asses.

If anyone of you know any good programmers or admins please do suggest them to me. Would be of great help.

[ServerMins]

I always advise on optimizing a server before upgrading to hardware that will add to your monthly costs.

[Abu Mami]

Check your db queries - you should optimize them so that they use fewer resources.

Use the limit clause to reduce memory usage
The join clause can help to reduce query overhead
check your indexes

[Hugin]

You've not provided much info, so to directly answer the question ("What should I do for excessive MySQL usage?"), check your indexes.

Depending on your load, it would also be wise to either move MySQL to another server or at least have it run on a different disk, as others have mentioned above.