Re: Horizontal scalability/sharding

On Tue, Sep 1, 2015 at 10:17 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Sep 1, 2015 at 1:06 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> You're assuming that our primary bottleneck for writes is IO.  It's not
>> at present for most users, and it certainly won't be in the future.  You
>> need to move your thinking on systems resources into the 21st century,
>> instead of solving the resource problems from 15 years ago.
>
> Your experience doesn't match mine.  I find that it's frequently
> impossible to get the system to use all of the available CPU capacity,
> either because you're bottlenecked on locks or because you are
> bottlenecked on the  I/O subsystem, and with the locking improvements
> in newer versions, the former is becoming less and less common.

I think you're both right. I think that we need to fix the buffer
manager, to make its caching algorithm smarter. Since we're mostly
using the filesystem cache, this is particularly important for
PostgreSQL. We need to remember usage information for evicted blocks
for some period of time afterwards. This problem is largely a problem
with Postgres in particular, I suspect.

At the same time, I agree with Josh's assessment that long-term, we
are going to have the biggest problem with memory latency and memory
bandwidth, which are usually considered facets of CPU performance, and
with internal lock contention. Addressing the memory access bottleneck
dovetails with parallelism, in that it must be considered alongside
parallelism. Josh's "21st century" remark seems quite justified to me.
For a further example of this, check out my latest progress with
external sorting, which I plan to post later in the week. I/O isn't
the big problem there at all, and I now think we can make external
sorts close to internal sorts in performance across the board.

I imagine that Josh's experience is based on workloads that mostly fit
in shared_buffers, so I can see why you'd disagree if that was
something you've seen less of. I'll quote Hellerstein and Stonebraker
in 2007 [1]:

"Copying data in memory can be a serious bottleneck. Copies contribute
latency, consume CPU cycles, and can flood the CPU data. This fact is
often a surprise to people who have not operated or implemented a
database system, and assume that main-memory operations are “free”
compared to disk I/O. But in practice, throughput in a well-tuned
transaction processing DBMS is typically not I/O-bound. This is
achieved in high-end installations by purchasing sufficient disks and
RAM so that repeated page requests are absorbed by the buffer pool,
and disk I/Os are shared across the disk arms at a rate that can feed
the data appetite of all the processors in the system."

>> Our real future bottlenecks are:
>>
>> * ability to handle more than a few hundred connections
>> * locking limits on the scalability of writes
>> * ability to manage large RAM and data caches
>
> I do agree that all of those things are problems, FWIW.

These seem like our long term problems, to me.

[1] http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf, page 213
--
Peter Geoghegan