Re: index bloat in 8.4-dev

I am seeing index bloat in the current head when the indexed values are
constantly increasing, and the lower values are being constantly
deleted.

I attached a simple python script that can be fed into psql. If this is
done on 8.3.3, the number of index relpages stays fairly constant,
around 500, and doesn't change after that.

If I do this against head, then the index relpages explode, and I
stopped the test when it reached about 46k pages for about 100k live
rows.

It's possible that this has something to do with the new FSM. I checked
out a release just before the new FSM, and I did see some bloat,
reaching about 3-4k index pages for 100k live rows (which is still worse
than 8.3.3), but it did not appear to happen nearly as fast. I don't
think it's entirely the FSM though, because 8.3.3 showed no problem at
all. Perhaps the FSM just makes some problem introduced in 8.4 more
apparent?

Regards,
Jeff Davis

Jeff Davis wrote:
> I am seeing index bloat in the current head when the indexed values are
> constantly increasing, and the lower values are being constantly
> deleted.
>
> ...
> It's possible that this has something to do with the new FSM.

Yep, it clearly has. Looks like I forgot about the index FSMs when I
added the FSM vacuum code. Index FSMs need to be vacuumed just like the
heap FSM. Fixed, thanks for the report.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Mon, 2008-10-06 at 11:10 +0300, Heikki Linnakangas wrote:
> Jeff Davis wrote:
> > I am seeing index bloat in the current head when the indexed values are
> > constantly increasing, and the lower values are being constantly
> > deleted.
> >
> > ...
> > It's possible that this has something to do with the new FSM.
>
> Yep, it clearly has. Looks like I forgot about the index FSMs when I
> added the FSM vacuum code. Index FSMs need to be vacuumed just like the
> heap FSM. Fixed, thanks for the report.

There is some other bug still at work here. I am still seeing some
pretty severe bloat with the same script I attached in the previous
email. This is the current head (including your patch).

Note that these results are after running my script for about an hour
(although you can probably see the effects after 10 minutes), and my
script has a VACUUM after every DELETE.

I see this problem on 8.3.3 now, too. Originally, I suppose my test was
not long enough, but now I see the problem after about 10 minutes of
running.

Regards,
Jeff Davis

-- results from current head
=> select relpages from pg_class where relname = 'foo_i_key';
relpages
----------
35255
(1 row)

=> select relpages from pg_class where relname = 'foo_i_key';
relpages
----------
35255
(1 row)

=> select count(*) from mytest.foo;
count
--------
100000
(1 row)

=> vacuum verbose mytest.foo;
INFO: vacuuming "mytest.foo"
INFO: index "foo_i_key" now contains 101163 row versions in 35255 pages
DETAIL: 0 index row versions were removed.
35029 index pages have been deleted, 35029 are currently reusable.
CPU 0.11s/0.07u sec elapsed 0.20 sec.
INFO: "foo": found 0 removable, 101163 nonremovable row versions in 785
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 98837 unused item pointers.
0 pages are entirely empty.
CPU 0.11s/0.08u sec elapsed 0.21 sec.
VACUUM

=> select min(i), max(i) from mytest.foo;
min | max
-----------+-----------
205100001 | 205200000
(1 row)

Jeff Davis wrote:
> I see this problem on 8.3.3 now, too. Originally, I suppose my test was
> not long enough, but now I see the problem after about 10 minutes of
> running.

I ran the script for about 30 minutes on CVS HEAD, and the index didn't
grow at all after the first three iterations. Are you sure you didn't
have a long-running transaction open that prevented vacuum from working?
Did the heap bloat as well, or just the index?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, 2008-10-07 at 11:04 +0300, Heikki Linnakangas wrote:
> Jeff Davis wrote:
> > I see this problem on 8.3.3 now, too. Originally, I suppose my test was
> > not long enough, but now I see the problem after about 10 minutes of
> > running.
>
> I ran the script for about 30 minutes on CVS HEAD, and the index didn't
> grow at all after the first three iterations. Are you sure you didn't
> have a long-running transaction open that prevented vacuum from working?
> Did the heap bloat as well, or just the index?

In the VACUUM VERBOSE output I included, you can see that the heap is
only 785 pages (after 200M rows went through that table), and it
maintains that consistently. That means to me that the VACUUMs are
running and properly freeing the space in the heap. And the output seems
to indicate that it thinks it can re-use those index pages, it just
doesn't.

This is a bit non-deterministic: the index pages will be steady for a
while, and then jump all of a sudden. Maybe it only happens when
resources are strained? I will try on a few other machines today and see
if I can identify the conditions a little more clearly.

Regards,
Jeff Davis

On Tue, 2008-10-07 at 08:14 -0700, Jeff Davis wrote:
> In the VACUUM VERBOSE output I included, you can see that the heap is
> only 785 pages (after 200M rows went through that table), and it
> maintains that consistently. That means to me that the VACUUMs are
> running and properly freeing the space in the heap. And the output seems
> to indicate that it thinks it can re-use those index pages, it just
> doesn't.

I think something may have been flawed in my simpler tests, so ignore
this.

I did see some kind of problem here in a more complex case, but I think
I'll have to narrow it down again.

Regards,
Jeff Davis