FreeBSD MySQL still WAY slower than Linux

[David Sze]

At 04:59 PM 19/06/2005 +0100, Robert Watson wrote this to All:

>BTW, could you run 'file' on the Linux and FreeBSD MySQL binaries and 
>confirm that they're both either 32-bit i386 binaries, or 64-bit amd64 
>binaries?

I can no longer run tests on the machine because it's in production, but I 
will be re-running the tests on a different machine in the next few days, 
for reasons outlined below.  They should both be amd64 binaries, I compiled 
from ports for FreeBSD and installs the x86_64 RPM for CentOS.


>BTW, could you confirm that on 6.x, you're setting /etc/malloc.conf so 
>that it's not scrubbing memory on free()?  In particular:
>
>   ln -s jz /etc/malloc.conf
>
>as root.  Most 6.x debugging features are set as part of the kernel 
>configuration, and your performance results suggest that you've caught 
>them all already, I think, but this one is user space.  Also, make sure 
>you are compiling without INVARIANT_SUPPORT -- for packet send tests, I 
>find that even without INVARIANTS, I see a 4% hit for having 
>INVARIANT_SUPPORT in the kernel.

I was using "ln -s aj /etc/malloc.conf" because I read somewhere that AJ 
were the only debugging options enabled in -CURRENT.  I'll use "ajz" for 
the next set of tests.

I had everything in the "Debugging for use in -current" section in GENERIC 
"nooptions"ed in my kernel config file, so INVARIANT_SUPPORT was gone.


The reason I'm re-running the tests is due to things pointed out by various 
people last week.  In particular:

- ufs+soft_updates and ext3 are both async, but they guarantee 
consistency.  So mounting the ext3 partition as sync and comparing it to 
ufs+soft_updates was meaningless.

- Consistency guarantees are useless for databases because they have pretty 
much zero filesystem meta data updates.

- MyISAM has no data durability guarantees, so using it on an async file 
system like ufs+soft_updates or ext3 is also meaningless (at least for my 
application, where writes are more common than reads, and durability is 
important).  You can get durability with MyISAM by mounting sync, but write 
performance is horrible.

- InnoDB has durability guarantees on an async filesystem (it uses one of 
fdatasync/fsync/O_SYNC/etc. at the appropriate times), so the results of 
the update-select super-smack benchmark look a lot different.

I'll be re-running super-smack against an InnoDB table.  Any additional 
requests for configurations to test, or other tweaking suggestions?

This is what I'll be trying:

         FreeBSD/amd64 4.11-RELEASE, linuxthreads
         FreeBSD/amd64 5.4-RELEASE, libpthread
         FreeBSD/amd64 5.4-RELEASE, libpthread, process scope
         FreeBSD/amd64 6.0-CURRENT, libpthread
         FreeBSD/amd64 6.0-CURRENT, libpthread, process scope
         FreeBSD/amd64 6.0-CURRENT, libthr, system scope
         FreeBSD/amd64 6.0-CURRENT, libthr, process scope
         CentOS/amd64 4.0

The different threading libraries are more for completeness.  In my last 
test I saw <10% difference between them on amd64.