Re: Configuration Advice

[Heikki Linnakangas <heikki(at)enterprisedb(dot)com>]

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