Re: General performance/load issue

From: Tomas Vondra <tv(at)fuzzy(dot)cz>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: General performance/load issue
Date: 2011-11-26 18:15:14
Message-ID: 4ED12CB2.5070309@fuzzy.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dne 26.11.2011 17:47, Gaëtan Allart napsal(a):
> Rahh :/
>
> It's getting worse and worse :/ Database has to be restarted every 2 hours
> causing much traffic loss :/
>
> As far as the server is concerned, it was running great 7 days ago and had
> been running like this for months. I really don't get why it suddenly went
> "I/oing"Š

Given the info provided so far we can rule out checkpoint issues.

These sudden changes in performance happen when the when the stats are
off or vacuum is not able to maintain all the tables.

1) Post EXPLAIN ANALYZE of the queries that cause a lot of writes.

2) How much did the database grew recently?

3) Is the autovacuum running fine? Check the logs, and maybe set

log_autovacuum_min_duration = 100

or a different value. Set the log_line_prefix (add '%t').

4) Check the pg_stat_all_tables:

- large n_dead_tup values (especially compared to n_live_tup)

- tables with a lot of changed (n_tup_ins + n_tup_upd + n_tup_del +
n_tup_hot_upd) that were not vacuumed / analyzed recently

Tomas

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Carlos Henrique Reimer 2011-11-26 18:18:56 CPU move
Previous Message Tom Lane 2011-11-26 18:08:39 Re: General performance/load issue