I’ve just finished presenting my work on adaptive fault detection at Surge and Percona Live NYC. My slides are available on Slideshare, and embedded below.

Adaptive Fault Detection from xaprb

Further Reading:

• Products that scale linearly to hundreds of servers
• …

MySQL 5.6 will have “checksums in the binary log,” which can be variously described, but one phrase I’ve heard a few times is, loosely, that it helps ensure replication integrity. This isn’t specific enough to make it clear what it does, and when I’ve talked about pt-table-checksum and its purpose (for example, on webinars), people often ask whether pt-table-checksum will be obsoleted by replication checksums in MySQL 5.6. The answer is no, they do completely different things. But it’s kind of confusing, a bit like semi-synchronous replication in that regard.

pt-table-checksum ensures that your replicas have the same logical dataset as their masters. They can drift for any number …

Have you seen Damn Cool Algorithms: Cardinality Estimation yet? If not, take a few minutes and read through it. Now, what if we try using that approach instead of COUNT(DISTINCT) in MySQL to see how many distinct values there are in a column?

I recently needed this information in real life, and the table is large with many duplicate values. The column is some 32-character hex string, a hash value that represents a session ID. I’ll call the column sess_id. I wanted to know how many distinct values it had, but I thought it would be cool (damn cool, really) to try this approach and see what happened.

I read the blog post, convinced myself that it made sense, and tried to code it. Here’s my rough translation of the algorithm into MySQL-speak. Note that I’m using crc32(), which may not be a great choice …

High Performance MySQL has a long discussion on “sharding,” examining many options and their benefits and drawbacks. What does sharding look like in the real world?

Years ago I helped shard a MySQL-based application, partitioning its data across multiple database servers. It was already pretty large and significantly complex, so as usual for applications that aren’t designed with sharding in mind from day one, a major consideration for sharding was to make the migration strategy workable and minimize the disruption to the application code. It’s never easy to build this in after the fact, but there is a clear line between approaches that keep the business running and those that don’t.

We chose to shard by client. Each of the major tables had a client column already, and clients could be grouped onto servers without much effort. In addition, we maintained a one-to-one mapping of application servers with the database servers, …

After Gavin Towey’s recent blog post about Percona Server’s buffer pool dump locking the server for the duration of the operation, I thought I should re-examine MySQL 5.6′s implementation of a similar feature. When InnoDB engineers first announced the feature, I didn’t think it was complete enough to serve a DBA’s needs fully.

If you’re not familiar with this topic, MySQL 5.6 will allow the DBA to save the IDs of the database pages that are in the buffer pool, and reload the pages later. This technique can help a server to warm up in minutes instead of hours after a restart or failover.

I read through the documentation, and it looks good. I still think it might be good to have a built-in …

Github’s recent post-mortem is well worth reading. They had a series of interrelated failures that caused their MySQL servers to become unavailable. The money quote:

The automated failover of our main production database could be described as the root cause of both of these downtime events. In each situation in which that occurred, if any member of our operations team had been asked if the failover should have been performed, the answer would have been a resounding no. There are many situations in which automated failover is an excellent strategy for ensuring the availability of a service. After careful consideration, we’ve determined that ensuring the availability of our primary production database is not one of these situations.

Most automated failover tools receive a lot of engineering effort to answer questions like these: Is the …

One of the MySQL servers I help manage was encountering some problems with a full data directory. It was a bit mysterious, because we couldn’t find any files to account for the increased usage. Here are some things we checked:

1. A recursive ls -l didn’t show any more, or larger, files than usual.
2. Using lsof and looking at the SIZE column didn’t either.
3. There were not enough temporary files or tables open (as shown by lsof) to account for the disk space.

Oddly, someone discovered that FLUSH TABLES would drop disk usage by about 15GB in a fraction of a second, allowing the server to continue running without problems.

I carefully measured all of the items in the above list before and after FLUSH TABLES. No doubt about it: no files went away, no files shrank, yet df and du showed the difference in the space free and …

If you’ve ever looked at the lsof or listing of /proc/$pid/fd for a running MySQL server, you’ve probably seen files like these:

# ls -l /proc/$(pidof mysqld)/fd/* | grep tmp
lrwx------ 1 root root 64 Sep 12 10:21 /proc/17222/fd/18 -> /var/lib/mysql/tmp/ibDOy0eD (deleted)
lrwx------ 1 root root 64 Sep 12 10:21 /proc/17222/fd/323 -> /var/lib/mysql/tmp/MLhfWsbz (deleted)
lrwx------ 1 root root 64 Sep 12 10:21 /proc/17222/fd/6 -> /var/lib/mysql/tmp/ib65H6A5 (deleted)
lrwx------ 1 root root 64 Sep 12 10:21 /proc/17222/fd/7 -> /var/lib/mysql/tmp/ibllu2yi (deleted)
lrwx------ 1 root root 64 Sep 12 10:21 /proc/17222/fd/8 -> /var/lib/mysql/tmp/ib9yRYwv (deleted)
lrwx------ 1 root root 64 Sep 12 10:21 /proc/17222/fd/9 -> /var/lib/mysql/tmp/ibhUCeRO (deleted)

What are those? It’s not hard to find out, actually. Just open them and look at them! The ib* files are InnoDB’s temporary …

On the lighter side: I’ve always had trouble with mysqldump’s expected syntax. You know, as the author of a book and all that, you might think I can get this to work. But pretty much every time I run this tool, it humiliates me. Witness:

$ mysqldump --host localhost --password secr3t --all-databases
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

Alas.

Further Reading:

• A review of SecoBackup Amazon S3 backups for MySQL
• Introducing MySQL Parallel Restore
• …

I’ve just released innotop version 1.9.0. This version fixes a lot of bugs, makes the tool work better when monitoring dozens of MySQL servers, and adds two new modes: a Health Dashboard and an InnoDB Blockers/Blocked mode.

Further Reading:

• A new dashboard for innotop
• innotop version 1.8.0 released
• Version 0.1.106 of innotop MySQL/InnoDB monitor released
• Getting ready for a new …