Обсуждение: enabling autovacuum
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
The table description is:
id | integer | not null default nextval(('"rcpt_audit_id_seq"'::text)::regclass) |
msg_audit_id | integer | |
mailuser_id | integer | |
username | text | |
domain | text | |
copies | integer | |
end_msg_size | integer | |
disp_type | integer | |
disp_id | integer | |
disp_action | text | |
disposition | text | |
hdrs | text | |
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?
Thanks,
Jeremy
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.