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
Results of running time vacuum analyze over the newly vacuumed rpmfiles db:
real 807m15.694s user 0m0.084s sys 0m0.020s
Devrim suggested we increase maintenance_work_mem to 1GB which I've done in the configs. That won't go live until we have an opportunity to restart the postgres server. That parameter will allow vacuums, create index, and other "maintenance" functions to use up to 1GB of memory when they are running.
Full Log:
[toshio@db2 ~]$ time echo 'vacuum analyze verbose rpmfiles' | sudo -u postgres psql -d koji INFO: vacuuming "public.rpmfiles" INFO: index "rpmfiles_by_rpm_id" now contains 99710482 row versions in 464395 pages DETAIL: 125033 index pages have been deleted, 125033 are currently reusable. CPU 3.51s/0.76u sec elapsed 1789.79 sec. INFO: index "rpmfiles_by_filename" now contains 99739365 row versions in 2162 981 pages DETAIL: 316678 index pages have been deleted, 316678 are currently reusable. CPU 74.13s/16.68u sec elapsed 18957.20 sec. INFO: index "rpmfiles_pkey" now contains 99912850 row versions in 2451399 pages DETAIL: 342208 index pages have been deleted, 342208 are currently reusable. CPU 70.60s/13.16u sec elapsed 24427.02 sec. INFO: "rpmfiles": found 0 removable, 99693793 nonremovable row versions in 24 67550 pages DETAIL: 0 dead row versions cannot be removed yet. There were 35028550 unused item pointers. 0 pages are entirely empty. CPU 152.49s/31.79u sec elapsed 46435.11 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.01 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.03 sec. INFO: analyzing "public.rpmfiles" INFO: "rpmfiles": scanned 90000 of 2467550 pages, containing 3662103 live rows and 0 dead rows; 90000 rows in sample, 100404692 estimated total rows VACUUM
real 807m15.694s user 0m0.084s sys 0m0.020s
-Toshio
On Tue, Jan 29, 2008 at 01:41:52PM -0800, Toshio Kuratomi wrote:
Devrim suggested we increase maintenance_work_mem to 1GB which I've done in the configs. That won't go live until we have an opportunity to restart the postgres server.
Any reason not to change the setting on the fly?
Kostas
Kostas Georgiou wrote:
On Tue, Jan 29, 2008 at 01:41:52PM -0800, Toshio Kuratomi wrote:
Devrim suggested we increase maintenance_work_mem to 1GB which I've done in the configs. That won't go live until we have an opportunity to restart the postgres server.
Any reason not to change the setting on the fly?
No reason but ignorance :-)
Can you tell me how? All I've been able to find are ways to change the value per-session.
-Toshio
On Tue, Jan 29, 2008 at 07:18:53PM -0800, Toshio Kuratomi wrote:
Kostas Georgiou wrote:
On Tue, Jan 29, 2008 at 01:41:52PM -0800, Toshio Kuratomi wrote:
Devrim suggested we increase maintenance_work_mem to 1GB which I've done in the configs. That won't go live until we have an opportunity to restart the postgres server.
Any reason not to change the setting on the fly?
No reason but ignorance :-)
Can you tell me how? All I've been able to find are ways to change the value per-session.
You can also change it for a user or a database with: ALTER DATABASE koji SET maintenance_work_mem TO 1048576; ALTER USER koji SET maintenance_work_mem TO 1048576; The per session setting should be enough though if you only need the extra memory for the vacuum.
Kostas
Kostas Georgiou wrote:
You can also change it for a user or a database with: ALTER DATABASE koji SET maintenance_work_mem TO 1048576; ALTER USER koji SET maintenance_work_mem TO 1048576; The per session setting should be enough though if you only need the extra memory for the vacuum.
Thanks Kostas!
I think I'll be setting this in the session whenever we run a vacuum until we have a chance to restart postgres with the new config.
-Toshio
infrastructure@lists.fedoraproject.org
-
Kostas Georgiou -
Toshio Kuratomi