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

> On Wed, Nov 16, 2011 at 4:07 AM, Maksym Boguk <maxim.boguk@gmail.com> wro=
te:
>> 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 t=
he
>> 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=3D# SELECT * from pg_stat_bgwriter ;
>> =C2=A0checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers=
_clean |
>> maxwritten_clean | buffers_backend | buffers_alloc
>> -------------------+-----------------+--------------------+-------------=
--+-
>> -----------------+-----------------+---------------
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 0 | =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 2 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 171675 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 804 |
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00 | =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 1010429 | =C2=A0 =C2=A0 =C2=A0 1131106
>> (1 row)
>>
>> eg almost any new buffer allocated by backend leading to backend write o=
ut
>> 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 monito=
ring.

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

--=20
Maxim Boguk
Senior Postgresql DBA.