You used to use mk-query-digest to aggregate and report on MySQL’s slow query log. Then it got the ability to grab query events from polling SHOW PROCESSLIST. Next we thought, really, how hard can it be to implement the libmysql wire protocol, so we can sniff TCP packets? … it’s hard, but not that hard as it turns out. But why stop there, why not implement memcached protocol too? I think you can see where this is headed.

So now mk-query-digest is a tool that can understand and “do stuff with” a variety of query/response types of information. The latest is HTTP. HTTP traffic is just a query-response flow of events, perfectly suitable for response-time analysis. Example:

baron@kanga:~$ mk-query-digest sample-http.txt --type http
# 1.6s user time, 100ms system time, 14.20M rss, 17.07M vsz
# Overall: 56 total, 30 unique, 1.27 QPS, 0.06x concurrency ______________
# …

Last week I attended the Enterprise LAMP Summit and Camp in Nashville, Tennessee. I enjoyed the event and met or reconnected with a lot of great people. I was glad to be able to spend time with some folks from the Postgres community. My own sessions focused on MySQL.

During the Summit I tried to help people understand how to think about performance, and made the case that the Percona versions of the MySQL server are not only the highest-performance available, but uniquely provide the instrumentation necessary to follow a disciplined performance optimization process such as Method R or Goal-Driven Performance Optimization.

At the Camp the next day, there were several sessions on MySQL. My talk was later in the day, so I elected to skip …

Optimizing Oracle Performance

Optimizing Oracle Performance. By Cary Millsap, O’Reilly 2003. Page count: about 375 pages with appendices. (Here’s a link to the publisher’s site.)

This is easily one of the best books I’ve ever read on performance optimization. I’ve just finished reading it for the second-and-a-half time in two weeks, and I very rarely read a book more than once. I’ve been telling a lot of people about it.

Despite the title, it is actually not about Oracle performance. It is a book on how to optimize a) any system, …

Someone recently posted this to an email list as a sample of an interesting SHOW INNODB STATUS output:

mysql> SHOW ENGINE INNODB STATUS\G
          _______  _______
|\     /|(  ____ \(  ____ \
| )   ( || (    \/| (    \/
| |   | || (_____ | (__
| |   | |(_____  )|  __)
| |   | |      ) || (
| (___) |/\____) || (____/\
(_______)\_______)(_______/

 _______  _______  _______ _________ _______  _______  _______  _______
(  ____ )(  ___  )(  ____ \\__   __/(  ____ \(  ____ )(  ____ \(  ____ \
| (    )|| (   ) || (    \/   ) (   | (    \/| (    )|| (    \/| (    \/
| (____)|| |   | || (_____    | |   | |      | (____)|| (__    | (_____
|  _____)| |   | |(_____  )   | |   | | ____ |     __)|  __)   (_____  )
| (      | |   | |      ) |   | |   | | \_  )| (\ (   | (            ) |
| )      | (___) |/\____) |   | |   | (___) || ) \ \__| (____/\/\____) |
|/       (_______)\_______)   )_(   (_______)|/   \__/(_______/\_______) 

I thought it was worth …

MySQL Proxy is a really neat tool. I remember a few years back when I first saw Jan talking about it. Back in those days it was significantly different than it is now, but the concept remains the same: direct your database traffic through a man-in-the-middle. Chris Calender’s post on capturing erroneous queries with MySQL Proxy shows one use for Proxy. But wait. MySQL Proxy is just inspecting the MySQL protocol. And unless you’re using it for something else too, having a man in the middle to catch errors is like standing in the middle of the street and blocking traffic to count the cars on the street. Why don’t you stand on the sidewalk to count the cars instead?

Observing without interrupting

Maybe we can use tcpdump. If you search Google you’ll see lots of examples of using tcpdump and grep to extract queries from the MySQL protocol. These examples usually …

As an alternative to another recent blog post that answered the question “how can I truncate all the tables in my database,” I thought I’d show another way to do it, which does not use the INFORMATION_SCHEMA.

$ wget http://www.maatkit.org/get/mk-find
$ perl mk-find --exec 'TRUNCATE TABLE %D.%N'

The other example is how to alter MyISAM tables to be InnoDB. That one’s easy, too. Let’s alter all MyISAM tables in the ‘test’ database:

$ wget http://www.maatkit.org/get/mk-find
$ perl mk-find test --engine MyISAM --exec 'ALTER TABLE %D.%N ENGINE=InnoDB'

If you want to print out the commands instead of executing them, you can just use –printf instead of –exec.

Why would you do it this way instead of through the INFORMATION_SCHEMA database? I don’t think this can be …

I’ve released version 1.1.4 of my improved Cacti templates. Unlike the prior release, which was solely bug fixes, this one includes new graphs in the MySQL template. Some of the graphs are of data that’s exposed in standard MySQL versions, but some of it is available only in Percona’s high-performance builds of the MySQL database server. If you don’t have a Percona build, those graphs will just contain nothing, but there is no detrimental effect.

This template release is fully backwards compatible with the previous release. The upgrade process is to copy the new PHP file into place and import the new template file. You can then add the new graphs to your hosts.

I’ve also taken the opportunity to address many of the other open issues. Most of these were minor, such as a debug …

Ever seen this in SHOW INNODB STATUS and wondered what it means?

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 4634, seg size 4636,

I’ve never been quite sure, and Peter didn’t really clarify things himself, so I took a look at the source. If I’m not mistaken, the seg size is really one more than the number of records the insert buffer can hold, the free list length is the number that aren’t in use, and the size is just seg_size - (1 + free_list_len). These seem to be kept in lockstep as records are inserted and merged.

Related posts:

1. How to …

The Art of Capacity Planning

The Art of Capacity Planning. By John Allspaw, O’Reilly 2008. Page count: 130 pages. (Here’s a link to the publisher’s site.)

This is an outstanding book. As far as I know Ewen Fortune was the first Perconian to read it, and it’s been spreading amongst us since then. I got my copy last week, and read it last night when I couldn’t sleep for some reason. It took me about 90 minutes to read.

This book doesn’t teach in generalities — it shows you exactly what to do. Rather than outlining the process of capacity planning (and it is a process!) and then letting you …

I’ve just released version 1.1.3 of the Cacti templates I wrote for MySQL. This is a bug-fix release only, and affects only ss_get_mysql_stats.php. To upgrade from the previous release, upgrade ss_get_mysql_stats.php. Don’t forget to save and restore your configuration options, if any. (Note that there is a feature to help with this: you can keep configuration options in ss_get_mysql_stats.php.cnf to avoid making them in ss_get_mysql_stats.php.)

Next up: actual template changes! More graphs!

The changelog follows.

2009-10-24: version 1.1.3

        * This is a bug-fix release only, and contains no template changes.
        * To upgrade from the previous release, upgrade ss_get_mysql_stats.php.
        * MySQL 5.1 broke backwards compatibility with table_cache (issue 63).
        * Added a version number to the script (partial fix for issue …