Re: random_page_cost vs seq_page_cost

On Tue, Feb 07, 2012 at 05:06:18PM -0500, Greg Smith wrote:
> On 02/07/2012 03:23 PM, Bruce Momjian wrote:
> >Where did you see that there will be an improvement in the 9.2
> >documentation?  I don't see an improvement.
> 
> I commented that I'm hoping for an improvement in the documentation
> of how much timing overhead impacts attempts to measure this area
> better.  That's from the "add timing of buffer I/O requests" feature
> submission.  I'm not sure if Bene read too much into that or not; I
> didn't mean to imply that the docs around random_page_cost have
> gotten better.
> 
> This particular complaint is extremely common though, seems to pop
> up on one of the lists a few times each year.  Your suggested doc
> fix is fine as a quick one, but I think it might be worth expanding
> further on this topic.  Something discussing SSDs seems due here
> too.  Here's a first draft of a longer discussion, to be inserted
> just after where it states the default value is 4.0:

I was initially concerned that tuning advice in this part of the docs
would look out of place, but now see the 25% shared_buffers
recommentation, and it looks fine, so we are OK.  (Should we caution
against more than 8GB of shared buffers?  I don't see that in the docs.)

I agree we are overdue for better a explanation of random page cost, so
I agree with your direction.  I did a little word-smithing to tighten up
your text;  feel free to discard what you don't like:
Random access to mechanical disk storage is normally much more expensivethan four-times sequential access.  However, a
lowerdefault is used(4.0) because the majority of random accesses to disk, such as indexedreads, are assumed to be in
cache. The default value can be thought ofas modeling random access as 40 times slower than sequential, whileexpecting
90%of random reads to be cached.If you believe a 90% cache rate is an incorrect assumptionfor your workload, you can
increaserandom_page_cost to betterreflect the true cost of random storage reads. Correspondingly,if your data is likely
tobe completely in cache, such as whenthe database is smaller than the total server memory, decreasingrandom_page_cost
canbe appropriate.  Storage that has a low randomread cost relative to sequential, e.g. solid-state drives, mightalso
bebetter modeled with a lower value for random_page_cost.
 

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +