From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Bruce McAlister <bruce.mcalister@blueface.ie>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 8.2.3 VACUUM Timings/Performance
Date: Mon, 05 Mar 2007 13:20:49 +0000
Message-ID: <45EC1931.3020701@enterprisedb.com> (raw)
In-Reply-To: <esh2mg$16vu$1@news.hub.org>
References: <esh2mg$16vu$1@news.hub.org>
Bruce McAlister wrote:
> Over time we have noticed increased response times from the database which
> has an adverse affect on our registration times. After doing some research
> it appears that this may have been related to our maintenance regime, and
> has thus been amended as follows:
>
>
> [1] AutoVacuum runs during the day over the entire PostgreSQL cluster,
>
> [2] A Vacuum Full Verbose is run during our least busy period (generally
> 03:30) against the Database,
>
> [3] A Re-Index on the table is performed,
>
> [4] A Cluster on the table is performed against the most used index,
>
> [5] A Vacuum Analyze Verbose is run against the database.
>
>
> These maintenance steps have been setup to run every 24 hours.
>
>
> The database in essence, once loaded up and re-index is generally around
> 17MB for data and 4.7MB for indexes in size.
>
>
> Over a period of 24 hours the database can grow up to around 250MB and the
> indexes around 33MB (Worst case thus far). When the maintenance kicks in,
> the vacuum full verbose step can take up to 15 minutes to complete (worst
> case). The re-index, cluster and vacuum analyze verbose steps complete in
> under 1 second each. The problem here is the vacuum full verbose, which
> renders the environment unusable during the vacuum phase. The idea here is
> to try and get the vacuum full verbose step to complete in less than a
> minute. Ideally, if we could get it to complete quicker then that would be
> GREAT, but our minimal requirement is for it to complete at the very most 1
> minute. Looking at the specifications of our environment below, do you think
> that this is at all possible?
250MB+33MB isn't very much. It should easily fit in memory, I don't see
why you need the 12 disk RAID array. Are you sure you got the numbers right?
Vacuum full is most likely a waste of time. Especially on the tables
that you cluster later, cluster will rewrite the whole table and indexes
anyway. A regular normal vacuum should be enough to keep the table in
shape. A reindex is also not usually necessary, and for the tables that
you cluster, it's a waste of time like vacuum full.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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: heikki@enterprisedb.com, bruce.mcalister@blueface.ie
Subject: Re: PostgreSQL 8.2.3 VACUUM Timings/Performance
In-Reply-To: <45EC1931.3020701@enterprisedb.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