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=G+6I0YaaOXvYpNpNkDvv34SaL0aHUIeDCk4zAbk1fsWnZwcG3wIKN6lVrl5h8Jxq52MWaIp1qjRbs1+pwPCkoWgaOSlp2y03Q2fzwlvl+1qdz7UIksv60DDrzJ2MjdlXbPnSXgOoTnh9Sl2KqB+ErtH/IaE3Fc5NqGxNGGBfmZo=
Message-ID: <45AE972E.3000904@enterprisedb.com>
Date: Wed, 17 Jan 2007 21:37:50 +0000
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.8 (X11/20061116)
MIME-Version: 1.0
To: Steve <cheetah@tanabi.org>
CC: pgsql-performance@postgresql.org
Subject: Re: Configuration Advice
References: <1168982580.8275.1169588211@webmail.messagingengine.com>
	<26412.1168983547@sss.pgh.pa.us>
	<1168986053.16393.1169598917@webmail.messagingengine.com>
	<81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com>
	<1169002739.19758.1169636285@webmail.messagingengine.com>
	<1169012767.6271.7.camel@localhost.localdomain>
	<1169044649.19974.1169715743@webmail.messagingengine.com>
	<1169050746.9586.44.camel@state.g2switchworks.com>
	<45AE79D5.9080000@fuzzy.cz>
	<Pine.GSO.4.64.0701171445570.4471@kingcheetah.tanabi.org>
In-Reply-To: <Pine.GSO.4.64.0701171445570.4471@kingcheetah.tanabi.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Steve wrote:
> SO ... our goal here is to make this load process take less time.  It 
> seems the big part is building the big summary table; this big summary 
> table is currently 9 million rows big.  Every night, we drop the table, 
> re-create it, build the 9 million rows of data (we use COPY to put hte 
> data in when it's prepared, not INSERT), and then build the indexes on 
> it -- of which there are many.  

Would it be possible to just update the summary table, instead of 
recreating it from scratch every night?

> Unfortunately this table gets queried in 
> a lot of different ways and needs these indexes; also unfortunately, we 
> have operator class indexes to support both ASC and DESC sorting on 
> columns so these are for all intents and purposes duplicate but required 
> under Postgres 8.1 (we've recently upgraded to Postgres 8.2, is this 
> still a requirement?)

I don't think this has changed in 8.2.

> Building these indexes takes forever!  It's a long grind through inserts 
> and then building the indexes takes a hefty amount of time too.  (about 
> 9 hours).  Now, the application is likely part at fault, and we're 
> working to make it more efficient, but it has nothing to do with the 
> index building time.  I'm wondering what we can do to make this better 
> if anything; would it be better to leave the indexes on?  It doesn't 
> seem to be.  Would it be better to use INSERTs instead of copies?  
> Doesn't seem to be.

Would it help if you created multiple indexes simultaneously? You have 
enough CPU to do it. Is the index creation CPU or I/O bound? 9 million 
rows should fit in 16 GB of memory, right?

> - The load process itself takes about 6 gigs of memory, the rest is free 
> for postgres because this is basically all the machine does.

Can you describe the load process in more detail? What's it doing with 
the 6 gigs?

> - If this was your machine and situation, how would you lay out the 
> emmory settings?  What would you set the FSM to? 

FSM seems irrelevant here..

> Do wal_buffers/full_page_writes matter of FSYNC is off?  

Better turn off full_page_writes, since you can kiss goodbye to data 
integrity anyway with fsync=off.

> Anyway... any advice would be appreciated :)

What's your maintenance_work_mem setting? It can make a big difference 
in sorting the data for indexes.

If you could post the schema including the indexes, people might have 
more ideas...

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com