Re: [WIP PATCH] for Performance Improvement in Buffer Management - Mailing list pgsql-hackers

<div class="WordSection1"><p class="MsoNormal"><span lang="EN" style="font-size:13.0pt;font-family:"Courier
New";color:black">>>Shouldn'tthat data be in the shared buffers if not the OS cache and hence approximately same
IOwill be required?</span><p class="MsoNormal"><span lang="EN" style="font-size:13.0pt;font-family:"Courier
New";color:black"> </span><pclass="MsoNormal"><span lang="EN" style="font-size:13.0pt;font-family:"Courier
New";color:black">>Idon't think so as the data in OS cache or PG Shared buffers doesn't have any direct relation,
>OScan flush its buffers based on its scheduler algorithm.</span><p class="MsoNormal"><span lang="EN"
style="font-size:13.0pt;font-family:"CourierNew";color:black"> </span><p class="MsoNormal"><span lang="EN"
style="font-size:13.0pt;font-family:"CourierNew";color:black">>Let us try to see by example:</span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black">>Total RAM -
22G</span><pclass="MsoNormal"><span lang="EN" style="font-size:13.0pt;font-family:"Courier
New";color:black">>Databasesize - 16G</span><p class="MsoNormal"><span lang="EN"
style="font-size:13.0pt;font-family:"CourierNew";color:black"> </span><p class="MsoNormal"><span lang="EN"
style="font-size:13.0pt;font-family:"CourierNew";color:black">>Case -1 (Shared Buffers - 5G)</span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black">>a. Load all the
filesin OS buffers. Chances are good that all 16G data will be there in OS >buffers as OS has still 17G of memory
available.</span><pclass="MsoNormal"><span lang="EN" style="font-size:13.0pt;font-family:"Courier
New";color:black">>b.Try to load all in Shared buffers. Last 5G will be there in shared buffers.</span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black">>c. Chances are high
thatremaining 11G buffers access will not lead to IO as they will be in OS >buffers.</span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black"> </span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black">>Case -2 (Shared
Buffers- 10G)</span><p class="MsoNormal"><span lang="EN" style="font-size:13.0pt;font-family:"Courier
New";color:black">>a.Load all the files in OS buffers. In best case OS buffers can contain10-12G data as OS has
>12Gof memory available.</span><p class="MsoNormal"><span lang="EN" style="font-size:13.0pt;font-family:"Courier
New";color:black">>b.Try to load all in Shared buffers. Last 10G will be there in shared buffers.</span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black">>c. Now as there is
nodirect correlation of data between Shared Buffers and OS buffers, so >whenever PG has to access any data</span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black">>   which is not
therein Shared Buffers, good chances are there that it can lead to IO.</span><p class="MsoNormal"><span lang="EN"
style="font-size:13.0pt;font-family:"CourierNew";color:black"> </span><p class="MsoNormal"><span lang="EN"
style="font-size:13.0pt;font-family:"CourierNew";color:black"> </span><p class="MsoNormal"><span lang="EN"
style="font-size:13.0pt;font-family:"CourierNew";color:black">>> Again, the drop in the performance is so severe
thatit seems worth investigating that further, especially because you can reproduce it reliably.</span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black"> </span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black">>   Yes, I agree
thatit is worth investigating, but IMO this is a different problem which might >not be addressed with the Patch in
discussion.</span><p class="MsoNormal"><span lang="EN" style="font-size:13.0pt;font-family:"Courier
New";color:black">>   The 2 reasons I can think for dip in performance when Shared Buffers increase beyond certain
>threshhold percentage of RAM are, </span><p class="MsoNormal"><span lang="EN"
style="font-size:13.0pt;font-family:"CourierNew";color:black">> a. either the algorithm of Buffer Management has
somebottleneck</span><p class="MsoNormal"><span lang="EN" style="font-size:13.0pt;font-family:"Courier
New";color:black">>  b. due to the way data is managed in Shared Buffers and OS buffer cache</span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black"> </span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black">The point I want to
tellis explained at below link as well.</span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><a
href="http://blog.kimiensoftware.com/2011/05/postgresql-vs-oracle-differences-4-shared-memory-usage-257">http://blog.kimiensoftware.com/2011/05/postgresql-vs-oracle-differences-4-shared-memory-usage-257</a></span><p
class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Arial","sans-serif""> </span><p class="MsoNormal"><span
lang="EN"style="font-size:13.0pt;font-family:"Courier New";color:black">So if above is true, I think the performance
willregain if in the test Shared Buffers are set to 16G. I shall once try that setting for test run.</span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black"> </span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black">With Regards,</span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black">Amit Kapila.</span><p
class="MsoNormal"><spanlang="EN" style="font-size:13.0pt;font-family:"Courier New";color:black"> </span></div>