Re: Final background writer cleanup for 8.3

On Sat, 25 Aug 2007, Kevin Grittner wrote:

> in our environment there tends to be a lot of activity on a singe court
> case, and then they're done with it.

I submitted a patch to 8.3 that lets contrib/pg_buffercache show the
usage_count data for each of the buffers. It's actually pretty tiny; you
might consider applying just that patch to your 8.2 production system and
installing the module (as an add-in, it's easy enough to back out). See
http://archives.postgresql.org/pgsql-patches/2007-03/msg00555.php

With that patch in place, try a query like

select usagecount,count(*),isdirty from pg_buffercache group by
isdirty,usagecount order by isdirty,usagecount;

That lets you estimate how much waste would be involved for your
particular data if you wrote it out early--the more high usage_count
blocks in there cache, the worse the potential waste. With the tests I
was running, the hot index blocks were pegged at the maximum count allowed
(5) and they were taking up around 20% of the buffer cache. If those were
written out every time they were touched, it would be a bad scene.

It sounds like your system has a lot of data where the usage_count would
be much lower on average, which would explain why you've been so
successful with resolving it using the background writer. That's a
slightly easier problem to solve than the one I've been banging on.

> I'm not moving to it for production until I've established that as a
> fact, however.

And you'd be crazy to do otherwise.

> I'm not entirely convinced that it's a sound assumption that we should
> always try to keep some dirty buffers in the cache on the off chance that
> we might be smarter than the OS/FS/RAID controller algorithms about when to
> write them.

All I can say is that every time someone had tried to tune the code toward
writing that much more proactively, the results haven't seemed like an
improvement. I wouldn't characterize it as an assumption--it's a theory
that seems to hold every time it's tested. At least on the kind of Linux
systems people put into production right now (which often have relatively
old kernels), the OS is not as smart as everyone would like to to be in
this area.

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