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 EB37FD1B470 for ; Wed, 3 Dec 2003 21:23:33 +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 04772-02 for ; Wed, 3 Dec 2003 17:23:03 -0400 (AST) Received: from web.opentransfer.com (unknown [69.49.227.40]) by svr1.postgresql.org (Postfix) with ESMTP id D2511D1D0AE for ; Wed, 3 Dec 2003 17:22:45 -0400 (AST) Received: (from httpd@localhost) by web.opentransfer.com (8.11.6/8.11.6) id hB3LMRX17342 for pgsql-performance@postgresql.org; Wed, 3 Dec 2003 15:22:27 -0600 X-Authentication-Warning: web.opentransfer.com: httpd set sender to erik@norvelle.net using -f Received: from 212.21.224.151 ( [212.21.224.151]) as user erik@norvelle.net@69.49.238.2 by 69.49.227.23 with HTTP; Wed, 3 Dec 2003 15:22:27 -0600 Message-ID: <1070486547.3fce54135a65d@69.49.227.23> Date: Wed, 3 Dec 2003 15:22:27 -0600 From: erik@norvelle.net To: pgsql-performance@postgresql.org Subject: Re: Update performance ... Recommended configuration changes? MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.1 X-Originating-IP: 212.21.224.151 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/53 X-Sequence-Number: 4913 Thanks to Greg Stark, Tom Lane and Stephan Szabo for their advice on rewriting my query... the revised query plan claims it should only take about half the time my original query did. Now for a somewhat different question: How might I improve my DB performance by adjusting the various parameters in postgresql.conf and kernel config? Again, TKA. Here's what I've currently got (hardware, kernel config. and postgresql.conf) Hardware: Mac iBook, G3 900Mhz, 640MB memory (This is my research machine :p ) OS: OS X 10.2.6 Postgresql version: 7.3.2 Kernel Config: sysctl -w kern.sysv.shmmax=4194304 sysctl -w kern.sysv.shmmin=1 sysctl -w kern.sysv.shmmni=32 sysctl -w kern.sysv.shmseg=8 sysctl -w kern.sysv.shmall=1024 ========================= Snip of postgresql.conf ================= # # Shared Memory Size # shared_buffers = 128 # min max_connections*2 or 16, 8KB each max_fsm_relations = 2000 # min 10, fsm is free space map, ~40 bytes max_fsm_pages = 20000 # min 1000, fsm is free space map, ~6 bytes max_locks_per_transaction = 128 # min 10 wal_buffers = 16 # min 4, typically 8KB each # # Non-shared Memory Sizes # sort_mem = 65535 # min 64, size in KB vacuum_mem = 8192 # min 1024, size in KB # # Write-ahead log (WAL) # #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds # #commit_delay = 0 # range 0-100000, in microseconds #commit_siblings = 5 # range 1-1000 # fsync = false #wal_sync_method = fsync # the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync #wal_debug = 0 # range 0-16 ========================== End Snip ======================= Saludos, Erik Norvelle