Pimp My Drive by Richard and Barb

There’s remarkably little information online about using MySQL on ZFS, successfully or not, so I did what any enterprising geek would do: Built a box, threw some data on it, and tossed it into production to see if it would sink or swim.

I’m a Linux geek, have been since 1993 (Slackware!). All of SmugMug’s datacenters (and our EC2 images) are built on Linux. But the current state of filesystems on Linux is awful, and …

If you have been reading my previous entries the answer you will think is ‘not bloody well’.

After about 3 days of tuning we doubled the throughput, and got a much nicer picture, outperforming a x86-64 machine by 2.5 times in one case.

Thanks to Luojia Chen (Jenny) from Sun, Peter Zaitsev from Mysql, and Colm MacCárthaigh & Mads Toftum from the ASF.

oh.. the benchmark.. I nearly forgot ;-)

(Oh people..please link to the blog entry, and not the paper itself.. Thanks)

update: people were having issues downloading the PDF. so I placed a mirror of it here

this is the output of dtrace -n 'pid$target:::entry{ @[probefunc] = count() }'

when I run mysqlslap -c 20 -i 20

  pthread_getspecific                                             365
  malloc_internal                                                 377
  my_malloc                                                       377
  free                                                            378
  malloc                                                          378
  my_wc_mb_filename                                               399
  my_no_flags_free                                                420
  strmake                                                         444
  pthread_self                                                    468
  memset                                                          582
  alloc_root                                                      630
  strmov …

ok.. now this is getting stranger.

I have 2 windows open, in one I get

$ mysqlslap --use-threads -psunfire -c 90 -i 500
Benchmark
        Average number of seconds to run all queries: 2.180 seconds
        Minimum number of seconds to run all queries: 0.779 seconds
        Maximum number of seconds to run all queries: 4.497 seconds
        Number of clients running queries: 90
        Average number of queries per client: 0

and in the other i get

$ mysqlslap --use-threads -psunfire -c 90 -i 500
Benchmark
        Average number of seconds to run all queries: 0.012 seconds
        Minimum number of seconds to run all queries: 0.002 seconds
        Maximum number of seconds to run all queries: 0.015 seconds
        Number of clients running queries: 90
        Average number of queries per client: 0

same machine, same user-id, same binary. the only difference is that mysql started in the ‘fast’ window.

and doesn’t seem to take it too nicely :-(

I decided to install the mysql beta on my laptop (Mac Intel @2G ram and 2GHZdual core) and my sunfire (@8G ram and 16 1GHZ Ultrasparcs).

(I have it installed on a x86-64 box as well, but that machine is busy at the moment)..

so for a quick sanity check I ran mysqlslap to see how it performs.

I chose to simulate a 20 concurrent users

sunfire$ mysqlslap -psunfire -c 20 -i 20
Benchmark
        Average number of seconds to run all queries: 1.714 seconds
        Minimum number of seconds to run all queries: 0.941 seconds
        Maximum number of seconds to run all queries: 2.539 seconds
        Number of clients running queries: 20
        Average number of queries per client: 0

vs

laptop$ mysqlslap -i 20 -c 20
Benchmark
        Average number of seconds to run all queries: 1.201 seconds
        Minimum number of seconds to run all queries: 1.072 …

In anticipation of getting my sunfire , and doing some real benchamrks with it, I thought I’d do a quick, test to see if I can still do them..kind of like a warm up excerise before you do a 1500m swim.

So I grabbed 2 idle machines I have access too, and see how mysql 5.1 performed on both.

I took Brian’s mysqlslap tool and gave it a little whirl.

the 3 boxes I had in my aresenal are

• dual x86-64 @2.4G with 8G of ram

a

• 280R with 2 Ultrasparc III+ processors and 4G of ram.

and my new macbook

• a Intel Dual processor shiny thing

now in their time, both of these boxes were considered pretty sweet. (the 280R was purchased in 2002 I think, and for financial people just nearing the end of it’s lifecycle so you will still …