jcole’s weblog

Well, the conference is now over! I had a great time, and met a lot of awesome people. It was awesome, I truly enjoyed meeting many of you, and will definitely see you next year.

I’ve uploaded my photos and presentations for your enjoyment:

• MySQL UC 2006 Gallery
• Replication for Scaling and High Availability (ppt, pdf)
• MySQL GIS: Overview and Tools (ppt, pdf)

See you all next year!

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 entries¹. 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 cache². 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.

Yesterday, at the MySQL Users Conference 2006, Eric Bergen and I held a half-day tutorial on Replication for Scaling and High Availability. It went great! We had about 130 people, in my estimation—a full house!

I’ve put the slides online (2mb pdf, 650k ppt) for everyone interested in the content! Enjoy!

I’ve been working on a new project to fulfill a specific need: consistent, fast, cheap, and flexible backups for MySQL, for all storage engines¹. 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 PostgreSQL² 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 project³, 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!

¹ 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.

² 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!

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

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.

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.

I recently signed up with Vonage, since it seems pretty cool. They sent me the VoIP adapter via DHL, who picked it up on Thursday, April 6^th. It was sent 2^nd day delivery, which means it should have been delivered on Monday, April 10^th. Did I get it? Nope!

All through yesterday, April 10^th, the DHL website claimed:

Est. Delivery Date: 4/10/2006

Today, Tuesday, April 11^th, I figured I’d give them a call to see what’s up, and when I should expect to really receive the package, since the site still claims that the estimated delivery date is yesterday, which doesn’t inspire much confidence. The conversation went something like this:

DHL: Thanks for calling DHL, what can I help you with?
Me: I was sent a package which was supposed to be delivered yesterday, but I haven’t received it. I tracked the package on your website, and it still claims an estimated delivery date of yesterday, which can’t be right.
DHL: OK, can you give me the tracking number?
Me: OK, (reads tracking number)…
DHL: Well, we have a lot of packages and not all of them go out every day etc. etc. … your package is here at the sorting facility, it hasn’t gone out today.
Me: Uh, well, it was sent 2^nd day delivery, the 2^nd day was yesterday. Should I at least expect to receive it today?
DHL: Well, I can’t really tell you that. I don’t know if it will go out today, it’s still here… I don’t know if you’ll get it today.
Me: Don’t you have some sort of service guarantee, or the shipping is free?
DHL: Uh, uh, I don’t know, you’d have to talk to billing about that, I don’t know anything about that…

What the hell? You are DHL. Your only real business is moving other people’s stuff around. How is this considered customer service?

From the weekly beer:30 our group has on Fridays came this great quote, while we were talking about Guy1′s upcoming baby:

Guy1: (Talking about how ovulation testers are wonderful..)
Guy2: Are there any side effects?
Guy1: Uh, you pee on a stick. Side effect: You peed on your hand…
Guy2: Oh…

I managed to get Gmail‘s POP3 and SMTP support (via SSL/TLS, which they require) working on my spiffy new Sony Ericsson W600i, but not without a bit of headache.

After setting everything up, using their provided information, the W600i was complaining about not trusting the SSL certificates that Gmail uses, and would refuse to send mail because of it (although it would still receive mail, after asking you to acknowledge a stern warning). In order to get it working I needed to install two certificates on my phone:

• “Equifax Secure Certificate Authority” (direct link)
• “Thawte Premium Server CA” (ThawtePremiumServerCA.cer inside the zip file)

In order to get the phone to accept and install them, you have to use OBEX Object Push (aka “Send File…” under the Bluetooth menu in Mac OS X) to send the .cer files to the phone. The “Send File…” client will complain that your the device doesn’t accept the file type you are sending it; choose the “Send Anyway” option, it works fine. If you just transfer the files as regular files, you will see them on the phone, but will have no option (that I could find) to install them.

The phone is rockin’ now!

I’ve provided the above information here, because figuring out which certificates I needed, where to get them, and how to install them is like pulling teeth. I got each tiny bit of information from various different places. Hopefully this page will index well and help others.

First, a bit of background information… MySQL has a variable called tmp_table_size which is used whenever a temporary table is needed for many types of operations, but it’s mainly used for ORDER BY and GROUP BY. The tmp_table_size variable (in correlation with the max_heap_table_size variable) decide at what point to convert an in-memory (that is, HEAP storage engine) temporary table to an on-disk (that is, MyISAM) one.

So, if the result of your query will end up greater than the lesser of tmp_table_size or max_heap_table_size, once it reaches that point, it will be automatically converted to a MyISAM table, on-disk (in tmpdir, incidentally).

Here’s a scenario: You have a SELECT query, nothing particularly special. It isn’t any more complex than another query on the same tables, but it performs a bit worse. You put some load on it, and it performs miserably, even putting the server in pain in the process. You check what’s happening… hmm, lots of blocks being written? Why would there be any blocks written, this is a SELECT!

Does the SELECT have a BLOB or TEXT column in it? Are you using a GROUP BY or ORDER BY that can’t use the same index that MySQL is choosing? (Check EXPLAIN for Using temporary or Using filesort.)

In that case, MySQL will need to create a temporary table to do the sort. Since your query contains a BLOB or TEXT column, the temporary table will have to be created as a MyISAM table, as the HEAP storage engine doesn’t support BLOB or TEXT (or even VARCHAR, for that matter, which is changed to CHAR automatically).

This will mean a lot of extra writes to the disk, for no good reason. What can you do about it?

Short term, for 4.1+: Abuse a subquery in the FROM to read the primary key of the table with the BLOB/TEXT and do the sorting that is necessary. In the outer query, select the full row, with BLOB/TEXT and return it, but don’t use ORDER BY.

Long term: Support for BLOB and TEXT need to be added to the HEAP storage engine.

What if you need to ORDER BY or GROUP BY the BLOB or TEXT column? Well, you’re screwed. Think about redesigning your database. (Hint: Add a column containing the first up to 255 bytes of the column as a VARCHAR and sort on that instead…)

Good luck!