X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F42023A3CEA for ; Mon, 3 Jan 2005 19:01:28 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08147-09 for ; Mon, 3 Jan 2005 19:00:49 +0000 (GMT) Received: from lakermmtao08.cox.net (lakermmtao08.cox.net [68.230.240.31]) by svr1.postgresql.org (Postfix) with ESMTP id 5CD613A3D05 for ; Mon, 3 Jan 2005 18:56:25 +0000 (GMT) Received: from [192.168.0.8] (really [68.101.70.207]) by lakermmtao08.cox.net (InterMail vM.6.01.04.00 201-2131-117-20041022) with ESMTP id <20050103185625.SRUF16610.lakermmtao08.cox.net@[192.168.0.8]>; Mon, 3 Jan 2005 13:56:25 -0500 From: Robert Treat To: amrit@health2.moph.go.th Subject: Re: Low Performance for big hospital server .. Date: Mon, 3 Jan 2005 13:51:55 -0500 User-Agent: KMail/1.6.2 Cc: pg@fastcrypt.com, PGsql-performance References: <1104634472.41d762681b548@webmail.moph.go.th> <41D9525D.3000409@fastcrypt.com> <1104766805.41d96755a40d9@webmail.moph.go.th> In-Reply-To: <1104766805.41d96755a40d9@webmail.moph.go.th> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="tis-620" Content-Transfer-Encoding: 7bit Message-Id: <200501031351.55065.xzilla@users.sourceforge.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/20 X-Sequence-Number: 9710 On Monday 03 January 2005 10:40, amrit@health2.moph.go.th wrote: > > I realize you may be stuck with 7.3.x but you should be aware that 7.4 > > is considerably faster, and 8.0 appears to be even faster yet. > > There are a little bit incompatibility between 7.3 -8 , so rather difficult > to change. > Sure, but even moving to 7.4 would be a bonus, especially if you use a lot of select * from tab where id in (select ... ) type queries, and the incompataibility is less as well. > > I would seriously consider upgrading, if at all possible. > > > > A few more hints. > > One thing I didn't see mentioned that should have been was to watch for index bloat, which was a real problem on 7.3 machines. You can determine which indexes are bloated by studying vacuum output or by comparing index size on disk to table size on disk. Another thing I didn't see mentioned was to your free space map settings. Make sure these are large enough to hold your data... max_fsm_relations should be larger then the total # of tables you have in your system (check the archives for the exact query needed) and max_fsm_pages needs to be big enough to hold all of the pages you use in a day... this is hard to calculate in 7.3, but if you look at your vacuum output and add the number of pages cleaned up for all tables, this could give you a good number to work with. It would certainly tell you if your setting is too small. -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL