From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Steve <cheetah@tanabi.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Configuration Advice
Date: Wed, 17 Jan 2007 21:37:50 +0000
Message-ID: <45AE972E.3000904@enterprisedb.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0701171445570.4471@kingcheetah.tanabi.org>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, cheetah@tanabi.org
Subject: Re: Configuration Advice
In-Reply-To: <45AE972E.3000904@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox