What tools/methods do you recommend to diagnose and profile MySQL in live production server?
My goal to test alternative ways of scaling up the system and see their influence on read/write timing, memory, CPU load, disk access etc. and to find bottlenecks.
First of all you should set up some kind of monitoring with e.g.:
Other may helpful tools: mytop innotop mtop maatkit
In addtion you should enable logging slow-queries in your my.cnf.
Befor you start to tune/change parameters you should create some kind of test plan and compare the before/after results to see wether your changes made sense or not.
This is something that I have worked on quite a bit.
MonYog - MySQL monitoring service. We use this in production. It is not free but has a lot of features, including alerts and historical data.
MySQL Enterprise Monitor - available with MySQL enterprise (i.e., not cheap)
Roll Your Own!
About the roll your own option:
We actually developed a really cool monitoring application that uses RRD tool (used by the common MRTG) and a combination of MySQL statistics, and system stats, such as iostat. This was not only a great exercise but gave us a ton of flexibility to monitor exactly what we want from a single interface.
Here is a Brief Description of some approaches to building your own stats.
One of our big motivations for rolling our own, even though we also use MonYog, was to track disk statistics. Disk i/o can be a major bottleneck, and the standard MySQL monitoring systems do not have i/o monitoring. We use iostat which is part of the systat package.
We have an interface that displays graphs of MySQL statistics next to disk i/o stats, allowing us to really get an overall picture of how the MySQL load is affecting disk i/o.
Before this, we really had no idea why our production applications were getting bogged down. We discovered that disk i/o was a major issue, and that MySQL was creating a lot of temporary tables on disk when we were running complex queries. We were able to optimize our queries and improve disk performance dramatically.