Best filesystem for virtual machine running SQL Server (part 2)

This is the second part - please read part 1.

I don't care about synthetic tests like bonnie++ or dd if=/dev/zero etc. I am only interested in finding out which configuration runs my test the fastest.

However, it is nice to see the difference :) so these are the results of copying an 18GB file into each of the three configurations:

(recall, first config is XFS partition on single disk, second is RAID0 XFS and third is RAID1 XFS).

(Copying from the non-raid partition into a non-raid partition on the second disk)

time `dd if=test of=/data/singleb/test bs=1M ; sync`

18267+1 records in

18267+1 records out

19155058688 bytes (19 GB) copied, 164.756 s, 116 MB/s

real 2m52.385s

user 0m0.030s

sys 0m26.490s

(Copying from the non-raid partition into the RAID0 partition)

time `dd if=test of=/data/raid0/test bs=1M ; sync`

18267+1 records in

18267+1 records out

19155058688 bytes (19 GB) copied, 266.782 s, 71.8 MB/s

real 4m28.422s

user 0m0.010s

sys 0m27.840s

(Copying from the non-raid partition into the RAID0 partition)

time `dd if=test of=/data/raid1/test bs=1M ; sync`

18267+1 records in

18267+1 records out

19155058688 bytes (19 GB) copied, 400.644 s, 47.8 MB/s

real 6m47.905s

user 0m0.050s

sys 0m26.620s

Copying from one disk to another is the fastest, followed by RAID0 then RAID1. You might have expected RAID0 to be the fastest but recall that the 18GB file is being copied from a parition on the same disk so one of the disks in RAID0 (and RAID1) will be involved in reading as well as writing, hence the slow down.

OK, since I opened this door, maybe a fairer (but still meaningless :)) test would be to use dd if=/dev/zero so the disks are purely available for writing..... Results of that silly test (time `dd if=/dev/zero of=18G bs=1M count=18000; sync`) are:

single disk:

18000+0 records in

18000+0 records out

18874368000 bytes (19 GB) copied, 156.651 s, 120 MB/s

real 2m43.003s

user 0m0.040s

sys 0m16.760s

raid0:

18000+0 records in

18000+0 records out

18874368000 bytes (19 GB) copied, 82.0213 s, 230 MB/s

real 1m25.195s

user 0m0.030s

sys 0m16.800s

raid1:

18000+0 records in

18000+0 records out

18874368000 bytes (19 GB) copied, 189 s, 99.9 MB/s

real 3m16.593s

user 0m0.040s

sys 0m17.110s

All of this is meaningless really - the copy from one disk to another is probably the closest to the performance you will get in real life. Whilst RAID0 flies for a long sequential write it slows down significantly when it has to be read from as well - i.e. real life.

Based on this, I don't know whether it will be more performant to have the OS and one DB on disk1 and the second DB on disk2 or just both on RAID0... We shall see! :)

DISCLAIMER - RAID0 means losing all your data stored on that partition if *any* disk dies.