Re: [WIP PATCH] for Performance Improvement in Buffer Management

>>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-shared-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.