Re: Allow a per-tablespace effective_io_concurrency setting

On Fri, Sep 4, 2015 at 11:21 AM, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Thu, Sep 3, 2015 at 2:13 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> I find this talk of platters and spindles to be somewhat
>> baroque; for a 200$ part I have to work pretty hard to max out the
>> drive when reading and I'm still not completely sure if it's the drive
>> itself, postgres, cpu, or sata interface bottlenecking me. This will
>> require a rethink of e_i_o configuration; in the old days there were
>> physical limitations of the drives that were in the way regardless of
>> the software stack but we are in a new era, I think. I'm convinced
>> prefetching works and we're going to want to aggressively prefetch
>> anything and everything possible. SSD controllers (at least the intel
>> ones) are very smart.
>
>
> Wouldn't SSDs need much *less* aggressive prefetching? There's still
> latency and there are multiple I/O channels so they will still need
> some. But spinning media gives latencies measured in milliseconds. You
> can process a lot of tuples in milliseconds. If you have a hundred
> spindles you want them all busy doing seeks because in the 5ms it
> takes them to do that you can proess all the results on a single cpu
> and the rest of time is spend waiting.
>
> When your media has latency on the order of microseconds then you only
> need to have a small handful of I/O requests in flight to keep your
> processor busy.

I'm not sure I agree with that. 100 micosecond latency is still a
pretty big deal when memory latency is measured in nanoseconds. This
is why we have pcie storage devices. (if anyone has a fast pice flash
device I'd be interested in seing some e_i_o tests...I bet they'd
still show some impact but it'd be less).

For spinning media, any random i/o workload on large datasets is a
freight train to 100% iowait. As each device has to process each data
set synchronously minus what small gains can be eeked out by
reordering and combining writes. This drives data transfer rates
under a megabyte per second.

Flash devices OTOH are essentially giant raid 0 devices of electronic
media underneath the flash controller. Each device can functionally
do many read operations in parallel so it makes perfect sense that
they perform better when given multiple overlapping requests; unlike
spinning media they can actually make use of that information and
those assumptions are well supported by measurement.

Flash is getting better all the time and storage technologies that
remove its one weakness, random writes, appear to be right around the
corner. The age of storage being a bottleneck for database systems
without massive high cost engineering and optimization has come to an
end. This is good for software developers and especially good for
databases because most of the perceived mythology of databases being
'slow' are in fact storage issues. With that problem gone focus will
return to clean data processing models which is where database systems
excel with their many decades of refinement.

merlin