| Lists: | pgsql-performance |
|---|
| From: | "Ben Young" <Ben(dot)Young(at)etrials(dot)com> |
|---|---|
| To: | <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Slow Restoration of a template1 Database (ALTER GROUP) |
| Date: | 2005-02-08 18:23:55 |
| Message-ID: | 41A1CBC76FDECC42B67946519C6677A901D301FB@pippin.int.etrials.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
All,
When trying to restore my template1 database (7.3.4) I am
experiencing very long delays. For 600 users (pg_shadow)
and 4 groups (pg_group) it is taking 1hr and 17 minutes to
complete. All of the create user statements are processed in a
matter of seconds, but each alter groups statement takes
about 10 seconds to process. I have done a full vacuum,
reindex (contrib) and restart before my run, but this did
not have an impact on load times. I have also searched
the archives to no avail and modified my postgresql.conf
file as recommended General Bits on www.varlena.com
One other thing to mention is that this restoration has
been occurring on our slave server every hour for the
last 3-4 months and seems to be getting progressively
worse even if new users are not created in our master server.
DUMP: pg_dumpall -g > foo
RESTORE: psql template1 < foo
Cheers,
Ben Young
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | "Ben Young" <Ben(dot)Young(at)etrials(dot)com> |
| Cc: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Slow Restoration of a template1 Database (ALTER GROUP) |
| Date: | 2005-02-08 18:57:40 |
| Message-ID: | 9190.1107889060@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
"Ben Young" <Ben(dot)Young(at)etrials(dot)com> writes:
> When trying to restore my template1 database (7.3.4) I am
> experiencing very long delays. For 600 users (pg_shadow)
> and 4 groups (pg_group) it is taking 1hr and 17 minutes to
> complete. All of the create user statements are processed in a
> matter of seconds, but each alter groups statement takes
> about 10 seconds to process.
I tried doing 1000 ALTER GROUP ADD USER commands in 7.3, and didn't
see any particular performance problem. Could we see the output
of "VACUUM FULL VERBOSE pg_group"?
regards, tom lane