From: Jeff Davis <pgsql@j-davis.com>
To: Douglas J Hunley <doug@hunley.homeip.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: 7 hrs for a pg_restore?
Date: Tue, 19 Feb 2008 11:46:07 -0800
Message-ID: <1203450367.3846.101.camel@dogma.ljc.laika.com> (raw)
In-Reply-To: <200802191420.11347.doug@hunley.homeip.net>
References: <200802191303.58840.doug@hunley.homeip.net>
<1203445403.3846.86.camel@dogma.ljc.laika.com>
<200802191420.11347.doug@hunley.homeip.net>
On Tue, 2008-02-19 at 14:20 -0500, Douglas J Hunley wrote:
> > Keep in mind, if you have several GB worth of indexes, they take up
> > basically no space in the logical dump (just the "CREATE INDEX" command,
> > and that's it). But they can take a lot of processor time to build up
> > again, especially with localized text.
> >
>
> that could be a factor here. It is a UNICODE db, and we do a lot of text-based
> indexing for the application
I assume you're _not_ talking about full text indexes here.
These factors:
* unicode (i.e. non-C locale)
* low I/O utilization
* indexes taking up most of the 7 hours
mean that we've probably found the problem.
Localized text uses sorting rules that are not the same as binary sort
order, and it takes much more CPU power to do the comparisons, and sorts
are already processor-intensive operations.
Unfortunately postgresql does not parallelize this sorting/indexing at
all, so you're only using one core.
I'd recommend restoring everything except the indexes, and then you can
restore the indexes concurrently in several different sessions so that
it uses all of your cores. Build your primary key/unique indexes first,
and then after those are built you can start using the database while
the rest of the indexes are building (use "CREATE INDEX CONCURRENTLY").
Regards,
Jeff Davis
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: pgsql@j-davis.com, doug@hunley.homeip.net
Subject: Re: 7 hrs for a pg_restore?
In-Reply-To: <1203450367.3846.101.camel@dogma.ljc.laika.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