How to monitor MySQL Performance?
It seems that one of the biggest challenges for shared/business servers is server/database performance. Traffic spikes (Digg, Slashdot, etc.) and the server starts going crazy. For example, my client's site on Broughton has been experiencing some MySQL related problems. It seems to happen during periods of high traffic (eg. after a mass mailing to customers about a new sale, etc.). Naturally, this is frustrating.
First, I know that if we must have reliability, we should shell out the cash for a container or dedicated box. Given the current situation, I have a feeling that this isn't far off for us...
But the point of this post is to learn how we can optimize our server's performance - specifically MySQL. There doesn't seem to be a lot of attention paid to how we can monitor what's happening in MySQL. What queries are bogging down? Did we forget to index something that would speed things up? Has the size of the DB effected the performance? While we've certainly tried to account for everything in production, it's impossible to be sure how things are working without throwing a few thousand requests at the server at once.
After searching the TxD forum, KB, etc. I haven't found anything that details exactly what to do to monitor performance on TxD servers. I'd like to see something as simple as:
- SSH into account
- Get into Mysql admin
- Run whatever commands
- Examine the results for possible problems
- Possible solutions for problems
Once we know what the problems are, I'm sure we can modify our systems to speed things up. But, right now I'm not sure how to examine MySQL's performance.
Any thoughts would be greatly appreciated. And if general web-nerds like myself are unsure about this sort of thing, I'm sure many others are as well. Perhaps if we were all better informed we might be better about optimizing our code for performance in the first place.
Best,
-j
Offline
#2 2006-11-09 17:44:33
- jellisnyc
- New member
- Registered: 2006-07-09
- Posts: 10
- Expertise
Re: How to monitor MySQL Performance?
Update:
I have already google d around and found a few articles on the subject. This is the best I've found so far:
http://www.oreillynet.com/pub/a/Mysql /2006/10/19/Mysql -tools.html
I'll certainly be trying to sort through this stuff. But, the ideal would be for TxD admins to endorse a method/app that they know to be applicable to TxD servers. I'm sure TxD admins could come up with a quick list of usual MySQL bottlenecks. I'm curious...
-j
Offline
#3 2006-11-09 19:19:40
- mamash
- I Just Work Here
- From: Prague
- Registered: 2004-06-01
- Posts: 1856
- Website Expertise
Re: How to monitor MySQL Performance?
The problem here is that MySQL seriously lacks in auditing/monitoring features in general, especially in those that would be of benefit in shared hosting. Most of the solutions available simply assume that you're the only user out there, and that you want to see the performance of the entire MySQL server. MySQL doesn't let you keep any per-user or per-database statistics. Slow query log is something that can be parsed, but a lot of effort needs to be put into getting any good per-user data. Parsing a full query log is a huge pain and involves grepping through a gigabyte a day easily (on a shared hosting server), even for a simple thing like how many queries were run on a day. I hear that some of this may have gotten a bit better with 5.x, but we are not there yet.
Mytop is good in that you see if any queries take too long (e.g. several seconds) and if they do, you can have them EXPLAINed, which will show you where further optimization can be done. You'd be surprised how many folks do something like "SELECT * FROM table WHERE column < 'value'" on a table of several hundred thousands records with absolutely no indexing keys set up. It boils down to having as few queries as possible and have them finish as fast as possible.
We don't really have any good recommendation. I'm not aware of any good enough analysis tool for our purposes, most likely because MySQL doesn't provide the kind of data that such analysis could be run against.
