FreeBSD MySQL still WAY slower than Linux

[Robert Watson]

On Fri, 10 Jun 2005, Steve Roome wrote:

> We're using mostly:
>
>  5.4-STABLE FreeBSD 5.4-STABLE #0: Mon Jun 6 12:22:18 BST 2005

In my experience, the following factors make a big performance difference:

- Thread package.  In 5.x, you get process scope threads by default, but
   it turns out MySQL is tuned for system scope threads, and this is
   particularly visible in the supersmack benchmark, which competes many
   client processes against a few server threads.  I'm not sure what the
   condition is of libthr on 5.x, but you could give it a spin.  In 6.x,
   libthr has been largely rewritten and is a great deal faster.  I think
   there's a compile-time option to make libpthread use system scope
   threads but the details ellude me.  The Linuxthreads library may well
   provide a substantial improvement -- not as good for MySQL as the 6.x
   libthr, but perhaps much more appropriate than libpthread.

- Locking model.  Make sure that debug.mpsafenet is set to 1 (i.e., there
   aren't components in the kernel that force Giant over the network stack.
   Chances are there are none, but it's worth checking).

- Twiddling hyper-threading, which helps or hurts differently in various
   configurations.

- On a UP system, consider compiling a kernel without "options SMP" to
   reduce locking overhead.

I've found the single largest remaining factor to be threading package -- 
over the past year or so I've about doubled MySQL performance on 5.x 
leading up to 5.3, largely through SMP locking work, but the remainder of 
the difference appears to be in the threading package.

Robert N M Watson