freebsd mysql.so,FreeBSD or Linux for your MySQL Server?

This is an issue that comes up all the time at work. It is an issue for roughly four reasons:

Yahoo is a FreeBSD shop

Someone has heard that MySQL runs better on Linux

Someone knows that we run some of our servers on Linux rather than FreeBSD, and Yahoo is a FreeBSD shop (see #1), so they start to infer #2.

Most of the MySQL development is done on Linux and Windows. The only "FreeBSD guy" at MySQL AB seems to have left sometime in the last year or so.

This is an attempt to answer that question for folks at work and elsewhere.

The Short Version

I'll start with the simple answer.

If you don't have a preference, use Linux. You'll be happier. Trust me.

If, on the other hand, you'd like to stick with FreeBSD for some reason (and there are good reasons--one is noted later), read on.

The Problem: Threading

Having said that, let's look at the issues a little more closely. FreeBSD is a great operating system, but it has one important weakness that MySQL is very good at highlighting--threading support. FreeBSD's threads implementation isn't very good. I won't say that it sucks, because it could be a lot worse.

How Linux Threads

Threads on Linux are created using the clone() call, which is similar to fork(). You end up with a separate process in the process table, but the memory is shared among the processes. This means that the kernel gets involved in scheduling. From the kernel's point of view, they're all just processes. Many folks refer to this as kernel threading even though it's different than what the Solaris kernel does (for example). Some call that real kernel threading. :-)

Anyway, the LinuxThreads FAQ goes into a bit more detail. LinuxThreads is a library, available for other platforms--including FreeBSD. We'll come back to that in a bit.

How FreeBSD Threads

FreeBSD implements user-level threads. That means the kernel isn't aware of the threads and doesn't get involved in scheduling. Instead all the work is done in user space rather than kernel space. When your run top or ps on a machine that does this, unlike like in Linux, you'll see a single process rather than one per thread.

This is discussed a bit here in relation to LinuxThreads, which we'll get to.

Note that in FreeBSD 5.x, this may all be fixed. Time will tell. There is lot of working going on in the area of threading and kernel scheduling for FreeBSD 5.x.

FreeBSD's Threading Problems

Having run MySQL on various flavors of FreeBSD for the last 2.5 years, I can say that it has been a bumpy ride at times. Versions older than 4.2 (or maybe 4.3) have serious problems. I had a test case that could kill a MySQL server running on older versions of FreeBSD in a matter of minutes.

On more modern FreeBSD, things are better but not perfect. All the problems we've encountered at Yahoo seem to fall into 4 buckets.

1. Non-thread safe DNS Lookups

Certain operations are not thread-safe on FreeBSD. A fine example of that is gethostbyname(), which MySQL calls to convert host names in to IP addresses. Usually this happens for each new connection to the server and whenever MySQL needs to contact another machine--typically a replication slave connecting to its master.

Based on our testing, the only truly safe way to operate is to use the --skip-name-resolve flag for starting mysqld AND specifying the IP address of the master instead of the hostname. That virtually eliminates the need for MySQL to call gethostbyname().

The symptom of this problem is that the mysqld will consume all the available CPU time even when there are few (if any) queries running. You can try and kill -6 the mysqld process and then run it thru gdb to get a backtrace. You'll likely see something like this:

#0  0x829c94c in _thread_kern_sched_state_unlock () at ./cp/tinfo2.cc:300

#1  0x829c0e0 in _thread_kern_sched () at ./cp/tinfo2.cc:300

#2  0x829c787 in _thread_kern_sched_state () at ./cp/tinfo2.cc:300

#3  0x82c5fdc in kevent () at ./cp/tinfo2.cc:300

#4  0x82c5a4f in res_send () at ./cp/tinfo2.cc:300

#5  0x82a4308 in res_query () at ./cp/tinfo2.cc:300

#6  0x82a4737 in res_querydomain () at ./cp/tinfo2.cc:300

#7  0x82a44bb in res_search () at ./cp/tinfo2.cc:300

#8  0x82a9a00 in _gethostbydnsname () at ./cp/tinfo2.cc:300

#9  0x82a8739 in gethostbyname2 () at ./cp/tinfo2.cc:300

#10 0x82a86d4 in gethostbyname () at ./cp/tinfo2.cc:300

#11 0x8275fc4 in my_gethostbyname_r (

name=0x1b5f79a8 "your_hostanme", result=0x9fa659b8,

buffer=0x9fa651b8 "\032", buflen=2048, h_errnop=0x9fa651b0)

at my_gethostbyname.c:108

#12 0x80d6fbd in mc_mysql_connect ()

#13 0x80d6b37 in mc_mysql_reconnect ()

#14 0x80d4506 in safe_reconnect ()

#15 0x80d3fb8 in handle_slave ()

#16 0x828ffa5 in _thread_start () at ./cp/tinfo2.cc:300

#17 0x0 in ?? ()

If you see that, get rid of DNS lookups.

2. Unfair Scheduling

We've seen instances when a single MySQL thread doing a lot of I/O work (deleting a lot of rows from a table) seems to monopolize all the CPU time. When this happens, even the most trivial SELECT queries against unrelated tables can take a long time or even block until the heavy I/O work is complete. It feels like somehow the I/O thread is unfairly getting more of the CPU time.

3. High Load

Even without the other two problems, I've seen MySQL servers on FreeBSD start to act strangely under real stress--meaning a reasonable number of clients (30 or more) that are really pounding on MySQL. If I had a test case that could always reproduce it, I'd certainly make it available. But the fact is that it seems fairly random.

Luckily most of our MySQL installations don't have that problem because they're not hit really hard or they're not hit that hard for very long. If I had to guess, I'd say that 95% of folks never see this problem. The remaining %5, of course, are rather upset when they do.

4. No SMP Support

Because the threads are not managed by the kernel, there's no way to make use of multiple CPUs. The scheduler can't get two threads of the same process running on two CPUs at the same time. So you're limited to either running on single CPU machines or running multiple instances of MySQL on the same physical machine--and that involves some interesting management problems that I'd rather not deal with.

5. Missing Locks

This may somehow be related to problem #2. We've seen cases where several threads want to run queries against a single MyISAM table. When one thread is doing a lot of work, such as a massive delete, all the readers wait for a shared lock on the table. In order for that to happen, the writer needs to finish and release its exclusive lock. The strange thing is that sometimes the writer finishes and even disconnects, but the readers are all stuck waiting for a lock according to SHOW FULL PROCESSLIST.

When that happens, nobody can query the table. It's effectively off-limits. It's as if all the readers somehow "missed" the fact that the writer is done. Or maybe the writer's lock didn't get released properly. The only solution is to kill all the locked threads. Once that happens, it will usually begin to work normally.

LinuxThreads on FreeBSD

The most popular solution is to recompile MySQL and link it with the LinuxThreads library. Doing so gives you the benefits of kernel assisted "threading" (fair scheduling, SMP, and not needing thread-safe gethostbyname()).

Sounds perfect, right? Not exactly. It turns out that this does solve those problems quite well. But I've found at least one thing that stopped working with a LinuxThreads version of MySQL on FreeBSD and another occasional but very annoying problem.

MySQL's wait_timeout setting can be used to automatically close the connection (and terminate the thread) of a client who as been idle long than N seconds. It simply stops working with LinuxThreads. I don't know why (yet).

The more important bug is rather mysterious. Every once in a while, a LinuxThreads-enabled MySQL 4.0.x server will decided that no databases exist anymore. All clients will be rejected with an "Unknown database 'foo'" message (where "foo" is the name of the database you'd like to connect to ). The SQL slave thread stops. The only solution appears to be restarting MySQL. It has memory available. I've tried adjusting the table cache and other variables that might seem related, but it hasn't helped. No helpful messages are logged in the error log.

Using a snapshot of the exact same code, compiled on Linux, I never see that problem. Ever.

My guess is that the problem is related to load, but I cannot reliably reproduce it (yet?).

Other than those two problems, it seems to work quite well. But I've only been running it for a few months with alpha and beta versions of MySQL. I'm fairly sure that all the other odd problems I've had with them are not related to LinuxThreads, but I can't yet say so with 100% certainty.

From what I've seen so far, using LinuxThreads ought to help even on single-CPU FreeBSD boxes.

When FreeBSD is Better: VM

If you are in the majority of FreeBSD users who never see major problems with MySQL on FreeBSD, there are some real advantages to staying there--not moving to Linux. The single biggest advantage is FreeBSD's VM subsystem. Under the workloads I've tested, it always outperforms Linux 2.4.x (where x is 9, 12, 16, and 18). When Linux decides to swap out data that is destined to be used again soon (think key buffer), FreeBSD doesn't go down that route. See my earlier blog entry for more details.

Conclusion

Linux doesn't have the problems that FreeBSD does. Threading works quite well. On the other hand, its VM can be a bit dumb at times. Turning off swap makes that non-issue if it does become a real problem.

Linux is still my preferred choice.

If anyone is able to assist in further debugging the FreeBSD problems--especially the LinuxThreads problems, I'd love the help.