Re: General performance/load issue

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