Re: Configuration Advice - Mailing list pgsql-performance

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