Message-ID: <4DA31AA5.20101@peak6.com>
Date: Mon, 11 Apr 2011 10:13:41 -0500
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: <sthomas@peak6.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1
MIME-Version: 1.0
To: Chris Ruprecht <chris@ruprecht.org>
CC: <pgsql-performance@postgresql.org>
Subject: Re: Multiple index builds on same table - in one sweep?
References: <DD8709CB-474F-4002-B555-1ED974371364@ruprecht.org>
In-Reply-To: <DD8709CB-474F-4002-B555-1ED974371364@ruprecht.org>
Content-Type: multipart/mixed; boundary="------------070402040007030105000607"

--------------070402040007030105000607
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

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

--------------070402040007030105000607
Content-Type: application/x-sh; name="generate_rebuild_scripts.sh"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="generate_rebuild_scripts.sh"

#!/bin/bash

if [ $# -lt 1 ]; then
  echo
  echo "Usage: $0 DB_NAME NUM_THREADS"
  echo
  exit 1
fi

sDBName=$1
nThreads=$2

if [ ! -n tablist.txt ]; then
  echo
  echo "Error: Please create a file named 'tablist.txt' that"
  echo "       contains the list of tables to reindex."
  echo
  exit 1
fi

if [ -f index_rebuild_1.sql ]; then
  rm index_rebuild_*.sql
fi

for (( i=1 ; $i < $nThreads + 1; i=$i+1 )); do
  echo "\timing" > index_rebuild_${i}.sql
done

sTabList=''
for x in $(cat tablist.txt); do
  sTabList="${sTabList}, '$x'"
done
sTabList=${sTabList:2}

let i=1
for sIndex in $(psql -Atc "
      SELECT n.nspname || '.\"' || i.relname || '\"'
        FROM pg_index x
        JOIN pg_class c ON c.oid = x.indrelid
        JOIN pg_class i ON i.oid = x.indexrelid
        LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
        LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
       WHERE c.relkind = 'r' AND i.relkind = 'i'
         AND c.relname IN ($sTabList)
       ORDER BY c.reltuples,c.relname" $sDBName)
do
  let nThread=$i%nThreads+1

  echo "REINDEX INDEX $sIndex;" >> index_rebuild_${nThread}.sql

  let i=$i+1
done

--------------070402040007030105000607
Content-Type: application/x-sh; name="launch_rebuild_scripts.sh"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="launch_rebuild_scripts.sh"

#!/bin/bash

if [ ! -d log ]; then
  mkdir log
fi

sDBName=$1

for sScript in index_rebuild_*.sql; do
  sLog=log/${sScript/.sql}.log
  psql -e -f $sScript $sDBName &>${sLog} &
done

--------------070402040007030105000607--