Thread: How is random_page_cost=4 ok?
I'm kind of curious where the value of 4 for random_page_cost came from. IIRC, Tom, you mentioned it came from tests you ran -- were those raw i/o tests or Postgres cost estimates compared to execution times? Te reason I'm wondering about this is it seems out of line with raw i/o numbers. Typical values for consumer drives are about a sustained throughput of 60MB/s ( Ie .2ms per 8k) and seek latency of 4ms. That gives a ratio of 20. Server-class drives have even a ratio since a 15kRPM drive can have a sustained bandwidth of 110-170 MB/s (48us-75us) and an average seek latency of 2ms giving a ratio of 27-42. And of course that doesn't include the effects of a RAID array which magnifies that ratio. I'm concerned that if we start recommending such large random_page_costs as these it'll produce plans that are very different from what people have grown used to. And the value of 4 seems to work well in practice. I suspect the root of all this is that random_page_cost is encoding within it the effects of caching. If that's true shouldn't we find a way to model caching using effective_cache_size instead so that people can set random_page_cost realistically based on their hardware? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
Gregory Stark <stark@enterprisedb.com> writes:
> I'm kind of curious where the value of 4 for random_page_cost came from.
> IIRC, Tom, you mentioned it came from tests you ran -- were those raw i/o
> tests or Postgres cost estimates compared to execution times?
It was based on actual query execution times, but that was with 1990s
hardware. It doesn't surprise me at all that modern drives would show a
larger ratio --- seems like transfer rate has improved more than seek
times. And it's also the case that we've squeezed a lot of overhead
out of Postgres' tuple processing code since then, so that might be
another way in which seqscans have gotten cheaper.
> I'm concerned that if we start recommending such large random_page_costs as
> these it'll produce plans that are very different from what people have grown
> used to. And the value of 4 seems to work well in practice.
Yeah, it seems like raising random_page_cost is not something we ever
recommend in practice. I suspect what we'd really need here to make any
progress is a more detailed cost model, not just fooling with the
parameters of the existing one.
> I suspect the root of all this is that random_page_cost is encoding within it
> the effects of caching. If that's true shouldn't we find a way to model
> caching using effective_cache_size instead so that people can set
> random_page_cost realistically based on their hardware?
We do model caching using effective_cache_size. One thing we definitely
lack is any understanding of the effects of caching across multiple
queries. I'm not sure what other first-order effects are missing from
the model ...
regards, tom lane
Gregory Stark schrieb: > Te reason I'm wondering about this is it seems out of line with raw i/o > numbers. Typical values for consumer drives are about a sustained throughput > of 60MB/s ( Ie .2ms per 8k) and seek latency of 4ms. That gives a ratio of 20. > > Server-class drives have even a ratio since a 15kRPM drive can have a > sustained bandwidth of 110-170 MB/s (48us-75us) and an average seek latency of > 2ms giving a ratio of 27-42. And of course that doesn't include the effects of > a RAID array which magnifies that ratio. Hi Gregory, I think your numbers are a bit off: For "Consumer drives" (7.200 RPM SATA 3.5"), seek times are much worse, in the area of 8-9ms (see [1]), but sustained sequential read numbers are noticeable higher, around 80-90MB/sec. For "Server Drives" 3-4ms are more realistic ([2], [3]) for average seeks and the 110-170MB/sec are highly exaggerated. Unfortunately I have only 2.5" SAS 10k drives and no FreeBSD here, otherwise I could provide some real world numbers; the diskinfo tool in [3] looks really nice (and makes me crave FreeBSD). best regards, Michael [1] http://h18004.www1.hp.com/products/quickspecs/13021_div/13021_div.html [2] http://h18004.www1.hp.com/products/quickspecs/12244_div/12244_div.html [3] http://blog.insidesystems.net/articles/2007/04/09/unscientific-15k-v-10k-sas-drive-comparison
Michael Renner <michael.renner@amd.co.at> writes: > I think your numbers are a bit off: > > For "Consumer drives" (7.200 RPM SATA 3.5"), seek times are much worse, in the > area of 8-9ms (see [1]), but sustained sequential read numbers are noticeable > higher, around 80-90MB/sec. I took the seek latency from the data sheet for a Barracuda 7200.9 which is several generations old but still a current model. Just rotational latency would have a worst case of 8.3ms and half that is precisely the 4.16 they quote so I suspect that's where the number comes from. Not especially helpful perhaps. They don't quote sustained bandwidth for consumer drives but 50-60MB/s are the numbers I remembered -- admittedly from more than a couple years ago. I didn't realize 7200 RPM drives had reached such speeds yet. But with your numbers things look even weirder. With a 90MB/s sequential speed (91us) and 9ms seek latency that would be a random_page_cost of nearly 100! > For "Server Drives" 3-4ms are more realistic ([2], [3]) for average seeks and > the 110-170MB/sec are highly exaggerated. In that case both of those numbers come straight from Seagate's data sheet for their top-of-the-line data centre drives: http://www.seagate.com/docs/pdf/datasheet/disc/ds_cheetah_15k_6.pdf -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!
Gregory Stark <stark@enterprisedb.com> writes: >> For "Server Drives" 3-4ms are more realistic ([2], [3]) for average seeks and >> the 110-170MB/sec are highly exaggerated. > > In that case both of those numbers come straight from Seagate's data sheet for > their top-of-the-line data centre drives: > > http://www.seagate.com/docs/pdf/datasheet/disc/ds_cheetah_15k_6.pdf Oh, but I just noticed they separately quote latency and read/write seek time. The average read seek time is 3.4ms. That gives a random_page_cost of 45-71. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
Gregory Stark schrieb: > But with your numbers things look even weirder. With a 90MB/s sequential speed > (91us) and 9ms seek latency that would be a random_page_cost of nearly 100! Looks good :). If you actually want to base something on Real World numbers I'd suggest that we collect them beforehand from existing setups. I was introduced to IOmeter [1] at an HP performance course which is a nice GUI Tool which allows you to define workloads to your likings and test it against given block devices, unfortunately it's Windows only. fio [2] and Iozone [3] should do the same for the Unix-World, without the "nice" and "GUI" parts ;). For improving the model - in what situations would we benefit from a more accurate model here? Is it correct that this is only relevant for large (if not huge) tables which border on (or don't fit in) effective_cache_size (and respectively - the OS Page cache)? And we need the cost to decide between a sequential, index (order by, small expected result set) and a bitmap index scan? Speaking of bitmap index/heap scans - are those counted against seq or random_page_cost? regards, michael [1] http://www.iometer.org/ [2] http://freshmeat.net/projects/fio/ [3] http://www.iozone.org/
On Fri, 10 Oct 2008, Gregory Stark wrote: > They don't quote sustained bandwidth for consumer drives but 50-60MB/s are the > numbers I remembered -- admittedly from more than a couple years ago. I didn't > realize 7200 RPM drives had reached such speeds yet. The cheap ($42!) 7200RPM SATA disks I bought a stack of for my home server hit a sequential 110MB/s at the beginning edge, at the other end throughput is still 60-70MB/s. The smaller capacities of Seagate's 7200.11 average about 100MB/s nowadays. But by the time you seek to a location (8-9ms) and line the heads up (half a rotation at 7200RPM averages 4ms) you can easily end up at 12-13ms or higher measured access time on random reads with those. So the true random/sequential ratio reaches crazy numbers. I don't think random_page_cost actually corresponds with any real number anymore. I just treat it as an uncalibrated knob you can turn and benchmark the results at. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>>> Greg Smith <gsmith@gregsmith.com> wrote: > I don't think random_page_cost actually corresponds with any real number > anymore. I just treat it as an uncalibrated knob you can turn and > benchmark the results at. Same here. We have always found best performance in our production environments with this set to somewhere from the same as seq_page_cost to twice seq_page_cost -- depending on how much of the database is cached. As we get toward more heavily cached databases we also reduce seq_page_cost. So we range from (0.1,0.1) to (1,2). These have really become abstractions with legacy names. If I had to suggest how someone choose a starting setting, I would say that seq_page_cost should be the proportion of sequential scans likely to need to go the disk, and random_page_cost should be two times the proportion of heap data which doesn't fit in cache space. Add 0.1 to both numbers and then truncate to one decimal position. This, of course, assumes a battery backed caching RAID controller, a reasonable RAID for the data set, and one of the more typical types of usage patterns. -Kevin
Greg Smith <gsmith@gregsmith.com> writes:
> ... So the true random/sequential ratio
> reaches crazy numbers.
Bear in mind that seq_page_cost and random_page_cost are intended to
represent the time to read *and process* a page, so there's some CPU
component involved there, and this limits the ratio that could be
reached in practice.
In particular, if the OS lays out successive file pages in a way that
provides zero latency between logically adjacent blocks, I'd bet a good
bit that a Postgres seqscan would miss the read timing every time, and
degrade to handling about one block per disk rotation. Those 100MB/s
numbers are just mirages as far as seqscan speed goes.
regards, tom lane
Tom Lane wrote: > In particular, if the OS lays out successive file pages in a way that > provides zero latency between logically adjacent blocks, I'd bet a good > bit that a Postgres seqscan would miss the read timing every time, and > degrade to handling about one block per disk rotation. Unless the OS does some readahead when it sees something like a seq scan?
> I don't think random_page_cost actually corresponds with any real number > anymore. I just treat it as an uncalibrated knob you can turn and > benchmark the results at. And, frankly, not a useful knob. You get much more useful results out of effective_cache_size and cpu_* costs than you get out of messing with random_page_cost, unless you're running on SSD or something which would justify a lower RPC, or if you're compensating for our poor n-distinct estimation for very large tables. --Josh
Josh Berkus <josh@agliodbs.com> writes: >> I don't think random_page_cost actually corresponds with any real number >> anymore. I just treat it as an uncalibrated knob you can turn and benchmark >> the results at. > > And, frankly, not a useful knob. You get much more useful results out of > effective_cache_size and cpu_* costs than you get out of messing with > random_page_cost, unless you're running on SSD or something which would justify > a lower RPC, or if you're compensating for our poor n-distinct estimation for > very large tables. Uh, that doesn't make much sense. effective_cache_size is only used currently to estimate intra-query caching effects. It doesn't compensate for stead-state cache hit rates. And "our poor n-distinct estimation" is a problem which manifests by having inconsistent estimates for number of tuples. It could be high one day and low the next, so I don't see how biasing in any specific direction could be helpful. In any case adjusting random_page_cost would be missing the target by a wide margin since it's not going to fix the tuple count estimate itself in any way and the rest of the plan will be predicated on that estimate, not just the estimated cost of the scan. Adjusting the cpu_* costs together amounts to the same thing as adjusting seq_page_cost and random_page_cost together since the numbers are all relative to each other and that's the whole set. Ie, doubling all the cpu_* costs is the same has halving the two disk costs. In any case your experience doesn't match mine. On a machine with a sizable raid controller setting random_page_cost higher does generate, as expected, plans with more bitmap heap scans which are in fact faster. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
On Fri, 10 Oct 2008, Tom Lane wrote: > In particular, if the OS lays out successive file pages in a way that > provides zero latency between logically adjacent blocks, I'd bet a good > bit that a Postgres seqscan would miss the read timing every time, and > degrade to handling about one block per disk rotation. The drives themselves, and possibly the OS and disk controller, are all running read-ahead algorithms to accelerate this case. In fact, this *exact* case for the Linux read-ahead stuff that just went mainline recently: http://kerneltrap.org/node/6642 I was reading something the other day about how drives with bigger caches are starting to have firmware tuned to just start reading from wherever the head happens to be end up at once the seek has found the right area, even if it's not what you asked for, in hopes that you'll want those nearby blocks soon, too. If the drive has 32MB of cache in it and you're seeking around, you've got a pretty big working area relative to how fast you can fill that with requested data. And then there's a patch that helps accelerate this process I should get back to benchmarking again... -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
In any case your experience doesn't match mine. On a machine with a sizable
raid controller setting random_page_cost higher does generate, as expected,
plans with more bitmap heap scans which are in fact faster.
We're running postgres backed by a NetApp 3020 via fiber and have had a lot of success setting random page cost very high (10). Sequential reads are just that much faster. I'm not sure if thats because we've configured something wrong or what, but thats a really useful knob for us.
Greg Smith wrote: > The drives themselves, and possibly the OS and disk controller, are all > running read-ahead algorithms to accelerate this case. In fact, this > *exact* case for the Linux read-ahead stuff that just went mainline > recently: http://kerneltrap.org/node/6642 Apparently only the "simple" stuff hit mainline, see [1] and [2], not knowing how this turns out for pg-style loads, especially compared to the full-fledged patch. Readahead is probably too much of a beast that no one dares to touch with a 3-foot-pole, unless given a large team with good standing in the kernel community and concerted regression testing in whatever environment Linux is used these days... michael [1] http://lwn.net/Articles/235164/ [2] http://git.kernel.org/?p=linux/kernel/git/torvalds/linux-2.6.git;a=history;f=mm/readahead.c
Nikolas Everett wrote: > > > > In any case your experience doesn't match mine. On a machine with a sizable > > raid controller setting random_page_cost higher does generate, as expected, > > plans with more bitmap heap scans which are in fact faster. > > > > We're running postgres backed by a NetApp 3020 via fiber and have had a lot > of success setting random page cost very high (10). Sequential reads are > just that much faster. I'm not sure if thats because we've configured > something wrong or what, but thats a really useful knob for us. One other issue is that sequential I/O for data that is mostly random is going to wipe more of the buffer cache than random access, so there should perhaps be some additional cost associated with sequential access to bias toward random access. Not sure how our new code that prevents large table scans from wiping the cache affect this. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Oct 10, 2008, at 7:41 PM, Nikolas Everett wrote: > In any case your experience doesn't match mine. On a machine with a > sizable > raid controller setting random_page_cost higher does generate, as > expected, > plans with more bitmap heap scans which are in fact faster. > > We're running postgres backed by a NetApp 3020 via fiber and have > had a lot of success setting random page cost very high (10). > Sequential reads are just that much faster. I'm not sure if thats > because we've configured something wrong or what, but thats a > really useful knob for us. Is your workload OLTP or OLAP? Promoting seqscans in an OLTP environment seems to be a really bad idea to me... -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828