Comparative benchmark: PostgreSQL 9.1

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. PGBench1 PGBench2

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: benchpostgrereal1 The second curve is transactions per seconds: benchpostgrereal2 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.

Google Plus

19 thoughts on “Comparative benchmark: PostgreSQL 9.1

    • Hammer make also many guarantees, but hammer is a transactional FS on the FS layer. ZFS is on the block layer, i think this is a problem when we virtualize.
      Please note Hammer is a powerful FS, with many features (transactional replication over SSH, deduplication, possibility to get a removed file by returning back on the transactions…

  1. Your description doesn’t tally with the graphs you published. Those graphs clearly show Debian and Centos at the top, with DragonFly a distant third.

    • In fact, i have wrote the first part without testing nobarrier option, at this time hammer was the first. In a production environment you must be careful with nobarrier option and don’t use it if you’re not sure. Then DragonFly is the first 🙂

    • Hi David, i agree with you for ZFS, when i found good datas about ZFS + PgSQL i’ll update the graph with it. I don’t use virtio because FreeBSD 9.1 doesn’t recognize my virtio disk. Only FreeBSD 10-0-CURRENT recognize it.

  2. Since barrier=0 made such a huge impact, how about running the benchmark again on Linux with another disk scheduler (like noop or deadline)?

  3. Hello,

    Could you try with Linux on a XFS file system ? I’m using it on my main Postgresql database and I found that it works better than ext4.

    It would be also very interesting to have some informations about NetBSD, but only if you have some spare time.

  4. @Loïc BLOT, no idea; all I can say is that we use SAN @ work (with ext3 + barrier=0) and using the noop disk scheduler made a huge difference with I/O throughput etc. (compared with the default cfq scheduler).

  5. BSD on ZFS and Linuxes on EXT4 are completely flat and capped at the same value, it looks completely artificial. This is clearly a limitation with this virtualization enviroment.
    And then the performance explodes on UFS, Hammer and EXTnobarrier. There must be some disk operation that is capped on the VM that only Ext4 and ZFS use.

    • I tried the benchmark Ubuntu ext4 in bare metal and got similar results, completely flat and capped performance. While running the resource usage is near zero IO, iowait and CPU.
      Then I ran wheezy ext4 inside vbox, the performance was 3x to 5x faster than on bare metal. 3x better than the results in your bench.

      There is some weird bottleneck and I have no idea what it is.

        • Vbox was times faster* than bare metal. The mount options are default and exactly the same.
          The strange and important point is the performance cap in ZFS and ext. They are the most used file systems in servers. I run the dbench in ext4 and there is barely any CPU/IO activity.

          *About vbox weirdness:
          “The fastest virtualization method for the SQLite test was Oracle’s VirtualBox and it was even faster than the bare metal results. However, this is due to a problem with VirtualBox as it is likely not enforcing the sync/fsync requests by SQLite. ”

        • I’ve done some research. And the performance of ext4 and zfs seem to be completely capped by the fsync operation, ext4 used to have a much higher performance until they’ve enabled it by default. From what I’ve read from a Postgres developer 100tps is the performance expected from a common single hard drive, if get much higher than 100tps the hdd or the filesystem is ignoring fsync. That’s one of the reasons vbox is faster than baremetal, vbox simply ignores fsync.

          I really think you should reconsider the recommendation of DragonFly with hammer. FreeBSD with ZFS and Linux with Ext4 despite being the lowest, I think are the best and most reliable setups.

  6. Benchmarking filesystems in a VBox doesn’t really say a lot in my opinion and I think you might get a whole different set of results on bare metal; which would be a really interesting benchmark. Additionally I am pretty sure, those strange phoronix benchmarks are also done in VBox.

    Best wishes!

    • If you read my comment or the benchmark, you’d know why vbox is faster.
      “I think you might get a whole different set of results on bare metal; which would be a really interesting benchmark. Additionally I am pretty sure, those strange phoronix benchmarks are also done in VBox.”
      I think you didn’t even read the title.

Leave a Reply

Your email address will not be published. Required fields are marked *