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
What about MySQL 4.x/FreeBSD on a SMP machine?
Does compiling with LinuxThreads allow it to use both processors?
Adam, yes; I believe it was able to use multiple CPUs before too (with LinuxThreads).
Hey guys.
Read the original article. It specifically says that LinuxThreads gives you SMP (as well as other good stuff).
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?
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.
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)?
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.
This is all about compiling the server with Linuxthreads does one also need to compile the mysql client with linuxthreads?
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.
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.
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! ;)
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.
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.
./configure -DWITH_LINUXTHREADS
configure: error: unrecognized option: -DWITH_LINUXTHREADS
Try `./configure --help' for more information.
What gives?
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?
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).
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).
Does the new FreeBSD 5.0 binary distribution use the new KSEs? Anyone have info on this?
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??
"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
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.
Does the new FreeBSD 5.0 binary distribution use the new KSEs? Anyone have info on this?
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! ; )
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.
I installed mysql 4.0 with pkg_add on FreeBSD 5.3, should I rebuild Mysql as your post said? Thanks!
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
Hi, very intersting concerns, but there's some advice about FreeBSD 5.3+AMD64+MySQL41?
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?
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.
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.
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?
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.
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!
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.
# 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.
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.
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.
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.
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
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
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
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
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.
I'm using that same configuration without any noticeable problem.
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
NPTL is quite nice, and performs at a fraction of the overhead and cost of linuxthreads, while being binary compatible with linuxthreads as well.
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.
