From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Strahinja Kustudić <strahinjak@nordeus.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index Bloat Problem
Date: Tue, 14 Aug 2012 10:52:35 +1200
Message-ID: <50298533.7050904@catalyst.net.nz> (raw)
In-Reply-To: <CADKbJJW0aCD_jp6Dy1j-VUP-zHSEvUkyfJ0Az3T1NnLX8tu_vw@mail.gmail.com>
References: <CADKbJJW0aCD_jp6Dy1j-VUP-zHSEvUkyfJ0Az3T1NnLX8tu_vw@mail.gmail.com>
On 11/08/12 10:15, Strahinja Kustudić 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. We
> 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 performance of
> the DB drops.
>
> For example, yesterday when I checked the database size on the production
> server it was 30GB, and the restored dump of that database was only 17GB.
> The most interesting thing is that the data wasn't bloated that much, but
> 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 index
> 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 makes
> our DB go offline and it's not possible to do in the production enviroment.
>
> 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 there
> another way to remove this index bloat?
>
>
Some workloads can be difficult to tame. However I would try something
like this in postgresql.conf:
autovacuum_naptime= 10s
autovacuum_vacuum_scale_factor = 0.1
and maybe set log_autovacuum_min_duration so you see what autovacuum is
doing.
If the above settings don't help, then you could maybe monitor growth
and schedule regular REINDEXes on the tables concerned (at some suitably
quiet time).
Regards
Mark
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz, strahinjak@nordeus.com
Subject: Re: Index Bloat Problem
In-Reply-To: <50298533.7050904@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox