From owner-freebsd-performance@FreeBSD.ORG Mon Dec 19 20:36:42 2011 Return-Path: Delivered-To: freebsd-performance@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id E3AF61065672 for ; Mon, 19 Dec 2011 20:36:42 +0000 (UTC) (envelope-from nec556@retena.com) Received: from resmaa12.ono.com (smtp12.ono.com [62.42.230.20]) by mx1.freebsd.org (Postfix) with ESMTP id 77CD98FC08 for ; Mon, 19 Dec 2011 20:36:42 +0000 (UTC) Received: from GogPortatil.retena.com (95.22.232.16) by resmaa12.ono.com (8.5.113) (authenticated as nec556@retena.com) id 4EE6D4BA0014B834 for freebsd-performance@freebsd.org; Mon, 19 Dec 2011 21:36:40 +0100 Message-ID: <4EE6D4BA0014B834@> (added by postmaster@resmaa12.ono.com) X-Mailer: QUALCOMM Windows Eudora Version 7.1.0.9 Date: Mon, 19 Dec 2011 21:36:47 +0100 To: freebsd-performance@freebsd.org From: Eduardo Morras Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Antivirus: AVG for E-mail 10.0.1415 [2108/4090] Subject: Re: PostgreSQL user experience: FreeBSD (ZFS) vs OpenIndiana (ZFS) vs Linux (EXT4) X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 19 Dec 2011 20:36:43 -0000 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