X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 693CE9FB416 for ; Thu, 31 Aug 2006 14:46:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 11288-08-2 for ; Thu, 31 Aug 2006 14:46:33 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.195]) by postgresql.org (Postfix) with ESMTP id 912BA9FB3F4 for ; Thu, 31 Aug 2006 14:42:07 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id 14so527738nzn for ; Thu, 31 Aug 2006 10:42:06 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=YVnNGkLbQycwIjS0cqM0cJAvFAWwzbLITy9CuoIlSA8LVgMJYBjW6ppuk8IVq848EylR8KpkcR5IhCubj3dudW3kOTVOTMi+SHOtdn7VJqMANJJP/wdy2zwna8iu4EsArUuFn30oMiy4tns3otubY/R5/fzKDh0DBHKHYHMGu3w= Received: by 10.65.219.4 with SMTP id w4mr1557799qbq; Thu, 31 Aug 2006 10:42:06 -0700 (PDT) Received: by 10.65.137.12 with HTTP; Thu, 31 Aug 2006 10:42:06 -0700 (PDT) Message-ID: Date: Thu, 31 Aug 2006 13:42:06 -0400 From: "Merlin Moncure" To: "Cosimo Streppone" Subject: Re: High concurrency OLTP database performance tuning Cc: "Pg Performance list" In-Reply-To: <44F7040E.7040805@streppone.it> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <44F7040E.7040805@streppone.it> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.305 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/492 X-Sequence-Number: 20677 On 8/31/06, Cosimo Streppone wrote: > Good morning, > - postgresql.conf, especially: > effective_cache_size (now 5000) > bgwriter_delay (500) > commit_delay/commit_siblings (default) while thse settings may help, don't expect too much. ditto shared buffers. your fsync is false btw. the major gotcha in high transaction volume systems is stats_command_string (leave it off). > - start to use tablespaces for most intensive tables this is an i/o optimization mostly. again, dont expect much. > - analyze the locks situation while queries run > - upgrade to 8.1.n absolutely you want to do this. when I moved my converted isam projects which dont sound too far from your workload, I saw a huge speed increase with 8.1. > - convert db partition filesystem to ext2/xfs? > (now ext3+noatime+data=writeback) > - ??? meh. :-) I think application level improvements are the name of the game here. Make sure your application or middleware is using the parameterized query interface in libpq. Another possible optimiation is to attempt application level caching in conjunction with some server side locking, Since details are light, only general hints are possible :) consider move to opteron or intel woodcrest platform. a single opteron 170 will easily beat your two xeons, and 2x270 will be a whole new world. woodcrests are great as well if you can get them. also, if you are not already on a *nix kernel, get yourself on one. Merlin