ACM Paper relevant to our buffer algorithm

Here are some more recent papers that also give good insight into research
in this area:

http://www.cs.usask.ca/~wew036/comprehensive.pdf
http://www.cse.ohio-state.edu/hpcs/WWW/HTML/publications/papers/TR-05-3.pdf

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Greg Smith wrote:

Here are some more recent papers that also give good insight into
research in this area:

http://www.cs.usask.ca/~wew036/comprehensive.pdf
http://www.cse.ohio-state.edu/hpcs/WWW/HTML/publications/papers/TR-05-3.pdf

Nice papers.

What I'd like to see is a paper on precleaning strategies. I tried to
google for one but couldn't find any. That would be very relevant to the
bgwriter tuning discussions.

I'm still struggling to understand why and how bgwriter increases
performance. Under what circumstances, what workload?

The only benefit I can see is that it moves the write() of a page out of
the critical path. But as long as the OS cache can absorb the write, it
should be very cheap compared to doing real I/O. Apparently the workload
that benefits most is an OLTP workload where response times are
critical, on a database that doesn't fit in share_buffers, but fits in
OS cache.

Am I missing something? What kind of a test would show the most benefit?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Wed, Jul 04, 2007 at 11:09:19AM +0100, Heikki Linnakangas wrote:

The only benefit I can see is that it moves the write() of a page out of
the critical path. But as long as the OS cache can absorb the write, it
should be very cheap compared to doing real I/O. Apparently the workload
that benefits most is an OLTP workload where response times are
critical, on a database that doesn't fit in share_buffers, but fits in
OS cache.

I thought the point was to make checkpoints cheaper. Sure, the OS can
probably absorb the write() but you execute a fsync() shortly after so
you're going to block on that. The point being that by executing the
writes earlier you can get some of the writing done in the bakcground
prior to the fsync.

So it would be targetting people with lots of dirty shared buffers
where a checkpoint is going to eat your I/O bandwidth. An fsync will
make the OS write everything ASAP.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout wrote:

On Wed, Jul 04, 2007 at 11:09:19AM +0100, Heikki Linnakangas wrote:

The only benefit I can see is that it moves the write() of a page out of
the critical path. But as long as the OS cache can absorb the write, it
should be very cheap compared to doing real I/O. Apparently the workload
that benefits most is an OLTP workload where response times are
critical, on a database that doesn't fit in share_buffers, but fits in
OS cache.

I thought the point was to make checkpoints cheaper. Sure, the OS can
probably absorb the write() but you execute a fsync() shortly after so
you're going to block on that. The point being that by executing the
writes earlier you can get some of the writing done in the bakcground
prior to the fsync.

That was the purpose of the bgwriter "all"-scan. It was removed as part
of the load distributed checkpoints patch, as it's not needed anymore.

What's the purpose of the lru-scan?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki@enterprisedb.com> writes:

Greg Smith wrote:

Here are some more recent papers that also give good insight into research in
this area:
http://www.cs.usask.ca/~wew036/comprehensive.pdf
http://www.cse.ohio-state.edu/hpcs/WWW/HTML/publications/papers/TR-05-3.pdf

Nice papers.

What I'd like to see is a paper on precleaning strategies. I tried to google
for one but couldn't find any. That would be very relevant to the bgwriter
tuning discussions.

I saw some listed but they were for VM managers so they may not be perfectly
adaptable.

I'm still struggling to understand why and how bgwriter increases performance.
Under what circumstances, what workload?

The only benefit I can see is that it moves the write() of a page out of the
critical path. But as long as the OS cache can absorb the write, it should be
very cheap compared to doing real I/O.

Well you can't keep writing indefinitely faster than the i/o subsystem can
execute the writes. Eventually the kernel will block your write until a kernel
buffer becomes free. Ie, throttle your writes to the actual write bandwidth
available.

I think most systems are limited by the latency of reads though. Reads can't
be re-ordered as well as writes can, they happen synchronously as far as an
individual backend is concerned.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Gregory Stark <gsstark@mit.edu> writes:

Heikki Linnakangas <heikki@enterprisedb.com> writes:

I'm still struggling to understand why and how bgwriter increases performance.
Under what circumstances, what workload?

The only benefit I can see is that it moves the write() of a page out of the
critical path. But as long as the OS cache can absorb the write, it should be
very cheap compared to doing real I/O.

Well you can't keep writing indefinitely faster than the i/o subsystem can
execute the writes. Eventually the kernel will block your write until a kernel
buffer becomes free. Ie, throttle your writes to the actual write bandwidth
available.

Right. Also, a buffer write isn't "merely" a kernel call --- for
instance, you might have to flush some more WAL before you can execute
the write, and there are cases where you'd have to fsync the write
yourself (ie, if you can't pass it off to the bgwriter). The more of
that we can take out of foreground query paths, the better.

regards, tom lane

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

Gregory Stark <gsstark@mit.edu> writes:

Heikki Linnakangas <heikki@enterprisedb.com> writes:

I'm still struggling to understand why and how bgwriter increases performance.
Under what circumstances, what workload?

The only benefit I can see is that it moves the write() of a page out of the
critical path. But as long as the OS cache can absorb the write, it should be
very cheap compared to doing real I/O.

Well you can't keep writing indefinitely faster than the i/o subsystem can
execute the writes. Eventually the kernel will block your write until a kernel
buffer becomes free. Ie, throttle your writes to the actual write bandwidth
available.

Right. Also, a buffer write isn't "merely" a kernel call --- for
instance, you might have to flush some more WAL before you can execute
the write, and there are cases where you'd have to fsync the write
yourself (ie, if you can't pass it off to the bgwriter). The more of
that we can take out of foreground query paths, the better.

So it sounds like a good place to start to try to benchmark something where
bgwriter helps might be a setup which starts with a very large table (like
1-10M rows) and each transaction deletes a large number of random tuples (~ 1k
rows). Possibly waiting briefly before committing to give a chance for the
dirty pages to be needed before commit flushes the wal.

That way each transaction dirties a large number of pages and the next
transaction is likely to need a fresh page and find one which has been dirtied
and not had its wal record flushed. Deletes mean not much wal will be
generated so wal won't be a bottleneck and you won't get checkpoints due to
checkpoint_segments being reached.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com