Обсуждение: Bloat issue on 8.3; autovac ignores HOT page splits?
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
On Mon, Jan 24, 2011 at 9:26 PM, Josh Berkus <josh@agliodbs.com> wrote: > 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? I'm not sure what you mean by a page split. An update wherein the new heap tuple won't fit on the same page as the existing heap tuple should be treated as non-HOT. But nothing gets split in that case. I think of a page split as an index event, and if these are HOT updates there shouldn't be any index changes at all. Can we see those stats again with n_tup_ins/upd/del? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> Can we see those stats again with n_tup_ins/upd/del? Sure: -[ RECORD 2 ]----------+------------------------------ schemaname | public relname | general_info n_dead_tup | 12 n_live_tup | 1 n_tup_upd | 8817 n_tup_del | 0 n_tup_ins | 0 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 n_tup_upd | 3296 n_tup_del | 0 n_tup_ins | 0 n_tup_hot_upd | 3296 pg_relation_size | 385024 pg_total_relation_size | 409600 One question: in 8.3 and earlier, is the FSM used to track dead_rows for pg_stat_user_tables? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com
On 26/01/11 07:28, Josh Berkus wrote: > > One question: in 8.3 and earlier, is the FSM used to track dead_rows for > pg_stat_user_tables? > If I'm understanding you correctly, ANALYZE is the main guy tracking/updating the dead row count. regards Mark
Robert, Mark, I have not been able to reproduce this issue in a clean test on 9.0. As a result, I now think that it was related to the FSM being too small on the user's 8.3 instance, and will consider it resolved. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com
On 01/02/11 07:27, Josh Berkus wrote:
Right - it might be interesting to see if you can reproduce on 8.4. I would hazard a guess that you will not (on disk FSM + visibility map vacuum improvements seem to make this whole area way better).
Cheers
Mark
Robert, Mark, I have not been able to reproduce this issue in a clean test on 9.0. As a result, I now think that it was related to the FSM being too small on the user's 8.3 instance, and will consider it resolved.
Right - it might be interesting to see if you can reproduce on 8.4. I would hazard a guess that you will not (on disk FSM + visibility map vacuum improvements seem to make this whole area way better).
Cheers
Mark
On Mon, Jan 31, 2011 at 11:27 AM, Josh Berkus <josh@agliodbs.com> wrote: > Robert, Mark, > > I have not been able to reproduce this issue in a clean test on 9.0. As > a result, I now think that it was related to the FSM being too small on > the user's 8.3 instance, and will consider it resolved. I used to try and size free space map to be a little bigger than it needed to be. I now size 4 or 5 times what it needs to be. shared memory is cheap. So is going to 8.4, but on legacy systems that you can't upgrade, 8.3 with a huge FSM works well enough (with suitably aggressive autovac).
On 01/02/11 10:57, Scott Marlowe wrote:
Yeah, 8.3 with very aggressive autovac my experience too - I've had the naptime cranked down to 10s or even 1s in some cases, to try to tame bloat growth for web cache or session type tables that are heavily volatile.
regards
Mark
On Mon, Jan 31, 2011 at 11:27 AM, Josh Berkus <josh@agliodbs.com> wrote:Robert, Mark, I have not been able to reproduce this issue in a clean test on 9.0. As a result, I now think that it was related to the FSM being too small on the user's 8.3 instance, and will consider it resolved.I used to try and size free space map to be a little bigger than it needed to be. I now size 4 or 5 times what it needs to be. shared memory is cheap. So is going to 8.4, but on legacy systems that you can't upgrade, 8.3 with a huge FSM works well enough (with suitably aggressive autovac).
Yeah, 8.3 with very aggressive autovac my experience too - I've had the naptime cranked down to 10s or even 1s in some cases, to try to tame bloat growth for web cache or session type tables that are heavily volatile.
regards
Mark