enabling autovacuum

On Jan 28, 2008 10:17 PM, Jeremy Harris <jgh@wizmail.org> wrote:

Hi,

We're starting to run autovacuum for the first time on a system
that's been running with nightly cron-driven vacuum for some time.

Version:
PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070418 (Red Hat 4.1.2-10)

We have one problematic table, which has a steady stream of entries
and a weekly mass-delete of ancient history. The "bloat" query from
Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns:

schemaname | tablename | reltuples | relpages | otta | tbloat | wastedpages | wastedbytes | wastedsize | iname | ituples | ipages | iotta | ibloat | wastedipages | wastedibytes | wastedisize
------------+------------+-----------+----------+------+--------+-------------+-------------+------------+-----------------------------+---------+--------+-------+--------+--------------+--------------+-------------
public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 | 1194262528 | 1139 MB | rcpt_audit_msg_audit_id_idx | 1300300 | 6798 | 3819 | 1.8 | 2979 | 24403968 | 23 MB
public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 | 1194262528 | 1139 MB | rcpt_audit_id_idx | 1300300 | 4727 | 3819 | 1.2 | 908 | 7438336 | 7264 kB
We have uncommented "autovacuum = on" in postgresql.conf and run
"service postgresql reload". pg_stat_all_tables shows 4 tables
as autoanalyzed at about that time; 3 of which were also
autovacuumed. The problem table is not included; no other autos
are logged there in the succeeding 24 hours.
Is other action needed to enable autovacuum?

The autovacuum tuning parameters are all at default settings.
We have
max_fsm_pages = 2000000
max_fsm_relations = 100000

...

Are there any other changes we should make to stop this table
getting so bloated?

Is it possible that this table didn't see many updates, today?

You could add an entry to pg_catalog.pg_autovacuum to customize the
handling of your Favorite Table.

http://www.postgresql.org/docs/8.2/static/catalog-pg-autovacuum.html

You might lower the thresholds for that table...
--
http://linuxfinances.info/info/linuxdistributions.html
"The definition of insanity is doing the same thing over and over and
expecting different results." -- assortedly attributed to Albert
Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling

Christopher Browne wrote:

Is it possible that this table didn't see many updates, today?

Nope; about 24000 (according to the id sequence).
- Jeremy

On Mon, 2008-01-28 at 22:17 +0000, Jeremy Harris wrote:

We have one problematic table, which has a steady stream of entries
and a weekly mass-delete of ancient history. The "bloat" query from
Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns:

schemaname | tablename | reltuples | relpages | otta | tbloat | wastedpages | wastedbytes | wastedsize | iname | ituples | ipages | iotta | ibloat | wastedipages | wastedibytes | wastedisize
------------+------------+-----------+----------+------+--------+-------------+-------------+------------+-----------------------------+---------+--------+-------+--------+--------------+--------------+-------------
public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 | 1194262528 | 1139 MB | rcpt_audit_msg_audit_id_idx | 1300300 | 6798 | 3819 | 1.8 | 2979 | 24403968 | 23 MB
public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 | 1194262528 | 1139 MB | rcpt_audit_id_idx | 1300300 | 4727 | 3819 | 1.2 | 908 | 7438336 | 7264 kB

Can you let me know what is the sql used to generate such a nice summary
of the tables?

On Tue, 29 Jan 2008, Ow Mun Heng wrote:

Can you let me know what is the sql used to generate such a nice summary
of the tables?

Might as well dupe the old text; this went out to the performance list:

Greg Sabino Mullane released a Nagios plug-in for PostgreSQL that you can
grab at http://bucardo.org/nagios_postgres/ , and while that is itself
nice the thing I found most remarkable is the bloat check. The majority of
that code is an impressive bit of SQL that anyone could use even if you
have no interest in Nagios, which is why I point it out for broader
attention. Look in check_postgres.pl for the "check_bloat" routine and the
big statement starting at the aptly labled "This was fun to write"
section. If you pull that out of there and replace $MINPAGES and
$MINIPAGES near the end with real values, you can pop that into a
standalone query and execute it directly.

That's what gives the summary Jeremy included in his message.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Mon, 2008-01-28 at 20:57 -0500, Greg Smith wrote:

On Tue, 29 Jan 2008, Ow Mun Heng wrote:

Can you let me know what is the sql used to generate such a nice summary
of the tables?

Might as well dupe the old text; this went out to the performance list:

Greg Sabino Mullane released a Nagios plug-in for PostgreSQL that you can
grab at http://bucardo.org/nagios_postgres/ , and while that is itself
nice the thing I found most remarkable is the bloat check. The majority of
that code is an impressive bit of SQL that anyone could use even if you
have no interest in Nagios, which is why I point it out for broader
attention. Look in check_postgres.pl for the "check_bloat" routine and the
big statement starting at the aptly labled "This was fun to write"
section. If you pull that out of there and replace $MINPAGES and
$MINIPAGES near the end with real values, you can pop that into a
standalone query and execute it directly.

I'm subscribed to perf list and I _did_ take a look at the tool
previously. However, something happened and I didn't managed to look at
it throughly or something.

I'll take another look at it and thanks for the pointers..

Jeremy Harris wrote:

Hi,

We're starting to run autovacuum for the first time on a system
that's been running with nightly cron-driven vacuum for some time.

Version:
PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20070418 (Red Hat 4.1.2-10)

We have one problematic table, which has a steady stream of entries
and a weekly mass-delete of ancient history. The "bloat" query from
Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns:

Inserts don't generate dead tuples, and AVD looks at obsolete tuples..
As such, I wouldn't expect AVD to kick off until after you did a mass
delete...assuming that delete was sizable enough to trigger a vacuum.

--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
Ask me about our Expert PostgreSQL & PostGIS Training

Chander Ganesan wrote:

Jeremy Harris wrote:

Version:
PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20070418 (Red Hat 4.1.2-10)

We have one problematic table, which has a steady stream of entries
and a weekly mass-delete of ancient history. The "bloat" query from
Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns:

Inserts don't generate dead tuples, and AVD looks at obsolete tuples..
As such, I wouldn't expect AVD to kick off until after you did a mass
delete...assuming that delete was sizable enough to trigger a vacuum.

Ah, that would explain it - thankyou. So I need to retreat to
the question of why the weekly vacuum permits the observed bloat.
Any ideas? More information that I could gather?

Thanks,
Jeremy

Jeremy Harris wrote:

Chander Ganesan wrote:

Inserts don't generate dead tuples, and AVD looks at obsolete
tuples.. As such, I wouldn't expect AVD to kick off until after you
did a mass delete...assuming that delete was sizable enough to
trigger a vacuum.

Ah, that would explain it - thankyou. So I need to retreat to
the question of why the weekly vacuum permits the observed bloat.
Any ideas? More information that I could gather?

Autovacuum will kick off an analyze if you do enough inserts however.