Last September, I wrote entry that became quite popular. In FreeBSD or Linux for your MySQL Server? I detailed some of the reasons that led me to recommend Linux over FreeBSD for MySQL servers at Yahoo. You may have also noticed my Solved: MySQL, FreeBSD, and LinuxThreads post back in October. Many people have e-mailed me recently to ask if the old posting is still accurate. A lot has changed since then and I'm writing this as a brief follow-up.

Executive Summary

MySQL now runs very well on FreeBSD. I'm no longer steering people toward Linux. There are two important things you should do to make the FreeBSD/MySQL combo work well: (1) build MySQL with LinuxThreads rather than FreeBSD's native threads, and (2) use MySQL 4.x or newer. For more details, keep reading.

Some Details

By building MySQL with LinuxThreads support (easy to do with the mysql40-server from the ports collection), you'll end up with a recent version of MySQL that doesn't suffer from any of the problems I described in my previous notes.

Many months back, one of Yahoo's uber-hackers contacted me to volunteer his time toward making MySQL run as well on FreeBSD as it does on Linux. After a few weeks time of tracking down a few bugs, we reached that goal.

The problem of MySQL threads not "noticing" that they were killed turned out to be bug in the FreeBSD kernel. That bug has been patched in FreeBSD 5.0. There's a work-around in the MySQL 4.0 source and beyond. It was never back-ported to 3.23, but it wouldn't be hard. Simply look for "Yahoo!" in the vio/vio.c file of MySQL's source tree to see what was necessary. I think the same bug was responsible for the wait_timeout not working correctly when using LinuxThreads.

The problem of MySQL occasionally thinking that all databases had vanished resulted from FreeBSD's non-threadsafe implementation of realpath(). The solution, as mentioned earlier, is to compile MySQL with the -DHAVE_BROKEN_REALPATH option. MySQL's configure script has been updated to do that automatically on FreeBSD systems. And a new implementation of realpath() was recently committed to FreeBSD's CVS repository.

Posted by jzawodn at May 03, 2003 10:19 PM

Reader Comments
# Gerald said:

Thanks for your great update-service :)

on May 4, 2003 04:52 AM
# adam said:

What about MySQL 4.x/FreeBSD on a SMP machine?

Does compiling with LinuxThreads allow it to use both processors?

on May 4, 2003 10:09 AM
# Matt said:

Adam, yes; I believe it was able to use multiple CPUs before too (with LinuxThreads).

on May 4, 2003 02:34 PM
# Jeremy Zawodny said:

Hey guys.

Read the original article. It specifically says that LinuxThreads gives you SMP (as well as other good stuff).

on May 4, 2003 02:47 PM
# ryan said:

Wouldn't you know it.... wrong comment link ;-)

Anyway, have you done any testing to see if the native threads under FreeBSD5 make any improvement over the linuxthreads and FreeBSD4.x?

on May 5, 2003 02:06 PM
# garrett said:

both native threading libraries (the 1-1 libthr, and the M-N libkse) in freebsd 5.0 are still more in the 'hey, it actually works!' stages, as opposed to the 'wow, this is so much faster than the old stuff', so it might be hard to find any performance numbers for them.

on May 5, 2003 07:36 PM
# hirofumi said:

I heard FreeBSD5.0RELEASE is NOT recommendable for production servers.
So,I hesitate use FreeBSD5.0 RELEASE.
Do I should wait 'FreeBSD5 STABLE'? or I should use FreeBSD5.0 RELEASE? or FreeBSD4.X(maybe 4.8)?

on May 5, 2003 07:56 PM
# Alan Jay said:

Thanks for the update got slightly thrown by you sugested list of compile options. When I looked further it appears that there is a flag -DWITH_LINUXTHREADS to compile linuxthreads in.

Anyway thanks Jeremy for brining the light on this matter keep up the great work.

on May 7, 2003 02:41 PM
# Alan Jay said:

This is all about compiling the server with Linuxthreads does one also need to compile the mysql client with linuxthreads?

on May 9, 2003 06:20 AM
# Ozzwald said:

Try Linux 2.5 + NPTL (recompiling the glibc with 2.5.x kernel headers is enough to get it).

You will be amazed by the responsiveness of MySQL even under heavy load (1000+ concurrent threads), and the efficiency of the anticipatory scheduling elevator.

on May 25, 2003 02:38 AM
# John said:

I just attempted this and it seems to run worse than the precompiled binary on the mysql.com site.
I am running on freebsd 4.7 SMP and the load seems 15-20% higher.

on May 26, 2003 08:42 PM
# NuXY said:

Wow.. This just saved my ass. I was seriously losing hope in FreeBSD due to our servers thread issue.

Thanks Jeremy and all the UBER-Hackers that made this possible.

GREAT JOB! ;)

on May 28, 2003 11:22 PM
# Alan said:

Nice. Now that MySQL finally works as well on FreeBSD 5.0 + linuxthreads as it's been running on linux 2.2 - 2.4 for a while now,. has anyone looked at linux 2.5 + nptl?

NPTL is quite nice, and performs at a fraction of the overhead and cost of linuxthreads, while being binary compatible with linuxthreads as well.

I'd be interested in benchmarks. If 2.5 is too devel for some folks, RH9 shipped with NPTL, I think 0.27 or 0.29, with a 2.4 futex patch it depends on. Current NPTL is 0.42 I think.

on June 3, 2003 12:05 AM
# said:

Nice. Now that MySQL finally works as well on FreeBSD 5.0 + linuxthreads as it's been running on linux 2.2 - 2.4 for a while now,. has anyone looked at linux 2.5 + nptl?

NPTL is quite nice, and performs at a fraction of the overhead and cost of linuxthreads, while being binary compatible with linuxthreads as well.

I'd be interested in benchmarks. If 2.5 is too devel for some folks, RH9 shipped with NPTL, I think 0.27 or 0.29, with a 2.4 futex patch it depends on. Current NPTL is 0.42 I think.

on June 3, 2003 12:06 AM
# EEEE said:

./configure -DWITH_LINUXTHREADS
configure: error: unrecognized option: -DWITH_LINUXTHREADS
Try `./configure --help' for more information.

What gives?

on June 7, 2003 12:41 AM
# Pete said:

So if I understand you Jeremy, your suggestion for a new SMP FreeBSD implementation would be Freebsd 5.0/5.1 + LinuxThreads + MySQL 4.0 w/ LinuxThreads support compiled in? And all could be done from ports?

on June 13, 2003 10:59 AM
# David said:

Just a note for those using FreeBSD 5.x + linuxthreads + mysql 4.0.13. If you get errors about not being able to connect to the socket, build mysql statically (it is one of the options for the port that you pass to make).

on June 27, 2003 04:53 AM
# Dziugas said:

David, thank you very much for your comment about FreeBSD 5.x and MySQL 4.0.13. I had considered with the same problem after system upgrade from FreeBSD 5.0 to 5.1. Sad, but there isn't enough information about this issue, nor in freebsd.org neither in mysql.com. By the way, wait_timeout configuration directive still doesn't work (when compiled with LinuxThreads).

on July 8, 2003 11:58 PM
# Peter Grigor said:

Does the new FreeBSD 5.0 binary distribution use the new KSEs? Anyone have info on this?

on August 7, 2003 07:05 AM
# Lew Payne said:

Does anyone know if FreeBSD 4.8-REL includes the fixed or broken version of realpath? Also, if the FreeBSD *kernel* bug Jeremy described (in which the mySQL threads didn't know they were killed) has or will be fixed in 4.9-REL? How about 4.8-REL, does it suffer from the same kernel bug, too??

on September 4, 2003 02:29 PM
# SPEEDMAX said:

"Experimental 1:1 and M:N thread libraries provide kernel support for efficient application multithreading. " from freebsd 5.1 release announcement
i think that would help a lot for multithreading software like mysql

on September 6, 2003 10:16 PM
# said:

FreeBSD 5.0 has some weird problems with MySQL 4.0.14. It gets restarted automatically every 5-6 hours for no reason, nothing in the log file.

on September 17, 2003 06:30 PM
# Dr. Glaucoma said:

Does the new FreeBSD 5.0 binary distribution use the new KSEs? Anyone have info on this?

on September 26, 2004 10:05 AM
# Slon said:

Best porn here!

on October 5, 2004 10:44 PM
# TatianaKalin said:

You will be amazed by the responsiveness of MySQL even under heavy load (1000+ concurrent threads), and the efficiency of the anticipatory scheduling elevator.

It's crazy! ; )

on November 1, 2004 04:09 PM
# uruiamme said:

I've been watching this MySQL issue for years now! Thanks Jeremy! Has anyone tested the WITH_PROC_SCOPE_PTH=yes option for FreeBSD 5.3? I noticed that at least some guy (who probably never tested it) at http://soapbox.bartsplace.net/article.php/20040615170555313
said that this is the best option for an SMP computer running the new FreeBSD 5.x. Okay, so does anyone have any experience with that? Apparently, the pthreads library for fbsd (libpthread) is possibly not ready for production servers, but I don't know. Many people (thanks to Jeremy) are running linux threads instead and are very happy.

on December 11, 2004 12:30 PM
# said:

MySQL 4.1 has been released as production

on December 15, 2004 03:55 AM
# Kevin said:

I installed mysql 4.0 with pkg_add on FreeBSD 5.3, should I rebuild Mysql as your post said? Thanks!

on January 14, 2005 12:04 PM
# Sin said:

Yes it is necessary

on February 2, 2005 02:46 PM
# hardcore said:

MySQL 4.1 has been released as production, and it's a big step forward. Perhaps it's about time to update my book and start working on a Mastering MySQL 5. Also due to be released is FreeBSD 5.3 (it's just been delayed from Oct 25 to Nov 5). The databa... hardore

on March 28, 2005 08:35 AM
# Rafael Rubleske said:

Hi, very intersting concerns, but there's some advice about FreeBSD 5.3+AMD64+MySQL41?

on March 29, 2005 07:23 PM
# Jim said:

When using linuxthreads on freebsd (portinstall -m "WITH_LINUXTHREADS=yes" mysql-server), the mysql processes run under a nice level of 14. This causes problem when other processes are using up CPU, MySQL will get a lower priority resulting in connection attempts failing.

Has anyone got a solution or answer to solve this?

on April 4, 2005 07:23 AM
# neu from .at said:

use PostgreSQL ;)

on April 20, 2005 05:46 AM
# Bart van Leeuwen said:

Since quite a few people end up at my website from a link in this discussion, I would like to add a bit of information here.

In my article I suggest to try process scope threads instead of Linux threads. Unlike a poster here claims, I did actually test this, and for my specific situation, there is simply no noticable performance difference, neither are there any noticable problems. That does not mean that this will be true for other situations however.

The reason for me to suggest trying procscope threads is simply because it prevents requiring a threading library that is not a normal part of the system. IF procscope threads work well for you, then there is imho no reason to add another library to the system that does not add any functionality.

That said, Linux threads seem better supported and it is the first thing to try if procxscope threads dont work well for you.

on May 19, 2005 11:14 AM
# Don said:

Good blog. I add it to my bookmarks.

on July 21, 2005 04:28 AM
# Eugene said:

I have 2 XEON CPU+HT 2GB RAM box running FreeBSD 5.3, Apache-php and MySQL 4.0.21 (installed from ports without any additional options given to "make") and 1.5 million hits a day. When the server got more requests, MySQL got to "eat" CPU cycles, system load bumps to 40, MySQL became almost stack. I rebuilt MySQL with linux threads as per http://soapbox.bartsplace.net/article.php/20040615170555313, after that system load stays at 3-4, MySQL is dispatched over all CPUs.

Thanks everyone for sharing your experience in this blog.

on December 15, 2005 08:42 AM
# Peter said:

We used linuxthreads for a while on a FreeBSD4 box with just one CPU -> Quite good performance.

Now we upgraded to a dual Xeon machine running on FreeBSD6 and mysql5 has some strange performance problems (system load higher than user, huge amount of cotext switches).

Did the recommendation to use linuxthreads change with FreeBSD 6/mysql5?

on March 28, 2006 01:40 AM
# @Peter said:

migrate to linux, i believe it has better support for SMP

on March 30, 2006 02:48 AM
# davec said:

Peter,
Then you believe wrong.

With system load being higher, have you checked your thread creation policy?

mysql> SHOW STATUS LIKE 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 124 |
| Threads_connected | 389 |
| Threads_created | 582 |
| Threads_running | 98 |
+-------------------+-------+

Jeremy wrote an article on CPU utilization, with an altered wait_timeout, and how his system also showed the "system" status to be much higher (due to it continually creating threads) if you have a high number try tweaking thread cache.

on April 9, 2006 09:24 AM
# Aurimas said:

Guys,

is there something I'm missing? I'm about the FreeBSD and it's roadmap. Don't get me wrong, I'm working with FreeBSD for about 8 years now and I love it, but it seems that the relationship between FreeBSD and MySQL is fading away.

I've done hell a lot of benchmarking on various platforms and it seems that the more the better MySQL performs on Linux, the more the worse it does this on FreeBSD. Although it was very good on FreeBSD 4.x (with linuxthreads), it was reasonable on FreeBSD 5.x (with native threads), on FreeBSD 6.x - MySQL performs absolutely unsatisfactorily.

Last benchmark disappointed me completely. I found that MySQL on FreeBSD 6.1, compiled with identical optimizations and with identical configuration, runs two to four times faster on UP Pentium4 3.0GHz machine than it does on dual xeon 3.2GHz (amd64 version) even though xeon has a better disk subsystem.

Is it FreeBSD or is it just me? Am I missing something very important? Some very very basic setting? Anything. Please, any hints very very welcome!

on July 4, 2006 06:50 PM
# Abdulla Al-Marri said:

Aurimas,

I was having problems with MySQL using linuxthread in my dual xeon on FreeBSD 6.1-STABLE.

Then I changed it to libthr, and I got no single problems with it, and yes it's much much faster now!

For FreeBSD 6 tests show that using libthr gives the best performance (search on the net for some benchmarks).
To use libthr, just download the precompiled mysql for FreeBSD 6, and add the following lines to your /etc/libmap.conf :

[mysqld]
libpthread.so.2 libthr.so.2
libpthread.so libthr.so

After that restart your mysqld, and from then on it should be using libthr.

on July 9, 2006 02:15 AM
# qzheng said:

# Aurimas said:
Guys,
------------------------------------
I agree with you, I was lucky that I read author's article serval years ago, and finally chosed linux to be the OS of our production MYSQL server, yes I have been happy for many years from then on.

FreeBSD 6.1 is still a bad choice even with -libthr or whatever thread libaray. I dont want to show the 2-times per 1 hour crashing log on our test server here, trust me, chose Linux to run your MYSQL, if you dont want to be pissed off by those keep-coming signal 6, signal 11 thing, and keeping fixing the production database . Your boss will be pissed off too.

Why I kept trying MYSQL on FreeBSD? coz I love FreeBSD.

Why I still use Linux to run MYSQL? coz I love my job and my salary,much more.

Oh yes, our own thread-based middle-ware crashed on FreeBSD6.1, too,just right now.

on July 18, 2006 06:33 AM
# IT_Architect said:

Abdulla Al-Marri said: I was having problems with MySQL using linuxthread in my dual xeon on FreeBSD 6.1-STABLE. Then I changed it to libthr, and I got no single problems with it, and yes it's much much faster now!

Amen Abdulla! It's been a rocky between FreeBSD 4.x and 6.1. In the middle we tried Centos, RH, and Fedora and lost plenty of time and business trying to get them to stand up to the worlds 6th largest weather site and a very busy hotel booking site. FreeBSD 4.x was where we had to go until 6.0. 5.x was no way. We did run 6.0. Sometime it worked good and sometimes we had some wild loads, and had unexplained slowdowns that didn't follow the server loads or traffic loads. That was traced AGAIN to threading. The move to 6.1 was incredible. Mid-day we are running at 1/4th the load of a couple of weeks ago with no unexplained slow-downs. It's about time!

We're in the tweaking phase now before rolling the same combo out to a fail-over/load-balancing server. We're putting all the load on one server, which isn't pretty, but it's the only way to clarify the benefits or detractions of your changes.

on August 2, 2006 08:39 AM
# Darton Williams said:

After taking over hosting for a large fan site, we had some issues with MySQL. Running FreeBSD 6.1 and MySQL 4.1 (without linuxthreads), our 2.8GHz Dual Xeon with 4GB RAM was getting overloaded by a MySQL/PHP message board.

After experiencing constant corruption of a 1.5GB table, mysqld crashing, blaming MySQL the whole time, we tracked it down to PHP holding onto persistent connections. Once we disabled that, we had a stable system running at about 25% peak load.

on August 15, 2006 09:53 PM
# Huy Tran said:

Anyone else has experienced FreeBSD 6.1 and MySQL 5.0 version? I'm about to deploy system with this combination. Any suggestion will be helpful.

on September 29, 2006 09:57 PM
# falz said:

For those using linuxthreads on freebsd, if you're receiving errors such as:

error: 'Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug'

Be sure to check out this page, with a few slight patches to linuxthreads:

http://www.krellis.org/unix-stuff/mysql-freebsd-threads.html

on November 10, 2006 08:58 AM
# Abdullah said:

The main problem is within the MySQL itself since it's linux centric.

I'll give PGSQL another try since it's 8.2 very fast and considered as performance release.

http://it.slashdot.org/article.pl?sid=06/12/05/236220

on December 14, 2006 12:54 AM
# veejay said:

Hi

Just have couple of questions....

On a FreeBSD6.1 Box:

Does "/etc/libmap.conf" file already exists or one have to create it?

How one can know that after creating libmap.conf file, mysql is using this library?

Thanks...

vj

on December 17, 2006 09:21 AM
# Doom said:

Hi

Just have couple of questions....

On a FreeBSD6.1 Box:

Does "/etc/libmap.conf" file already exists or one have to create it?

How one can know that after creating libmap.conf file, mysql is using this library?

Thanks...

chat

on December 20, 2006 02:40 PM
# ben said:

Huy Tran said:
Anyone else has experienced FreeBSD 6.1 and MySQL 5.0 version? I'm about to deploy system with this combination. Any suggestion will be helpful.

I've used both for awhile now with great success. But then I've heard some people tried it together and hated it. All I can tell you is try it, and if you don't like it to do something else.

on April 10, 2007 02:41 PM
# Michael Montrief said:

I'm using that same configuration without any noticeable problem.

on May 25, 2007 09:50 AM
# Troy said:

Is it still required to do this for mysql 5.0?

To build the port with process scope threads:

cd /usr/ports/database/mysql40-server
make WITH_PROC_SCOPE_PTH=yes BUILD_OPTIMIZED=yes BUILD_STATIC=yes install

on June 7, 2007 06:54 PM
# Sohbet said:

thanks for updating.

on August 15, 2007 05:04 AM
# sohbet said:

thank you friends

on September 25, 2007 06:25 AM
# 广告笔 said:

NPTL is quite nice, and performs at a fraction of the overhead and cost of linuxthreads, while being binary compatible with linuxthreads as well.

on December 27, 2008 06:18 PM
# Marcus Reid said:

This information is very out of date. The picture has changed significantly with newer versions of FreeBSD. As of this writing, FreeBSD (and NetBSD as of 5.0) can outperform Linux on some benchmarks. FreeBSD 8.0 is almost certain to improve performance even further.

As always, it's best to search for the most up to date information available, and this blog entry is getting quite stale. It frequently appears high in the search rankings still though.

on May 13, 2009 09:31 PM
Disclaimer: The opinions expressed here are mine and mine alone. My current, past, or previous employers are not responsible for what I write here, the comments left by others, or the photos I may share. If you have questions, please contact me. Also, I am not a journalist or reporter. Don't "pitch" me.

 

Privacy: I do not share or publish the email addresses or IP addresses of anyone posting a comment here without consent. However, I do reserve the right to remove comments that are spammy, off-topic, or otherwise unsuitable based on my comment policy. In a few cases, I may leave spammy comments but remove any URLs they contain.