Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 91BA89FB833 for ; Tue, 24 Apr 2007 05:53:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 88643-07 for ; Tue, 24 Apr 2007 05:53:27 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from mu-out-0910.google.com (mu-out-0910.google.com [209.85.134.187]) by postgresql.org (Postfix) with ESMTP id 062339FB82D for ; Tue, 24 Apr 2007 05:53:26 -0300 (ADT) Received: by mu-out-0910.google.com with SMTP id g7so2342479muf for ; Tue, 24 Apr 2007 01:53:25 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=ggXDcShbZwCRZAKKgRIu08xlv0ftOABPNa6AximCXfXJYpHbAL384/uxWkkbA+p8dycqHxc43w3BASRk48G7yPvGOnxNOSutTs3DD9ElTPi/Z9k6ugAGgZ9Jr/6+Dbw2U9reG1WcDJsV0lNw3AjPKdliHpEOdDGmJHG8ykr5mME= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=ow4KYpkvqscwzHW8/HfR90xLNoC4I++FoPLXjq4h4ZWpKqVfQNzRh2nL7UEcg+EmnNvVErctMvH5tOlJVFBmo+EeDQSa4hrcPXD2z8pLqHO1H6Icl1RgSg2v/4x5oAorvgM8jqyXMcpNX6vRx+ohrq9Y4Ah0zWJw4+0EjJm4T1Y= Received: by 10.82.158.12 with SMTP id g12mr10440327bue.1177404805251; Tue, 24 Apr 2007 01:53:25 -0700 (PDT) Received: from ?192.168.1.34? ( [86.27.86.98]) by mx.google.com with ESMTP id b30sm23625ika.2007.04.24.01.53.06; Tue, 24 Apr 2007 01:53:17 -0700 (PDT) Message-ID: <462DC556.5080601@enterprisedb.com> Date: Tue, 24 Apr 2007 09:52:38 +0100 From: Heikki Linnakangas User-Agent: Icedove 1.5.0.8 (X11/20061116) MIME-Version: 1.0 To: Greg Smith CC: pgsql-performance@postgresql.org Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/403 X-Sequence-Number: 24280 Greg Smith wrote: > On Sat, 21 Apr 2007, Nelson Kotowski wrote: > >> I identified that the cluster command over the lineitem table (cluster >> idx_lineitem on lineitem) is the responsible. I got to this conclusion >> because when i run it in the 1GB and 2GB database i am able to >> complete this script in 10 and 30 minutes each. But when i run this >> command over the 5GB database, it simply seems as it won't end. > > Have you looked in the database log files for messages? Unless you > changed some other parameters from the defaults that you didn't mention, > I'd expect you've got a constant series of "checkpoint occuring too > frequently" errors in there, which would be a huge slowdown on your > index rebuild. Slowdowns from checkpoints would get worse with an > increase of shared_buffers, as you report. Index builds don't write WAL, unless archive_command has been set. A higher shared_buffers setting can hurt index build performance, but for a different reason: the memory spent on shared_buffers can't be used for sorting and caching the sort tapes. > The default setting for checkpoint_segments of 3 is extremely low for > even a 1GB database. Try increasing that to 30, restart the server, and > rebuild the index to see how much the 1GB case speeds up. If it's > significantly faster (it should be), try the 5GB one again. A good advice, but it's unlikely to make a difference at load time. BTW: With CVS HEAD, if you create the table in the same transaction (or TRUNCATE) as you load the data, the COPY will skip writing WAL which can give a nice speedup. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com