From owner-freebsd-database@FreeBSD.ORG Fri Jan 13 21:51:22 2006 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 67E2816A41F for ; Fri, 13 Jan 2006 21:51:22 +0000 (GMT) (envelope-from reinoud.v@n.leeuwen.net) Received: from spoetnik.xs4all.nl (spoetnik.xs4all.nl [213.84.24.58]) by mx1.FreeBSD.org (Postfix) with ESMTP id 8C13343D70 for ; Fri, 13 Jan 2006 21:51:19 +0000 (GMT) (envelope-from reinoud.v@n.leeuwen.net) Received: by spoetnik.xs4all.nl (Postfix, from userid 1000) id B209B1CC71; Fri, 13 Jan 2006 22:51:18 +0100 (CET) Date: Fri, 13 Jan 2006 22:51:18 +0100 From: Reinoud van Leeuwen To: freebsd-database@freebsd.org Message-ID: <20060113215118.GX32593@spoetnik.xs4all.nl> Mail-Followup-To: freebsd-database@freebsd.org References: <787bbe1c0601131220j7b3a052ege5fe4683156312a7@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <787bbe1c0601131220j7b3a052ege5fe4683156312a7@mail.gmail.com> User-Agent: Mutt/1.4.2.1i X-message-flag: Warning! The sender of this mail thinks you should use a more secure email client. Subject: Re: Horrible PostgreSQL performance with NFS X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 13 Jan 2006 21:51:22 -0000 On Fri, Jan 13, 2006 at 09:20:13PM +0100, Slawek Zak wrote: > Hello everyone, > > A couple of days ago I've moved our production database from local > disks to NetAPP filer serving NFS. Performance for this server dropped > by factor of 10 if not more. From a happy 10% load, the server hit the > ceiling and sees load of 100% all the time with runqueue above 30. The > server is a Sun V40z machine, 4x Opteron 852, 16GB RAM, tuned for 1600 > concurrent connections. I'm running FreeBSD 6.0 BETA5. I don't see any > problem with the network bandwidth or I/O opcount. Total bandwidth > generated by the machine tops at 10Mbps. NICs are bge configured for > 100Mbps full-duplex. Filer hardly notices the traffic. It stays around > 1% CPU load and 500 NFS ops/s including static file transfers. This > particular model can handle around 6000 I/Os per second. Trussing > database processes shows that most of the syscalls are semops. I tried > to search the archives but couldn't find anything useful for such > setup. > > I can provide postgresql config. Sysctl knobs remain untouched not > counting basic NFS tweaking. For mount_nfs I tried to change NFS > buffer sizes -r -w. I also tried the -L option to keep the lock > traffic local. On the postgres side, setting fsync = off in > postgresql.conf. It didn't change a thing. > > Any advice besides "give up on NFS" is most welcome. I need NetAPP > over NFS for snapshots and easy FS management. Snapshots of your database files? What do you plan to do with those? Posgresql does support PITR these days... -- __________________________________________________ "Nothing is as subjective as reality" Reinoud van Leeuwen reinoud.v@n.leeuwen.net http://www.xs4all.nl/~reinoud __________________________________________________