ZFS Few Questions

[Matthew Seaman]

On 17/11/2011 19:04, Mark Felder wrote:
>> Question 3:
>> Anyone Recommend for MySQL server? (Performance)
> 
> No idea; I haven't run any SQL servers on ZFS

The sort of randomly located small IOs that RDBMSes do is the hardest
sort of IO pattern for ZFS (or any filesystem for that matter) to
manage.  ZFS has a particular problem in that its default storage unit
is a 128kB block -- and the copy-on-write semantics mean that the
filesystem layer can in principle end up doing a 128kB read, altering a
few bytes, then doing a 128kB write to get that data back on disk.

You can get pretty reasonable DB performance on ZFS, but it takes quite
a bit of tuning.

   * ZFS needs plenty of RAM.  The DB needs plenty of RAM.  Exactly
     what the balance should be is hard to predict -- dependent on
     specific workloads -- so expect to spend some time benchmarking
     and experimenting with different settings.

   * Putting the ARC (Adaptive Replacement Cache) on a separate, fast
     device will make a big difference to performance.  SSD cards are
     popular for this purpose.  (Be aware though that SSDs have a
     limited lifetime, and tend to fail suddenly and completely when
     they do wear out.  You will need multiple layers of resilience and
     very good backups...)  While SSD cards are intrinsically faster
     than individual rotating magnetic media, they are no match for a
     large disk array that can spread the IO over lots of spindles.
     But that costs a very great deal of money...

   * Reducing the ZFS block size (the recordsize property when creating
     a zfs) to match the IO size of your DB system can help a lot.  Do
     this before creating the database.

   * Separating the DB's data and transaction logging onto separate ZFS
     pools helps.

See http://www.solarisinternals.com/wiki/index.php/ZFS_for_Databases for
more details.  Just about everything on that page applies equally to
FreeBSD as it does to Solaris.

	Cheers,

	Matthew

-- 
Dr Matthew J Seaman MA, D.Phil.                   7 Priory Courtyard
                                                  Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey     Ramsgate
JID: matthew at infracaninophile.co.uk               Kent, CT11 9PW

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 267 bytes
Desc: OpenPGP digital signature
Url : http://lists.freebsd.org/pipermail/freebsd-performance/attachments/20111117/3066b4ba/signature.pgp