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
>

I very sorry, it was a false alert.

Monitoring was based on sum() over pg_stat_all_tables.
But problem was bugged cron script which were every minute looping over

create table something_tmp as query (like 20M entries);
instant after
drop table something_tmp;

As a result of that instant drop - an insert volumes were missed by monitoring.

PS: xlogdump really great tool and it was good help in locating and
fixing problem.

--
Maxim Boguk
Senior Postgresql DBA.