On IPs, hostnames, and MySQL

This is the first official post in my new category: MySQL Tips. Feel free to subscribe to the category-specific RSS feed, if you prefer.

First, for a bit of background information…

In MySQL, access is always granted based on the combination of username and hostname (or IP address, in some cases), and password—for example, jcole@foo.example.bar could be a valid username and hostname combination. As far as I know, this is a historical thing—MySQL authentication is based on username and hostname because it has always been that way.

In order to verify the “network credentials” of the connecting client, MySQL uses a “double reverse-DNS lookup” on the IP of the incoming connection. In short, MySQL first finds out the hostname using the IP address of the incoming connection via a reverse-DNS lookup using the gethostbyaddr() function or similar. It then resolves that hostname to its IP addresses via a forward-DNS lookup using the gethostbyname() function or similar. Finally it verifies that the original connecting IP is one of the IPs returned.

This can be a problem because reverse-DNS and forward-DNS lookups are fairly expensive, and block waiting on a reply from a DNS server. There are timeouts involved, but they are fairly long, I believe 30 seconds. In order to avoid this long overhead, MySQL has a hostname cache internally.

There are a few problems with the current implementation:

  • The hostname cache is statically sized at 128 entries1. If the cache gets full, the older entries will be purged. If you have more than 128 distinct client machines connecting to a single MySQL server, the cache becomes completely ineffective.
  • If a DNS server cannot be reached to resolve an IP address or hostname, the connection cannot be accepted, and in fact in some circumstances MySQL will cache the failure and refuse further connections from the same IP.
  • If the forward-DNS mapping and reverse-DNS mapping don’t match up, MySQL will refuse to accept the connection.
  • There is no way to see the contents of the hostname cache2. The only available SQL command dealing with it is FLUSH HOSTS which just clears the entire cache.
  • If a particular client machine fails connecting for max_connect_errors, that client (or rather, that IP address) will be refused further connections unless FLUSH HOSTS is run.
  • If libwrap (tcp wrappers) support is enabled in MySQL, that can often cause the reverse-DNS lookup to be done while MySQL is in single-threaded accept(), where a delay in resolution will mean blocking all other clients from connecting.

The whole situation means a few bad things for running busy production MySQL servers:

  • MySQL’s reliability is only as good as your DNS infrastructure. If DNS is down/flaky, so is MySQL.
  • If your network is flaky, it can mean that you quickly reach the default max_connect_errors of 10 and further connections from that client machine will be refused.

I would recommend a few things:

  1. Never ever allow connections to a MySQL server from the outside world. This opens up a huge potential for DNS-based DoS attacks on MySQL.
  2. Don’t enable libwrap support in MySQL, as this generally only makes things worse.
  3. Enable skip_name_resolve in your my.cnf. This will disable all hostname resolutions, period. All GRANTs must be based on IP address.
  4. Set max_connect_errors to something very high, say 99999999. This will avoid your servers getting sporadically blocked because of network or client flakiness.

Good luck, and I hope the first entry of MySQL Tips is useful!

1, 2 I have a patch for MySQL 4.1, which adds a SHOW HOST CACHE command, and a configurable global variable host_cache_size.

Plugin-based backup for MySQL

I’ve been working on a new project to fulfill a specific need: consistent, fast, cheap, and flexible backups for MySQL, for all storage engines1. To that end I’m creating a tool called dbsnapper—a plugin-based backup tool. The tool itself is very basic and handles a few jobs: getting configuration information from the user, running through a “run sheet” of different configurable tasks, and reporting status and errors to the user.

The tasks then—the actual backup steps—are fully configurable, via plugins. In fact, the whole process isn’t even MySQL specific, and can potentially be used for PostgreSQL2 and other database as well. Remember the requirements for backups (above):

  • Consistent—We need to do some locking inside MySQL to make sure that the backups are consistent, for both MyISAM and InnoDB tables. This generally means the FLUSH TABLES WITH READ LOCK command.
  • Fast—There are two ways to get fast, but they both involve snapshotting: either inside the database, or on the volume level. The best way to get a backup quickly is by using Linux’s LVM, the Logical Volume Manager, to take a snapshot of the whole filesystem. Using mysqldump for backups fails miserably on this point.
  • Cheap—Well, backups should be free, and open source. Sorry ibbackup, sorry commercial utilities, become open source and we’ll talk.
  • Flexible—Everyone wants to do something slightly different with their backups, and in order for them to use one common tool, that tool needs to be very flexible. Most backup tools for MySQL are completely inflexible (other than the destination of the backup files). People often have slightly different requirements, why not try to make a single tool work?

It’s possible to meet all of the above requirements right now, but you would likely have to write your own backup script. When writing that script, you would likely do the minimum to make it work in your environment. Why should everyone write their own? My project3, dbsnapper is designed from the start to handle backups in a flexible and configurable way—to allow the user to decide what tools and processes to use, but to do it for them.

Keep an eye out, I’ll blog again once I’ve published the code!

1 Yes, yes, I know about the blue-sky internal online backup plans. Need I mention that online backup was originally planned for 4.0? Then it was moved to 4.1, where it would definitely get done… then to 5.0, a major new version, surely it will get done then. Now it’s likely not going to make it in 5.1, and slotted for 5.2, as far as I know. In the end, even if it does get done, that still doesn’t help people who want to backup their 4.0 or 4.1 installations, which is very common.

2 If someone is interested in working on the plugins for PostgreSQL, let me know, and I’ll give you a nudge once the plugin API is stable!

3 It need not be only “my” project. Anyone interested in helping?

New Storage Engines: A welcome change

There’s been a lot of buzz lately about new storage engines (Solid’s SolidDB and Jim Starkey’s Falcon) being developed for MySQL. Quite a few people have asked me what I think about them, and if it’s really a seamless process to switch storage engines. Everybody still has Oracle’s acquisition of Innobase Oy fresh on their minds, so nobody is really terribly surprised by the recent announcements. As for my opinion on the matter, well, it’ll take some discussion. I was quoted in ComputerWorld‘s article MySQL to encourage partners to build data storage engines:

Jeremy Cole, who oversees about 8,000 installations of the open-source database at Yahoo Inc., said the Sunnyvale, Calif.-based Web firm uses MyISAM for applications mostly requiring the reading of data—and InnoDB when many users may be writing data simultaneously.

Cole called InnoDB “great,” but also said it is “somewhat poorly integrated” with MySQL, lacking several common features such as full-text search and online configuration changes, while poorly supporting “referential integrity,” which keeps the relationships between data tables consistent.

Furthermore, the only way to do “reasonably fast online hot backups” with InnoDB is a closed-source tool called ibbackup, which is now owned by Oracle.

“If a new storage engine offered InnoDB’s current feature set without the above problems, and was stable, I would switch in a heartbeat,” Cole said. However, he doesn’t expect any of the unnannounced storage engines “to really be ready for use for another year or so.”

I wanted to follow up and provide a bit more depth and context, and some of the technical details that were not completely appropriate for ComputerWorld’s audience:

InnoDB has been great in that it has row-level locking, supports multi-versioning and isolation. That solves quite a few problems for transactional heavy-write applications. However, it’s somewhat poorly integrated into MySQL, and has some problems of its own. To name a few:

  • InnoDB’s only option for reasonably fast online hot backup is a tool called ibbackup, which is closed-source, was previously owned and sold by Innobase Oy (for about $1400 per server) and is now owned by Oracle. I haven’t heard anything about what Oracle intends to do with ibbackup.
  • InnoDB doesn’t allow any online configuration changes, as the rest of MySQL (and MyISAM) does. (Technically: It doesn’t support using the SET command to change its configuration on-the-fly.)
  • InnoDB doesn’t support full-text search—the ability to search for words within text documents. This is a feature used by many web applications. Effectively, users must choose between no transactions, table level locking, and full-text search (MyISAM), or transactions, row-level locking, and no full-text search (InnoDB). It’s sometimes a very painful choice.
  • Innobase Oy / InnoDB made a very “cowboy” effort to support foreign keys a.k.a. “referential integrity”—instead of working with MySQL to support it as a in-built feature of MySQL itself, they basically duck-taped it onto InnoDB itself. This has caused a lot of headaches for many people, including but not limited to completely useless error messages and mysterious failures.
  • InnoDB’s tablespace management leaves a lot to be desired. There are no online tablespace management commands (CREATE TABLESPACE, DROP TABLESPACE, ALTER TABLESPACE). You have two options:

    • All data for all tables and databases is stored in a single common set of files, with no online management, no ability to shrink the tablespace, the only way to add space is to set one of the files (and only one!) to auto-extend, and no ability to move data between the tablespace files; or
    • Each table’s data is stored in a single .ibd file—this file will be as large as the size of the table, so if you have 500GB of data in one table, you have a single unmanageable 500GB file. This file can never be shrunk, it can only grow.

Switching storage engines in MySQL is actually almost as easy as they claim. However, I don’t expect any of these new storage engines to really be ready for use for another year or so. There are a lot of integration issues to be had when pulling in a new storage engine. InnoDB had quite a lot of bugs in the first year due to this as well.

Will I use them? Absolutely.

Stop the madness: SHOW STATUS

Are you a MySQL user? Have you tried 5.0? Did you notice that SHOW STATUS was giving you strange results?

Here’s the skinny: The SHOW STATUS command, which has been in MySQL, well forever, has had a long-standing feature request; to have the ability to report its metrics per-session. That functionality was added in MySQL 5.0, in the form of two new syntaxes: SHOW SESSION STATUS and SHOW GLOBAL STATUS to give the per-session and server-wide statistics, respectively. It’s great, and I’m happy to see it.

The problem comes in because of the default behaviour that was chosen for the basic SHOW STATUS with no SESSION or GLOBAL keyword—that is, the command that every MySQL DBA has been using for years—now defaults to per-session statistics.

Every tool, program, monitoring script, performance graph, etc., that uses SHOW STATUS (which is pretty much all of them) is broken in 5.0. Why? Well, for no reason, in my opinion. There is absolutely no advantage to defaulting to per-session statistics instead of the old standard of global statistics.

If you’re upset about this, add your comment to MySQL Bug #19093 and fight the good fight with me.