Re: PostgreSQL Read IOPS limit per connection

On Wed, Jan 9, 2019 at 3:52 PM Haroldo Kerry <hkerry(at)callix(dot)com(dot)br> wrote:

> @Justin @Merlin @ Jeff,
> Thanks so much for your time and insights, they improved our understanding
> of the underpinnings of PostgreSQL and allowed us to deal the issues we
> were facing.
> Using parallel query on our PG 9.6 improved a lot the query performance -
> it turns out that a lot of our real world queries could benefit of parallel
> query, we saw about 4x improvements after turning it on, and now we see
> much higher storage IOPS thanks to the multiple workers.
> On our tests effective_io_concurrency did not show such a large effect as
> the link you sent, I'll have a new look at it, maybe we are doing something
> wrong or the fact that the SSDs are on the SAN and not local affects the
> results.
> On the process we also learned that changing the default Linux I/O
> scheduler from CFQ to Deadline worked wonders for our Dell SC2020 SAN
> Storage setup, we used to see latency peaks of 6,000 milliseconds on busy
> periods (yes, 6 seconds), we now see 80 milliseconds, an almost 100 fold
> improvement.
>

The links sent was using a contrived query to force a type of scan that
benefits from that kind of query; it's a very situational benefit. It
would be interesting if you couldn't reproduce using the same mechanic.

merlin

>