pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Slow Restoration of a template1 Database (ALTER GROUP)
5+ messages / 2 participants
[nested] [flat]

* Slow Restoration of a template1 Database (ALTER GROUP)
@ 2005-02-08 18:23 Ben Young <Ben.Young@etrials.com>
  2005-02-08 18:57 ` Re: Slow Restoration of a template1 Database (ALTER GROUP) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Ben Young @ 2005-02-08 18:23 UTC (permalink / raw)
  To: 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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Slow Restoration of a template1 Database (ALTER GROUP)
  2005-02-08 18:23 Slow Restoration of a template1 Database (ALTER GROUP) Ben Young <Ben.Young@etrials.com>
@ 2005-02-08 18:57 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2005-02-08 18:57 UTC (permalink / raw)
  To: Ben Young <Ben.Young@etrials.com>; +Cc: pgsql-performance

"Ben Young" <Ben.Young@etrials.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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Slow Restoration of a template1 Database (ALTER GROUP)
@ 2005-02-08 19:59 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2005-02-08 19:59 UTC (permalink / raw)
  To: Ben Young <Ben.Young@etrials.com>; +Cc: pgsql-performance

"Ben Young" <Ben.Young@etrials.com> writes:
> template1=# VACUUM FULL VERBOSE pg_group;
> INFO:  --Relation pg_catalog.pg_group--
> INFO:  Pages 124: Changed 1, reaped 124, Empty 0, New 0; Tup 4: Vac 966, Keep/VTL 0/0, UnUsed 156, MinLen 92, MaxLen 136; Re-using: Free/Avail. Space 1008360/1008360; EndEmpty/Avail. Pages 0/124.
> 	CPU 0.01s/0.00u sec elapsed 0.07 sec.
> INFO:  Index pg_group_name_index: Pages 19072; Tuples 4: Deleted 966.
                                          ^^^^^
> 	CPU 1.51s/0.25u sec elapsed 17.19 sec.
> INFO:  Index pg_group_sysid_index: Pages 4313; Tuples 4: Deleted 966.
                                           ^^^^
> 	CPU 0.48s/0.04u sec elapsed 6.06 sec.

Whoa.  Can you say "index bloat"?

I think that the only way to fix this is to REINDEX pg_group, which IIRC
in 7.3 requires stopping the postmaster and doing it in a standalone
backend (check the REINDEX reference page for details).  Make sure the
toast table gets reindexed too, as its index is oversized as well.
(Recent PG versions will automatically reindex the toast table when you
reindex its parent table, but I forget whether 7.3 did so; you might
have to explicitly "reindex pg_toast.pg_toast_1261".)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Slow Restoration of a template1 Database (ALTER GROUP)
@ 2005-02-08 20:08 Ben Young <Ben.Young@etrials.com>
  2005-02-08 20:32 ` Re: Slow Restoration of a template1 Database (ALTER GROUP) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Ben Young @ 2005-02-08 20:08 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom,

Is the "index bloat" prevented/reduced in newer versions of Postgres?

Is there a way to prevent/reduce it with the current version of Postgres I'm using?

Many Thanks,
Ben

"Ben Young" <Ben.Young@etrials.com> writes:
> template1=# VACUUM FULL VERBOSE pg_group;
> INFO:  --Relation pg_catalog.pg_group--
> INFO:  Pages 124: Changed 1, reaped 124, Empty 0, New 0; Tup 4: Vac 966, Keep/VTL 0/0, UnUsed 156, MinLen 92, MaxLen 136; Re-using: Free/Avail. Space 1008360/1008360; EndEmpty/Avail. Pages 0/124.
> 	CPU 0.01s/0.00u sec elapsed 0.07 sec.
> INFO:  Index pg_group_name_index: Pages 19072; Tuples 4: Deleted 966.
                                          ^^^^^
> 	CPU 1.51s/0.25u sec elapsed 17.19 sec.
> INFO:  Index pg_group_sysid_index: Pages 4313; Tuples 4: Deleted 966.
                                           ^^^^
> 	CPU 0.48s/0.04u sec elapsed 6.06 sec.

Whoa.  Can you say "index bloat"?

I think that the only way to fix this is to REINDEX pg_group, which IIRC
in 7.3 requires stopping the postmaster and doing it in a standalone
backend (check the REINDEX reference page for details).  Make sure the
toast table gets reindexed too, as its index is oversized as well.
(Recent PG versions will automatically reindex the toast table when you
reindex its parent table, but I forget whether 7.3 did so; you might
have to explicitly "reindex pg_toast.pg_toast_1261".)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Slow Restoration of a template1 Database (ALTER GROUP)
  2005-02-08 20:08 Re: Slow Restoration of a template1 Database (ALTER GROUP) Ben Young <Ben.Young@etrials.com>
@ 2005-02-08 20:32 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2005-02-08 20:32 UTC (permalink / raw)
  To: Ben Young <Ben.Young@etrials.com>; +Cc: pgsql-performance

"Ben Young" <Ben.Young@etrials.com> writes:
> Is the "index bloat" prevented/reduced in newer versions of Postgres?

Depends on what's causing it.  Have you been inventing alphabetically
greater group names and getting rid of smaller names over time?  If so,
this is a known problem that should be fixed in 7.4.  The 7.4 release
notes say:

     In previous releases, B-tree index pages that were left empty
     because of deleted rows could only be reused by rows with index
     values similar to the rows originally indexed on that page. In 7.4,
     VACUUM records empty index pages and allows them to be reused for
     any future index rows.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-02-08 20:32 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-08 18:23 Slow Restoration of a template1 Database (ALTER GROUP) Ben Young <Ben.Young@etrials.com>
2005-02-08 18:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-08 19:59 Re: Slow Restoration of a template1 Database (ALTER GROUP) Tom Lane <tgl@sss.pgh.pa.us>
2005-02-08 20:08 Re: Slow Restoration of a template1 Database (ALTER GROUP) Ben Young <Ben.Young@etrials.com>
2005-02-08 20:32 ` Tom Lane <tgl@sss.pgh.pa.us>

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