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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

               http://www.postgresql.org/about/donate

Reply via email to