Re: Speed up Clog Access by increasing CLOG buffers
| От | Dilip Kumar |
|---|---|
| Тема | Re: Speed up Clog Access by increasing CLOG buffers |
| Дата | |
| Msg-id | CAFiTN-taV4iVkPHrxg=YCicKjBS6=QZm_cM4hbS_2q2ryLhUUw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Speed up Clog Access by increasing CLOG buffers (Robert Haas <robertmhaas@gmail.com>) |
| Ответы |
Re: Speed up Clog Access by increasing CLOG buffers
(Tomas Vondra <tomas.vondra@2ndquadrant.com>)
Re: Speed up Clog Access by increasing CLOG buffers (Robert Haas <robertmhaas@gmail.com>) |
| Список | pgsql-hackers |
On Thu, Oct 13, 2016 at 12:25 AM, Robert Haas <robertmhaas@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.txt310489 LWLockNamed | CLogControlLock296152 | 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.txt540061 | 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.txt326601 | 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.txt228108 | 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.txt117762 | 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.txt116596 | 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.txt437848 |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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Ashutosh BapatДата:
Сообщение: Re: postgres_fdw : altering foreign table not invalidating prepare statement execution plan.