From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Configuration Advice
Date: Wed, 17 Jan 2007 18:08:44 -0500
Message-ID: <60irf5metf.fsf@dba2.int.libertyrms.com> (raw)
References: <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>
cheetah@tanabi.org (Steve) writes:
> 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.
Definitely NOT. Generating an index via a bulk sort is a LOT faster
than loading data into an index one tuple at a time.
We saw a BIG increase in performance of Slony-I when, in version
1.1.5, we added a modification that shuts off indexes during COPY and
then does a reindex. Conceivably, you might look at how Slony-I does
that, and try doing the same thing; it might well be faster than doing
a bunch of reindexes serially. (Or not...)
> Would it be better to use INSERTs instead of copies? Doesn't seem
> to be.
I'd be mighty surprised.
> - The load process itself takes about 6 gigs of memory, the rest is
> free for postgres because this is basically all the machine does.
The settings you have do not seem conspicuously wrong in any way.
The one thought which comes to mind is that if you could turn this
into a *mostly* incremental change, that might help.
The thought:
- Load the big chunk of data into a new table
- Generate some minimal set of indices on the new table
- Generate four queries that compare old to new:
q1 - See which tuples are unchanged from yesterday to today
q2 - See which tuples have been deleted from yesterday to today
q3 - See which tuples have been added
q4 - See which tuples have been modified
If the "unchanged" set is extremely large, then you might see benefit
to doing updates based on deleting the rows indicated by q2,
inserting rows based on q3, and updating based on q4.
In principle, computing and applying those 4 queries might be quicker
than rebuilding from scratch.
In principle, applying q2, then q4, then vacuuming, then q3, ought to
be "optimal."
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/linux.html
"A 'Cape Cod Salsa' just isn't right." -- Unknown
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: cbbrowne@acm.org
Subject: Re: Configuration Advice
In-Reply-To: <60irf5metf.fsf@dba2.int.libertyrms.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