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 A856F529E2 for ; Thu, 28 Jul 2005 06:35:02 -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 98894-02 for ; Thu, 28 Jul 2005 09:34:55 +0000 (GMT) Received: from linuxworld.com.au (unknown [203.34.46.50]) by svr1.postgresql.org (Postfix) with ESMTP id 67036529C8 for ; Thu, 28 Jul 2005 06:34:53 -0300 (ADT) Received: from linuxworld.com.au (IDENT:swm@localhost.localdomain [127.0.0.1]) by linuxworld.com.au (8.13.2/8.13.2) with ESMTP id j6S9Yoti002779; Thu, 28 Jul 2005 19:34:50 +1000 Received: from localhost (swm@localhost) by linuxworld.com.au (8.13.2/8.13.2/Submit) with ESMTP id j6S9Yorh002776; Thu, 28 Jul 2005 19:34:50 +1000 X-Authentication-Warning: linuxworld.com.au: swm owned process doing -bs Date: Thu, 28 Jul 2005 19:34:50 +1000 (EST) From: Gavin Sherry X-X-Sender: swm@linuxworld.com.au To: Kari Lavikka Cc: pgsql-performance@postgresql.org Subject: Re: Finding bottleneck In-Reply-To: Message-ID: References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/433 X-Sequence-Number: 13674 Hi, On Thu, 28 Jul 2005, Kari Lavikka wrote: > ----------------->8 Relevant rows from postgresql.conf 8<----------------- > > shared_buffers = 15000 # min 16, at least max_connections*2, 8KB each > work_mem = 1536 # min 64, size in KB As an aside, I'd increase work_mem -- but it doesn't sound like that is your problem. > maintenance_work_mem = 32768 # min 1024, size in KB > > max_fsm_pages = 1000000 # min max_fsm_relations*16, 6 bytes each > max_fsm_relations = 5000 # min 100, ~50 bytes each > > vacuum_cost_delay = 15 # 0-1000 milliseconds > vacuum_cost_limit = 120 # 0-10000 credits > > bgwriter_percent = 2 # 0-100% of dirty buffers in each round > > fsync = true # turns forced synchronization on or off > # fsync, fdatasync, open_sync, or open_datasync > wal_buffers = 128 # min 4, 8KB each Some benchmarking results out today suggest that wal_buffers = 1024 or even 2048 could greatly assist you. > commit_delay = 80000 # range 0-100000, in microseconds > commit_siblings = 10 # range 1-1000 This may explain the fact that you've got backed up queries and idle CPU -- I'm not certain though. What does disabling commit_delay do to your situation? Gavin