[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: PostgreSQL Read IOPS limit per connection
From: Mark Hogg <mark.hogg () 2ndquadrant ! com>
Date: 2019-01-10 2:49:00
Message-ID: CAAw7-4bRikBKVPsJiAstqss3q8jtu+kZrAe65EHcFwj7GPKHzQ () mail ! gmail ! com
[Download RAW message or body]
Hello,
I am happy to hear that you have received all the help.
Please feel free to contact us for professional assistance any time you may
need in the future.
Most Welcome!
Regards,
Mark Avinash Hogg
Director of Business Development
2ndQuadrant
+1(647) 770 9821 Cell
www.2ndquadrant.com
mark.hogg@2ndquadrant.com
On Wed, 9 Jan 2019 at 19:20, Merlin Moncure (via Accelo) <mmoncure@gmail.com>
wrote:
> 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
>
>>
[Attachment #3 (text/html)]
<div dir="ltr">Hello,<div><br></div><div>I am happy to hear that you have received \
all the help.</div><div><br></div><div>Please feel free to contact us for \
professional assistance any time you may need in the \
future.</div><div><br></div><div>Most Welcome!</div><div><div><div dir="ltr" \
class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div \
dir="ltr"><div><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div> \
<br></div><div>
<p class="MsoNormal">Regards,<span></span></p><p class="MsoNormal"><br></p>
<p class="MsoNormal">Mark Avinash Hogg</p><p class="MsoNormal"><span></span></p>
<p class="MsoNormal">Director of Business Development<span></span></p>
<p class="MsoNormal">2ndQuadrant<span></span></p>
<p class="MsoNormal">+1(647) 770 9821 Cell<span></span></p>
<p class="MsoNormal"><a href="http://www.2ndquadrant.com" \
target="_blank">www.2ndquadrant.com</a><span></span></p>
<p class="MsoNormal"><a href="mailto:mark.hogg@2ndquadrant.com" \
target="_blank">mark.hogg@2ndquadrant.com<br></a><span></span></p></div></div></div></div></div></div></div></div></div></div></div><br></div></div><br><div \
class="gmail_quote"><div dir="ltr">On Wed, 9 Jan 2019 at 19:20, Merlin Moncure (via \
Accelo) <<a href="mailto:mmoncure@gmail.com">mmoncure@gmail.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
dir="ltr">On Wed, Jan 9, 2019 at 3:52 PM Haroldo Kerry <<a \
href="mailto:hkerry@callix.com.br" target="_blank">hkerry@callix.com.br</a>> \
wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr">@Justin
@Merlin @ Jeff, <div>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.</div><div>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.</div><div>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. <br></div><div>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.</div></div></blockquote><div><br></div><div>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.</div><div><br></div><div>merlin</div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr" \
class="gmail-m_1229724669409159369gmail-m_3859555932449902729gmail_signature"></div> \
</blockquote></div></div> <img \
src="http://click.accelo.com/wf/open?upn=RponfwD6SWCG-2FiVEIcoB4Krk4PQ4MdAkVTpxnkpaPlh \
60s5IH6alVmNIemsbMAYTdE9xWTqNsPz7Z94qnaXMAiDFG-2FREVBXzX3UEQK-2BZM3NNCX8StR1dX-2FXW1dn \
7a0qeWlzYKZowKUUiQLBZ0uWbTUNhNGsK-2FVEMuxtEIa5dXAn-2Frvl61hj-2B0xQ7pD1Q9sADjTG37FfHdZP \
OnIPa9H0nqpkfx1R-2Bxzsxba4XaoU2Bt6c6hr7XHEtAewi7bv5dS5-2BcamDyAaaFqq8wxjW5mBw1sDzn6TlQsINsmHihGwLmFOhrFzSd6mdxGwMJ-2F0qLI-2Fc" \
alt="" width="1" height="1" border="0" style="height: 1px; width: 1px; border-width: \
0px; margin: 0px; padding: 0px;"> </blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic