Kazuho@Cybozu Labs

What comes up to your mind when you hear the phrase "MySQL performance tuning?"  Most of the discussions covering the topic are things like server parameter tuning and blog entries describing the use of the "EXPLAIN" command.  However, it is too time-consuming to check every single SQL query.  A better approach is at first to determine the SQL queries that are actually consuming the server resources, and then to optimize those queries.

So the question is how to find out the bottleneck queries.  At MySQL Conference & Expo 2009, Mark Callaghan explained in his keynote that Google was taking a statistical approach using the "SHOW PROCESSLIST" as a solution.  The same command is mentioned as a rival approach in the webpages of MySQL Query Analyzer as well.  Today, having a need for something alike, I wrote a set of tiny script files under the name "mprofile" that does the same thing.  It can be used as follows.

Sampling SQL queries

Use mpdump to sample running queries from mysqld periodically.  The default setting is to fetch 1,000 samples in 0.1 second interval  (in other words, it would take a little more than 100 seconds to execute).  Please use the --help option to find out the way to specify the number of samples, the interval, and connection parameters to mysqld can be specified as command line parameters.