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