From: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
---|---|
To: | Steve <cheetah(at)tanabi(dot)org> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Configuration Advice |
Date: | 2007-01-17 21:37:50 |
Message-ID: | 45AE972E.3000904@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Steve | 2007-01-17 21:56:30 | Re: Configuration Advice |
Previous Message | Chad Wagner | 2007-01-17 21:34:10 | Re: Configuration Advice |