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
