Re: Performance of autovacuum and full vacuum of database - Mailing list pgsql-general

Thank you for your response Matthew.  Currently I run pg_autovacuum with the
following scripts.
su -l postgres -c "pg_autovacuum -D -U postgres > /dev/null 2>&1"&

Do you suggest that I could change it to something like the following:
su -l postgres -c "pg_autovacuum -d2 -D -U postgres > /tmp/vacuum.log 2>&1"&

Thank you in advance for your response.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Matthew T. O'Connor
Sent: Thursday, November 10, 2005 2:46 PM
To: Carlos Oliva
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Performance of autovacuum and full vacuum of database

Couple of thing here:
1) Just because autovacuum is running, doesn't mean that it has actually
tried to vacuum a table. 5 minutes is the time that it sleeps in between
investigating activity to see if a vacuum is needed. If you want to see
if pg_autovacuum has actually tried to do anything you should up the the
logging with a -d2 switch on the pg_autovacuum command line.

2) pg_autovacuum only performs normal VACUUM commands, not VACUUM FULL.
As such, there will usually be some freespace left in the table. This is
not a bad thing as normal table activity will usually result in that
free space being reused, and it can be faster when it can use existing
free space rather than having to add space to the end off a table.

Matt


Carlos Oliva wrote:
>
> Hi Forum,
>
> Should autovacuum reclaim most of the free space of a database? We are
> trying to configure our database and running pg_autovacuum to
> streamline our database. We have increased the max_fsm_pages to a
> value larger than the total pages needed (see the output from a full
> vacuum bellow "*_LAST FEW LINES OF FULL VACUUM_*") and turned on
> pg_autovacuum.
>
> Nevertheless, it seems that a full vacuum that we run at night finds a
> lot of free space (see "*_EXCERPT FROM THE FULL VACUUM TO SHOW THE
> VACUUM OF ONE TABLE_*"). I would have expected that with the
> configuration of our database and with autovacuum working during the
> day, the amount of space that a full vacuum would find would be
> minimal.*__*
>
> We are running pg_autovacuum with its defaults parameters. I can see
> that autovacuum is working because the CPU utilization for the
> autovacuum PID goes up every five minutes or so and then it goes down
> to almost nothing.
>
> *_LAST FEW LINES OF FULL VACUUM_*
>
> INFO: free space map: 483 relations, 219546 pages stored; 153104 total
> pages needed
>
> DETAIL: Allocated FSM size: 1000 relations + 170000 pages = 1057 kB
> shared memory.
>
> *_EXCERPT FROM THE FULL VACUUM TO SHOW THE VACUUM OF ONE TABLE_*
>
> INFO: "en0029": found 66035 removable, 1310162 nonremovable row
> versions in 417
>
> 87 pages
>
> DETAIL: 0 dead row versions cannot be removed yet.
>
> Nonremovable row versions range from 233 to 1165 bytes long.
>
> There were 1746 unused item pointers.
>
> Total free space (including removable row versions) is 20825932 bytes.
>
> 1453 pages are or will become empty, including 0 at the end of the table.
>
> 2345 pages containing 16260040 free bytes are potential move destinations.
>
> CPU 2.20s/0.22u sec elapsed 62.59 sec.
>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings