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

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

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

iostat is one of the most important tools for measuring disk performance, which of course is very relevant for database administrators, whether your chosen database is Postgres, MySQL, Oracle, or anything else that runs on GNU/Linux. Have you ever wondered where statistics like await (average wait for the request to complete) come from? If you look at the disk statistics the Linux kernel makes available through files such as /proc/diskstats, you won’t see await there. How does iostat compute await? For that matter, how does it compute the average queue size, service time, and utilization? This blog post will show you how that’s computed.

First, let’s look at the fields in /proc/diskstats. The order and location varies between kernels, but the following

Here is a situation I’ve run into a few times when dealing with mysql databases. We’re trying to run a one-off query against a high-traffic, large table and the WHERE condition is against a non-indexed field. Let’s say our table is 5GB in size. We issue the following:

SELECT count(*) from five_gb_myisam_table WHERE non_idx_field = 'asdf';

and we wait…

and wait some more.

5GB is not a small table, but this ideally should not take more than a few minutes on a relatively modern system.

iostat is your friend

In cases like this, iostat -x 5 is your friend. While the query was running, this was a typical 5 second interval:

avg-cpu: %user %nice %sys %iowait %idle
2.30 0.00 1.30 96.40 0.00

Device: rrqm/s wrqm/s r/s w/s