Skip site navigation (1) Skip section navigation (2)

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

From: Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6296: High level backend-write activity on db
Date: 2011-11-17 04:52:27
Message-ID: CAK-MWwTUG31z0M-kUnPQoONmr=Y8vxksw5HMXe5gisGHSiTk1A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
> 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.

In response to

pgsql-bugs by date

Next:From: Archana Sachin GhagDate: 2011-11-17 09:29:10
Subject: ST_Distance Issue
Previous:From: Robert HaasDate: 2011-11-17 04:43:14
Subject: Re: BUG #6296: High level backend-write activity on db

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group