Update and some verbose output

R. Willmington

2004-09-16 16:35:58 UTC

Here is the copy of a reply i received via e-mail, and my answer to

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