>>Shouldn't that data be in the shared buffers if not the OS cache and hence approximately same IO will be required?
>I don't think so as the data in OS cache or PG Shared buffers doesn't have any direct relation, >OS can flush its buffers based on its scheduler algorithm. >Let us try to see by example: >Total RAM - 22G >Database size - 16G >Case -1 (Shared Buffers - 5G) >a. Load all the files in OS buffers. Chances are good that all 16G data will be there in OS >buffers as OS has still 17G of memory available. >b. Try to load all in Shared buffers. Last 5G will be there in shared buffers. >c. Chances are high that remaining 11G buffers access will not lead to IO as they will be in OS >buffers. >Case -2 (Shared Buffers - 10G) >a. Load all the files in OS buffers. In best case OS buffers can contain10-12G data as OS has >12G of memory available. >b. Try to load all in Shared buffers. Last 10G will be there in shared buffers. >c. Now as there is no direct correlation of data between Shared Buffers and OS buffers, so >whenever PG has to access any data > which is not there in Shared Buffers, good chances are there that it can lead to IO. >> Again, the drop in the performance is so severe that it seems worth investigating that further, especially because you can reproduce it reliably. > Yes, I agree that it is worth investigating, but IMO this is a different problem which might >not be addressed with the Patch in discussion. > The 2 reasons I can think for dip in performance when Shared Buffers increase beyond certain > threshhold percentage of RAM are, > a. either the algorithm of Buffer Management has some bottleneck > b. due to the way data is managed in Shared Buffers and OS buffer cache The point I want to tell is explained at below link as well. http://blog.kimiensoftware.com/2011/05/postgresql-vs-oracle-differences-4-sh ared-memory-usage-257 So if above is true, I think the performance will regain if in the test Shared Buffers are set to 16G. I shall once try that setting for test run. With Regards, Amit Kapila.
