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)
