Horton

Re: Speed up Clog Access by increasing CLOG buffers

Amit Kapila <amit.kapila16@gmail.com>

View thread View on postgr.es
From: Amit Kapila <amit.kapila16@gmail.com>
To: Pavan Deolasee <pavan.deolasee@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>, Robert Haas <robertmhaas@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Andres Freund <andres@anarazel.de>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2016-09-23T13:15:33Z
Lists: pgsql-hackers 
On Fri, Sep 23, 2016 at 6:29 PM, Pavan Deolasee
<pavan.deolasee@gmail.com> wrote:
> On Fri, Sep 23, 2016 at 6:05 PM, Tomas Vondra <tomas.vondra@2ndquadrant.com>
> wrote:
>>
>> On 09/23/2016 05:10 AM, Amit Kapila wrote:
>>>
>>> On Fri, Sep 23, 2016 at 5:14 AM, Tomas Vondra
>>> <tomas.vondra@2ndquadrant.com> wrote:
>>>>
>>>> On 09/21/2016 08:04 AM, Amit Kapila wrote:
>>>>>
>>>>>
>>>>
>>>> (c) Although it's not visible in the results, 4.5.5 almost perfectly
>>>> eliminated the fluctuations in the results. For example when 3.2.80
>>>> produced
>>>> this results (10 runs with the same parameters):
>>>>
>>>>     12118 11610 27939 11771 18065
>>>>     12152 14375 10983 13614 11077
>>>>
>>>> we get this on 4.5.5
>>>>
>>>>     37354 37650 37371 37190 37233
>>>>     38498 37166 36862 37928 38509
>>>>
>>>> Notice how much more even the 4.5.5 results are, compared to 3.2.80.
>>>>
>>>
>>> how long each run was?  Generally, I do half-hour run to get stable
>>> results.
>>>
>>
>> 10 x 5-minute runs for each client count. The full shell script driving
>> the benchmark is here: http://bit.ly/2doY6ID and in short it looks like
>> this:
>>
>>     for r in `seq 1 $runs`; do
>>         for c in 1 8 16 32 64 128 192; do
>>             psql -c checkpoint
>>             pgbench -j 8 -c $c ...
>>         done
>>     done
>
>
>
> I see couple of problems with the tests:
>
> 1. You're running regular pgbench, which also updates the small tables. At
> scale 300 and higher clients, there is going to heavy contention on the
> pgbench_branches table. Why not test with pgbench -N? As far as this patch
> is concerned, we are only interested in seeing contention on
> ClogControlLock. In fact, how about a test which only consumes an XID, but
> does not do any write activity at all? Complete artificial workload, but
> good enough to tell us if and how much the patch helps in the best case. We
> can probably do that with a simple txid_current() call, right?
>

Right, that is why in the initial tests done by Dilip, he has used
Select .. for Update.  I think using txid_current will generate lot of
contention on XidGenLock which will mask the contention around
CLOGControlLock, in-fact we have tried that.


-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Commits

  1. Use group updates when setting transaction status in clog.

  2. Improve 64bit atomics support.

  3. Add ProcArrayGroupUpdate wait event.

  4. Make the different Unix-y semaphore implementations ABI-compatible.

  5. Fix broken ALTER INDEX documentation

  6. Code and docs review for commit 3187d6de0e5a9e805b27c48437897e8c39071d45.

  7. Partition the freelist for shared dynahash tables.

  8. Correct StartupSUBTRANS for page wraparound

  9. Make idle backends exit if the postmaster dies.

  10. contrib/sslinfo: add ssl_extension_info SRF

  11. Reduce ProcArrayLock contention by removing backends in batches.

  12. Fix `make installcheck` for serializable transactions.

  13. Lockless StrategyGetBuffer clock sweep hot path.

  14. Reduce sinval synchronization overhead.