On 11/01/2011 08:58 AM, Kevin Grittner wrote:
Brian Fehrle<brianf@xxxxxxxxxxxxxxxxxxx> wrote:
So my main question is, where is the issue?
That's my question, too. What problem are you trying to solve?
We're trying to offload dirty buffer writes from checkpoints and from
backends (not the bgwriter) to the background writer, I believe with the
hope that it's the same amount of disk IO, but spread out evenly rather
than just when a checkpoint is occurring.
It doesn't seem (to me) that the background writer is having a
hard time keeping up, because there are simply tons of times where
it's doing nothing. So is it just not determining that it needs to
do anything because there are already enough 'clean buffers' ready
for use at any given time?
Yes. Writing dirty buffers when there are enough buffers available
to service requests would tend to increase overall disk writes and
degrade performance. You don't have a problem unless you have a
high percentage of writes from normal backends which need to flush a
buffer in order to get one.
This seems to be the case, as buffers_backend is between
checkpoint_buffers and clean_buffers from pg_stat_bgwriter.
For example, on 2011-10-19:
checkpoint buffers: 622,519
clean_buffers: 65,879
clean_max_written: 56
backend_buffers: 460,471
Am I reading these right in wanting to reduce backend_buffers and
checkpoint_buffers?
- Brian F
Would increasing bgwriter_lru_multiplier to a higher value help
get more to be written by the bgwriter, and if so are there any
negative side effects I would need to consider for this?
Yes. If a buffer is written to again by PostgreSQL after it hits
disk because your background writer was overly aggressive, you will
hurt your overall throughput. Now, sometimes that's worth doing to
control latency, but you haven't described any such problem.
-Kevin
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
