Re: PostgreSQL 8.2.3 VACUUM Timings/Performance

Hi Heikki,

Thanks for the reply.

The RAID array was implemented due to a projected growth pattern which
incorporate all 18 of our databases. The sizings I mentioned only refer to 1
of those databases, which, is also the most heavily used database :)

If I understand you correctly, we could in essence change our maintenance
routine to the follwing:

[1] Cluster on most used index
[2] Perform a vacuum analyze on the table

If I read your post correctly, this will regenerate the index that the
cluster is performed on (1 of 3) and also re-generate the table in the
sequence of that index?

If that is the case, why would anyone use the vacuum full approach if they
could use the cluster command on a table/database that will regen these
files for you. It almost seems like the vacuum full approach would, or
could, be obsoleted by the cluster command, especially if the timings in
their respective runs are that different (in our case the vacuum full took
15 minutes in our worst case, and the cluster command took under 1 second
for the same table and scenario).

The output of our script for that specific run is as follows (just in-case
i'm missing something):

Checking disk usage before maintenance on service (sipaccounts) at
02-Mar-2007 03:30:00

258M /database/pgsql/bf_service/data
33M /database/pgsql/bf_service/index

Completed checking disk usage before maintenance on service (sipaccounts) at
02-Mar-2007 03:30:00

Starting VACUUM FULL VERBOSE on service (sipaccounts) at 02-Mar-2007
03:30:00

INFO: vacuuming "public.sipaccounts"
INFO: "sipaccounts": found 71759 removable, 9314 nonremovable row versions
in 30324 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 318 to 540 bytes long.
There were 439069 unused item pointers.
Total free space (including removable row versions) is 241845076 bytes.
28731 pages are or will become empty, including 41 at the end of the table.
30274 pages containing 241510688 free bytes are potential move destinations.
CPU 0.00s/0.05u sec elapsed 31.70 sec.
INFO: index "sippeers_name_key" now contains 9314 row versions in 69 pages
DETAIL: 7265 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 1.52 sec.
INFO: index "sippeers_pkey" now contains 9314 row versions in 135 pages
DETAIL: 7161 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 3.07 sec.
INFO: index "accountcode_index" now contains 9314 row versions in 3347 pages
DETAIL: 71759 index row versions were removed.
1151 index pages have been deleted, 1151 are currently reusable.
CPU 0.02s/0.08u sec elapsed 56.31 sec.
INFO: "sipaccounts": moved 3395 row versions, truncated 30324 to 492 pages
DETAIL: CPU 0.03s/0.56u sec elapsed 751.99 sec.
INFO: index "sippeers_name_key" now contains 9314 row versions in 69 pages
DETAIL: 3395 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.21 sec.
INFO: index "sippeers_pkey" now contains 9314 row versions in 135 pages
DETAIL: 3395 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "accountcode_index" now contains 9314 row versions in 3347 pages
DETAIL: 3395 index row versions were removed.
1159 index pages have been deleted, 1159 are currently reusable.
CPU 0.01s/0.01u sec elapsed 30.03 sec.
INFO: vacuuming "pg_toast.pg_toast_2384131"
INFO: "pg_toast_2384131": found 0 removable, 0 nonremovable row versions in
0 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_2384131_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.
VACUUM

Completed VACUUM FULL VERBOSE on service (sipaccounts) at 02-Mar-2007
03:44:35
Starting REINDEX on service (sipaccounts) at 02-Mar-2007 03:44:35

REINDEX

Completed REINDEX on service (sipaccounts) at 02-Mar-2007 03:44:35
Starting CLUSTER on service (sipaccounts) at 02-Mar-2007 03:44:35

CLUSTER sipaccounts;
CLUSTER

Completed CLUSTER on service (sipaccounts) at 02-Mar-2007 03:44:36
Starting VACUUM ANALYZE VERBOSE on service (sipaccounts) at 02-Mar-2007
03:44:36

INFO: vacuuming "public.sipaccounts"
INFO: scanned index "sippeers_name_key" to remove 9 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "sippeers_pkey" to remove 9 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.20 sec.
INFO: scanned index "accountcode_index" to remove 9 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "sipaccounts": removed 9 row versions in 9 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "sippeers_name_key" now contains 9361 row versions in 36 pages
DETAIL: 9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "sippeers_pkey" now contains 9361 row versions in 69 pages
DETAIL: 9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "accountcode_index" now contains 9361 row versions in 49 pages
DETAIL: 9 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "sipaccounts": found 9 removable, 9361 nonremovable row versions in
495 pages
DETAIL: 131 dead row versions cannot be removed yet.
There were 0 unused item pointers.
28 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.84 sec.
INFO: vacuuming "pg_toast.pg_toast_2386447"
INFO: index "pg_toast_2386447_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_2386447": 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 contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.sipaccounts"
INFO: "sipaccounts": scanned 517 of 517 pages, containing 8966 live rows and
800 dead rows; 3000 rows in sample, 8966 estimated total rows VACUUM

Completed VACUUM ANALYZE VERBOSE on service (sipaccounts) at 02-Mar-2007
03:44:39
Checking disk usage after maintenance on service (sipaccounts) at
02-Mar-2007 03:44:39

22M /database/pgsql/bf_service/data

6.7M /database/pgsql/bf_service/index

Completed checking disk usage after maintenance on service (sipaccounts) at
02-Mar-2007 03:44:39

Thanks
Bruce
"Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> wrote in message
news:45EC1931(dot)3020701(at)enterprisedb(dot)com(dot)(dot)(dot)
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>