Performance/Maintenance test result collection

From: Chris Mckenzie <Chris(dot)McKenzie(at)entrust(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Performance/Maintenance test result collection
Date: 2006-05-17 17:50:22
Message-ID: DE02FD24E5A7504FA1D53631DA8CEA9BF8B6A3@sottmxs10.entrust.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Simon Riggs 2006-05-17 18:22:09 Re: Optimizer: limit not taken into account
Previous Message Bruno Wolff III 2006-05-17 17:44:26 Re: Optimizer: limit not taken into account