Is the fsync() fake on FreeBSD6.1?

[Michael Vince]

Greg 'groggy' Lehey wrote:

>On Tuesday, 27 June 2006 at 10:18:47 +0800, leo huang wrote:
>  
>
>>Hi,
>>
>>I benchmarked MySQL 4.1.18 on FreeBSD 6.1 and Debian 3.1 using Super Smack
>>1.3 some days ago.
>>
>>...
>>
>>The result surprise me. The MySQL Performance on FreeBSD6.1 is about
>>10 times of on Debian3.1??and the output of iostat also shows it.
>>
>>I know that MySQL uses fsync() to flush both the data and log files
>>at default when using innodb engine(
>>http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html). Our
>>evaluating computer only has a 10000RPM SCSI hard disk. I think it
>>can do about 200 sequential fsync() calls per second if the fsync()
>>is real.
>>
>>Is the fsync() on FreeBSD6.1 fake?
>>    
>>
>
>My understanding from the last time I looked at the code was that
>fsync does the right thing:
>
>     The fsync() system call causes all modified data and attributes of fd to
>     be moved to a permanent storage device.  This normally results in all in-
>     core modified copies of buffers for the associated file to be written to
>     a disk.
>
>This is not the case for Linux, where fsync syncs the entire file
>system.  That could explain some of the performance difference, but
>not all of it.  I suppose it's worth noting that, in general, people
>report much better performance with MySQL on Linux than on FreeBSD.
>
>  
>
>>I mean than the data is only written to the drives memory and so can
>>be lost if power goes down.
>>    
>>
>
>I don't believe that fsync is required to flush the drive buffers.  It
>would be nice to have a function that did, though.
>
>  
>
>>And how I can confirm this?
>>    
>>
>
>Trial and error?
>
>Greg
>--
>See complete headers for address and phone numbers.
>  
>
I actually tried once mounting /var/db/mysql on a md device to see if it 
could give any difference in performance.
After some supersmack benchmarking I decided it made just to a few % 
points to no difference, you are using a larger data set though in your 
benchmark maybe there would be a noticeable difference.

I saw a post a few months ago that on -current there was about a per CPU 
core 90% performance increase on a quad core system, which is very good 
scaling.
I don't know if what ever gave that kind of performance scaling got 
merged in to 6, but his numbers looked very good.

The standard Dell server I bought in late 2005 being 2 socket/2cores 
total now costs the same price as a Dell server with 2 socket ~ 4 CPU 
cores, early next year Intel will release its 4 cores per CPU so the 
standard server will be 8 cores, and will not take long to cost the same 
again.
With performance increasing at such a fast rate I have the daydream 
belief that its is going to be hard to have db performance problems in 
the future CPU wise, well at the very least its where supersmack 
benchmarks tend to be tested for.

Mike