X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 315E6D1DD3B for ; Fri, 16 Jan 2004 00:18:17 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 14970-03 for ; Thu, 15 Jan 2004 20:17:48 -0400 (AST) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr1.postgresql.org (Postfix) with ESMTP id 085E9D1D661 for ; Thu, 15 Jan 2004 20:17:45 -0400 (AST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-34.mail.demon.net with esmtp (Exim 3.35 #1) id 1AhHgJ-0004sV-0Y; Fri, 16 Jan 2004 00:17:47 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id 8D85D1691E; Fri, 16 Jan 2004 00:17:46 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 7CA09167DB; Fri, 16 Jan 2004 00:17:43 +0000 (GMT) From: Richard Huxton To: "Anjan Dave" , Subject: Re: shared_buffer value Date: Fri, 16 Jan 2004 00:17:42 +0000 User-Agent: KMail/1.5 References: <203C7FC3FF2D7A4588CE0429A87F3C9A04DE82@vt-pe2550-001.vantage.vantage.com> In-Reply-To: <203C7FC3FF2D7A4588CE0429A87F3C9A04DE82@vt-pe2550-001.vantage.vantage.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200401160017.42790.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/161 X-Sequence-Number: 5336 On Thursday 15 January 2004 22:49, Anjan Dave wrote: > Gurus, > > I have defined the following values on a db: > > shared_buffers = 10240 # 10240 = 80MB > max_connections = 100 > sort_mem = 1024 # 1024KB is 1MB per operation > effective_cache_size = 262144 # equals to 2GB for 8k pages > > Rest of the values are unchanged from default. > > > The poweredge 2650 machine has 4GB RAM, and the size of the database > (size of 'data' folder) is about 5GB. PG is 7.4, RH9. OK - settings don't look unreasonable so far. > The machine has been getting quite busy (when, say, 50 students login at > the same time, when others have logged in already) and is maxing out at > 100 connections (will increase this tonight probably to 200). We have > been getting "too many clients" message upon trying to connect. Once > connected, the pgmonitor, and the 'pg_stat_activity' show connections > reaching about 100. > > There's a series of SELECT and UPDATE statements that get called for > when a group of users log in simultaneously...and for some reason, many > of them stay there for a while... > > During that time, if i do a 'top', i can see multiple postmaster > processes, each about 87MB in size. The Memory utilization drops down to > about 30MB free, and i can see a little bit of swap utilization in > vmstat then. On linux you'll see three values: SIZE, RSS and SHARE. SIZE is what you're looking at, RSS is resident set size (it's in main memory) and SHARE is how much is shared with other processes. So - 3 processes each with RSS=15MB, SIZE=10MB take up 10+5+5+5 = 25MB. Don't worry about a tiny bit of swap - how is your buff/cache doing then? > Should i decrease the buffer value to about 50MB and monitor? That shared_buffer is between all backends. The sort_mem however, is *per sort*, not even per backend. So - if a complicated query uses four sorts you could use 4MB in one backend. > Interestingly, at one point, we vacuumed the database, and the size > reported by 'df -k' on the pgsql slice dropped very > significantly...guess, it had been using a lot of temp files? You need to run VACUUM regularly to reclaim unused space. Since you're on 7.4, take a look at the pg_autovacuum utility, or start by running VACUUM ANALYZE from a cron job every evening. Perhaps a VACUUM FULL at weekends? > Further steps will be to add more memory, and possibly drop/recreate a > couple of indexes that are used in the UPDATE statements. A REINDEX might be worthwhile. Details on this and VACUUM in the manuals. -- Richard Huxton Archonet Ltd