What's choking the server?

[michaellunsford]

I have a leased, non-managed, server. The company who owns the server tells me it's my responsibility to figure out what's getting my system load (sometimes in the 60's) that high. But, just as I get frustrated and ready to reboot the server, it just starts dropping again. Eventually (like 5 minutes) the 2-minute mark sometimes goes back down to 0.05 or lower.

 

So, I try what I know to try. "top -c" has a bunch of stuff in it, but nothing is really high in either ram or cpu. When it cooks, it's always mysqld. Okay, so some website on the server is probably just getting blasted - but which one? And, which page is getting nailed so hard that it's throwing mysql into a tizzy? This information would be infinitely valuable in the creation of a plan to defeat the problem.

 

Stumped on where to find this information?

[steviewdr]

If you think it could be mysql, then this could quite possibly be the case.

 

Google for mysqltuner.pl and mysqlreport.

These scripts will report mysql slow queries and if you have enough memory set for mysql in mysql.cnf.

 

 

I HIGHLY recommend you install munin and munin-node.

There are centos, debian packages etc. etc.

See: http://brage.sunsite.dk/munin/dotsrc.org/munin.dotsrc.org.html

http://www.gnegg.ch/2009/01/monitoring-servers-with-munin/

 

-steve

 

[michaellunsford]

There were some configuration issues it mentioned - cache sizes and such - that were pretty easy to get fixed in the my.cnf. However, the following "general" list doesn't have any indication on how to accomplish.

 

    Reduce your overall MySQL memory footprint for system stability

    Enable the slow query log to troubleshoot bad queries

 

Any idea how to accomplish the two above?

 

One thing it also mentioned was the possibility that some database connections were being left open. So, I have started digging through the heaver traffic sites and making sure all connections close at the end of their usefulness. I've already found a few ajax pages that had left their connections open, so hopefully this will fix the immediate problem.

[steviewdr]

You should be logging mysql slow queries.

 

Add or uncomment the following line in your my.cnf:

log-slow-queries        = /var/log/mysql/mysql-slow.log

Then watch that file.

 

As I mentioned earlier, munin would be very very useful. It will graph for you mysql connections in a 5 minute period.

 

-steve

[hostman]

Often running something simple such as "mysqladmin processlist" might enlighten you on which database has lots of connections open the moment load creeps up.