From owner-freebsd-performance@FreeBSD.ORG Tue Dec 28 14:15:15 2010 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 CD25F1065672 for ; Tue, 28 Dec 2010 14:15:15 +0000 (UTC) (envelope-from nicolas.haller@corp.nerim.fr) Received: from baneblade.noc.nerim.net (baneblade.noc.nerim.net [62.4.17.143]) by mx1.freebsd.org (Postfix) with ESMTP id 997BD8FC16 for ; Tue, 28 Dec 2010 14:15:15 +0000 (UTC) Received: by baneblade.noc.nerim.net (Postfix, from userid 1000) id 866251DBDE; Tue, 28 Dec 2010 14:59:41 +0100 (CET) Date: Tue, 28 Dec 2010 14:59:41 +0100 From: Nicolas Haller To: freebsd-performance@freebsd.org Message-ID: <20101228135940.GE2660@baneblade.noc.nerim.net> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline User-Agent: Mutt/1.5.20 (2009-06-14) X-Mailman-Approved-At: Tue, 28 Dec 2010 14:56:31 +0000 Subject: tunning disk cache for pgsql? 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: Tue, 28 Dec 2010 14:15:15 -0000 Hi all, I use a new box with 4GB RAM as a pgsql server. In pgsql, you can set the effective_cache_size to indicate the memory available to cache disk I/O. As "recommended", my box use 1300MB to shared buffers (IPC shared memory) and 2700 Mo to disk cache. If I look memory usage in top, it say: Mem: 1154M Active, 1911M Inact, 601M Wired, 112M Cache, 417M Buf, 148M Free So I think FreeBSD must be limited using memory to cache I/O disk by some sysctl variables but I don't know which ones. I'm a little bit confused with some posts I found in this ML :-/ If someone can help me, it will be great. Regards, -- Nicolas Haller From owner-freebsd-performance@FreeBSD.ORG Fri Dec 31 00:32:06 2010 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 0AD981065741 for ; Fri, 31 Dec 2010 00:32:06 +0000 (UTC) (envelope-from jim@nasby.net) Received: from noel.decibel.org (99-153-64-76.uvs.austtx.sbcglobal.net [99.153.64.76]) by mx1.freebsd.org (Postfix) with ESMTP id C7E858FC08 for ; Fri, 31 Dec 2010 00:32:05 +0000 (UTC) Received: from [192.168.1.70] (adsl-69-211-50-5.dsl.chcgil.ameritech.net [69.211.50.5]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by noel.decibel.org (Postfix) with ESMTPSA id 1072F6D45D; Fri, 31 Dec 2010 00:15:36 +0000 (UTC) Mime-Version: 1.0 (Apple Message framework v1082) Content-Type: text/plain; charset=us-ascii From: Jim Nasby In-Reply-To: <20101228135940.GE2660@baneblade.noc.nerim.net> Date: Thu, 30 Dec 2010 18:15:34 -0600 Content-Transfer-Encoding: quoted-printable Message-Id: References: <20101228135940.GE2660@baneblade.noc.nerim.net> To: Nicolas Haller X-Mailer: Apple Mail (2.1082) X-Mailman-Approved-At: Fri, 31 Dec 2010 00:51:00 +0000 Cc: freebsd-performance@freebsd.org Subject: Re: tunning disk cache for pgsql? 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: Fri, 31 Dec 2010 00:32:06 -0000 On Dec 28, 2010, at 7:59 AM, Nicolas Haller wrote: > I use a new box with 4GB RAM as a pgsql server. In pgsql, you can > set the effective_cache_size to indicate the memory available to cache > disk I/O. > As "recommended", my box use 1300MB to shared buffers (IPC shared = memory) > and 2700 Mo to disk cache. That's probably not a great mix unless your workload is very read-heavy. = Writes will push data through shared buffers back into the OS, which = will also try to cache it, so you'll end up with double-buffering. > If I look memory usage in top, it say: > Mem: 1154M Active, 1911M Inact, 601M Wired, 112M Cache, 417M Buf, 148M = Free The Cache reported by top in FreeBSD isn't filesystem cache; it's a = cache for some internal stuff. Buf are filesystem buffers, but they're = not the only mechanism for the OS to cache data. Most data is actually = cached via active and inactive pages. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net From owner-freebsd-performance@FreeBSD.ORG Fri Dec 31 09:06:14 2010 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 EDC631065696 for ; Fri, 31 Dec 2010 09:06:14 +0000 (UTC) (envelope-from nicolas.haller@corp.nerim.fr) Received: from baneblade.noc.nerim.net (baneblade.noc.nerim.net [62.4.17.143]) by mx1.freebsd.org (Postfix) with ESMTP id B76138FC14 for ; Fri, 31 Dec 2010 09:06:14 +0000 (UTC) Received: by baneblade.noc.nerim.net (Postfix, from userid 1000) id 3E6B6B0A40; Fri, 31 Dec 2010 10:06:13 +0100 (CET) Date: Fri, 31 Dec 2010 10:06:13 +0100 From: Nicolas Haller To: Jim Nasby Message-ID: <20101231090612.GN2660@baneblade.noc.nerim.net> References: <20101228135940.GE2660@baneblade.noc.nerim.net> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.20 (2009-06-14) X-Mailman-Approved-At: Fri, 31 Dec 2010 12:24:11 +0000 Cc: freebsd-performance@freebsd.org Subject: Re: tunning disk cache for pgsql? 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: Fri, 31 Dec 2010 09:06:15 -0000 On Thu, Dec 30, 2010 at 06:15:34PM -0600, Jim Nasby wrote: > On Dec 28, 2010, at 7:59 AM, Nicolas Haller wrote: > > I use a new box with 4GB RAM as a pgsql server. In pgsql, you can > > set the effective_cache_size to indicate the memory available to cache > > disk I/O. > > As "recommended", my box use 1300MB to shared buffers (IPC shared memory) > > and 2700 Mo to disk cache. > That's probably not a great mix unless your workload is very > read-heavy. Writes will push data through shared buffers back into the > OS, which will also try to cache it, so you'll end up with > double-buffering. Interresting. My DB is not heavily loaded yet so I don't see any problem at this time. I just follow recommendations. I thought Postgresql write and sync disk after writing WAL or doing a checkpoint. > > If I look memory usage in top, it say: > > Mem: 1154M Active, 1911M Inact, 601M Wired, 112M Cache, 417M Buf, 148M Free > The Cache reported by top in FreeBSD isn't filesystem cache; it's a > cache for some internal stuff. Buf are filesystem buffers, but they're > not the only mechanism for the OS to cache data. Most data is actually > cached via active and inactive pages. Ok, so when Postgresql doing a query for a second time for (very simple) example, FreeBSD can retrieve data from inactive mem too? Someone knows if there is a page which explains FreeBSD mechanisms about memory and fs cache management? I think I must read something on it :-) Thanks, -- Nicolas Haller