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 B7B08D1B183 for ; Thu, 29 Jul 2004 23:33:49 -0300 (ADT) 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 73270-07 for ; Fri, 30 Jul 2004 02:33:48 +0000 (GMT) Received: from mail.coretech.co.nz (coretech.co.nz [202.36.204.41]) by svr1.postgresql.org (Postfix) with ESMTP id EAB77D1B179 for ; Thu, 29 Jul 2004 23:33:42 -0300 (ADT) Received: (qmail 30810 invoked from network); 30 Jul 2004 02:33:42 -0000 Received: from localhost (127.0.0.1) by 0 with SMTP; 30 Jul 2004 02:33:42 -0000 Received: from 203.167.203.141 ([203.167.203.141]) by mail.coretech.co.nz (Horde) with HTTP for ; Fri, 30 Jul 2004 14:33:42 +1200 Message-ID: <1091154822.df11fea7be44f@mail.coretech.co.nz> Date: Fri, 30 Jul 2004 14:33:42 +1200 From: markir@coretech.co.nz To: Stephane Tessier Cc: pgsql-performance@postgresql.org Subject: Re: my boss want to migrate to ORACLE References: <000001c474c5$73e2cfc0$4e00020a@develavoie> In-Reply-To: <000001c474c5$73e2cfc0$4e00020a@develavoie> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Disposition: inline Content-Transfer-Encoding: 7bit User-Agent: Internet Messaging Program (IMP) 4.0-cvs X-Originating-IP: Withheld X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200407/234 X-Sequence-Number: 7625 Quoting Stephane Tessier : General parameter suggestions: > shared_buffers = 128000 # min max_connections*2 or 16, 8KB each > effective_cache_size = 196608 # typically 8KB each Try reducing shared_buffers (say 30000). There has been much discussion regards setting this parameter - most folks see minimal gains, or even performance *loss* with settings higher than 30000. > #sort_mem = 32168 # min 64, size in KB Depending on what queries you are experiencing problems with, you might want to set this parameter to something. However with 160 connections you need to be careful, as each one uses this much memory (if it needs to sort). > #wal_buffers = 8 Try this in the 100-1000 range > #checkpoint_segments = 3 Try this quite a lot higher - say 10-50. Pg_autovacuum: I have not used this, so can't really give any help about setup. However you can check how well it is working by selecting relname, reltuples, relpages out of pg_class, and see if you have relations with stupidly low numbers of rows per page (clearly you need to be ANALYZing reasonably frequently for this query to give accurate information). Other Comments: To provide you with more help, we need to know what is slow. Particulary what queries are the most troublesome (post EXPLAIN ANALYZE of them). You can identify these by setting the log_statement and log_duration parameters. Finally: (comment for your boss), it is *not* a given that ORACLE will perform any better..... regards Mark