Re: PostgreSQL 8.2.3 VACUUM Timings/Performance

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