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’ve released version 1.1.6 of the Better Cacti Templates project. This release includes a bunch of bug fixes (but not all of them!) and two new sets of graphs. One set is for disk I/O on GNU/Linux, and the other is a new set of templates for OpenVZ. I’m looking for feedback on both of those. This release also has a bunch of code-level features: much better test coverage (hooray!), and a refactored ss_get_by_ssh.php that makes it much easier to create new graphs and templates. The SSH-based templates also take advantage of the same caching as the MySQL templates, which makes them a lot more efficient.

There are upgrade instructions on the project wiki for this and all releases. There is also a comprehensive tutorial on how to create your own graphs and templates with this project. Use the project issue tracker to view and report issues, and use the project mailing list to discuss the templates and scripts.

The full changelog follows.

2010-01-10: version 1.1.6

  * Added OpenVZ graphs (--type openvz) (issue 95).
  * Added IO usage graphs (--type diskstats) (issue 97).
  * Added extra error-reporting (issue 110).
  * The $debug $debug_log options couldn't be set in the .cnf file (issue 110).
  * Added a --use-ssh option to ss_get_by_ssh.php (issue 66).
  * Added a debugging log to ss_get_by_ssh.php (issue 54).
  * Enabled caching of results in ss_get_by_ssh.php (issue 46).
  * Added a test suite for ss_get_by_ssh.php (issue 110).
  * The 'free' stats suffered from PHP's issues with big numbers (issue 102).
  * There was ambiguity (but no error) in SHOW STATUS overrides (issue 106).
  * It was hard to debug failures caused by missing ext/mysql (issue 105).
  * Code to make ss_get_mysql_stats.php testable was broken (issue 108).