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-21 17:28:58
Message-ID: 87ablu5icl.fsf@mnc.ch (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2008-02-21 18:09:32
Subject: Re: 7 hrs for a pg_restore?
Previous:From: MatthewDate: 2008-02-21 13:58:16
Subject: Re: 7 hrs for a pg_restore?

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