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

[Eduardo Morras]

At 14:26 17/12/2011, you wrote:
>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.

What FreeBSD tunings have you used in tests? Have you set 
kern.ipc.shm_use_phys=1? Have you tuned the FreeBSD semaphores? Them 
increased more than 5-10% on my server, i increased clients from 25 
(with saturation problems) to 40 (without problems). About UFS/ZFS, i 
prefer UFS, it has less memory/cpu footprint that can use for more 
postgresql cache and tps.

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