Re: User concurrency thresholding: where do I look?

["Jignesh K. Shah" <J.K.Shah@xxxxxxx>]

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster