buffercache/bgwriter

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux