FreeBSD MySQL still WAY slower than Linux

[Peter Jeremy]

On Fri, 2005-Jun-17 10:38:34 -0400, David Sze wrote:
>It turns out that the problem was the same thing everyone usually points 
>the finger at, but no one actually mentioned this time:  Linux mounts its 
>partitions async by default.

This shouldn't be an issue here.  The FreeBSD default has always been
that data is written asynchronously (see below) and there should be
virtually no metadata updates in a database application.  If an
application needs control over when the data is physically written to
the disk (eg a database server), it needs to use fsync(2) and/or
msync(2) calls.  If Linux (or FreeBSD) isn't complying with fsync(2)
or msync(2) semantics then it has a serious problem.

>super-smack select-key
>        5.4-RELEASE             ~20,000 queries/second
>        6.0-CURRENT             ~24,000 queries/second
>        CentOS w/async  ~36,000 queries/second
>        CentOS w/sync   ~26,000 queries/second

I can't see where this benchmark is doing any writes so I'd like to see
an explanation of the difference in CentOS performance figures before
making any decision on CentOS vs FreeBSD.

>super-smack update-select
>        5.4-RELEASE             ~4,000 queries/second
>        6.0-CURRENT             ~4,500 queries/second
>        CentOS w/async  ~7,500 queries/second
>        CentOS w/sync   ~750 queries/second
>
>That last CentOS number is not a typo, it was an order of magnitude 
>slower.

That's a surprising difference - I wouldn't have expected it to be
so large.

A couple of people have mentioned the impact of journalling.
Journalling improves performance by converting time-critical random
I/Os into time-critical sequential I/Os and background random I/Os -
and sequential I/O is many orders of magnitude faster than random I/O.
All transactional databases do their own journalling (REDO logs).  As
long as the database correctly issues filesystem synchronisation
commands and the filesystem correctly implements them, database
application, a journalling filesystem provides no benefits.

A more interesting test would be a client that issues a series of
updates to a server and, immediately after receiving the acknowledge
for the last update turns, off the server's power.  After the server
is rebooted, confirm that all the updates have been applied correctly.

Filesystem I/O behaviour:

Data    Metadata   Mount type
async    async     async
async     sync     'traditional' UFS
async    async[*]  UFS + softupdates
 sync     sync     sync

'Metadata' covers directory updates and some inode updates

[*] softupdates controls write ordering to ensure on-disk consistency.

-- 
Peter Jeremy