Re: PostgreSQL Read IOPS limit per connection

On Thu, Dec 27, 2018 at 7:29 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
>
> On Thu, Dec 27, 2018 at 08:20:23PM -0500, Jeff Janes wrote:
> > Also, you would want to use the newest version of PostgreSQL, as 9.6
> > doesn't have parallel query, which is much more generally applicable than
> > effective_io_concurrency is.

effective_io_concurrency only applies to certain queries. When it
does apply it can work wonders. See:
https://www.postgresql.org/message-id/CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azsTdQ@mail.gmail.com
for an example of how it can benefit.

parallel query is not going to help single threaded pg_bench results.
you are going to be entirely latency bound (network from bebench to
postgres, then postgres to storage). On my dell crapbox I was getting
2200tps so you have some point of slowness relative to me, probably
not the disk itself.

Geetting faster performance is an age-old problem; you need to
aggregate specific requests into more general ones, move the
controlling logic into the database itself, or use various other
strategies. Lowering latency is a hardware problem and can force
trade-offs (like, don't use a SAN) and has specific boundaries that
are not easy to bust through.

merlin