Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1B0A69F9255 for ; Wed, 5 Sep 2007 13:06:13 -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 57192-01 for ; Wed, 5 Sep 2007 13:06:10 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.189]) by postgresql.org (Postfix) with ESMTP id 799A29F9577 for ; Wed, 5 Sep 2007 13:06:09 -0300 (ADT) Received: by rv-out-0910.google.com with SMTP id l15so1374111rvb for ; Wed, 05 Sep 2007 09:06:08 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Pq4IUfA36NBWbp8Q+XKixR1YOSif31kls8yLTCYI8X24QSeUIJUgWW6xRVz8SwPcReKoamof5HLUnYVdN2m3UumMGW9YF8qPn+p3HdNjBXmUjYFK6UzDtGThR0M+NzAlYql1bBk+4wrWPywPfq7XkYx7p7h6DJaoguhx+r+NqK8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=LxyFghZOHzsicOrsPEXKiHrB24PWSJzCT/R6FfgVId2M2gHmSpYju63cI3VWpGTDs8HmRJTRvlMWFhf9Ze4tZI1QNMQ8g75geNYHB96vq8iwQi6zrug40m7qMd1cVJbGuV2BR/fsIuJX5X5ZtwbVfY4x9yRj66iojJLufiFuVr8= Received: by 10.141.28.12 with SMTP id f12mr2889915rvj.1189008368373; Wed, 05 Sep 2007 09:06:08 -0700 (PDT) Received: by 10.141.159.11 with HTTP; Wed, 5 Sep 2007 09:06:07 -0700 (PDT) Message-ID: Date: Wed, 5 Sep 2007 11:06:07 -0500 From: "Scott Marlowe" To: "Carlo Stonebanks" Subject: Re: Performance on 8CPU's and 32GB of RAM Cc: "Alvaro Herrera" , pgsql-performance@postgresql.org In-Reply-To: <4F731254987542AF807CA3EDF411D12B@serenity> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20070904230639.GA23731@alvh.no-ip.org> <4F731254987542AF807CA3EDF411D12B@serenity> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/55 X-Sequence-Number: 26600 On 9/5/07, Carlo Stonebanks wrote: > >> Large shared_buffers and Windows do not mix. Perhaps you should leave > the shmem config low, so that the kernel can cache the file pages. > << > > Is there a problem BESIDES the one that used to cause windows to fail to > allocate memory in blocks larger than 1.5GB? > > The symptom of this problem was that postgresql would just refuse to > restart. Microsoft released a patch for this problem and we can now start > postgresql with larger shared buffers. If this is indeed the problem that > you refer to - and it has indeed been solved by Microsoft - is there a down > side to this? There have been some reports that performance-wise large shared buffer settings don't work as well on windows as they do on linux / unix. Don't know myself. Just what I've read. > >> It sounds like you will need a huge lot of vacuuming effort to keep up. > Maybe you should lower autovac scale factors so that your tables are > visited more frequently. A vacuum_delay of 40 sounds like too much > though. > << > > Does autovacuum not impede performance while it is vacuuming a table? Of course vacuum impedes performance. Depends on your I/O subsystem. By adjusting your vacuum parameters in postgresql.conf, the impact can be made pretty small. But not vacuuming has a slow but sure deteriorating effect over time. So, it's generally better to let autovacuum take care of things and run vacuum with a reasonable set of parameters so it doesn't eat all your I/O bandwidth.