Re: PostgreSQL Read IOPS limit per connection

Поиск
Список
Период
Сортировка
От Merlin Moncure
Тема Re: PostgreSQL Read IOPS limit per connection
Дата
Msg-id CAHyXU0ybCOdEFmHxN6WGarRMkws=ACbZHAwu2B3sNHbciDbjCw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: PostgreSQL Read IOPS limit per connection  (Haroldo Kerry <hkerry@callix.com.br>)
Ответы Re: PostgreSQL Read IOPS limit per connection  (Mark Hogg <mark.hogg@2ndquadrant.com>)
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Haroldo Kerry
Дата:
Сообщение: Re: PostgreSQL Read IOPS limit per connection
Следующее
От: Mark Hogg
Дата:
Сообщение: Re: PostgreSQL Read IOPS limit per connection