Re: Postgres becoming slow, only full vacuum fixes it

Hi,

On 24 September 2012 20:33, Kiriakos Tsourapas <ktsour(at)gmail(dot)com> wrote:
> The problem : Postgres is becoming slow, day after day, and only a full
> vacuum fixes the problem.
>
> Information you may need to evaluate :
>
> The problem lies on all tables and queries, as far as I can tell, but we can
> focus on a single table for better comprehension.
>
> The queries I am running to test the speed are :
> INSERT INTO "AWAITINGSTATUSSMPP" VALUES('143428', '1111', 1, '2012-06-16
> 13:39:19', '111');
> DELETE FROM "AWAITINGSTATUSSMPP" WHERE "SMSCMSGID" = '1111' AND
> "CONNECTIONID" = 1;
> SELECT * FROM "AWAITINGSTATUSSMPP" WHERE "SMSCMSGID" = '1111' AND
> "CONNECTIONID" = 1;
>
> After a full vacuum, they run in about 100ms.
> Today, before the full vacuum, they were taking around 500ms.

I had similar issue and I disabled cost based auto vacuum:
autovacuum_vacuum_cost_delay = -1

-1 says that vacuum_cost_delay will be used and default value for
vacuum_cost_delay is 0 (ie. off)

Of couse you need to change other autovacuum settings but you did that.

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)