Message-ID: <50298533.7050904@catalyst.net.nz>
Date: Tue, 14 Aug 2012 10:52:35 +1200
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120714 Thunderbird/14.0
MIME-Version: 1.0
To: =?UTF-8?B?U3RyYWhpbmphIEt1c3R1ZGnEhw==?= <strahinjak@nordeus.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Index Bloat Problem
References: 
 <CADKbJJW0aCD_jp6Dy1j-VUP-zHSEvUkyfJ0Az3T1NnLX8tu_vw@mail.gmail.com>
In-Reply-To: 
 <CADKbJJW0aCD_jp6Dy1j-VUP-zHSEvUkyfJ0Az3T1NnLX8tu_vw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

On 11/08/12 10:15, Strahinja Kustudi=C4=87 wrote:
> We have PostgreSQL 9.1 running on Centos 5 on two SSDs, one for indices=
 and
> one for data. The database is extremely active with reads and writes. W=
e
> have autovacuum enabled, but we didn't tweak it's aggressiveness. The
> problem is that after some time the database grows even more than 100% =
on
> the file system and most of the growth is because the indices are a few
> times bigger than they should be, and when this happens, the performanc=
e of
> the DB drops.
>
> For example, yesterday when I checked the database size on the producti=
on
> server it was 30GB, and the restored dump of that database was only 17G=
B.
> The most interesting thing is that the data wasn't bloated that much, b=
ut
> the indices were. Some of them were a few times bigger than they should=
 be.
> For example an index on the production db is 440MB, while that same ind=
ex
> after dump/restore is 17MB, and there are many indices with that high
> difference. We could fix the problem if we reindex the DB, but that mak=
es
> our DB go offline and it's not possible to do in the production envirom=
ent.
>
> Is there a way to make the autovacuum daemon more aggressive, since I'm=
 not
> exactly sure how to do that in this case? Would that even help? Is ther=
e
> another way to remove this index bloat?
>
>

Some workloads can be difficult to tame. However I would try something=20
like this in postgresql.conf:

autovacuum_naptime=3D 10s
autovacuum_vacuum_scale_factor =3D 0.1

and maybe set log_autovacuum_min_duration so you see what autovacuum is=20
doing.

If the above settings don't help, then you could maybe monitor growth=20
and schedule regular REINDEXes on the tables concerned (at some suitably=20
quiet time).

Regards

Mark