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

On Tuesday, May 21, 2013 12:36 PM Amit Kapila wrote:
> 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)

On looking more closely at data posted by you, I believe that there is some
problem with data (8
clients @ scale factor 1000 on master) as in all other cases, the data for
scale factor 1000 is better than 3000 except for this case.
So I think no need to run again.

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