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
