Test data of PostgreSQL running on FreeBSD and Linux

Test data of PostgreSQL running on FreeBSD and Linux

This afternoon I wanted to test DragonFly dports. I suddenly wondered why I didn't compare the performance difference between PostgreSQL in BSD and Linux. For this reason, I set some standards to compare the performance differences on different operating systems. I use the same hardware and software to obtain data more accurately. These tests are based on PGSQL9.1.

After I tested it In Debian Linux, I was surprised by the results. So I did a test in other Linux systems, such as Centos6.4 IN THE Redhat class.

The following are some of my Tested operating systems:

• DragonFlyBSD 3.4.1 (Hammer)
• FreeBSD 9.1-p3 (UFS2 + J)
• Freeebsd 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, I use 24 GB memory and a Phenom x6 1055 T processor based on the KVM (libvirt) system. The version is as follows:

 
 

  
  
qemu 1.4.1-3  
  
  
libvirt 1.0.5-4 
 
 

Each virtual machine uses the same configuration:

• 50 GB hard drive (except FreeBSD)
• 12 GB memory
• 4-core CPU

Test now. Command: pgbench-T 60-cX-jX

Perform pgbench instance testing within 60 seconds on the database, and use X user and X-ray thread for one user)

Each database is configured by default and supports simultaneous connection of 300 requests.

Part 1: Virtual drivers

The first figure shows the real-time transaction processing capacity, and the second figure shows the transaction processing capacity per second.

The performance test results are surprising.‍The default Operating System of DragonflyBSD) has higher performance than all other systems, followed by FreeBSD. The performance of DragonflyBSD is 25% higher than that of FreeBSD than that of Linux (s!

We have two Linux systems that far exceed other systems, with a transaction volume of 7000. If you do not consider the number of clients, you still cannot surpass it. The graph is surprisingly uniform. Only Debian does not reach the end point of the test, because the Debian PostgreSQL parameter cannot be modified manually to have 100 connections at the same time.

In fact, these 7000 curves explain that the ext4 barrier system) protects the file system, which has very poor performance in PostgreSQL. In the second test, we have added the nobarrier/barrier = 0 option to ext4 through/etc/fstab ). This option handles debrid) FS, but it is a very risky option. It is used only when you have a 1/5/6 controller of the disk array. When a write operation is completed on your disk, if your server restarts, for example, power failure ......), the file or the entire disk may crash, and if it is in a database instance, the consequences will be miserable.

In short, our FreeBSD work on ZFS makes up for Linux (s). Maybe it is caused by virtualization? Or is the concept of ZFS the problem?

Part 2: Physical drive

To validate our findings, we realize that the same baseline is on a physical drive. I only need to maintain performance and optimization, except ZFS, there must be a point in this supported comparison. Here, Centos is deleted because its performance is similar to Debian.

The first graph shows the processing capacity per minute:

The second graph shows the processing capacity per second:

DragonFlyBSD has very similar performance between physical and virtual. We can say that virtio drivers are very good. Debian has the same issue, with 50,000 requests per minute.

Note the following two points:

• UFS performance async and noatime options), can improve) twice or three times the performance, but you must also ensure that it is ext4nobarrier option)
• By setting the sync = disabled and atime = off options, ZFS can improve performance by about 10-15, better than all other FS and their associated performance. In addition, the sync = disabled option is less risky than the nobarrier/async option.

ZFS is the leader in this physical indicator.

You can see the link of the exact benchmark data below the benchmark:

Benchmarks-PostGreSQL

Finally, if you choose a system for your PostgreSQL database, you do not have to hesitate to use BSD. Even if you do not have a disk array controller, you can choose FreeBSDUFS or DragonFlyBSDHammer. Next, you can choose Linux.

Thanks to Emmanuel Florac and Axel Perrier for providing accurate data for the ext4 barrier option ).