Re: [PERFORM] Slow Restoration of a template1 Database (ALTER GROUP)
"Ben Young" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Slow Restoration of a template1 Database (ALTER GROUP)
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" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Slow Restoration of a template1 Database (ALTER GROUP)
"Ben Young" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Slow Restoration of a template1 Database (ALTER GROUP)
"Ben Young" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
