DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:from:to:subject:date:user-agent:cc:references:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:message-id;
	b=bCmJeJXHNyhjl3NlqsrUBf1UOIm8I2jA+CFs9xAKtKVlu1FrN6eX0vMNwAeHt7wdxxSN2vQ+PoAaFJU/JWNiYhQWNUayAdgsuhtG3YNSAfy7PTyuJtuMpTYFAkOxoW8JGb6TOvWltjjyEOIyxEpaSWKbNnt29KVbB33YlOXWT2g=
From: Dimitri <dimitrik.fr@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Parallel Vacuum
Date: Fri, 23 Mar 2007 16:37:32 +0100
User-Agent: KMail/1.9.3
Cc: Michael Stone <mstone+postgres@mathom.us>
References: <200703221435.54408.dimitrik.fr@gmail.com>
	<200703221924.39278.dimitrik.fr@gmail.com>
	<20070322184613.GJ11402@mathom.us>
In-Reply-To: <20070322184613.GJ11402@mathom.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200703231637.33211.dimitrik.fr@gmail.com>

On Thursday 22 March 2007 19:46, Michael Stone wrote:
> On Thu, Mar 22, 2007 at 07:24:38PM +0100, Dimitri wrote:
> >you're right until you're using a single disk :)
> >Now, imagine you have more disks
>
> I do have more disks. I maximize the I/O performance by dedicating
> different sets of disks to different tables. YMMV. I do suggest watching
> your I/O rates and wallclock time if you try this to see if your
> aggregate is actually substantially faster than the single case. (I
> assume that you haven't yet gotten far enough to actually do performance
> testing.) You may also want to look into tuning your sequential I/O
> performance.
>
> Mike Stone

Mike, specially for you :)

Parallel Vacuum Test
======================

- Database 'db_OBJ'
   PgSQL 8.2.3
   tables: object1, object2, ... object8 (all the same)
   volume: 10.000.000 rows in each table, 22GB in total

- Script Mono Vacuum
  $ cat vac_mono.sh
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object1
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object2
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object3
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object4
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object5
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object6
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object7
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object8
  $

- Script Parallel Vacuum
  $ cat vac_pll.sh
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object1 &
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object2 &
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object3 &
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object4 &
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object5 &
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object6 &
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object7 &
/usr/local/pgsqlFF/bin/vacuumdb -p 5464 -d db_OBJ -t object8 &
wait
  $


Test 1: Cold Clean database (already previously vacuumed)
=========================================================
 Scenario:
  - stop database
  - flush FS cache (umount/mount)
  - start database
  - execute vacuum script

$ time sh vac_mono.sh
real    4m24.23s
user    0m0.00s
sys     0m0.01s

$ time sh vac_pll.sh
real    1m9.36s
user    0m0.00s
sys     0m0.01s


Test 2: Hot Dirty database (modified and not vacuumed)
======================================================
 Scenario:
  - stop database
  - flush FS cache (umount/mount)
  - start database
  - execute 200.000 updates against each from 8 object' tables
  - execute vacuum script

$ time sh vac_mono.sh
real    9m36.90s
user    0m0.00s
sys     0m0.01s

$ time sh vac_pll.sh
real    2m10.41s
user    0m0.00s
sys     0m0.02s


Speed-up x4 is obtained just because single vacuum process reaching max 
80MB/sec in throughput, while with 8 parallel vacuum processes I'm jumping to 
360MB/sec... And speakink about Sequential I/O: while you're doing read - 
file system may again prefetch incoming data in way once you reclaim next 
read - your data will be already in FS cache. However, file system 
cannot 'pre-write' data for you - so having more concurrent writers helps a 
lot! (Of course in case you have a storage configured to keep concurrent 
I/O :))

Well, why all this staff?...
Let's imagine once you need more performance, and you buy 10x times more 
performant storage box, will you still able to kill it with a single-process 
I/O activity? No... :)  To scale well you need to be able to split your work 
in several task executed in parallel. And personally, I'm very happy we can 
do it with vacuum now - the one of the most critical part of PostgreSQL...

Best regards!
-Dimitri