From owner-freebsd-performance@FreeBSD.ORG Sat Dec 17 13:26:45 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 4F6A4106566C for ; Sat, 17 Dec 2011 13:26:45 +0000 (UTC) (envelope-from mm@FreeBSD.org) Received: from mail.vx.sk (mail.vx.sk [IPv6:2a01:4f8:150:6101::4]) by mx1.freebsd.org (Postfix) with ESMTP id E0E728FC0C for ; Sat, 17 Dec 2011 13:26:41 +0000 (UTC) Received: from core.vx.sk (localhost [127.0.0.2]) by mail.vx.sk (Postfix) with ESMTP id 0F7D1E63B for ; Sat, 17 Dec 2011 14:26:41 +0100 (CET) X-Virus-Scanned: amavisd-new at mail.vx.sk Received: from mail.vx.sk by core.vx.sk (amavisd-new, unix socket) with LMTP id HL6oPzlwqwR4 for ; Sat, 17 Dec 2011 14:26:38 +0100 (CET) Received: from [10.9.8.1] (188-167-78-15.dynamic.chello.sk [188.167.78.15]) by mail.vx.sk (Postfix) with ESMTPSA id B4CFBE62C for ; Sat, 17 Dec 2011 14:26:38 +0100 (CET) Message-ID: <4EEC9890.7040806@FreeBSD.org> Date: Sat, 17 Dec 2011 14:26:40 +0100 From: Martin Matuska User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:8.0) Gecko/20111105 Thunderbird/8.0 MIME-Version: 1.0 To: freebsd-performance@FreeBSD.org X-Enigmail-Version: 1.3.4 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Cc: Subject: 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: Sat, 17 Dec 2011 13:26:45 -0000 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