Quick Links

Re: Speed up Clog Access by increasing CLOG buffers

From:	Dilip Kumar <dilipbalaut(at)gmail(dot)com>
To:	Robert Haas <robertmhaas(at)gmail(dot)com>
Cc:	Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject:	Re: Speed up Clog Access by increasing CLOG buffers
Date:	2016-10-20 07:36:00
Message-ID:	CAFiTN-taV4iVkPHrxg=YCicKjBS6=QZm_cM4hbS_2q2ryLhUUw@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-hackers

On Thu, Oct 13, 2016 at 12:25 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I agree with these conclusions. I had a chance to talk with Andres
> this morning at Postgres Vision and based on that conversation I'd
> like to suggest a couple of additional tests:
>
> 1. Repeat this test on x86. In particular, I think you should test on
> the EnterpriseDB server cthulhu, which is an 8-socket x86 server.

I have done my test on cthulhu, basic difference is that In POWER we
saw ClogControlLock on top at 96 and more client with 300 scale
factor. But, on cthulhu at 300 scale factor transactionid lock is
always on top. So I repeated my test with 1000 scale factor as well on
cthulhu.

All configuration is same as my last test.

Test with 1000 scale factor
-------------------------------------

Test1: number of clients: 192

Test2: number of clients: 96

Test3: number of clients: 64

Head:

tps = 28264.194438 (including connections establishing)
tps = 28264.336270 (excluding connections establishing)

Test4: number of clients: 32

Summary:
- At 96 and more clients count we can see ClogControlLock at the top.
- With patch contention on ClogControlLock is reduced significantly.
I think these behaviours are same as we saw on power.

With 300 scale factor:
- Contention on ClogControlLock is significant only at 192 client
(still transaction id lock is on top), Which is completely removed
with group lock patch.

For 300 scale factor, I am posting data only at 192 client count (If
anyone interested in other data I can post).

Head:
scaling factor: 300
query mode: prepared
number of clients: 192
number of threads: 192
duration: 1800 s
number of transactions actually processed: 65930726
latency average: 5.242 ms
tps = 36621.827041 (including connections establishing)
tps = 36622.064081 (excluding connections establishing)
[dilip(dot)kumar(at)cthulhu bin]$ cat 300_192_ul.txt
437848 |
118966 Lock | transactionid
88869 LWLockNamed | CLogControlLock
18558 Lock | tuple
6183 LWLockTranche | buffer_content
5664 LWLockTranche | lock_manager
3995 LWLockNamed | ProcArrayLock
3646
1748 Lock | extend
1635 LWLockNamed | XidGenLock
401 LWLockTranche | wal_insert
33 BufferPin | BufferPin
5 LWLockTranche | proc
3 LWLockTranche | buffer_mapping

Patch:
scaling factor: 300
query mode: prepared
number of clients: 192
number of threads: 192
duration: 1800 s
number of transactions actually processed: 82616270
latency average: 4.183 ms
tps = 45894.737813 (including connections establishing)
tps = 45894.995634 (excluding connections establishing)
120372 Lock | transactionid
16346 Lock | tuple
7489 LWLockTranche | lock_manager
4514 LWLockNamed | ProcArrayLock
3632
3310 LWLockNamed | CLogControlLock
2287 LWLockNamed | XidGenLock
2271 Lock | extend
709 LWLockTranche | buffer_content
490 LWLockTranche | wal_insert
30 BufferPin | BufferPin
10 LWLockTranche | proc
6 LWLockTranche | buffer_mapping

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

In response to

Re: Speed up Clog Access by increasing CLOG buffers at 2016-10-12 18:55:26 from Robert Haas

Responses

Re: Speed up Clog Access by increasing CLOG buffers at 2016-10-20 15:33:21 from Tomas Vondra
Re: Speed up Clog Access by increasing CLOG buffers at 2016-10-20 15:45:06 from Robert Haas

Browse pgsql-hackers by date

	From	Date	Subject
Next Message	Amit Kapila	2016-10-20 07:42:11	Re: Gather Merge
Previous Message	Ashutosh Bapat	2016-10-20 07:27:30	Re: postgres_fdw : altering foreign table not invalidating prepare statement execution plan.