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

On Wed, May 17, 2006 at 01:50:22PM -0400, Chris Mckenzie wrote:
> 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.

What do you mean by "regular and full maintenance"? Do you mean VACUUM
FULL?

If you're vacuuming appropriately you shouldn't have any need to ever
VACUUM FULL...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org