Обсуждение: Initial prefetch performance testing

The complicated patch I've been working with for a while now is labeled 
"sequential scan posix fadvise" in the CommitFest queue.  There are a lot 
of parts to that, going back to last December, and I've added the many 
most relevant links to the September CommitFest page.

The first message there on this topic is 
http://archives.postgresql.org/message-id/87ve7egxow.fsf@oxford.xeocode.com 
which is a program from Greg Stark that measures how much prefetching 
advisory information improves the overall transfer speed on a synthetic 
random read benchmark.  The idea is that you advise the OS about up to n 
requests at a time, where n goes from 1 (no prefetch at all) to 8192.  As 
n goes up, the total net bandwidth usually goes up as well.  You can 
basically divide the bandwidth at any prefetch level by the baseline (1=no 
prefetch) to get a speedup multiplier.  The program allows you to submit 
both unsorted and sorted requests, and the speedup is pretty large and 
similarly distributed (but of different magnitude) in both cases.

While not a useful PostgreSQL patch on its own, this program lets one 
figure out if the basic idea here, advise about blocks ahead of time to 
speed up the whole thing, works on a particular system without having to 
cope with a larger test.  What I have to report here are some results from 
many systems running both Linux and Solaris with various numbers of disk 
spindles.  The Linux systems use the posix fadvise call, while the Solaris 
ones use its aio library.

Using the maximum prefetch working set tested, 8192, here's the speedup 
multiplier on this benchmark for both sorted and unsorted requests using a 
8GB file:

OS        Spindles    Unsorted X    Sorted X
1:Linux        1        2.3        2.1
2:Linux        1        1.5        1.0
3:Solaris    1        2.6        3.0
4:Linux        3        6.3        2.8
5:Linux (Stark)    3        5.3        3.6
6:Linux        10        5.4        4.9
7:Solaris*    48        16.9        9.2

Systems (1)-(3) are standard single-disk workstations with various speed 
and size disks.  (4) is a 3-disk software RAID0 (on an Areca card in JBOD 
mode).  (5) is the system Greg Stark originally reported his results on, 
which is also a 3-disk array of some sort.  (6) uses a Sun 2640 disk array 
with a 10 disk RAID0+1 setup, while (7) is a Sun Fire X4500 with 48 disks 
in a giant RAID-Z array.

The Linux systems drop the OS cache after each run, they're all running 
kernel 2.6.18 or higher with that feature.  Solaris system (3) is using 
the UFS filesystem with the default tuning, which doesn't cache enough 
information for that to be necessary[1]--the results look very similar to 
the Linux case even without explicitly dropping the cache.

* For (7) the results there showed obvious caching (>150MB/s), as I 
expected from Solaris's ZFS which does cache aggressively by default.  In 
order to get useful results with the server's 16GB of RAM, I increased the 
test file to 64GB, at which point the results looked reasonable.

Comparing with a prefetch working set of 256, which I eyeballed on the 
results spreadsheet I made as the best return on prefetch effort before 
improvements leveled off, the speedups looked like this:

OS        Spindles    Unsorted X    Sorted X
1:Linux        1        2.3        2.0
2:Linux        1        1.5        0.9
3:Solaris    1        2.5        3.3
4:Linux        3        5.8        2.6
5:Linux (Stark)    3        5.6        3.7
6:Linux        10        5.7        5.1
7:Solaris    48        10.0        7.8

Observations:

-For the most part, using the fadvise/aio technique was a significant win 
even on single disk systems.  The worst result, on system (2) with sorted 
blocks, was basically break even within the measurement tolerance here: 
94% of the no prefetch rate is the worst result I saw, but all these 
bounced around about +/- 5% so I wouldn't read too much into that.  In 
every other case, there was at least a 50% speed increase even with a 
single disk.

-As Greg Stark suggested, the larger the spindle count the larger the 
speedup, and the larger the prefetch size that might make sense.  His 
suggestion to model the user GUC as "effective_spindle_count" looks like a 
good one.  The sequential scan fadvise implementation patch submitted uses 
the earlier preread_pages name for that parameter, which I agree seems 
less friendly.

-The Solaris aio implementation seems to perform a bit better relative to 
no prefetch than the Linux fadvise one.  I'm left wondering a bit about 
whether that's just a Solaris vs. Linux thing, in particular whether 
that's just some lucky caching on Solaris where the cache isn't completely 
cleared, or whether Linux's aio library might work better than its fadvise 
call does.

The attached archive file includes a couple of useful bits for anyone who 
wants to try this test on their hardware.  I think I filed away all the 
rough edges here and it should be real easy for someone else to run this 
test now.  It includes:

-prefetch.c is a slightly modified version of the original test program. 
I fixed a couple of minor bugs in the parameter input/output code that 
only showed up under some platform combinations, the actual prefetch 
implementation is untouched.

-prefetchtest is a shell script that compiles the program and runs it 
against a full range of prefetch sizes.  Just run it and tell it where you 
want the test data file to go (with an optional size that defaults to 
8GB), and it produces an output file named prefetch-results.csv with all 
the results in it.

-I included all of the raw data for the various systems I tested so other 
testers have baselines to compare against.  An OpenOffice spreadsheet 
comparing all the results and that computes the ratios shown above is also 
included.

Conclusion:  on all the systems I tested on, this approach gave excellent 
results, which makes me feel confident that I should see a corresponding 
speedup on database-level tests that use this same basic technique.  I'm 
not sure whether it might make sense to bundle this test program up 
somehow so others can use it for similar compatibility tests (I'm thinking 
of something similar to contrib/test_fsync), will revisit that after the 
rest of the review.

Next step:  I've got two data sets (one generated, one real-world sample) 
that should demonstrate a useful heap scan prefetch speedup, and one test 
program I think will demonstrate whether the sequential scan prefetch code 
works right.  Now that I've vetted all the hardware/OS combinations I hope 
I can squeeze that in this week, I don't need to test all of them now that 
I know which are the interesting systems.

As far as other platforms go, I should get a Mac OS system in the near 
future to test on as well (once I have the database tests working, not 
worth scheduling yet), but as it will only have a single disk that will 
basically just be a compatibility test rather than a serious performance 
one.  Would be nice to get a report from someone running FreeBSD to see 
what's needed to make the test script run on that OS.

[1] http://blogs.sun.com/jkshah/entry/postgresql_east_2008_talk_best : 
Page 8 of the presentation covers just how limited the default UFS cache 
tuning is.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Mon, 2008-09-22 at 04:57 -0400, Greg Smith wrote:

> -As Greg Stark suggested, the larger the spindle count the larger the 
> speedup, and the larger the prefetch size that might make sense.  His 
> suggestion to model the user GUC as "effective_spindle_count" looks like a 
> good one.  The sequential scan fadvise implementation patch submitted uses 
> the earlier preread_pages name for that parameter, which I agree seems 
> less friendly.

Good news about the testing.

I'd prefer to set this as a tablespace level storage parameter. Since
that is where it would need to live when we have multiple tablespaces.
Specifically as a storage parameter, so we have same syntax for
table-level and tablespace-level storage parameters. That would also
allow us to have tablespace-level defaults for table-level settings.

prefetch_... is a much better name since its an existing industry term.
I'm not in favour of introducing the concept of spindles, since I can
almost hear the questions about ramdisks and memory-based storage. Plus
I don't ever want to discover that the best setting for
effective_spindles is 7 (or 5) when I have 6 disks because of some
technology shift or postgres behaviour change in the future.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support

On Sep 22, 2008, at 12:02 PM, Simon Riggs wrote:

>
> On Mon, 2008-09-22 at 04:57 -0400, Greg Smith wrote:
>
>> -As Greg Stark suggested, the larger the spindle count the larger the
>> speedup, and the larger the prefetch size that might make sense.  His
>> suggestion to model the user GUC as "effective_spindle_count" looks
>> like a
>> good one.  The sequential scan fadvise implementation patch
>> submitted uses
>> the earlier preread_pages name for that parameter, which I agree
>> seems
>> less friendly.
>
> Good news about the testing.


absolutely; we made tests and got similar figures.
also, I/O is much more stable and steady with the patch.


>
> I'd prefer to set this as a tablespace level storage parameter. Since
> that is where it would need to live when we have multiple tablespaces.
> Specifically as a storage parameter, so we have same syntax for
> table-level and tablespace-level storage parameters. That would also
> allow us to have tablespace-level defaults for table-level settings.
>


+1


> prefetch_... is a much better name since its an existing industry
> term.
> I'm not in favour of introducing the concept of spindles, since I can
> almost hear the questions about ramdisks and memory-based storage.
> Plus
> I don't ever want to discover that the best setting for
> effective_spindles is 7 (or 5) when I have 6 disks because of some
> technology shift or postgres behaviour change in the future.



i would definitely avoid to use of "spindles".
i totally agree with simon here. once mature SSD storage or some in-
memory stuff will be available for the masses, this is not suitable
anymore.
the best thing would be to simply use the parameter as it was in the
original patch.
maybe we should simply make the parameter adjustable per table and per
index. this would automatically cover 95% of all cases such as
clustered tables and so on.
many thanks and best regards,
    hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: www.postgresql-support.de

Simon Riggs <simon@2ndQuadrant.com> writes:

> On Mon, 2008-09-22 at 04:57 -0400, Greg Smith wrote:
>
>> -As Greg Stark suggested, the larger the spindle count the larger the 
>> speedup, and the larger the prefetch size that might make sense.  His 
>> suggestion to model the user GUC as "effective_spindle_count" looks like a 
>> good one.  The sequential scan fadvise implementation patch submitted uses 
>> the earlier preread_pages name for that parameter, which I agree seems 
>> less friendly.
>
> Good news about the testing.
>
> I'd prefer to set this as a tablespace level storage parameter. 

Sounds, like a good idea, except... what's a tablespace level storage parameter?


> prefetch_... is a much better name since its an existing industry term.
> I'm not in favour of introducing the concept of spindles, since I can
> almost hear the questions about ramdisks and memory-based storage. Plus
> I don't ever want to discover that the best setting for
> effective_spindles is 7 (or 5) when I have 6 disks because of some
> technology shift or postgres behaviour change in the future.

In principle I quite strongly disagree with this.

Someone might very well want to set spindle_count to 6 when he actually has 7
but at least he can have an intuitive feel for what he's doing -- he's setting
it to slightly less than Postgres thinks is optimal.

Number of blocks to prefetch is an internal implementation detail that the DBA
has absolutely no way to know what the correct value is. That's how we get the
cargo cult configuration tweaks we've seen in the past where people follow
recommendations with no idea what the consequences are or whether they apply.

In an ideal world we would have a half-dozen parameters to tell Postgres how
much memory is available, how many disks available, etc and Postgres would
know how best to use the resources. I think if we expose internal knobs like
you propose then we end up with hundreds of parameters and to adjust them
you'll have to be an expert in Postgres internals.

That said, there is a place for these internal knobs when we don't really know
how to best make use of resources. At this point we only have results from a
few systems and the results don't seem to jibe with the theory.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!

On Mon, 2008-09-22 at 16:46 +0100, Gregory Stark wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
> 
> > On Mon, 2008-09-22 at 04:57 -0400, Greg Smith wrote:
> >
> >> -As Greg Stark suggested, the larger the spindle count the larger the 
> >> speedup, and the larger the prefetch size that might make sense.  His 
> >> suggestion to model the user GUC as "effective_spindle_count" looks like a 
> >> good one.  The sequential scan fadvise implementation patch submitted uses 
> >> the earlier preread_pages name for that parameter, which I agree seems 
> >> less friendly.
> >
> > Good news about the testing.
> >
> > I'd prefer to set this as a tablespace level storage parameter. 
> 
> Sounds, like a good idea, except... what's a tablespace level storage parameter?

A storage parameter, just at tablespace level.

WITH (storage_parameter = value)

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support

On Mon, 22 Sep 2008, Simon Riggs wrote:

> I'd prefer to set this as a tablespace level storage parameter.

That seems reasonable, but I'm not working at that level yet.  There's 
still a larger open questions about how the buffer manager interaction 
will work here, and I'd like to have a better view of that first before 
getting into the exact syntax used to set the parameter.  For now, a GUC 
works well enough, but you're right that something finer-grained may make 
sense before this actually hits the codebase.

> prefetch_... is a much better name since its an existing industry term.
> I'm not in favour of introducing the concept of spindles, since I can
> almost hear the questions about ramdisks and memory-based storage.

It's possible to make a case for exposing the internal number that's 
getting varied here, naming the parameter something like prefetch_depth, 
and letting people set that to whatever they want.  Based on the current 
data I might suggest a default of 256, using 0 to turn the feature off 
altogether, and a maximum of at least 8192 and possibly more.

In practice I expect there to only be a couple of popular values and the 
idea of fine-tuning is a bit questionable.  I think that's what Greg Stark 
was driving at with how the value was re-spun.  Instead of using 
effective_spindle_count, you could just as easily make a case for an enum 
like [off,low,medium,high] mapping to [0,16,256,8192].  From what I've 
seen so far, that would reduce tweaking time in the field considerably 
while not really changing the range of available behavior very much.

I will be running a set of tests on a fast SSD device before I'm done, 
that's another one that I'll try once I've got the database-level tests 
ready to run, too.  What I expect is that it will favor 0, presumably you 
might as well just read the blocks rather than advise about them when the 
seek overhead is close to zero.  Should be able to do a RAM disk run as 
well.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Mon, 2008-09-22 at 13:06 -0400, Greg Smith wrote:

> > prefetch_... is a much better name since its an existing industry term.
> > I'm not in favour of introducing the concept of spindles, since I can
> > almost hear the questions about ramdisks and memory-based storage.
> 
> It's possible to make a case for exposing the internal number that's 
> getting varied here, naming the parameter something like prefetch_depth, 
> and letting people set that to whatever they want.  Based on the current 
> data I might suggest a default of 256, using 0 to turn the feature off 
> altogether, and a maximum of at least 8192 and possibly more.
> 
> In practice I expect there to only be a couple of popular values and the 
> idea of fine-tuning is a bit questionable.  I think that's what Greg Stark 
> was driving at with how the value was re-spun.  Instead of using 
> effective_spindle_count, you could just as easily make a case for an enum 
> like [off,low,medium,high] mapping to [0,16,256,8192].  From what I've 
> seen so far, that would reduce tweaking time in the field considerably 
> while not really changing the range of available behavior very much.

Tuning Postgres I/O already involves quite a few parameters called
buffersize, segment width, stripe size, etc.. I've never heard anything
from a disk manufacturer say this is wrong and we should just have
"spindle equivalents". I don't think we should dress this up too much,
that's all. We aren't going to make anybody's life any easier. But we
will probably generate lots of annoying phone calls to disk
manufacturers asking "so how many spindles is your subsystem worth in
Postgres terms?" to which they will shrug and say "no idea".

Is the behaviour of this sufficiently linear to be able to say that 3
spindles = 3 effective_spindles and 6=6 etc.? I would guess it won't be
and you're left with a name more misleading than useful.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support

Gregory Stark wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> I'm not in favour of introducing the concept of spindles....
> 
> In principle I quite strongly disagree with this....
> Number of blocks to prefetch is an internal implementation detail that the DBA
> has absolutely no way to know what the correct value is. 

Even more often on systems I see these days, "spindles"
is an implementation detail that the DBA has no way to know
what the correct value is.

For example, on our sites hosted with Amazon's compute cloud (a great
place to host web sites), I know nothing about spindles, but know
about Amazon Elastic Block Store[2]'s and Instance Store's[1].   I
have some specs and are able to run benchmarks on them; but couldn't
guess how many spindles my X% of the N-disk device that corresponds
to.  For another example, some of our salesguys with SSD drives
have 0 spindles on their demo machines.

I'd rather a parameter that expressed things more in terms of
measurable quantities -- perhaps seeks/second?  perhaps
random-access/sequential-access times?



[1] http://www.amazon.com/gp/browse.html?node=201590011
[2] http://www.amazon.com/b/ref=sc_fe_c_0_201590011_1?ie=UTF8&node=689343011&no=201590011&me=A36L942TSJ2AJA

On Mon, 22 Sep 2008, Gregory Stark wrote:

> Hm, I'm disappointed with the 48-drive array here. I wonder why it maxed out
> at only 10x the bandwidth of one drive. I would expect more like 24x or more.

The ZFS RAID-Z implementation doesn't really scale that linearly.  It's 
rather hard to get the full bandwidth out of a X4500 with any single 
process, and I haven't done any filesystem tuning to improve 
things--everything is at the defaults.

> I'm quite surprised Solaris doesn't support posix_fadvise -- perhaps 
> it's in some other version of Solaris?

Both the systems I used were standard Solaris 10 boxes and I'm not aware 
of any changes in this area in the later OpenSolaris releases (which is 
where I'd expect something like this to change first).  The test program I 
tried failed to find #ifdef POSIX_FADV_WILLNEED, and the message I saw 
from you at 
http://archives.postgresql.org/message-id/877imua265.fsf@oxford.xeocode.com 
suggested you didn't find any fadvise either so I didn't look much 
further.

The above is a cue for someone from Sun to chime in on this subject.

> I have an updated patch I'll be sending along shortly. You might want to test
> with that?

Obviously I've got everything setup to test right now, am currently 
analyzing your earlier patch and the sequential scan fork that derived 
from it.  If you've got a later version of the bitmap heap scan one as 
well, I'll replace the one I had been planning to test (your 
bitmap-preread-v9) with that one when it's available.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:

> For example, on our sites hosted with Amazon's compute cloud (a great
> place to host web sites), I know nothing about spindles, but know
> about Amazon Elastic Block Store[2]'s and Instance Store's[1].   I
> have some specs and are able to run benchmarks on them; but couldn't
> guess how many spindles my X% of the N-disk device that corresponds
> to.  

Well I don't see how you're going to guess how much prefetching is optimal for
those environments either... 

> For another example, some of our salesguys with SSD drives
> have 0 spindles on their demo machines.

Sounds to me like you're finding it pretty intuitive. Actually you would want
"1" because it can handle one request at a time. Actually if you have a
multipath array I imagine you would want to think of each interface as a
spindle because that's the bottleneck and you'll want to keep all the
interfaces busy.

> I'd rather a parameter that expressed things more in terms of
> measurable quantities -- perhaps seeks/second?  perhaps
> random-access/sequential-access times?

Well that's precisely what I'm saying. Simon et al want a parameter to control
how much prefetching to do. That's *not* a measurable quantity. I'm suggesting
effective_spindle_count which *is* a measurable quantity even if it might be a
bit harder to measure in some environments than others.

The two other quantities you describe are both currently represented by our
random_page_cost (or random_page_cost/sequential_page_cost). What we're
dealing with now is an entirely orthogonal property of your system: how many
concurrent requests can the system handle.

If you have ten spindles then you really want to send enough requests to
ensure there are ten concurrent requests being processed on ten different
drives (assuming you want each scan to make maximum use of the resources which
is primarily true in DSS but might not be true in OLTP). That's a lot more
than ten requests though because if you sent ten requests many of them would
end up on the same devices.

In theory my logic led me to think for ten drives it would be about 30.
Experiments seem to show it's more like 300-400. That discrepancy might be a
reason to put this debate aside for now anywaysand expose the internal
implementation until we understand better what's going on there.

Ironically I'm pretty happy to lose this argument because EDB is interested in
rolling this into its dynamic tuning module. If there's a consensus -- by my
count three people have spoken up already which is more than usual -- then
I'll gladly concede. Anyone object to going back to preread_pages? Or should
it be prefetch_pages? prefetch_blocks? Something else?

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication
support!

Gregory Stark wrote:
> Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
>> I'd rather a parameter that expressed things more in terms of
>> measurable quantities [...]
> 
> ...What we're
> dealing with now is an entirely orthogonal property of your system: how many
> concurrent requests can the system handle.

Really?  I'd have thought you'd want to give the OS enough guesses
about the future that it's elevator algorithms for the drive heads
don't keep seeking back-and-forth but rather do as much per sweep
across a device that they can.

> Ironically I'm pretty happy to lose this argument because EDB is interested in
> rolling this into its dynamic tuning module. If there's a consensus -- by my
> count three people have spoken up already which is more than usual -- then
> I'll gladly concede. Anyone object to going back to preread_pages? Or should
> it be prefetch_pages? prefetch_blocks? Something else?

Well - as you pointed out, I'm not on their side of the debate either.
I'm not sure what a relevant measurable parameter would be so I'm not
being too helpful in the conversation either.

[resending due to the attachment being too large for the -hackers list --
weren't we going to raise it when we killed -patches?]

Greg Smith <gsmith@gregsmith.com> writes:

> Using the maximum prefetch working set tested, 8192, here's the speedup
> multiplier on this benchmark for both sorted and unsorted requests using a 8GB
> file:
>
> OS        Spindles    Unsorted X    Sorted X
> 1:Linux        1        2.3        2.1
> 2:Linux        1        1.5        1.0
> 3:Solaris    1        2.6        3.0
> 4:Linux        3        6.3        2.8
> 5:Linux (Stark)    3        5.3        3.6
> 6:Linux        10        5.4        4.9
> 7:Solaris*    48        16.9        9.2

Incidentally I've been looking primarily at the sorted numbers because they
parallel bitmap heap scans. (Note that the heap scan is only about half the
i/o of a bitmap index scan + heap scan so even if it's infinitely faster it'll
only halve the time spent in the two nodes.)

Hm, I'm disappointed with the 48-drive array here. I wonder why it maxed out
at only 10x the bandwidth of one drive. I would expect more like 24x or more.
I wonder if Solaris's aio has an internal limit on how many pending i/o
requests it can handle. Perhaps it's a tunable?

Unfortunately I don't see a convenient low-invasive way to integrate aio into
Postgres. posix_fadvise we can just issue the advice and then forget about it.
But aio we would pretty much have to pick a target buffer, pin it, issue the
aio and then remember the pin later when we need to read the buffer. That
would require restructuring the code significantly. I'm quite surprised
Solaris doesn't support posix_fadvise -- perhaps it's in some other version of
Solaris?

Here's a graph of results from this program for various sized arrays on a
single machine:

http://wiki.postgresql.org/images/a/a3/Results.svg

Each colour corresponds to an array of a different number of spindles ranging
from 1 to 15 drives. The X axis is how much prefetching was done and the Y
axis is the bandwidth obtained.

There is a distinct maximum and then dropoff and it would be great to get some
data points for larger arrays to understand where that maximum goes as the
array gets larger.

> Conclusion:  on all the systems I tested on, this approach gave excellent
> results, which makes me feel confident that I should see a corresponding
> speedup on database-level tests that use this same basic technique.  I'm not
> sure whether it might make sense to bundle this test program up somehow so
> others can use it for similar compatibility tests (I'm thinking of something
> similar to contrib/test_fsync), will revisit that after the rest of the review.
>
> Next step:  I've got two data sets (one generated, one real-world sample) that
> should demonstrate a useful heap scan prefetch speedup, and one test program I
> think will demonstrate whether the sequential scan prefetch code works right.
> Now that I've vetted all the hardware/OS combinations I hope I can squeeze that
> in this week, I don't need to test all of them now that I know which are the
> interesting systems.

I have an updated patch I'll be sending along shortly. You might want to test
with that?




--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

Greg Smith napsal(a):
> On Mon, 22 Sep 2008, Gregory Stark wrote:

> 
>> I'm quite surprised Solaris doesn't support posix_fadvise -- perhaps 
>> it's in some other version of Solaris?

Solaris has only fake variant of posix_fadvise. See 
http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/lib/libc/port/gen/posix_fadvise.c

UFS has own optimization. For example if it detects sequential scan then  file cache is limited on 80kB. Or it also has
prefetching.

ZFS has intelligent read patter recognitions algorithms and other 
improvements.
    Zdenek

Greg Smith <gsmith@gregsmith.com> writes:

> On Mon, 22 Sep 2008, Gregory Stark wrote:
>
>> Hm, I'm disappointed with the 48-drive array here. I wonder why it maxed out
>> at only 10x the bandwidth of one drive. I would expect more like 24x or more.
>
> The ZFS RAID-Z implementation doesn't really scale that linearly.  It's rather
> hard to get the full bandwidth out of a X4500 with any single process, and I
> haven't done any filesystem tuning to improve things--everything is at the
> defaults.

Well random access i/o will fall pretty far short of the full bandwidth.
Actually this is a major issue, our sequential_page_cost vs random_page_cost
dichotomy doesn't really work when we're prefetching pages.

In my experiments an array capable of supplying about 1.4GB/s in sequential
i/o could only muster about 40MB/s of random i/o with prefetching and only
about 5MB/s without.

For this machine we would have quite a dilemma setting random_page_cost -- do
we set it to 280 or 35?

Perhaps access paths which expect to be able to prefetch most of their
accesses should use random_page_cost / effective_spindle_count for their i/o
costs?

But then if people don't set random_page_cost high enough they could easily
find themselves with random fetches being costed as less expensive than
sequential fetches. And I have a feeling it'll be a hard sell to get people to
set random_page_cost in the double digits let alone triple digits.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about
EnterpriseDB'sPostgreSQL training!
 

Gregory Stark <stark@enterprisedb.com> writes:
> Perhaps access paths which expect to be able to prefetch most of their
> accesses should use random_page_cost / effective_spindle_count for their i/o
> costs?

> But then if people don't set random_page_cost high enough they could easily
> find themselves with random fetches being costed as less expensive than
> sequential fetches. And I have a feeling it'll be a hard sell to get people to
> set random_page_cost in the double digits let alone triple digits.

Well, we could use something likeMax(random_page_cost / effective_spindle_count, seq_page_cost)
to ensure the result remains somewhat sane.
        regards, tom lane

Greg Smith <gsmith@gregsmith.com> writes:

>> I have an updated patch I'll be sending along shortly. You might want to test
>> with that?
>
> Obviously I've got everything setup to test right now, am currently analyzing
> your earlier patch and the sequential scan fork that derived from it.  If
> you've got a later version of the bitmap heap scan one as well, I'll replace
> the one I had been planning to test (your bitmap-preread-v9) with that one when
> it's available.

Well here you go. It includes:

. Bitmap heap scans (as before)
. Index scans
. Setting POSIX_FADV_SEQUENTIAL for bulk sequential scans
. Improved (ie, debugged) autoconf tests for posix_fadvise
  (and posix_fallocate though I don't have any code using it yet)

The bitmap heap scans are still prefetching the number of buffers I estimate
based on effective_spindle_count according to my magic formula. We've been
discussing throwing that out, I just haven't modified this to do that yet.

Index scans prefetch all pages for matching index tuples on the leaf page when
we do page-at-a-time scans. I haven't bothered doing the gradual ramp-up or
keeping a ring of the optimal size prefetched.

The sequential scan stuff is based on Zoltan's posts but done in a different
way. It passes an i/o access strategy to smgr and fd.c which keeps track of
what the previous strategy was and calls posix_fadvise if it's changed. This
will correctly handle queries which reference the same table twice even if one
reference is a sequential scan and the other is an index lookup.

I have *not* been able to observe any significant effect from
POSIX_FADV_SEQUENTIAL but I'm not sure what circumstances it was a problem. It
sounds like it's a peculiar situation which is not easy to reliably reproduce.



--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

On Tue, 23 Sep 2008, Gregory Stark wrote:

> I have *not* been able to observe any significant effect from
> POSIX_FADV_SEQUENTIAL but I'm not sure what circumstances it was a problem. It
> sounds like it's a peculiar situation which is not easy to reliably reproduce.

Zoltan, Hans-Juergen:  would it be possible for you to try the latest 
bitmap-preread-v18.diff.gz patch Greg Stark just sent over to the list? 
It's at 
http://archives.postgresql.org/message-id/87ljxjudu7.fsf@oxford.xeocode.com 
as well.  That's a refinement of the original strategy you used, and I'd 
be curious to hear whether it still works usefully on the troublesome 
workload you submitted your original patch against.  Since none of the 
rest of us have been successful so far replicating the large speed-up on 
multiple concurrent sequential scans you reported, I think you're the best 
candidate to see if there was any regression because of how the patch was 
refactored.

I'm excited to see index scans in the new patch as well, since I've got 
1TB of test data that gets navigated that way I can test with.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Ron Mayer wrote:
> Even more often on systems I see these days, "spindles"
> is an implementation detail that the DBA has no way to know
> what the correct value is.
> 
> For example, on our sites hosted with Amazon's compute cloud (a great
> place to host web sites), I know nothing about spindles, but know
> about Amazon Elastic Block Store[2]'s and Instance Store's[1].   I
> have some specs and are able to run benchmarks on them; but couldn't
> guess how many spindles my X% of the N-disk device that corresponds
> to.  For another example, some of our salesguys with SSD drives
> have 0 spindles on their demo machines.
> 
> I'd rather a parameter that expressed things more in terms of
> measurable quantities -- perhaps seeks/second?  perhaps
> random-access/sequential-access times?

I assume SAN users might not know the number of spindles either.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Gregory Stark wrote:
> 
> Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> 
> > For example, on our sites hosted with Amazon's compute cloud (a great
> > place to host web sites), I know nothing about spindles, but know
> > about Amazon Elastic Block Store[2]'s and Instance Store's[1].   I
> > have some specs and are able to run benchmarks on them; but couldn't
> > guess how many spindles my X% of the N-disk device that corresponds
> > to.  
> 
> Well I don't see how you're going to guess how much prefetching is optimal for
> those environments either... 
> 
> > For another example, some of our salesguys with SSD drives
> > have 0 spindles on their demo machines.
> 
> Sounds to me like you're finding it pretty intuitive. Actually you would want
> "1" because it can handle one request at a time. Actually if you have a
> multipath array I imagine you would want to think of each interface as a
> spindle because that's the bottleneck and you'll want to keep all the
> interfaces busy.

I assume everyone would want at least one because you would want the
page to be prefetched while you are processing the existing page.  I
think a larger problem is that it is likely the page prefetch will take
longer than processing the existing page, which might mean that you need
to prefetch a few pages ahead to allow read reordering for better
performance.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Ron Mayer wrote:
>> Even more often on systems I see these days, "spindles"
>> is an implementation detail that the DBA has no way to know
>> what the correct value is.
>>
>> For example, on our sites hosted with Amazon's compute cloud (a great
>> place to host web sites), I know nothing about spindles, but know
>> about Amazon Elastic Block Store[2]'s and Instance Store's[1].   I
>> have some specs and are able to run benchmarks on them; but couldn't
>> guess how many spindles my X% of the N-disk device that corresponds
>> to.  For another example, some of our salesguys with SSD drives
>> have 0 spindles on their demo machines.
>>
>> I'd rather a parameter that expressed things more in terms of
>> measurable quantities -- perhaps seeks/second?  perhaps
>> random-access/sequential-access times?
> 
> I assume SAN users might not know the number of spindles either.

Yeah. Nevertheless I like the way effective_spindle_count works, as 
opposed to an unintuitive "number of blocks to prefetch" (assuming the 
formula we use to turn the former into latter works). Perhaps we should 
keep the meaning the same, but call it "effective_io_concurrency"? 
Something that conveys the idea of "how many simultaneous I/O requests 
the I/O subsystem can handle", without referring to any specific 
technology. That concept applies to SANs and RAM drives as well.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

On Wed, 2008-09-24 at 17:42 +0300, Heikki Linnakangas wrote:

> Yeah. Nevertheless I like the way effective_spindle_count works, as 
> opposed to an unintuitive "number of blocks to prefetch" (assuming the 
> formula we use to turn the former into latter works). Perhaps we should 
> keep the meaning the same, but call it "effective_io_concurrency"? 
> Something that conveys the idea of "how many simultaneous I/O requests 
> the I/O subsystem can handle", without referring to any specific 
> technology. That concept applies to SANs and RAM drives as well.

You've spoiled all the fun now with a good suggestion.

I was looking forward to the Jules Verne-like nostalgia of the other
suggestion over the years to come.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support

Simon Riggs <simon@2ndQuadrant.com> writes:

> On Mon, 2008-09-22 at 16:46 +0100, Gregory Stark wrote:
>
>> Simon Riggs <simon@2ndQuadrant.com> writes:
>> 
>> > I'd prefer to set this as a tablespace level storage parameter. 
>> 
>> Sounds, like a good idea, except... what's a tablespace level storage parameter?
>
> A storage parameter, just at tablespace level.
>
> WITH (storage_parameter = value)

I still think this is a good idea but I still think there are unanswered
questions about it. Surely whatever we do with this parameter also holds for
random_page_cost and sequential_page_cost as well? 

Should they remain GUC parameters at all? If so, how would setting any of them
locally interact with the tablespace parameter for tables used in the query? 

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!