Separate shared_buffer management process

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Would it be a good idea to have a separate shared buffer process to
manage the cache? Could such a process take workload off of the main
backend and improve their performance?

Just an idea?

I can't recall if this has been discussed on the list, but I know I've
thought about the idea of a background "buffer writer" process that
would simply cycle through the buffer cache and write out dirty buffers
in some low-priority fashion.

The idea is this would reduce the I/O crunch at checkpoint times, as
well as reducing the odds that any foreground backend process would have
to block waiting for I/O before it could recycle a buffer slot to read
in a page it needs. (Perhaps the background writer could be tuned to
preferentially write dirty buffers that are near the tail of the LRU
queue, and thus are likely to get recycled soon.)

In the WAL world, you cannot "write a dirty buffer" until you have
written *and synced* the WAL log as least as far as the LSN of the
buffer you want to write. So a background buffer writer would have
to write WAL buffers as well, and in that context it could find itself
blocking foreground processes. I'm not sure what this does to the
notion of "background I/O". Maybe only buffers whose changes are
already synced in WAL should be eligible for background write.
It needs some thought anyway.

regards, tom lane

Added to TODO:

* Use background process to write dirty shared buffers to disk

---------------------------------------------------------------------------

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Would it be a good idea to have a separate shared buffer process to
manage the cache? Could such a process take workload off of the main
backend and improve their performance?

Just an idea?

I can't recall if this has been discussed on the list, but I know I've
thought about the idea of a background "buffer writer" process that
would simply cycle through the buffer cache and write out dirty buffers
in some low-priority fashion.

The idea is this would reduce the I/O crunch at checkpoint times, as
well as reducing the odds that any foreground backend process would have
to block waiting for I/O before it could recycle a buffer slot to read
in a page it needs. (Perhaps the background writer could be tuned to
preferentially write dirty buffers that are near the tail of the LRU
queue, and thus are likely to get recycled soon.)

In the WAL world, you cannot "write a dirty buffer" until you have
written *and synced* the WAL log as least as far as the LSN of the
buffer you want to write. So a background buffer writer would have
to write WAL buffers as well, and in that context it could find itself
blocking foreground processes. I'm not sure what this does to the
notion of "background I/O". Maybe only buffers whose changes are
already synced in WAL should be eligible for background write.
It needs some thought anyway.

regards, tom lane

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

This would be a good idea I think. DB2 has a page-cleaner background process that periodically writes out dirty pages to disk. Reduces checkpoint I/O.

I don't see much point in serializing all bufferpool I/O through a separate dedicated backend. Informix uses something like this.

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh
Ph: (510) 642-8072

----- Original Message -----
From: Bruce Momjian <pgman@candle.pha.pa.us>
Date: Wednesday, October 8, 2003 12:33 pm
Subject: Re: [HACKERS] Separate shared_buffer management process