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