[UPDATE: There's important new information in this article.]

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

  1. Yahoo is a FreeBSD shop
  2. Someone has heard that MySQL runs better on Linux
  3. 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.
  4. 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.

Posted by jzawodn at September 30, 2002 05:22 PM

Reader Comments
# Stoyan Zhekov said:

What about file system? Phisically sql files
are hosted on some type of FS. And from what
I know FFS is better then Linux implemented file systems. Is this factor included in the analysis?
And my opinion - more efficive is to get better sql engine (maybe PostgeSQL) than to change the OS. But this maybe will start SQL war, so this is just my point of view.

on October 1, 2002 06:30 AM
# Cyril Margorin said:

I've found, that MySQL with LinuxThreads enabled have a problem with 'lost databases' even on version 3.23.52. Also I've found that it is lose databases, but started to search them in /var/tmp directory (May be it's a just a TMPDIR variable in mysql enviroment). meanwhile, all of the variables are normal (as I see).
I've find it, 'cause I see `vi.recover' database in my databases list (SHOW DATABASES). This directory is only in /var/tmp dir.

on October 7, 2002 10:28 AM
# Simas Cepaitis said:

For MySQL losing databases problem, you can try compiling MySQL with -DHAVE_BROKEN_REALPATH option. It should help.

on November 26, 2002 12:25 AM
# Scott Hess said:

I believe that #2 is because disk I/O is inherently blocking. The threading library can set "non-blocking" all it wants, but when you hit a read(), you block until the read is complete. We ran across this when we got a hefty RAID array and only got 20% more performance out of MYSQL, even though other measures quadrupled.

The core problem is that when many processes have a disk read outstanding, the OS can elevator sort so that one seek across the disk picks up many sectors. With userland threading, you won't see the N+1'th sector request until the N'th sector is delivered, so you effectively serialize things.

Various solutions were attempted using async I/O, seperate I/O processes (fork and rfork), but in the end we just ran multiple mysqld against the same tables, and bit the management nightmare. Now we can push the RAID array to its limits.

on February 24, 2003 05:16 PM
# A Mac Developer said:

Since hundreds of people are looking at this article via the Macintouch link, I thought I'd toss out a useful tidbit:

Mac OS X's threading model is more like Linux's. POSIX Threads are mapped 1:1 to mach threads, which are what the kernel scheduler picks from when selecting a process to run.

on March 13, 2003 04:50 PM
# Wang Xianren said:

i have download mysql 4.0.13 source code and get Linuxthread installed on my FreeBSD 4.8 box, but when i do ./configure --prefix=/usr/local/mysql, it says: checking "LinuxThreads"... "Not found".
So, i don't know how to compile my own MySQL server on FreeBSD and link with the LinuxThreads library. pls help me.

on June 7, 2003 02:26 PM
# Sharper said:

We've been running Books Under Review on FreeBSD 5.0 w/SMP for a few months and the new SMP and threading models have been rock solid. It's my understanding that 5.1 (just out as of this writing) has further major improvements in the threading and SMP interaction. We'll be moving to it soon.

We're looking at clustered Apache/JBOSS/MySQL for some large shared platform E-commerce Projects on Freebsd sharing files on a Netapp Filer and most of our concerns now revolve around clustering MySQL while sharing the physical files to save storage. It looks like we're going to have to use a cluster for reading from the DB and a failover setup for writes to the DB to get around MySQL's lack of built in true clustering.

If anyone's come up with anything better in terms of design for this type of setup, I'd love to hear about it. I know there is a project out there for generic DB clustering, but it still doesn't get around some of MySQL's write limitations unless you want to keep a different set of data files around for each DB instance. Using small (AMD and FreeBSD) hardware for the DB instances it just makes too much sense to have them share files on a clustered NAS array and create really nice scalability at very low cost for the DB servers that way.

on June 12, 2003 05:53 PM
# anupama said:

jjljjl

on August 20, 2003 09:22 PM
# SPEEDMAX said:

it runs quite well with linuxthreads + mysql 4.1 alpha + FreeBSD 4.62

however, it takes a while to get the threadcache setting right.

good luck on that. i look forward the new release of Freebsd5.2

on September 6, 2003 10:12 PM
# cancel said:

Mac OS X's threading model is not more like Linux's, imho.

on September 23, 2003 05:45 PM
# driverdave said:

Wow, I really had no idea about Free BSD's threading problems. Time for some reading. Thanks!

on November 1, 2003 10:22 PM
# Ingresso said:

I agree, it runs well with linuxthreads + mysql 4.1 alpha + FreeBSD 4.62,
but it takes too much time to get the threadcache setting right.

on November 11, 2003 07:07 PM
# Slon said:

Best porn here!

on October 5, 2004 10:43 PM
# John said:

Nice site

on November 16, 2004 10:03 AM
# Karl said:

Thanks for the informations!

on November 16, 2004 10:04 AM
# bill said:

Nice job :)

on November 16, 2004 10:09 AM
# Jared Kuolt said:

I'm curious how this applies in 2005/FreeBSD 5.3+

on January 25, 2005 01:03 PM
# Mookstah said:

I've seen mysql releases binaries for freebsd 5.3
(not on their main site but you can find it in their mirror download sites / ftps if you browse...)
as fas as i could see, for version 5.3 they use freebsd safethread and not linuxthreads.
correct me if I'm wrong, but i do believe mysql developers will give use the best solution with their binaries.

on February 8, 2005 02:31 AM
# yang said:
on April 24, 2005 07:03 PM
# D said:

机票联盟网服务电话:机票预订:010-89590599 酒店预订:010-89597840 国内机票:010-89590599 国际机票:010-89595606 机票联盟网服务承诺:为您提供打折机票机票价格查询、特价机票机票查询、飞机票预订服务, 全力打造优质机票服务。
机票联盟网 是专门提供给网友的机票预订电话的机票导航网。通过机票联盟网,能够很轻松的找到当地很有优势的机票代理商,他们的价格低廉,服务优质。我们力争寻找当地特价机票提供商,为您的出行节约资金。如果您在打折机票联盟网中查到的民航售票处所提供的飞机票价格,不是最低的。我们将立即更换该机票代理。同时,诚邀各地优秀的特价机票,国际机票代理人,加入我们。北京机票预订:010-89590599 010-89595606上海机票预订:021-52385605 021-52385606

on August 5, 2005 05:43 PM
# 机票 said:

http://jiansuji.myrice.com/ 减速机
http://www.cdxinlilai.com/ 三面翻
http://wxq01.blog.ccidnet.com/ 减速机
http://wxq07.blog.ccidnet.com/ 机柜
http://wxq09.blog.ccidnet.com/ 光端机
http://wxq11.blog.ccidnet.com/ 地暖
http://wxq13.blog.ccidnet.com/ 琥珀
http://wxq15.blog.ccidnet.com/ 乳腺增生
http://my.opera.com/hcy/blog/ 成都机票
http://www.43things.com/entries/view/610948 成都机票
http://wxq05.blog.ccidnet.com/ 三维扫描仪
http://wxq05.blog.ccidnet.com/blog/ccid/do_showone/tid_22630.html 逆向工程
http://wxq05.blog.ccidnet.com/blog/ccid/do_showone/tid_22629.html 抄数机
http://wxq03.blog.ccidnet.com/ 升降机|液压升降机|升降平台|升降台|升降车|升降梯|液压梯
http://wxq17.blog.ccidnet.com/blog/ccid/do_showone/tid_22641.html 激光对中仪/激光几何测量仪/机械轴承故障检测仪/机械故障试验台
http://hcy01.blog.ccidnet.com/blog/ccid/do_showone/tid_22656.html 北京机票/北京飞机票/北京打折机票/北京特价机票
http://hcy01.blog.ccidnet.com/blog/ccid/do_showone/tid_22659.html 上海机票/上海飞机票/上海打折机票/上海特价机票
http://hcy01.blog.ccidnet.com/blog/ccid/do_showone/tid_22661.html 广州机票/广州飞机票/广州打折机票/广州特价机票
http://hcy01.blog.ccidnet.com/blog/ccid/do_showone/tid_22662.html 成都机票/成都飞机票/成都打折机票/成都特价机票
http://hcy01.blog.ccidnet.com/blog/ccid/do_showone/tid_22665.html 重庆机票/重庆飞机票/重庆打折机票/重庆特价机票
http://hcy01.blog.ccidnet.com/blog/ccid/do_showone/tid_22669.html 厦门机票/厦门飞机票/厦门打折机票/厦门特价机票
http://hcy01.blog.ccidnet.com/blog/ccid/do_showone/tid_22672.html 福州机票/福州飞机票/福州打折机票/福州特价机票
http://hcy01.blog.ccidnet.com/blog/ccid/do_showone/tid_22673.html 天津机票/天津飞机票/天津打折机票/天津特价机票
http://vip.jipiao365.net/ 机票
http://vip.jipiao365.net/user/67_1.html 北京机票
http://vip.jipiao365.net/user/68_1.html 上海机票
http://vip.jipiao365.net/user/69_1.html 广州机票
http://vip.jipiao365.net/user/70_1.html 成都机票
http://vip.jipiao365.net/user/71_1.html 重庆机票
http://vip.jipiao365.net/user/72_1.html 厦门机票
http://vip.jipiao365.net/user/73_1.html 福州机票
http://vip.jipiao365.net/user/74_1.html 天津机票
http://vip.jipiao365.net/user/75_1.html 西安机票
http://vip.jipiao365.net/user/76_1.html 杭州机票
http://vip.jipiao365.net/user/77_1.html 深圳机票
http://vip.jipiao365.net/user/78_1.html 北京飞机票
http://vip.jipiao365.net/user/79_1.html 上海飞机票
http://vip.jipiao365.net/user/80_1.html 广州飞机票
http://vip.jipiao365.net/user/81_1.html 成都飞机票
http://vip.jipiao365.net/user/82_1.html 重庆飞机票
http://vip.jipiao365.net/user/83_1.html 厦门飞机票
http://vip.jipiao365.net/user/84_1.html 福州飞机票
http://vip.jipiao365.net/user/85_1.html 天津飞机票
http://vip.jipiao365.net/user/86_1.html 西安飞机票
http://vip.jipiao365.net/user/87_1.html 杭州飞机票
http://vip.jipiao365.net/user/88_1.html 深圳飞机票
http://vip.jipiao365.net/user/89_1.html 北京打折机票
http://vip.jipiao365.net/user/90_1.html 上海打折机票
http://vip.jipiao365.net/user/91_1.html 广州打折机票
http://vip.jipiao365.net/user/92_1.html 成都打折机票
http://vip.jipiao365.net/user/93_1.html 重庆打折机票
http://vip.jipiao365.net/user/94_1.html 厦门打折机票
http://vip.jipiao365.net/user/95_1.html 福州打折机票
http://vip.jipiao365.net/user/96_1.html 天津打折机票
http://vip.jipiao365.net/user/97_1.html 西安打折机票
http://vip.jipiao365.net/user/98_1.html 杭州打折机票
http://vip.jipiao365.net/user/99_1.html 深圳打折机票
http://vip.jipiao365.net/user/100_1.html 北京特价机票
http://vip.jipiao365.net/user/101_1.html 上海特价机票
http://vip.jipiao365.net/user/102_1.html 广州特价机票
http://vip.jipiao365.net/user/103_1.html 成都特价机票
http://vip.jipiao365.net/user/104_1.html 重庆特价机票
http://vip.jipiao365.net/user/105_1.html 厦门特价机票
http://vip.jipiao365.net/user/106_1.html 福州特价机票
http://vip.jipiao365.net/user/107_1.html 天津特价机票
http://vip.jipiao365.net/user/108_1.html 西安特价机票
http://vip.jipiao365.net/user/109_1.html 杭州特价机票
http://vip.jipiao365.net/user/110_1.html 深圳特价机票
http://vip.jipiao365.net/user/122_1.html 北京航班查询
http://vip.jipiao365.net/user/123_1.html 上海航班查询
http://vip.jipiao365.net/user/124_1.html 广州航班查询
http://vip.jipiao365.net/user/125_1.html 成都航班查询
http://vip.jipiao365.net/user/126_1.html 重庆航班查询
http://vip.jipiao365.net/user/127_1.html 厦门航班查询
http://vip.jipiao365.net/user/128_1.html 福州航班查询
http://vip.jipiao365.net/user/129_1.html 天津航班查询
http://vip.jipiao365.net/user/130_1.html 西安航班查询
http://vip.jipiao365.net/user/133_1.html 杭州航班查询
http://vip.jipiao365.net/user/131_1.html 深圳航班查询

on March 1, 2006 05:13 AM
# jam said:

Brand New Handbag.


For more pictures and details please contact us at dr-replica.com


Huge discounts on 2006 Brand New Handbag.





Which handbag fits your personality? Classic? Hobo?...

To View Product...
If you have any question,please contact us with:
http://dr-replica.com or
MSN: wholesaleproduct@hotmail.com



We send this email daily to our all listing companies. If you don't want to get any update from us please Contact Us.




on March 14, 2006 07:44 PM
# Peter said:

Thanks for this article and other people comment to this subject, it would be great help for me, thanks.

on July 10, 2006 02:23 AM
# Dj Tiesto said:

Thanks for the informations!

on September 20, 2007 12:16 AM
# sashant said:

I know FFS is better then Linux implemented file systems. Is this factor included in the analysis?.
The core problem is that when many processes have a disk read outstanding, the OS can elevator sort so that one seek across the disk picks up many sectors. With userland threading, you won't see the N+1'th sector request until the N'th sector is delivered, so you effectively serialize things

on April 8, 2010 02:23 AM
# sashant said:

Plz Help me
How can I create a thread in Linux without informing the Linux Kernal.

on April 16, 2010 01:42 AM
# Amitabh said:


There are a lot of great points here,
but I'm not sure I agree with real-time search being discarded.
I agree that it's not verey relevant,
but isn't the point of it to show what people are currently saying about a topic,

on April 17, 2010 12:36 AM
# Calvin said:

What’s really interesting, though, is that not all of those SEO blogs on the list have nofollow implemented on their comments.
http://www.jodimilade.com/matrimony/index.php

on April 17, 2010 12:38 AM
# Jenny said:

"Sometimes in large deployments, there are cases when MySQL server, setup by you long time back which has been in use by multiple teams in your organization, needs some change or update or intrupption in its service and you are in need to know how many clients are there which connects to this server.

"One way to know is to check user table in ‘mysql’ database, you might have created individual users/databases for your clients/users. But over the time, unless taking care seriously, we tend to forget the exactly how many users/scripts/machines are there making connections. May be you have used same read-only user in various scripts scattered over many machines which you’ve created just for a single client."

on April 26, 2010 05:45 AM
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.