From: Shaun Thomas <sthomas@peak6.com>
To: Chris Ruprecht <chris@ruprecht.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Multiple index builds on same table - in one sweep?
Date: Mon, 11 Apr 2011 10:13:41 -0500
Message-ID: <4DA31AA5.20101@peak6.com> (raw)
In-Reply-To: <DD8709CB-474F-4002-B555-1ED974371364@ruprecht.org>
References: <DD8709CB-474F-4002-B555-1ED974371364@ruprecht.org>
On 04/09/2011 11:28 AM, Chris Ruprecht wrote:
> I'm wondering if there is a way to build these indexes in parallel
> while reading the table only once for all indexes and building them
> all at the same time. Is there an index build tool that I missed
> somehow, that can do this?
I threw together a very crude duo of shell scripts to do this. I've
attached them for you. To use them, you make a file named tablist.txt
which contains the names of all the tables you want to reindex, and then
you run them like this:
bash generate_rebuild_scripts.sh my_database 8
bash launch_rebuild_scripts.sh my_database
The first one in the above example would connect to my_database and
create eight scripts that would run in parallel, with indexes ordered
smallest to largest to prevent one script from getting stuck with
several large indexes while the rest got small ones. The second script
just launches them and makes a log directory so you can watch the progress.
I've run this with up to 16 concurrent threads without major issue. It
comes in handy.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
Attachments:
generate_rebuild_scripts.sh (application/x-sh, 1.1K) download
launch_rebuild_scripts.sh (application/x-sh, 180B) download
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: sthomas@peak6.com, chris@ruprecht.org
Subject: Re: Multiple index builds on same table - in one sweep?
In-Reply-To: <4DA31AA5.20101@peak6.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