Re: Automatic adjustment of bgwriter_lru_maxpages

On Mon, 14 May 2007, Tom Lane wrote:

> If you can write something like that, why do we need the parameter at all?

Couple of reasons:

-As I already mentioned in my last message, I think it's unwise to let the
LRU writes go completely unbounded. I still think there should be a
maximum, and if there is one it should be tunable. You can get into
situations where the only way to get the LRU writer to work at all is to
set the % to scan fairly high, but that exposes you to way more writes
than you might want per interval in situations where buffers to write are
easy to find.

-There is considerable coupling between how the LRU and the all background
writers work. There are workloads where the LRU writer is relatively
ineffective, and only the all one really works well. If there is a
limiter on the writes from the all writer, but not on the LRU, admins may
not be able to get the balance between the two they want. I know I
wouldn't.

-Just because I can advise what is generally the right move, that doesn't
mean it's always the right one. Someone may notice that the maximum pages
written limit is being nailed and not care.

The last system I really got deep into the background writer mechanics on,
it could be very effective at improving performance and reducing
checkpoint spikes under low to medium loads. But under heavy load, it
just got in the way of the individual backends running, which was
absolutely necessary in order to execute the LRU mechanics (usage_count--)
so less important buffers could be kicked out. I would like people to
still be able to set a tuning such that the background writers were useful
under average loads, but didn't ever try to do too much. It's much more
difficult to do that if bgwriter_lru_maxpages goes away.

I realized recently the task I should take on here is to run some more
experiments with the latest code and pass along suggested techniques for
producing/identifying the kind of problem conditions I've run into in the
past; then we can see if other people can reproduce them. I got a new
8-core server I need to thrash anyway and will try and do just that
starting tomorrow.

For all I know my concerns are strictly a rare edge case. But since the
final adjustments to things like whether there is an upper limit or not
are very small patches compared to what's already been done here, I sent
in what I thought was ready to go because I didn't want to hold up
reviewing the bulk of the code over some of these fine details.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD