Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 314D465024B for ; Thu, 30 Oct 2008 19:41:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 35003-01-4 for ; Thu, 30 Oct 2008 19:41:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0708.google.com (rv-out-0708.google.com [209.85.198.248]) by postgresql.org (Postfix) with ESMTP id 9EC7B6502C4 for ; Thu, 30 Oct 2008 19:41:25 -0300 (ADT) Received: by rv-out-0708.google.com with SMTP id k29so1009902rvb.0 for ; Thu, 30 Oct 2008 15:41:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; 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; bh=XIykVYfOkdYpbeVxWmWldwqzb/a5MOkPx9wIaw3GEV4=; b=KpiijOEvheDYCekYEuugZ49MCDyn4T8FkNLiDw8slvRKeyNB6HCET1agCmXsWT29Fs YTlYcI22dE1Q1bVD7/J8NXRr+kT4LRSkPQD1gAphZIoWFaHHzQotzJtDvxKuozcNI0+H z/zPOKGgWbiqCJYMeDc9sf6gaTyCb+Dcj0EaU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=NsgJE3l0gD0nu6bnHKbwoyDj7L2obZKW4Sb6LDjd4K4586Hb+P1svAWfksvRxDk4A7 EGqqKw+O8aCROEEf/f8IuwZCu9+K0c5GM2wwWN7PhZ3/ELtFFPUV35/JUfhljTL0ig3A 478Usm5uMuqCl/+utBh3vHo+LnMhsWt8RrFgE= Received: by 10.114.75.1 with SMTP id x1mr9324497waa.25.1225406483569; Thu, 30 Oct 2008 15:41:23 -0700 (PDT) Received: by 10.114.39.11 with HTTP; Thu, 30 Oct 2008 15:41:23 -0700 (PDT) Message-ID: Date: Thu, 30 Oct 2008 16:41:23 -0600 From: "Scott Marlowe" To: "Oliver Johnson" Subject: Re: CPU utilization vs. IO wait, shared buffers? Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/134 X-Sequence-Number: 31715 On Thu, Oct 30, 2008 at 3:41 PM, Oliver Johnson wrote: > Another thing to note, we have VACUUM ANALYZE running on an hourly > interval and the switch from CPU to IO wait appears to always coincide > with a vacuum. Why are you not using autovacuum with appropriate wait parameters to keep it out of your way? Autovacuum tends to make pretty good decisions and you can adjust the aggressiveness with which it kicks in if you need to. > What might cause this shift? > > I have tried adjusting buffer_cache from 512 MB to 1024 MB, but this > did not appear to have an impact. Do you mean shared_buffers? It may well be that larger shared_buffers aren't going to help if you're dealing with a largely random transactional load. that said, 1G shared_buffers is not that big nowadays. I'm assuming by your testing methods you're on a real db server with several dozen gigs of ram... > I also tried upping the work_mem from 1MB to 10MB, and this did not > appear to have an impact either. Look into upping your checkpoint_segments (64 or so is reasonable for a large production server) and possibly increasing your checkpoint_completion_target to something closer to 1.0 (0.7 to 0.8) and see if that helps.