Skip site navigation (1) Skip section navigation (2)

Re: 7 hrs for a pg_restore?

From: Guillaume Cottenceau <gc(at)mnc(dot)ch>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: 7 hrs for a pg_restore?
Date: 2008-02-22 10:40:42
Message-ID: 874pc146l1.fsf@mnc.ch (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane <tgl 'at' sss.pgh.pa.us> writes:

> Guillaume Cottenceau <gc(at)mnc(dot)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

In response to

pgsql-performance by date

Next:From: Moritz OnkenDate: 2008-02-22 15:42:29
Subject: store A LOT of 3-tuples for comparisons
Previous:From: Mark KirkwoodDate: 2008-02-22 05:10:18
Subject: Re: 4s query want to run faster

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group