Hi.
I'm trying to plan for a performance test session where a large database is subject to regular hits from my application while both regular and full database maintenance is being performed. The idea is to gain a better idea on the impact maintenance will have on regular usage, and when to reasonably schedule both regular and full maintenance.
Is the verbose option for the VACUUM command and physical disk space usage enough? What's a good rule of thumb for verifying that the space supposedly recovered from a FULL vacuum is real? I can turn on verbose for a FULL vacuum and watch the "Total free space (including removable row versions) is 7032 bytes." details, but can it be reasonably correlated against disk linux system tools? (like du) Or only as a guidance that some maintenance is being performed?
Any other stat collection points I should be watching?
Here's an example lazy vacuum verbose output from an empty schema table: (not that you guys haven't seen this stuff enough)
VACUUM VERBOSE app.queuedemails;
INFO: vacuuming "app.queuedemails"
INFO: index "queuedemails1" 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.00 sec.
INFO: index "queuedemails2" 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.00 sec.
INFO: "queuedemails": 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.00 sec.
INFO: vacuuming "pg_toast.pg_toast_17595"
INFO: index "pg_toast_17595_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.00 sec.
INFO: "pg_toast_17595": 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.00 sec.
THANKS!!!
- Chris