Re: determining random_page_cost value

Yohanes,

> Yesterday in #pgsql, I was talking with neilc about determining rpc
> value in a more concrete way. So I created a program that compares
> exhaustive (all blocks are eventually read) random reads with
> sequential reads. The full source is attached.

Thanks for code.

> I tested the db files residing on a software RAID-1 composed of 2 IDE
> 7200rpm drives on linux 2.6.12.

FWIW, most performance-conscious users will be using a SCSI RAID array.

> is not precise enough. Which pages? Those that belong to the dbase
> file or sequential pages on the media?

Well, it's actually calculating the cost ratio of pulling non-sequential
random *rows* from the db files against pulling sequential blocks.

> On dbases smaller (calculated from du <dbase_dir>)than 500M, I got a
> ratio (random over sequential time) of 4.5:1. A 3.0GB dbase has a
> ratio of 10:1. On a 3GB contiguous file, the ratio is about 4:1.

All of this goes to uphold Tom's general assertion that the default of 4 is
more or less correct but the calculation in which we're using that number is
not.

--
Josh Berkus
Aglio Database Solutions
San Francisco