Xaprb

These days I spend more time looking at /proc/diskstats than I do at iostat. The problem with iostat is that it lumps reads and writes together, and I want to see them separately. That’s really important on a database server (e.g. MySQL performance analysis).

It’s not easy to read /proc/diskstats by looking at them, though. So I usually do the following to get a nice readable table:

• Grep out the device I want to examine.
• Push that through “rel” from the Aspersa project.
• Add column headers, then format it with “align” from the same project.

Here’s a recipe. You might want to refer to the kernel iostat documentation too.

wget http://aspersa.googlecode.com/svn/trunk/rel
wget http://aspersa.googlecode.com/svn/trunk/align
chmod +x rel align
while sleep 1; do grep sdb1 /proc/diskstats; done > stats
# CTRL-C after a while
echo m m dev  reads   rd_mrg rd_sectors ms_reading \
  writes   wr_mrg     wr_sectors  ms_writing  cur_ios \
  ms_doing_io ms_weighted | cat - stats | ./rel | ./align
m  m dev      reads rd_mrg  rd_sectors ms_reading    writes wr_mrg  wr_sectors ms_writing cur_ios ms_doing_io ms_weighted
8 17 sdb1 233290130 310126 22472222903 2032292523 479678266 883257 35319718188 1491098806       0   675768709  3523591184
0  0 sdb0        80      0        2560       1049       236      1       13621        253       0         161        1302
0  0 sdb0       226      0        7232       1418       638      0       40156        235       0         224        1653
0  0 sdb0        39      0        1248        295       519      0       35440        573      17         196        1669
0  0 sdb0        73      0        2336       4031      2104      0      134480       3076     -17         908        6308
0  0 sdb0        33      0        1056        293         3      0         834          0       0         293         293
0  0 sdb0        35      0        1120        157         3      0         642          0       0         150         157
0  0 sdb0        36      0        1152        161         3      0         586          0       1         155         162
0  0 sdb0        36      0        1152        140         3      0         738          0       0         139         141
0  0 sdb0       208      0        6656       4514       630      0       40552       1002      -1         455        5514
0  0 sdb0        57      0        1824        547       485      0       35156        485      16         195        1566
0  0 sdb0        99      0        3168       4442      2067      0      133972       3491     -16         869        7403
0  0 sdb0        22      0         704        140        20      0        8801         12       0         144         152
0  0 sdb0        16      0         512         62         2      0         466          0       0          62          62

I like to write tools that make hard things easy, when possible. By and large, MySQL is easy and simple. But some simple things are too hard with MySQL. I want to change that, at least for the things that matter the most to me, and which I think I know how to fix.

I will probably write a lot about this. I have already written a number of rants blog posts about the lack of instrumentation in MySQL, and that is where I’ll probably continue to put most of my energy.

To begin with, imagine this simple scenario. You are a remote DBA. Your client says “New Relic is showing periods of slow response time from the database.” You connect to MySQL at the command line and try to troubleshoot. How do you catch the problem in action, from within the database itself? The following are no good:

• It doesn’t count to see the problem two minutes later by observing the application tier, as New Relic does. That’s too late, and it’s not from within the database.
• It’s also not good enough to catch just after the slow queries finish executing, which you can get from the slow query log if you have a realtime log analyzer running; that is a) also after the fact, and b) again not possible from within the database itself.
• Watching TCP traffic or using a proxy is similarly off limits.

This is a database, a complex piece of software — comparable to an operating system in some respects. It should be possible to know that response time is spiking while the slow queries are executing slowly, before they even finish and return their results to the application. This is a simple question that should be easy to answer.

It’s not currently possible inside MySQL, because MySQL doesn’t tell you how much time queries spend executing. It’s that simple. Fortunately the fix is equally simple: measure how long queries spend executing.

We can look at the lowly iostat (or /proc/diskstats in Linux) for an example of how this is possible to solve. We need a counter that shows the sum of execution time, including currently executing queries. I saw that the Facebook patch adds counters similar to this. Search for “Query timing” on that page. I want something slightly more complex, the way Linux’s IO counters work, because they add memory. Simple is really beautiful; a counter that has memory is an amazing thing, and you can apply Little’s Law to derive a surprising amount of information from it.

I might change my opinion about some of the more complex things that are being added to MySQL; much smarter people have, and I’m probably a hold-out because I’m not that smart. But I still see the need for simple combinations of counters and timers for essential metrics, which do not need complex queries or tools to use. I have a few more things on my wishlist, which I’ll write about later.

I was just reading up on the syntax for index hints in MySQL, and noticed this:

An index_name value need not be a full index name. It can be an unambiguous prefix of an index name. If a prefix is ambiguous, an error occurs.

I actually prefer not to have extra “syntactic sugar” features such as this. It helps avoid bugs and unexpected behavior. Even if I don’t use it intentionally, I can get bitten by it, if someone adds another index whose name has the same prefix as one that I already use:

mysql> create table t(a int primary key);
mysql> select * from t force index(PRIMAR);
Empty set (0.00 sec)

mysql> alter table t add key PRIMARY_2(a);

mysql> select * from t force index(PRIMAR);
ERROR 1176 (HY000): Key 'PRIMAR' doesn't exist in table 't'

I actually considered adding support for prefixes of command-line options to Maatkit, once upon a time. This way you’d be able to say --rep instead of spelling out the full option name; some of the options are very long-winded. This is pretty standard behavior, and even the MySQL command-line tools let you do it. But I came to my senses quickly when I realized that this would never let us rest easy about backwards and forwards compatibility. Even if it weren’t a potential problem, I think there are more important things to work on in Maatkit.