Re: BUG #6296: High level backend-write activity on db

On Wed, Nov 16, 2011 at 4:07 AM, Maksym Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:
> Again I not sure it is actual bug or something else go wrong with my
> database.
>
> One of my databases just started produce 256 wal segments in 5 minutes
> instead of 100-200 wal segments per hour (averages).
>
> In the same time write and read activity on DB stay same (according to the
> pg_stat_database data and historical graphs).
> No anti-wraparound vacuum and such things going on.
> Situation staying same during last few days.
>
>
> I found some other anomalies:
> 1)5 minutes after
> SELECT pg_stat_reset_shared('bgwriter');
>
> mirtesen=# SELECT * from pg_stat_bgwriter ;
>  checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
> maxwritten_clean | buffers_backend | buffers_alloc
> -------------------+-----------------+--------------------+---------------+-
> -----------------+-----------------+---------------
>                 0 |               2 |             171675 |           804 |
>              0 |         1010429 |       1131106
> (1 row)
>
> eg almost any new buffer allocated by backend leading to backend write out
> dirty page (and that situation do not change overtime).

This sure sounds like a ring buffer is being used, suggesting VACUUM
or COPY IN activity.

What does pg_stat_activity say?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company