Koji vacuuming

[Toshio Kuratomi]

We've been having some issues vacuuming the huge rpmfiles table in koji 
for over a month.  After some help optimizing our postgres server from 
Devrim GÜNDÜZ of Command Prompt we were finally able to complete that 
task (as well as the whole server running much better.)

Here's some preliminary information about the vacuuming.  I'll have more 
later today -- a combination of a script I'm writing to help us evaluate 
which tables need frequent vacuuming and more exact timing from a second 
run of this vacuum process (to see if it will be markedly faster when 
run on an already vacuumed database.).

Approximate vacuum runtime: 14 hours

Before Vacuum
=============
koji=# select * from pgstattuple('rpmfiles');

table_len          | 20169555968
tuple_count        | 99381945
tuple_len          | 14163528564
tuple_percent      | 70.22
dead_tuple_count   | 5036605
dead_tuple_len     | 741444680
dead_tuple_percent | 3.68
free_space         | 4460801412
free_percent       | 22.12

After Vacuum
============
table_len          | 20214169600
tuple_count        | 99690347
tuple_len          | 14206464600
tuple_percent      | 70.28
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 5211934688
free_percent       | 25.78

Notes
=====
The vacuum succeeded in clearing out all of the dead tuples that had 
accumulated in the database which is what vacuuming is suposed to do. 
(Dead tuples are old rows that have either been deleted or updated.)

One thing that was interesting to me was that the free space (space that 
was formerly in dead_tuples that the database is unable to restore to 
the system without physically reordering where data is on the disk but 
is able to reuse for new rows) increased by more than what was moved in 
from dead_tuples.  This means that not every new row created in the 
table is drawn from the free space. We'll probably want to either 
perform a vacuum full of the table or dump and reload it when we have 
the ability to take an extended outage.

Log of the vacuum run
=====================
* Note: Devrim is taking a look at this to see if there's any further 
optimizations we can perform on the db server.

koji=# vacuum verbose rpmfiles;
INFO:  vacuuming "public.rpmfiles"
INFO:  index "rpmfiles_by_rpm_id" now contains 99401971 row versions in 
464395 pages
DETAIL:  0 index row versions were removed.
126139 index pages have been deleted, 126139 are currently reusable.
CPU 9.82s/10.98u sec elapsed 2720.37 sec.
INFO:  index "rpmfiles_by_filename" now contains 99444842 row versions 
in 2162981 pages
DETAIL:  0 index row versions were removed.
320028 index pages have been deleted, 320028 are currently reusable.
CPU 39.41s/14.81u sec elapsed 20121.68 sec.
INFO:  index "rpmfiles_pkey" now contains 99595304 row versions in 
2451380 pages
DETAIL:  0 index row versions were removed.
345115 index pages have been deleted, 297534 are currently reusable.
CPU 47.37s/18.01u sec elapsed 22697.21 sec.
INFO:  "rpmfiles": removed 5036605 row versions in 95692 pages
DETAIL:  CPU 5.19s/0.65u sec elapsed 946.13 sec.
INFO:  "rpmfiles": found 5036605 removable, 99399621 nonremovable row 
versions in 2462392 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 30000388 unused item pointers.
0 pages are entirely empty.
CPU 111.12s/46.87u sec elapsed 48686.50 sec.
INFO:  vacuuming "pg_toast.pg_toast_396022"
INFO:  index "pg_toast_396022_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  "pg_toast_396022": found 0 removable, 0 nonremovable row versions 
in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.05 sec.

-Toshio

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: OpenPGP digital signature
URL: <http://listman.redhat.com/archives/fedora-infrastructure-list/attachments/20080129/146def9e/attachment.sig>