Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 7BD682E00D5
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 21 Feb 2008 13:29:30 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 93296-02-8
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 21 Feb 2008 13:29:19 -0400 (AST)
Received: from mail.mnc.ch (62-2-77-205.static.cablecom.ch [62.2.77.205])
	by postgresql.org (Postfix) with ESMTP id DA41F2E00D6
	for <pgsql-performance@postgresql.org>;
	Thu, 21 Feb 2008 13:29:02 -0400 (AST)
Received: from localhost (mail2.mnc.ch [127.0.0.1])
	by mail.mnc.ch (Postfix) with ESMTP id B019F23079
	for <pgsql-performance@postgresql.org>;
	Thu, 21 Feb 2008 17:29:00 +0000 (GMT)
X-Virus-Scanned: amavisd-new at mnc.ch
Received: from mail.mnc.ch ([127.0.0.1])
	by localhost (mail2.mnc.ch [127.0.0.1]) (amavisd-new, port 10025)
	with ESMTP id zqw7RSAE4p00 for <pgsql-performance@postgresql.org>;
	Thu, 21 Feb 2008 17:28:59 +0000 (GMT)
Received: from meuh.localdomain (62-2-77-204.static.cablecom.ch [62.2.77.204])
	by mail.mnc.ch (Postfix) with ESMTP id EA3A023073
	for <pgsql-performance@postgresql.org>;
	Thu, 21 Feb 2008 17:28:59 +0000 (GMT)
Received: by meuh.localdomain (Postfix, from userid 1000)
	id 9BFB6183760; Thu, 21 Feb 2008 18:28:57 +0100 (CET)
To: pgsql-performance@postgresql.org
Subject: Re: 7 hrs for a pg_restore?
References: <200802191303.58840.doug@hunley.homeip.net>
	<47BB1C51.6010203@archonet.com> <19241.1203445378@sss.pgh.pa.us>
	<F80D4D50-9F3F-4CAA-B01E-B644912126D7@torgo.978.org>
From: Guillaume Cottenceau <gc@mnc.ch>
X-Hashcash: 
 1:20:080221:pgsql-performance@postgresql.org::sWVU56kPh8wV3MpB:0000000000000000000000000000000a5
Date: Thu, 21 Feb 2008 18:28:58 +0100
In-Reply-To: <F80D4D50-9F3F-4CAA-B01E-B644912126D7@torgo.978.org> (Jeff's
	message of "Tue\, 19 Feb 2008 15\:07\:30 -0500")
Message-ID: <87ablu5icl.fsf@mnc.ch>
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/267
X-Sequence-Number: 28809

Jeff <threshar 'at' torgo.978.org> writes:

> I wonder if it would be worthwhile if pg_restore could emit a warning
> if maint_work_mem is "low" (start flamewar on what "low" is).
>
> And as an addition to that - allow a cmd line arg to have pg_restore
> bump it before doing its work?  On several occasions I was moving a
> largish table and the COPY part went plenty fast, but when it hit
> index creation it slowed down to a crawl due to low maint_work_mem..

I have made a comparison restoring a production dump with default
and large maintenance_work_mem. The speedup improvement here is
only of 5% (12'30 => 11'50).

Apprently, on the restored database, data is 1337 MB[1] and
indexes 644 MB[2][2]. Pg is 8.2.3, checkpoint_segments 3,
maintenance_work_mem default (16MB) then 512MB, shared_buffers
384MB. It is rather slow disks (Dell's LSI Logic RAID1), hdparm
reports 82 MB/sec for reads.

Ref: 
[1] db=# SELECT sum(relpages)*8/1024 FROM pg_class, pg_namespace WHERE pg_namespace.oid = pg_class.relnamespace AND relkind = 'r' AND nspname = 'public';
 ?column? 
----------
     1337
 
    (query run after ANALYZE)

    notice there are quite few toast pages to account:

    db=# SELECT relname, relpages FROM pg_class WHERE relname like '%toast%' ORDER BY relpages DESC;
       relname        | relpages 
----------------------+----------
 pg_toast_2618        |       17
 pg_toast_2618_index  |        2
 pg_toast_87570_index |        1
 pg_toast_87582_index |        1
 (...)

[2] db=# SELECT sum(relpages)*8/1024 FROM pg_class, pg_namespace WHERE pg_namespace.oid = pg_class.relnamespace AND relkind = 'i' AND nspname = 'public';
 ?column? 
----------
      644

-- 
Guillaume Cottenceau