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