Thread: Bloat issue on 8.3; autovac ignores HOT page splits?

Bloat issue on 8.3; autovac ignores HOT page splits?

From
Josh Berkus
Date:
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

Re: Bloat issue on 8.3; autovac ignores HOT page splits?

From
Robert Haas
Date:
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

Re: Bloat issue on 8.3; autovac ignores HOT page splits?

From
Josh Berkus
Date:
> 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

Re: Bloat issue on 8.3; autovac ignores HOT page splits?

From
Mark Kirkwood
Date:
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

Re: Bloat issue on 8.3; autovac ignores HOT new pages?

From
Josh Berkus
Date:
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

Re: Bloat issue on 8.3; autovac ignores HOT new pages?

From
Mark Kirkwood
Date:
On 01/02/11 07:27, Josh Berkus 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.


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

Re: Bloat issue on 8.3; autovac ignores HOT new pages?

From
Scott Marlowe
Date:
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).

Re: Bloat issue on 8.3; autovac ignores HOT new pages?

From
Mark Kirkwood
Date:
On 01/02/11 10:57, Scott Marlowe wrote:
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