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

From: "Maksym Boguk" <maxim(dot)boguk(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6296: High level backend-write activity on db
Date: 2011-11-16 09:07:55
Message-ID: 201111160907.pAG97tKr078227@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 6296
Logged by: Maksym Boguk
Email address: maxim(dot)boguk(at)gmail(dot)com
PostgreSQL version: 9.0.5
Operating system: Linux RedHat
Description: High level backend-write activity on db
Details:

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).

That is very strange... because in DB a lot non-dirty pages with
usagecount=0:

mirtesen=# SELECT
usagecount,count(*),isdirty
FROM public.pg_buffercache
GROUP BY isdirty,usagecount
ORDER BY isdirty,usagecount
;
usagecount | count | isdirty
------------+---------+---------
0 | 902086 | f
1 | 1541321 | f
2 | 1045230 | f
3 | 500173 | f
4 | 1183640 | f
5 | 3926461 | f
1 | 10866 | t
2 | 5127 | t
3 | 6987 | t
4 | 17445 | t
5 | 35687 | t
| 17 |
(12 rows)

so 1:1 backend buffer allocation to backend dirty page writes seems
exceptionally strange.

And that situation stay same during last few days.

I out of ideas now.

PS: I going to build xlogdump and try examine what filling all my wal files.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2011-11-16 15:37:33 Re: BUG #6295: Update fails on empty concatenated strings
Previous Message Jonas Forsman 2011-11-16 08:47:12 BUG #6295: Update fails on empty concatenated strings