The latest from the MySQL community

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, …

The second edition of High Performance MySQL has 27 reviews on Amazon, but the third edition only has 5 so far. By this point I assume many of you have a copy and have read it cover to cover. I’d really appreciate your reviews — when purchasing, people look not only at the star rating but at the number of reviews. You can create a review here. And thanks!

Further Reading:

• Are you sure you’re reading the second edition of High Performance MySQL?
• Update on High Performance MySQL, 2nd Edition …

MySQL is a great database server. It has lots of flaws, but if you work with its strong points and try to minimize its weaknesses, it works fantastically well for a lot of use cases. Unfortunately, if you bang on its weak points, sometimes you get hit by falling bricks.

A couple of its riskiest weak points relate to unavailability of an expected resource, particularly disk space and memory. For example, Stewart Smith has blogged about the interesting results you can get if you intentionally make malloc() fail. I think many of us probably have some experience with filling up the disk and causing the server to hang, breaking replication, or crashing something.

I’m managing a couple of servers that have taught me some interesting new lessons along these lines. They use innodb_file_per_table, but their main (shared) tablespace is fixed-size, and not very big. The tablespace tends to fill up when there are long-running transactions …

I have a fairly lightly loaded MySQL server with a few tables that are updated every five minutes. Other than these updates, there are very few queries run against the database. The data is queried just a few times per month. Ever so often, one of the more complicated queries will result in the process getting hung in the "copying to tmp table" state. To be honest, the queries that get hung aren't even that complicated. Usually there's one or two joins, a GROUP BY, and an ORDER BY.

I was thinking recently about what a DBA does, and decided to blog about what I think a DBA could/should do. Most DBAs I know are mired in day-to-day firefighting and time-consuming tedium. This forces them to operate in reactive mode (because they don’t have enough time to “get caught up”), and keeps them from more valuable things they could be doing. Here’s my short and incomplete list:

1. Working with the developers (programmers) to help architect upcoming projects. If the DBA leaves design to the developers, then suboptimal designs might be found after the fact. This often happens after deploying to production, where the design impacts the business. Without early input, the DBA also has no chance to assess and prepare for future needs.
2. Teaching developers how to work with the database. Many developers struggle to understand databases and SQL, and are unable to fully optimize the queries they write. They …