Re: Performance

2011/4/14 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Nathan Boley <npboley(at)gmail(dot)com> writes:
>> FWIW, awhile ago I wrote a simple script to measure this and found
>> that the *actual* random_page / seq_page cost ratio was much higher
>> than 4/1.
>
> That 4:1 ratio is based on some rather extensive experimentation that
> I did back in 2000.  In the interim, disk transfer rates have improved
> quite a lot more than disk seek times have, and the CPU cost to process
> a page's worth of data has also improved compared to the seek time.
> So yeah, you'd likely get a higher number if you redid those experiments
> on modern hardware (at least assuming it was rotating media and not SSD).
> On the other hand, the effects of caching push the numbers in the other
> direction, and modern machines also have a lot more RAM to cache in than
> was typical ten years ago.  I'm not sure how much point there is in
> trying to improve the default number in the abstract --- we'd really
> need to have a more robust model of cache effects before I'd trust any
> automatic tuning procedure to set the value for me.

Well, at spare time, I am doing some POC with "ANALYZE OSCACHE
relation;", pg stats are updated accordingly with new data ( it is not
finish yet) : at least the percentage in OS cache, maybe the number of
groups in cache and/or the distribution.

Anyway the idea is to allow the planner to use random and seq page
cost to be applyed on the part not-in-cache, without replacing the
algo using effective_cache_size. The planner may have one other GUC
like 'mem_page_cost' to set a cost on access from cache and use it
while estinating the cost...

Side effect is that random page cost and seq page cost should be more
stable and easiest to set based on a script because they won't have
the mixed sources of disk/memory, only the disk acces cost. (if
ANALYZE OSCACHE is good enough)
--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support