PostgreSQL user experience: FreeBSD (ZFS) vs OpenIndiana (ZFS) vs Linux (EXT4)

[Martin Matuska]

Hi everyone,

I would like to share some of our expreience with PostgreSQL on FreeBSD.
It has been a while ago since we had to stop using FreeBSD for our
customer's PostgreSQL servers.

PostgreSQL (8.4 and 9.0) was demonstrating slow performance under heavy
loads and one day I decided to compare it to other alternatives on the
very same system (one 8-core with 16GB RAM, another 12-core with 48GB
RAM). With ZFS it was extremely slow and with UFS the speed was
acceptable (still 10-20% slower) if not under high load. Our databases
have a size from several gigabytes to tens of gigabytes.

A single real-world query on a idle system was noticeably slower on a
FreeBSD system than on the other systems (ZFS and UFS). We compared the
EXPLAIN ANALYZE output and the performance penalty was almost equally
spread on all items. With rising loads, PostgreSQL processes remain a
long time in "semwait" and "msgwait" states and the "top" output shows a
high system load on FreeBSD.

I have also tried different tunings, compilers and optimizations, but
with that I was able to gain only 5-10% better results.

The result of a pgbench run by one of my customers on a 12-core system
with 48GB RAM is here (FreeBSD ZFS vs OpenIndiana (ZFS) vs Linux (EXT4):
http://www.vx.sk/benchmarks/postgresql/pgbench_20110630.ods

So our decision so far is the following:
- if we are building a PostgreSQL server for heavy loads, we prefer
Solaris/OpenIndiana (ZFS or UFS) or Linux (EXT4) to FreeBSD (ZFS or UFS)
- if we want to use PostgreSQL on FreeBSD, we prefer UFS to ZFS

P.S: Our webservers still run FreeBSD and e.g. OpenIndiana (Solaris)
performed much worse in our high load real-world web application.

-- 
Martin Matuska
FreeBSD committer
http://blog.vx.sk