From: Jim C. Nasby <jnasby@pervasive.com>
To: Chris Mckenzie <Chris.McKenzie@entrust.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance/Maintenance test result collection
Date: Fri, 19 May 2006 15:50:14 -0500
Message-ID: <20060519205013.GN64371@pervasive.com> (raw)
In-Reply-To: <DE02FD24E5A7504FA1D53631DA8CEA9BF8B6A8@sottmxs10.entrust.com>
References: <DE02FD24E5A7504FA1D53631DA8CEA9BF8B6A8@sottmxs10.entrust.com>
On Thu, May 18, 2006 at 11:20:17AM -0400, Chris Mckenzie wrote:
> Yes, regular versus full vacuum. Thanks for the comment but I was hoping to
> come to that conclusion on my own by observing the affects of the different
> vacuums.
>
> My original question was guidance on collecting data for confirmation on the
> impact that maintenance of a large database (as a result of my applications
> regular usage over a period of time) has.
>
> I can du the various tables and compare their size before/after against the
> verbose output of a VACUUM FULL. I can use sar during all of this to monitor
> cpu and i/o activity. I can turn on transaction logging once I get a better
> idea of maintenance impact on my hardware so identify the biggest
> transactions that might statement timeout if a VACUUM was running at the
> same time.
Well, vacuum full re-writes the table completely from scratch. Lazy
vacuum reads the entire table (just like full), but only writes out
pages that have dead space on them.
But if you're wondering about the impact that will have on your
application, you can stop wondering, because vacuum full will
essentially shut your application down because it locks out use of the
table while it's being vacuumed.
> Any suggestions or comments related to collection of this type of data would
> be helpful. I've already read the Postges 7.4 (yes, I'm stuck on 7.4)
> manual, I was hoping for this mail-list' wisdom to supply me with some tips
> that can only be learnt through painful experience. :-)
If you're stuck on 7.4, at least make sure you're using the most recent
version. Otherwise you're exposing yourself to a number of data loss
bugs.
As for vacuuming, it depends a lot on what your application is doing. If
you have frequent-enough slow periods (like at night), you can probably
schedule a database-wide vacuum during that time, possibly supplimented
by vacuums on critical tables during the day. If you have something
closer to a 24x7 load then pg_autovacuum is probably your best bet,
along with vacuum_cost_delay (if that's available in 7.4; it's been so
long I don't remember).
There's a few articles in our knowledge base
(http://www.pervasivepostgres.com/kb/index.asp) that might be worth
reading as well (search for 'vacuum'). In particular, "Is PostgreSQL
remembering what I vacuumed" has some critical information about
managing the free space map.
--
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, Chris.McKenzie@entrust.com
Subject: Re: Performance/Maintenance test result collection
In-Reply-To: <20060519205013.GN64371@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox