PostgreSQL 9.1 benchmark

This afternoon i want to test DragonFly dports. Then i got an idea, why not compare PostgreSQL performances between BSD and Linux ? I have done a little benchmark to see the performances gap between multiple OS. To have the best bench, i use the same hardware and the same software. Those tests have been done under pgsql 9.1.

When i did the bench under Linux Debian, I was surprized of the stats, then also do it on a Redhat like, Centos 6.4.

Those operating systems has been choosed.

DragonFlyBSD 3.4.1 (Hammer)
FreeBSD 9.1-p3 (UFS2+J)
FreeBSD 9.1-p3 (ZFS v28)
Debian 7: Wheezy (ext4, kernel 3.2)
Debian 7: Wheezy (ext4, kernel 3.2, barrier=0)
Centos 6.4 (ext4, kernel 2.6.32)
Centos 6.4 (ext4, kernel 2.6.32, nobarrier)

For the hardware part, the test was done under a KVM (libvirt) system with 24GB memory and a Phenom x6 1055T processor. Here are the packages version:

qemu 1.4.1-3
libvirt 1.0.5-4

Each VM has the same configuration:

50GB disk storage (virtio for each OS except FreeBSD)
12GB memory
quad-core CPU

Now the benchmark. The used command was following: pgbench -T 60 -cX -jX pgbench utility tests during 60sec on the database, using X clients and X threads (1 client per thread). Each database is configured by default, but supports 300 connections simultaneously.

First part: Virtual drive

The first graph show transaction number on time, and second for each second.

The performance test is surprizing. We have DragonflyBSD first (of the OS with default options) which outdo all other systems, followed by FreeBSD. DragonFlyBSD performances are amazing, more than 25% over FreeBSD and 200% over Linux(s) !

Far behind the challenger, we have the two Linux, which reach 7000 transactions but don’t seem to want to get over it, regardless the client number. This curve is very suprizing, very uniform. Only Debian doesn’t reach the end of the test, because the Debian PostgreSQL version cannot exceed the 100 connections without tuning other parameters.

In fact, this 7000 curve is explained by ext4 barrier system which protect file system and make very bad performances on PostgreSQL. In a second test, we have added nobarrier/barrier=0 options to ext4 (via /etc/fstab). This options debrid the FS but it’s a very risky option. Use it only if you have a hardware raid 1/5/6 controller why a battery. If your server reboots (electric fault…) when there is a write is done on your disks, the file or the entire disk may be corrupt, and in the database case it’s very tragic.

Finally, the last, our FreeBSD with ZFS labor to make up the Linux(s). Maybe it’s due to virtualization ? Or a problem in the ZFS conception ?

Second part: physical drive

To verify our results, we realise the same benchmark on a physical drive.I only keep performances with optimizations, except for ZFS, which must have a comparison point on this support. Centos has also been removed because it has same performances as Debian (a little fewer). The first curve is total transactions for 1 minute: The second curve is transactions per seconds: DragonFlyBSD has similar performances between physical and virtual, we can say virtio driver is very good. Debian has the same issue, with 50k transactions per minute. The two point you can note are:

UFS performances (with async and noatime options), double or triple performances, but you must have the same guarantees as ext4 (with nobarrier)
ZFS multiply by 10-15 its performances with sync=disabled and atime=off options, surpassing all other FS and offering concurrencing performances. Moreover, sync=disabled is less dangerous than nobarrier/async options.

ZFS is the leader of this physical benchmark.

Can you look at the precise benchmark datas on this link: Benchmarks - PostGreSQL

To conclude, if you would choose a system for your PostgreSQL databases, use a BSD without hesitation, wheter a FreeBSD (UFS) or a DragonFlyBSD (Hammer) if you haven’t a raid controller, else choose a Linux

Thanks to Emmanuel Florac and Axel Perrier for their precisions about ext4 barrier option.