Re: ATA disks and RAID controllers for database servers

Dear all,

Here is the second installment concerning ATA disks and RAID controller
use in a database server.

In this post a 2 disk RAID0 configuration is tested, and the results
compared to the JBOD configuration in the previous message.

So again, what I was attempting to examine here was : is it feasable to
build a reasonably well performing database server using ATA disks? (in
particular would disabling the ATA write cache spoil performance
completely?)

The System
----------

Dell 410
2x700Mhz PIII 512Mb
Promise Fastrack TX2000 Controller
2x40G 7200RPM ATA-133 Maxtor Diamond +8 configured as JBOD or
2x40G 7200RPM ATA-133 Maxtor Diamond +8 configured as RAID0
Freebsd 4.8 (options SMP APIC_IO i686)
Postgresql 7.4beta2 (-O2 -funroll-loops -fexpensive-optimizations
-march=i686)
ATA Write caching controlled via the loader.conf variable hw.ata.wc (1 = on)

The Tests
---------

1. Sequential and random writes and reads of a file twice the size of memory

Files were written using read(2), write(2) functions - buffered at 8K.
For the random case 10% of the file was sampled using lseek(2), and read
or written. (see
http://techdocs.postgresql.org/markir/download/iowork/iowork-1.0.tar.gz)

The filesystem was built with newfs options :
-U -b 32768 -f 4096 [ softupdates, 32K blocks, 4K fragments ]

The RAID0 strip size was 128K. This gave the best performance (32K, 64K
were tried - I got tired of rebuilding the system at this point, so 256K
and above may be better).

2. Postgresql pgbench benchmark program

This was run using the options :
-t 1000 [ 1000 transactions ]
-s 10 [ scale factor 10 ]
-c 1,2,4,8,16 [ 1-16 clients ]

Non default postgresql.conf settings were:
shared_buffers = 5000
wal_buffers = 100
checkpoint_segments = 10

A checkpoint was forced after each run to prevent cross run
interference. Three runs through were performed for each configuration,
and the results averaged. A new database was created for each 1-16
client "set" of runs.

Results
-------

Test 1

System IO Operation Throughput(M/s) Options
------------------------------------------------
Dell
JBOD seq write 11 hw.ata.wc=0
seq read 50 hw.ata.wc=0
random write 1.3 hw.ata.wc=0
random read 4.2 hw.ata.wc=0

seq write 20 hw.ata.wc=1
seq read 53 hw.ata.wc=1
random write 1.7 hw.ata.wc=1
random read 4.1 hw.ata.wc=1

RAID0 seq write 13 hw.ata.wc=0
seq read 100 hw.ata.wc=0
random write 1.7 hw.ata.wc=0
random read 4.2 hw.ata.wc=0

seq write 38 hw.ata.wc=1
seq read 100 hw.ata.wc=1
random write 2.5 hw.ata.wc=1
random read 4.3 hw.ata.wc=1

Test 2

System Clients Throughput(tps) Options
------------------------------------------------
Dell
JBOD 1 27 hw.ata.wc=0
2 38 hw.ata.wc=0
4 55 hw.ata.wc=0
8 58 hw.ata.wc=0
16 66 hw.ata.wc=0

1 82 hw.ata.wc=1
2 137 hw.ata.wc=1
4 166 hw.ata.wc=1
8 128 hw.ata.wc=1
16 117 hw.ata.wc=1

RAID0 1 33 hw.ata.wc=0
2 39 hw.ata.wc=0
4 61 hw.ata.wc=0
8 73 hw.ata.wc=0
16 80 hw.ata.wc=0

1 95 hw.ata.wc=1
2 156 hw.ata.wc=1
4 194 hw.ata.wc=1
8 179 hw.ata.wc=1
16 144 hw.ata.wc=1

Conclusions
-----------

Test 1

It is clear that with write caching on the RAID0 configuration greatly
improves sequential read and write performance - almost twice as fast as
the JBOD case. The random write performance is improved by a reasonable
factor too.

For write caching disabled, the write rates are similar to the JBOD
case. This *may* indicate some design issue in the Promise controller.

Test 2

For write caching on or off, the RAID0 configuration is faster - by
about 18 percent.

General

Clearly it is possible to obtain very good performance with write
caching on using RAID0, and if you have a UPS together with good backup
practice then this could be the way to go.

With caching off there is a considerable decrease in performance,
however this performance may be "good enough" if viewed in a
cost-benefit-safely manner.

Criticisms
----------

It would have been good to have two SCSI disks to test in the Dell
machine (as opposed to using a Sun 280R), unfortunately I can't justify
the cost of them for this test :-(. However there are some examples of
similar comparisons in the Postgresql General thread "Recomended FS"
(without an ATA RAID controller).

Mark