From: Guillaume Cottenceau <gc@mnc.ch>
To: pgsql-performance@postgresql.org
Subject: Re: 7 hrs for a pg_restore?
Date: Thu, 21 Feb 2008 18:28:58 +0100
Message-ID: <87ablu5icl.fsf@mnc.ch> (raw)
In-Reply-To: <F80D4D50-9F3F-4CAA-B01E-B644912126D7@torgo.978.org>
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>
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
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: gc@mnc.ch
Subject: Re: 7 hrs for a pg_restore?
In-Reply-To: <87ablu5icl.fsf@mnc.ch>
* 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