Update and some verbose output - Mailing list pgsql-admin
From | willmington@gmx.net (R. Willmington) |
---|---|
Subject | Update and some verbose output |
Date | |
Msg-id | e6568d.0409160835.392a4b1b@posting.google.com Whole thread Raw |
List | pgsql-admin |
Here is the copy of a reply i received via e-mail, and my answer to it: >>you should have given the verbose output from the vacuum full. you >>could have seen how many dead row versions it had removed. perhaps >>there were enough versions to justify 33 minutes of whatever it was >>doing. Well i was absolutly content with the 33 minutes for the vacuum full on the staging system. I ran it on the whole database and it did a lot of defragmentation, the resulting filsystem i/o justified the running time. But this is not the problem, I just wished vacuum full would perform the exact same way on the live system. >>it is likely other connections are preventing the row versions from >>being reclaimed. again, you should have included verbose output. See my reply to post from Gaetano Mendola. There are no connections opened that are not in "idle" state. BTW, the staging server has the same applications running with the same connections opened and vacuum full did fine on it. >>well, to prove it disconnect all the other connections and then try the >>delete. you are not really eliminating the unknowns by assuming they >>don't exist. Very true. The point is, disconnecting all applications on the live system is not really an option unless it's the last thing i haven't tried. Ok, here is some input: 1.) Table description _____________________________ Table "public.cms_contents" Column | Type | Modifiers -----------------------+------------------------+------------------------------- id | integer | not null domain_id | integer | not null language | character(2) | not null state_id | character(1) | not null type_name | character varying(50) | not null parent_id | integer | not null sort_children_by | character varying(100) | not null default 'order_hint' show_children_in_tree | bit(1) | not null default B'1'::"bit" is_online | bit(1) | Indexes: pk__cms_contents primary key btree (id, domain_id, "language"), cms_contents_State_idx btree (state_id) Triggers: RI_ConstraintTrigger_56418, RI_ConstraintTrigger_56421, RI_ConstraintTrigger_56424, RI_ConstraintTrigger_56470, RI_ConstraintTrigger_56471 2.) Output of VACUUM FULL _________________________ INFO: Pages 182: Changed 0, reaped 111, Empty 0, New 0; Tup 12675: Vac 0, Keep/VTL 0/0, UnUsed 231, MinLen 97, MaxLen 149; Re-using: Free/Avail. Space 12024/4228; EndEmpty/Avail. Pages 0/14. CPU 0.00s/0.01u sec elapsed 0.00 sec. INFO: Index pk__cms_contents: Pages 52; Tuples 12675: Deleted 0. CPU 0.00s/0.01u sec elapsed 0.01 sec. INFO: Index cms_contents_State_idx: Pages 38; Tuples 12675: Deleted 0. CPU 0.00s/0.01u sec elapsed 0.01 sec. INFO: Rel cms_contents: Pages: 182 --> 182; Tuple(s) moved: 0. CPU 0.00s/0.02u sec elapsed 0.01 sec. 3.) Now i delete five records (takes almost 5 seconds...) ______________________________ DELETE FROM cms_contents where state_id = 'd'; #DELETE 5 4.) Now i re - run vacuum full verbose: ______________________________ INFO: Pages 182: Changed 0, reaped 111, Empty 0, New 0; Tup 12670: Vac 5, Keep/VTL 0/0, UnUsed 231, MinLen 97, MaxLen 149; Re-using: Free/Avail. Space 12596/4928; EndEmpty/Avail. Pages 0/16. CPU 0.00s/0.01u sec elapsed 0.00 sec. INFO: Index pk__cms_contents: Pages 52; Tuples 12670: Deleted 5. CPU 0.00s/0.01u sec elapsed 0.01 sec. INFO: Index cms_contents_State_idx: Pages 38; Tuples 12670: Deleted 5. CPU 0.01s/0.00u sec elapsed 0.01 sec. INFO: Rel cms_contents: Pages: 182 --> 182; Tuple(s) moved: 5. CPU 0.00s/0.05u sec elapsed 0.04 sec. INFO: Index pk__cms_contents: Pages 52; Tuples 12670: Deleted 5. CPU 0.00s/0.01u sec elapsed 0.00 sec. INFO: Index cms_contents_State_idx: Pages 38; Tuples 12670: Deleted 5. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM __________ In fact, it seems to perform well and removes the deleted records. The problem is that the performance of queries against the table is as low as it was before i ran VACUUM FULL for the first time in a year, whereas performance on the staging system with the SAME database and the exact same data was dramatically increased. Thus, i have a staging db on a slow 1 -processor server with the exact same data and load performing more than a hundred times better than my live 4 - processor machine. Could it be that VACUUM FULL is not defragmenting the live DB? Kind Regards, R. Willmington
pgsql-admin by date: