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

On Monday, October 22, 2012 11:21 PM Amit kapila wrote
On Sunday, October 21, 2012 1:29 PM Amit kapila wrote:
On Saturday, October 20, 2012 11:03 PM Jeff Janes wrote:
On Fri, Sep 7, 2012 at 6:14 AM, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:

>>>>> The results for the updated code is attached with this mail.
>>>>> The scenario is same as in original mail.

>
>>>> The data for shared_buffers = 7GB is attached with this mail. I have also attached scripts used to take this data.

>>> Is this result reproducible? Did you monitor IO (with something like
>>>vmstat) to make sure there was no IO going on during the runs?

>> Yes, I have reproduced it 2 times. However I shall reproduce once more and use vmstat as well.
>> I have not observed with vmstat but it is observable in the data.
>> When I have kept shared buffers = 5G, the tps is more and when I increased it to 7G, the tps is reduced which shows there is some I/O started happening.
>> When I increased to 10G, the tps reduced drastically which shows there is lot of I/O. Tommorow I will post 10G shared buffers data as well.

> Today again I have again collected the data for configuration Shared_buffers = 7G along with vmstat.
> The data and vmstat information (bi) are attached with this mail. It is observed from vmstat info that I/O is happening for both cases, however after running for
> long time, the I/O is also comparatively less with new patch.

Please find the data for shared buffers = 5G and 10G attached with this mail.
Following is consolidated data for avg. of multiple runs:

-Patch- -tps(at)-c8- -tps(at)-c16- -tps(at)-c32- -tps(at)-c64- -tps(at)-c100-
head,-sb-5G 59731 59185 56282 30068 12608
head+patch,-sb-5G 59177 59957 57831 47986 21325

head,-sb-7G 5866 6319 6604 5841
head+patch,-sb-7G 15939 40501 38199 18025

head,-sb-10G 2079 2824 3217 3206 2657
head+patch,-sb-10G 2044 2706 3012 2967 2515

Script for collecting performance data are also attached with this mail:

# $1 = Initialize pgbench
# $2 = Scale Factor
# $3 = No Of Clients
# $4 = No Of pgbench Threads
# $5 = Execution time in seconds
# $6 = Shared Buffers
# $7 = number of sample runs
# $8 = Drop the tables

Eg: taking 16GB database & 5GB shared buffers.

./run_reading.sh 1 1200 8 8 1200 5GB 4 0
./run_reading.sh 0 1200 16 16 1200 5GB 4 0
./run_reading.sh 0 1200 32 32 1200 5GB 4 0
./run_reading.sh 0 1200 64 64 1200 5GB 4 0

Let me know your suggestions, how can we proceed to ensure whether it can be win or loss to have such a patch.

With Regards,
Amit Kapila.