Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C75929FBC66 for ; Sat, 2 Jun 2007 12:21:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 47329-01 for ; Sat, 2 Jun 2007 12:21:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 867189FB888 for ; Sat, 2 Jun 2007 12:21:39 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from pat.uio.no (pat.uio.no [129.240.10.15]) by svr4.postgresql.org (Postfix) with ESMTP id A07955AF044 for ; Sat, 2 Jun 2007 15:21:38 +0000 (GMT) Received: from mail-mx1.uio.no ([129.240.10.29]) by pat.uio.no with esmtp (Exim 4.66) (envelope-from ) id 1HuVQ8-00035m-1B; Sat, 02 Jun 2007 17:21:36 +0200 Received: from smtp.uio.no ([129.240.10.9] helo=mail-mx1.uio.no) by mail-mx1.uio.no with esmtp (Exim 4.66) (envelope-from ) id 1HuVQ7-0000Mz-Lo; Sat, 02 Jun 2007 17:21:35 +0200 Received: from ti200710a080-3665.bb.online.no ([85.164.142.81] helo=[10.1.1.102]) by mail-mx1.uio.no with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.66) (envelope-from ) id 1HuVQ7-0000Mw-HS; Sat, 02 Jun 2007 17:21:35 +0200 Message-ID: <46618B12.9000700@usit.uio.no> Date: Sat, 02 Jun 2007 17:21:54 +0200 From: Rafael Martinez Organization: University of Oslo User-Agent: Thunderbird 1.5.0.10 (X11/20070403) MIME-Version: 1.0 To: Douglas J Hunley Cc: pgsql-performance@postgresql.org Subject: Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x References: <200706020913.32241.doug@hunley.homeip.net> In-Reply-To: <200706020913.32241.doug@hunley.homeip.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-UiO-Resend: resent X-UiO-Spam-info: not spam, SpamAssassin (score=-0.2, required=12.0, autolearn=disabled, AWL=-0.156) X-UiO-Scanned: 8DC2F4EB8002E1394FCD5BE5BBCC2EF8BF6F0F8D X-UiO-SPAM-Test: remote_host: 129.240.10.9 spam_score: -1 maxlevel 200 minaction 2 bait 0 mail/h: 173 total 2118180 max/h 8345 blacklist 0 greylist 0 ratelimit 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/11 X-Sequence-Number: 25044 Douglas J Hunley wrote: Hello > The DB server in question does nothing else, is running CentOS 4.5, kernel > 2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon > 3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap. > After a very quick read of your configuration files, I found some paramaters that need to be change if your server has 8GB of RAM. The values of these parameters depend a lot of how much RAM you have, what type of database you have (reading vs. writing) and how big the database is. I do not have experience with 8.2.x yet, but with 8.1.x we are using as defaults in out 8GB RAM servers these values in some of the paramaters (they are not the only ones, but they are the minimum to change): 25% of RAM for shared_buffers 2/3 of ram for effective_cache_size 256MB for maintenance_work_mem 32-64MB for work_mem 128 checkpoint_segments 2 random_page_cost And the most important of all: fsync should be ***ON*** if you appreciate your data. It looks like you are using default values .... > > #shared_buffers = 32MB # min 128kB or max_connections*16kB > #work_mem = 1MB # min 64kB > #maintenance_work_mem = 16MB # min 1MB > fsync = off # turns forced synchronization on or off > #effective_cache_size = 128MB [........................] -- Rafael Martinez, Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/>