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