MySQL Query Profiling Tools — part 0, Maatkit Query Profiler

Dec 18, 2007 / By Sheeri Cabral

Tags:

Today I’ve been checking out a new client environment. My mission is to figure out (cold) some of the characteristics of the queries being run, and particularly if they’re “good” or “bad”. In my arsenal of “tools I really want to check out” has been Maatkit‘s Query Profiler.

They’re very different tools. Maatkit’s query profiler profiles a batch of queries, without granularity (at least not the way I ran it) to see what query is doing what. So I ran this against a production machine:
(I ran the query profiler for a while and then hit “enter” (apparently after about 17 minutes))

> ./mk-query-profiler --external --host localhost --user <user> --password <password>
 Press <enter> when the external program is finished
 +----------------------------------------------------------+
 |                     1 (1028.2091 sec)                    |
 +----------------------------------------------------------+

__ Overall stats _______________________ Value _____________
 Total elapsed time                     1028.209
 Questions                               882

COMMIT                                 72
 DELETE                                 18
 DELETE MULTI                            0
 INSERT                                 18
 INSERT SELECT                           0
 REPLACE                                 0
 REPLACE SELECT                          0
 SELECT                                 18
 UPDATE                                  0
 UPDATE MULTI                            0
 Data into server                      52133
 Data out of server                   694261

__ Table and index accesses ____________ Value _____________
 Table locks acquired                     54
 Table scans                             135
 Join                                    0
 Index range scans                         0
 Join without check                      0
 Join with check                         0
 Rows sorted                               0
 Range sorts                             0
 Merge passes                            0
 Table scans                             0
 Potential filesorts                     0
 Query cache
 Hits                                  288
 Inserts                                18
 Invalidations                           0

This tells me a few things — there are many table scans and few joins, meaning the application is probably doing the work of the joining, and that there are a lot of table scans happening that are likely needless. The query cache is great, with few invalidations and a bunch of indexes. It has room for growth, so if other memory parameters need more memory there is “extra” in the query cache”.

There were 882 total queries/USE/SHOW statements sent, of which 72 were COMMITs, and there were 18 each of INSERT, SELECT and DELETE. This to me says again that the application is doing all the processing, and as such perhaps those queries have logic that could be moved into the database. For instance, using INSERT IGNORE instead of a separate SELECT and INSERT.

So I get a good overall picture of the database activity with that.

Special thanks to Baron, Maatkit is great! I also could compile the programs without needing to install them anywhere other than my home directory, so I did not need root on the machine. There are plenty of options, the learning curve is *really* low on this, so experiment!

7 Responses to “MySQL Query Profiling Tools — part 0, Maatkit Query Profiler”

  • Nice post Sheeri – I’m interested to see what MySQL DBA’s have in their tools arsenal and compare it to Oracle.

    This particular tool (at least, the part described here) brought to the surface the memory of excitement that I had first running UTLBSTAT/UTLESTAT in Oracle 7 and 8. Cool ratios and number of statistics I could manipulate to produce even “cooler” ratios.

    But that was a while ago… and then later I read several papers and books that introduced performance tuning based on response time profiling. This basically revolutionized my understanding of performance tuning, quickly put things into the right perspective and cleaned up lots of confusion.

    So what I see in MySQL performance tools now? Oracle tools and methods used 10 years ago! Why would MySQL go this long way and not take a shortcut?

    Is there anyone working on instrumentation of MySQL so that a DBA would be able to profile selected activities and see where the time is spent instead of guessing what’s worse – 100 table scans or 42 joins? Maybe there are such tools but I’m just not aware.

    I think there won’t be any “external” tools without proper support from MySQL engine. On the other hand, MySQL source is available and one can compile it with some code profiler or whatever suitable debugging options but I bet one should be a MySQL developer to be able to utilize such capabilities.

  • Sheri, the “driving force” behind any tuning is (or at least should be) Amdahl’s Law which just can’t be applied based on a numbers above (unless you have a crystal ball next to you).

    While someone could tell what I’m too idealistic regarding instrumentation degree what I’m willing to expect in software (I’m not making this up!), proper instrumentation is a key to any tuning. But, perhaps, I’ve spent too much time dealing with extremely large system where guess-based tuning based on incomplete and/or irrelevant stats lead to anything but problem solving.

  • Sheeri Cabral says:

    Alex G — The Query Profiler also allows using a file instead of –external, so you can actually run a batch of queries and see how that batch performs. I used the Query Profiler for my specific needs — that is, seeing a server cold and how it performs overall “in general’. Certainly from a development perspective I want to be able to run a batch of queries to see how it performs, and the Query Profiler does indeed allow that to be done.

    As well, the MySQL Proxy can be scripted to do response time profiling. I’d love to hear more about what kinds of performance tuning are available with Oracle.

    The fact is that the low learning curve for MySQL is a blessing and a curse — it means that it’s easy to adopt, which is how it got to be the most popular open source database, but it also means that there are some awfully written queries out there, so performance tuning is usually a matter of fixing a few bad queries, which are easily found in a slow query log.

    Keep in mind, too, that this is a 3rd party tool, and the mysqldumpslow that comes with mysql has even fewer features.

  • Sheeri Cabral says:

    Alex F. — you’re right, of course — but for the first pass getting into things, it’s not a bad start. It is a guess, but you need to start with a “sense” of things, and that’s what this does.

  • Not sure about short learning curve to the performance tuning based on counters – one should really understand how MySQL works and nature of the application to make sensible conclusions bases on number of various counters.

    If that would be bases on time profile than even novice DBA could conclude where the time is spent and what part requires attention.

    In the meantime, one of my clients came back to me on IM and pointed out standard MySQL profiling coming with 5.0.37 (thanks Gennady) – SHOW PROFILES and SHOW PROFILE. Still not exactly full response time profile but it looks promising as its main statistics is time-based — duration.

    Now, I’d love to play with that a bit more and see how it actually works in real life.

  • […] in the sense that if they exist they are not slow. But given that this is the same client as in the previous installment of this series, I can surmise that there are not a lot of joins, period, and I can work with the […]

  • http://www.php-trivandrum.org/open-php-myprofiler

    Open PHP MyProfiler is just a trial to run query profiling on a php-mysql application, without changing the architecture too much. The profiler is open and downloadable, though the analzer, is just a mockup or a bare one and not yet ripe to be opened up.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>