Re: PostgreSQL Read IOPS limit per connection - Mailing list pgsql-performance

On Wed, Jan 9, 2019 at 3:52 PM Haroldo Kerry <hkerry@callix.com.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