From: Guillaume Cottenceau <gc@mnc.ch>
To: pgsql-performance@postgresql.org
Subject: Re: 7 hrs for a pg_restore?
Date: Fri, 22 Feb 2008 11:40:42 +0100
Message-ID: <874pc146l1.fsf@mnc.ch> (raw)
In-Reply-To: <13059.1203617372@sss.pgh.pa.us>
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>
<87ablu5icl.fsf@mnc.ch>
<13059.1203617372@sss.pgh.pa.us>
Tom Lane <tgl 'at' sss.pgh.pa.us> writes:
> Guillaume Cottenceau <gc@mnc.ch> writes:
>> 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.
>
> The main thing that jumps out at me is that boosting checkpoint_segments
> would probably help. I tend to set it to 30 or so (note that this
> corresponds to about 1GB taken up by pg_xlog).
Interestingly, from a bzipped dump, there is no win; however,
from an uncompressed dump, increasing checkpoint_segments from 3
to 30 decreases clock time from 9'50 to 8'30 (15% if I'm
correct).
--
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: <874pc146l1.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