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
Attachment:
signature.asc
Description: OpenPGP digital signature
_______________________________________________ Fedora-infrastructure-list mailing list Fedora-infrastructure-list@xxxxxxxxxx https://www.redhat.com/mailman/listinfo/fedora-infrastructure-list
