From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Bloat issue on 8.3; autovac ignores HOT page splits?
Date: Mon, 24 Jan 2011 18:26:16 -0800
Message-ID: <4D3E34C8.9040209@agliodbs.com> (raw)
Folks,
I'm doing a postmortem on an 8.3 database which recently had to be
rebuilt. The database was over 200% bloated ... 176GB as opposed to
dump/reload size of 55GB. What I find really interesting is *which*
tables were bloated. Consider these two tables, for example, which
consist of one row which gets updated around 1000 times/day:
-[ RECORD 2 ]----------+------------------------------
schemaname | public
relname | general_info
n_dead_tup | 12
n_live_tup | 1
changed | 8817
n_tup_hot_upd | 8817
pg_relation_size | 155648
pg_total_relation_size | 172032
-[ RECORD 4 ]----------+------------------------------
schemaname | public
relname | current_info
n_dead_tup | 27
n_live_tup | 1
changed | 3296
n_tup_hot_upd | 3296
pg_relation_size | 385024
pg_total_relation_size | 409600
As you can see, in both cases almost all of the updates on these tables
were HOT updates. Yet these HOT updates led to bloat (hundreds of disk
pages instead of the one required for each table), and autovacuum
doesn't seem to think it needed to do anything about them ... neither
table was *ever* autovacuumed.
It looks to me like autovacuum doesn't ever consider when HOT updates
lead to page splits, and so require vacuuming. Or am I diagnosing it wrong?
max_fsm_pages may also have been slightly undersized.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: Bloat issue on 8.3; autovac ignores HOT page splits?
In-Reply-To: <4D3E34C8.9040209@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox