Hi,
I have very bad bgwriter statistics on a server which runs since many weeks
and it is still the same after a recent restart.
There are roughly 50% of buffers written by the backend processes and the
rest by checkpoints.
The statistics below are from a server with 140GB RAM, 32GB shared_buffers
and a runtime of one hour.
As you can see in the pg_buffercache view that there are most buffers
without usagecount - so they are as free or even virgen as they can be.
At the same time I have 53% percent of the dirty buffers written by the
backend process.
I want to tune the database to achieve a ratio of max 10% backend writer vs.
90% checkpoint or bgwriter writes.
But I don't understand how postgres is unable to fetch a free buffer.
Does any body have an idea?
I'm running postgres 8.4.4 64 Bit on linux.
Best Regards,
Uwe
background writer stats
checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
maxwritten_clean | buffers_backend | buffers_alloc
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
3 | 0 | 99754 | 0
| 0 | 115307 | 246173
(1 row)
background writer relative stats
checkpoints_timed | minutes_between_checkpoint | buffers_checkpoint |
buffers_clean | buffers_backend | total_writes | avg_checkpoint_write
-------------------+----------------------------+--------------------+---------------+-----------------+--------------+----------------------
100% | 10 | 46% |
0% | 53% | 0.933 MB/s | 259.000 MB
(1 row)
postgres=# select usagecount,count(*),isdirty from pg_buffercache group by
isdirty,usagecount order by isdirty,usagecount;
usagecount | count | isdirty
------------+---------+---------
1 | 31035 | f
2 | 13109 | f
3 | 184290 | f
4 | 6581 | f
5 | 912068 | f
1 | 6 | t
2 | 35 | t
3 | 48 | t
4 | 53 | t
5 | 43066 | t
| 3004013 |
(11 rows)
