On Thu, Oct 13, 2016 at 12:25 AM, Robert Haas <robertmh...@gmail.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
Head:
tps = 21206.108856 (including connections establishing)
tps = 21206.245441 (excluding connections establishing)
[dilip.kumar@cthulhu bin]$ cat 1000_192_ul.txt
310489 LWLockNamed | CLogControlLock
296152 |
35537 Lock | transactionid
15821 LWLockTranche | buffer_mapping
10342 LWLockTranche | buffer_content
8427 LWLockTranche | clog
3961
3165 Lock | extend
2861 Lock | tuple
2781 LWLockNamed | ProcArrayLock
1104 LWLockNamed | XidGenLock
745 LWLockTranche | lock_manager
371 LWLockNamed | CheckpointerCommLock
70 LWLockTranche | wal_insert
5 BufferPin | BufferPin
3 LWLockTranche | proc
Patch:
tps = 28725.038933 (including connections establishing)
tps = 28725.367102 (excluding connections establishing)
[dilip.kumar@cthulhu bin]$ cat 1000_192_ul.txt
540061 |
57810 LWLockNamed | CLogControlLock
36264 LWLockTranche | buffer_mapping
29976 Lock | transactionid
4770 Lock | extend
4735 LWLockTranche | clog
4479 LWLockNamed | ProcArrayLock
4006
3955 LWLockTranche | buffer_content
2505 LWLockTranche | lock_manager
2179 Lock | tuple
1977 LWLockNamed | XidGenLock
905 LWLockNamed | CheckpointerCommLock
222 LWLockTranche | wal_insert
8 LWLockTranche | proc
Test2: number of clients: 96
Head:
tps = 25447.861572 (including connections establishing)
tps = 25448.012739 (excluding connections establishing)
261611 |
69604 LWLockNamed | CLogControlLock
6119 Lock | transactionid
4008
2874 LWLockTranche | buffer_mapping
2578 LWLockTranche | buffer_content
2355 LWLockNamed | ProcArrayLock
1245 Lock | extend
1168 LWLockTranche | clog
232 Lock | tuple
217 LWLockNamed | CheckpointerCommLock
160 LWLockNamed | XidGenLock
158 LWLockTranche | lock_manager
78 LWLockTranche | wal_insert
5 BufferPin | BufferPin
Patch:
tps = 32708.368938 (including connections establishing)
tps = 32708.765989 (excluding connections establishing)
[dilip.kumar@cthulhu bin]$ cat 1000_96_ul.txt
326601 |
7471 LWLockNamed | CLogControlLock
5387 Lock | transactionid
4018
3331 LWLockTranche | buffer_mapping
3144 LWLockNamed | ProcArrayLock
1372 Lock | extend
722 LWLockTranche | buffer_content
393 LWLockNamed | XidGenLock
237 LWLockTranche | lock_manager
234 Lock | tuple
194 LWLockTranche | clog
96 Lock | relation
88 LWLockTranche | wal_insert
34 LWLockNamed | CheckpointerCommLock
Test3: number of clients: 64
Head:
tps = 28264.194438 (including connections establishing)
tps = 28264.336270 (excluding connections establishing)
218264 |
10314 LWLockNamed | CLogControlLock
4019
2067 Lock | transactionid
1950 LWLockTranche | buffer_mapping
1879 LWLockNamed | ProcArrayLock
592 Lock | extend
565 LWLockTranche | buffer_content
222 LWLockNamed | XidGenLock
143 LWLockTranche | clog
131 LWLockNamed | CheckpointerCommLock
63 LWLockTranche | lock_manager
52 Lock | tuple
35 LWLockTranche | wal_insert
Patch:
tps = 27906.376194 (including connections establishing)
tps = 27906.531392 (excluding connections establishing)
[dilip.kumar@cthulhu bin]$ cat 1000_64_ul.txt
228108 |
4039
2294 Lock | transactionid
2116 LWLockTranche | buffer_mapping
1757 LWLockNamed | ProcArrayLock
1553 LWLockNamed | CLogControlLock
800 Lock | extend
403 LWLockTranche | buffer_content
92 LWLockNamed | XidGenLock
74 LWLockTranche | lock_manager
42 Lock | tuple
35 LWLockTranche | wal_insert
34 LWLockTranche | clog
14 LWLockNamed | CheckpointerCommLock
Test4: number of clients: 32
Head:
tps = 27587.999912 (including connections establishing)
tps = 27588.119611 (excluding connections establishing)
[dilip.kumar@cthulhu bin]$ cat 1000_32_ul.txt
117762 |
4031
614 LWLockNamed | ProcArrayLock
379 LWLockNamed | CLogControlLock
344 Lock | transactionid
183 Lock | extend
102 LWLockTranche | buffer_mapping
71 LWLockTranche | buffer_content
39 LWLockNamed | XidGenLock
25 LWLockTranche | lock_manager
3 LWLockTranche | wal_insert
3 LWLockTranche | clog
2 LWLockNamed | CheckpointerCommLock
2 Lock | tuple
Patch:
tps = 28291.428848 (including connections establishing)
tps = 28291.586435 (excluding connections establishing)
[dilip.kumar@cthulhu bin]$ cat 1000_32_ul.txt
116596 |
4041
757 LWLockNamed | ProcArrayLock
407 LWLockNamed | CLogControlLock
358 Lock | transactionid
183 Lock | extend
142 LWLockTranche | buffer_mapping
77 LWLockTranche | buffer_content
68 LWLockNamed | XidGenLock
35 LWLockTranche | lock_manager
15 LWLockTranche | wal_insert
7 LWLockTranche | clog
7 Lock | tuple
4 LWLockNamed | CheckpointerCommLock
1 Tuples only is on.
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.kumar@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
