Re: Move unused buffers to freelist - Mailing list pgsql-hackers

On Monday, May 20, 2013 6:54 PM Robert Haas wrote:
> On Thu, May 16, 2013 at 10:18 AM, Amit Kapila <amit.kapila@huawei.com>
> wrote:
> > Further Performance Data:
> >
> > Below data is for average 3 runs of 20 minutes
> >
> > Scale Factor   - 1200
> > Shared Buffers - 7G
> 
> These results are good but I don't get similar results in my own
> testing.  

Thanks for running detailed tests

> I ran pgbench tests at a variety of client counts and scale
> factors, using 30-minute test runs and the following non-default
> configuration parameters.
> 
> shared_buffers = 8GB
> maintenance_work_mem = 1GB
> synchronous_commit = off
> checkpoint_segments = 300
> checkpoint_timeout = 15min
> checkpoint_completion_target = 0.9
> log_line_prefix = '%t [%p] '
> 
> Here are the results.  The first field in each line is the number of
> clients. The second number is the scale factor.  The numbers after
> "master" and "patched" are the median of three runs.
> 
> 01 100 master 1433.297699 patched 1420.306088
> 01 300 master 1371.286876 patched 1368.910732
> 01 1000 master 1056.891901 patched 1067.341658
> 01 3000 master 637.312651 patched 685.205011
> 08 100 master 10575.017704 patched 11456.043638
> 08 300 master 9262.601107 patched 9120.925071
> 08 1000 master 1721.807658 patched 1800.733257
> 08 3000 master 819.694049 patched 854.333830
> 32 100 master 26981.677368 patched 27024.507600
> 32 300 master 14554.870871 patched 14778.285400
> 32 1000 master 1941.733251 patched 1990.248137
> 32 3000 master 846.654654 patched 892.554222


Is the above test for tpc-b?
In the above tests, there is performance increase from 1~8% and decrease
from 0.2~1.5%

> And here's the same results for 5-minute, read-only tests:
> 
> 01 100 master 9361.073952 patched 9049.553997
> 01 300 master 8640.235680 patched 8646.590739
> 01 1000 master 8339.364026 patched 8342.799468
> 01 3000 master 7968.428287 patched 7882.121547
> 08 100 master 71311.491773 patched 71812.899492
> 08 300 master 69238.839225 patched 70063.632081
> 08 1000 master 34794.778567 patched 65998.468775
> 08 3000 master 60834.509571 patched 61165.998080
> 32 100 master 203168.264456 patched 205258.283852
> 32 300 master 199137.276025 patched 200391.633074
> 32 1000 master 177996.853496 patched 176365.732087
> 32 3000 master 149891.147442 patched 148683.269107
> 
> Something appears to have screwed up my results for 8 clients @ scale
> factor 300 on master, 
 Do you want to say the reading of 1000 scale factor? 
>but overall, on both the read-only and
> read-write tests, I'm not seeing anything that resembles the big gains
> you reported.

I have not generated numbers for read-write tests, I will check that once.
For read-only tests, the performance increase is minor and different from
what I saw. 
Few points which I could think of for difference in data:

1. In my test's I always observed best data when number of clients/threads
are equal to number of cores which in your case should be at 16.
2. I think for scale factor 100 and 300, there should not be much
performance increase, as for them they should mostly get buffer from
freelist inspite of even bgwriter adds to freelist or not. 
3. In my tests variance is for shared buffers, database size is always less
than RAM (Scale Factor -1200, approx db size 16~17GB, RAM -24 GB), but due
to variance in shared buffers, it can lead to I/O. 
4. Each run is of 20 minutes, not sure if this has any difference.
> Tests were run on a 16-core, 64-hwthread PPC64 machine provided to the
> PostgreSQL community courtesy of IBM.  Fedora 16, Linux kernel 3.2.6.

To think about the difference in your and my runs, could you please tell me
about below points
1. What is RAM in machine.
2. Are number of threads equal to number of clients.
3. Before starting tests I have always done pre-warming of buffers (used
pg_prewarm written by you last year), is it same for above read-only tests.
4. Can you please once again run only the test where you saw variation(8
clients @ scale> factor 1000 on master), because I have also seen that
performance difference is very good for certain      configurations(Scale Factor, RAM, Shared Buffers)

Apart from above, I had one more observation during my investigation to find
why in some cases, there is a small dip:
1. Many times, it finds the buffer in free list is not usable, means it's
refcount or usage count is not zero, due to which it had to spend more time
under BufFreelistLock.  I had not any further experiments related to this finding like if it
really adds any overhead.

Currently I am trying to find reasons for small dip of performance and see
if I could do something to avoid it. Also I will run tests with various
configurations.

Any other suggestions?

With Regards,
Amit Kapila.