Re: User concurrency thresholding: where do I look? - Mailing list pgsql-performance

With CLOG 16 the drp[s comes at about 1150 users with the following lock
stats
bash-3.00# ./4_lwlock_waits.d 16404

             Lock Id            Mode           Count
       ProcArrayLock          Shared               2
          XidGenLock       Exclusive               2
          XidGenLock          Shared               4
       WALInsertLock       Exclusive               7
     CLogControlLock          Shared               8
        WALWriteLock       Exclusive              46
       ProcArrayLock       Exclusive              64
     CLogControlLock       Exclusive             263

             Lock Id            Mode   Combined Time (ns)
          XidGenLock       Exclusive               528300
       ProcArrayLock          Shared               968800
       WALInsertLock       Exclusive              4090900
          XidGenLock          Shared             73987600
        WALWriteLock       Exclusive             86200700
       ProcArrayLock       Exclusive            130756000
     CLogControlLock          Shared            240471000
     CLogControlLock       Exclusive           4115158500

So I think 32  is a better option for CLogs before ProcArrayLock becomes
the bottleneck.

Though I havent seen what we can do with ProcArrayLock problem.


Regards,
Jignesh



Jignesh K. Shah wrote:
> Using CLOG Buffers 32 and the commit sibling check patch I still see a
> drop at 1200-1300 users..
>
>
>
> bash-3.00# ./4_lwlock_waits.d 18250
>
>             Lock Id            Mode           Count
>          XidGenLock          Shared               1
>     CLogControlLock          Shared               2
>       ProcArrayLock          Shared               2
>          XidGenLock       Exclusive               4
>     CLogControlLock       Exclusive              15
>       WALInsertLock       Exclusive              18
>        WALWriteLock       Exclusive              38
>       ProcArrayLock       Exclusive              77
>
>             Lock Id            Mode   Combined Time (ns)
>          XidGenLock          Shared                88700
>       WALInsertLock       Exclusive             69556000
>       ProcArrayLock          Shared             95656800
>          XidGenLock       Exclusive            139634100
>     CLogControlLock       Exclusive            148822200
>     CLogControlLock          Shared            161630000
>        WALWriteLock       Exclusive            332781800
>       ProcArrayLock       Exclusive           5688265500
>
> bash-3.00# ./4_lwlock_waits.d 18599
>
>             Lock Id            Mode           Count
>       ProcArrayLock          Shared               2
>          XidGenLock       Exclusive               3
>          XidGenLock          Shared               4
>     CLogControlLock          Shared               5
>       WALInsertLock       Exclusive              10
>     CLogControlLock       Exclusive              21
>        WALWriteLock       Exclusive              28
>       ProcArrayLock       Exclusive              54
>
>             Lock Id            Mode   Combined Time (ns)
>          XidGenLock       Exclusive              5688800
>       WALInsertLock       Exclusive             11424700
>     CLogControlLock          Shared             55589100
>       ProcArrayLock          Shared            135220400
>        WALWriteLock       Exclusive            177906900
>          XidGenLock          Shared            524146500
>     CLogControlLock       Exclusive            524563900
>       ProcArrayLock       Exclusive           5828744500
>
> bash-3.00#
> bash-3.00# ./6_lwlock_stack.d 4 18599
>
>             Lock Id            Mode           Count
>       ProcArrayLock          Shared               1
>       ProcArrayLock       Exclusive              52
>
>             Lock Id            Mode   Combined Time (ns)
>       ProcArrayLock          Shared             41428300
>       ProcArrayLock       Exclusive           3858386500
>
>             Lock Id   Combined Time (ns)
>
>
>              postgres`LWLockAcquire+0x1f0
>              postgres`GetSnapshotData+0x120
>              postgres`GetTransactionSnapshot+0x80
>              postgres`PortalStart+0x198
>              postgres`exec_bind_message+0x84c
>              postgres`PostgresMain+0x17f8
>              postgres`BackendRun+0x2f8
>              postgres`ServerLoop+0x680
>              postgres`PostmasterMain+0xda8
>              postgres`main+0x3d0
>              postgres`_start+0x17c
>  Shared                                                     41428300
>
>              postgres`LWLockAcquire+0x1f0
>              postgres`CommitTransaction+0x104
>              postgres`CommitTransactionCommand+0xbc
>              postgres`finish_xact_command+0x78
>              postgres`exec_execute_message+0x42c
>              postgres`PostgresMain+0x1838
>              postgres`BackendRun+0x2f8
>              postgres`ServerLoop+0x680
>              postgres`PostmasterMain+0xda8
>              postgres`main+0x3d0
>              postgres`_start+0x17c
>  Exclusive                                                3858386500
>
>
> -Jignesh
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings