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 …

With innodb_file_per_table=1, InnoDB places every table’s data and indexes in a separate .ibd file, but there is still a “global” system tablespace, stored by default in a file named ibdata1. This contains some of each table’s data, such as the undo log and insert buffer. If it is fixed-size, you can fill it up and crash the server, as I’ve mentioned in a few recent blog posts.

In older versions of MySQL, the SHOW TABLE STATUS command showed the amount of space free in the tablespace as an entry in the Comment column. If you weren’t using innodb_file_per_table, you could use this to see how full your tablespace was.

The servers I’m managing use innodb_file_per_table=1, so I thought perhaps I can find out how full the system tablespace is by disabling innodb_file_per_table, creating a table, and enabling it again. …

I previously blogged about work I was doing on automatically finding problems in a MySQL server, with no hardcoded thresholds or predetermined indicators of what is “bad behavior.” I had to pause my studies on that for a while, due to time constraints. I’ve recently been able to resume and I’m happy to report that I’m making good progress.

One of the things I’ve done is a survey of existing literature on this subject. It turns out that the abnormality-detection techniques I’ve developed over the years are well-known in the operations research field. I reinvented some classic techniques used in Statistical Process Control (SPC). These include Shewhart Control Charts, …