Help!! Mysql is causing heavy server load

yeah mysql server sometimes can be down so maybe your hosting admin got no choice : suspend or got down, both are the same to all of you (server and user).

 

its your wordpress theme causing more mysql usage. Retreat to a simpler theme, which might reduce SQL queries and thereby mysql load.
VPS is good, but considering your traffic its not worth it. I suggest you do the above.

 

Hi,

Try to optimize your PHP code and your MySQL requests, don't forget to create indexes for your MySQL tables. If the problem is not resolved, then move to another webhost or upgrade your existing account.

 

I had a similar problem where MySQL would just randomly get hogged up by a random script under one of my virtual hosts. I could never seem to target it because there was always no real pattern leading up to its occurrence. My assumption later was that someone found an exploit in my clients open sourced directory listing software and was causing a ddos. Either way, I made this to bring the server back to normal when I started to see the problem arise before I realized what the root of the problem actually was.

http://ckozler.net/?p=3

It executes SHOW FULL PROCESSLIST MySQL query and then loops through killing each ID based on dead/lingering MySQL connections. Its pretty effective and quick. Though your problem may not be dead and lingering MySQL connections, it can be modified accordingly.

Dont forget to make sure you close all MySQL connections that you open and avoid using mysql_pconnect (persistent connect) wherever possible unless needed.

 

You could also check out a number of on-page factors that might increase the server load draw. This could come from a lot of tables, images, or even your headers/menus/etc. that might cause additional load time.

One thing I've found that has helped in the past has been to run your site primarily from css and check each size of the files you are developing through a compiler program. Even Dreamweaver could do this, though there are a lot of pros and cons out there about it (as many people I'm sure will tell you).

Making some small tweaks to your site here and there could even help you with your ranking as spiders are more likely to index easier to draw from links and pages so it might be worth your time to simply go through the code and see if you can make it work in your favor, shaving off any unnecessary items, than adding additional plugins that may cause wp to use more of your server's processing to run each program.

 

100% agree. I actually digged through codes of every page and dropped most of unnecessary tables and tags, and that helped a lot.
Fortunately server load is doing much better now and my host has ceased fire for the time being!
I appreciate your help and indeed everyone else who recommended a solution. They were all helpful and I also learned a lot from you guys.
Cheers.

 

No, it's not the super cache plugin that I'm referring too. I'm talking about the object cache script for memcached:

http://plugins.trac.wordpress.org/browser/memcached/trunk/object-cache.php