Skip site navigation (1) Skip section navigation (2)

Re: User concurrency thresholding: where do I look?

From: "Jignesh K(dot) Shah" <J(dot)K(dot)Shah(at)Sun(dot)COM>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-performance(at)postgresql(dot)org, Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Subject: Re: User concurrency thresholding: where do I look?
Date: 2007-07-25 22:58:53
Message-ID: 46A7D5AD.9000706@sun.com (view raw or flat)
Thread:
Lists: pgsql-performance
Here is how I got the numbers..
I had about 1600 users login into postgresql. Then started the run with 
500 users and using DTrace I started tracking Postgresql  Locking "as 
viewed from one user/connection". Echo statements indicate how many 
users were active at that point and how was throughput performing. All 
IO is done on /tmp which means on a RAM disk.

bash-3.00# echo 500 users - baseline number
500 users
bash-3.00# ./3_lwlock_acquires.d 19178

             Lock Id            Mode           Count
    FirstLockMgrLock       Exclusive               1
    RelCacheInitLock       Exclusive               2
          SInvalLock       Exclusive               2
       WALInsertLock       Exclusive              10
      BufMappingLock       Exclusive              12
      CheckpointLock          Shared              29
 CheckpointStartLock          Shared              29
          OidGenLock       Exclusive              29
          XidGenLock       Exclusive              29
    FirstLockMgrLock          Shared              33
 CheckpointStartLock       Exclusive              78
       FreeSpaceLock       Exclusive             114
          OidGenLock          Shared             126
          XidGenLock          Shared             152
       ProcArrayLock          Shared             482

             Lock Id   Combined Time (ns)
          SInvalLock                29800
    RelCacheInitLock                30300
      BufMappingLock               168800
    FirstLockMgrLock               414300
       FreeSpaceLock              1281700
       ProcArrayLock              7869900
       WALInsertLock             11113200
 CheckpointStartLock             13494700
          OidGenLock             25719100
          XidGenLock             26443300
      CheckpointLock            194267800

bash-3.00# echo 600 users - Throughput rising
600 users
bash-3.00# ./3_lwlock_acquires.d 19178

             Lock Id            Mode           Count
    RelCacheInitLock       Exclusive               1
          SInvalLock       Exclusive               1
      BufMappingLock       Exclusive               2
     CLogControlLock       Exclusive               2
       WALInsertLock       Exclusive              11
    FirstLockMgrLock          Shared              20
      CheckpointLock          Shared              24
 CheckpointStartLock          Shared              24
          OidGenLock       Exclusive              24
          XidGenLock       Exclusive              24
 CheckpointStartLock       Exclusive              72
       FreeSpaceLock       Exclusive             102
          OidGenLock          Shared             106
          XidGenLock          Shared             128
       ProcArrayLock          Shared             394

             Lock Id   Combined Time (ns)
          SInvalLock                15600
    RelCacheInitLock                15700
      BufMappingLock                31000
     CLogControlLock                41500
    FirstLockMgrLock               289000
       FreeSpaceLock              3045400
 CheckpointStartLock              7371800
       WALInsertLock              9383200
       ProcArrayLock             10457900
          OidGenLock             20005900
          XidGenLock             20331500
      CheckpointLock            187067900

bash-3.00# echo 700 users  - Throughput rising
700 users
bash-3.00# ./3_lwlock_acquires.d 19178

             Lock Id            Mode           Count
    RelCacheInitLock       Exclusive               1
          SInvalLock       Exclusive               1
      BufMappingLock       Exclusive               2
       WALInsertLock       Exclusive              17
      CheckpointLock          Shared              33
 CheckpointStartLock          Shared              33
          OidGenLock       Exclusive              33
          XidGenLock       Exclusive              33
    FirstLockMgrLock          Shared              81
 CheckpointStartLock       Exclusive              87
       FreeSpaceLock       Exclusive             124
          OidGenLock          Shared             125
          XidGenLock          Shared             150
       ProcArrayLock          Shared             500

             Lock Id   Combined Time (ns)
    RelCacheInitLock                15100
          SInvalLock                15400
      BufMappingLock                47400
    FirstLockMgrLock              3021000
       FreeSpaceLock              3794300
       WALInsertLock              7567300
          XidGenLock             18427400
       ProcArrayLock             20884000
 CheckpointStartLock             24084900
          OidGenLock             26399500
      CheckpointLock            256549800

bash-3.00# echo 800 users  - Throughput rising
800 users
bash-3.00# ./3_lwlock_acquires.d 19178

             Lock Id            Mode           Count
      BufMappingLock       Exclusive               1
    RelCacheInitLock       Exclusive               1
          SInvalLock       Exclusive               1
        WALWriteLock       Exclusive               1
       WALInsertLock       Exclusive              11
      CheckpointLock          Shared              27
 CheckpointStartLock          Shared              27
          OidGenLock       Exclusive              27
          XidGenLock       Exclusive              27
    FirstLockMgrLock          Shared              32
 CheckpointStartLock       Exclusive              73
       FreeSpaceLock       Exclusive             110
          OidGenLock          Shared             118
          XidGenLock          Shared             140
       ProcArrayLock          Shared             442

             Lock Id   Combined Time (ns)
        WALWriteLock                13900
          SInvalLock                15000
    RelCacheInitLock                15500
      BufMappingLock                18600
    FirstLockMgrLock               391100
       WALInsertLock              3953700
       FreeSpaceLock              4801300
 CheckpointStartLock             13131800
       ProcArrayLock             14480500
          OidGenLock             17736500
          XidGenLock             21723100
      CheckpointLock            206423500

bash-3.00# echo 850 users - SLIGHT DROP in throughput
850 users
bash-3.00# ./3_lwlock_acquires.d 19178

             Lock Id            Mode           Count
    FirstLockMgrLock       Exclusive               1
    RelCacheInitLock       Exclusive               1
          SInvalLock       Exclusive               1
        WALWriteLock       Exclusive               1
      BufMappingLock       Exclusive               3
       WALInsertLock       Exclusive               7
      CheckpointLock          Shared              39
 CheckpointStartLock          Shared              39
          OidGenLock       Exclusive              39
          XidGenLock       Exclusive              39
    FirstLockMgrLock          Shared              47
 CheckpointStartLock       Exclusive             113
       FreeSpaceLock       Exclusive             152
          OidGenLock          Shared             162
          XidGenLock          Shared             194
       ProcArrayLock          Shared             621

             Lock Id   Combined Time (ns)
        WALWriteLock                14200
    RelCacheInitLock                15100
          SInvalLock                15600
      BufMappingLock                64100
       WALInsertLock              2073200
    FirstLockMgrLock              3040300
       FreeSpaceLock              7329500
          OidGenLock             21619100
 CheckpointStartLock             23261300
       ProcArrayLock             23917500
          XidGenLock             24873100
      CheckpointLock            309221200

bash-3.00# echo 900 users - ANOTHER SLIGHT DROP IN THROUGPUT
900 users
bash-3.00# ./3_lwlock_acquires.d 19178

             Lock Id            Mode           Count
        WALWriteLock       Exclusive               1
       WALInsertLock       Exclusive               7
 CheckpointStartLock          Shared              13
          OidGenLock       Exclusive              13
      CheckpointLock          Shared              14
          XidGenLock       Exclusive              14
    FirstLockMgrLock          Shared              15
       FreeSpaceLock       Exclusive              51
          OidGenLock          Shared              51
          XidGenLock          Shared              62
 CheckpointStartLock       Exclusive             170
       ProcArrayLock          Shared             202

             Lock Id   Combined Time (ns)
        WALWriteLock                16800
    FirstLockMgrLock               170300
       FreeSpaceLock               601500
       ProcArrayLock              3971300
       WALInsertLock              7757200
          OidGenLock              8261900
          XidGenLock             18450900
 CheckpointStartLock             39155100
      CheckpointLock            143751500

bash-3.00# echo 950 users  - BIG DROP IN THROUGHPUT
950 users
bash-3.00# ./3_lwlock_acquires.d 19178

             Lock Id            Mode           Count
       WALInsertLock       Exclusive               3
    FirstLockMgrLock          Shared               4
      CheckpointLock          Shared               7
 CheckpointStartLock          Shared               7
          OidGenLock       Exclusive               7
          XidGenLock       Exclusive               7
       FreeSpaceLock       Exclusive              29
          OidGenLock          Shared              30
          XidGenLock          Shared              36
       ProcArrayLock          Shared             115
 CheckpointStartLock       Exclusive             134

             Lock Id   Combined Time (ns)
    FirstLockMgrLock                64400
       FreeSpaceLock               342300
       WALInsertLock              1759600
          OidGenLock              4276900
       ProcArrayLock              6234300
          XidGenLock              6865000
 CheckpointStartLock             37590800
      CheckpointLock             58994300

bash-3.00# echo 1000 users - STEADY AT PREVIOUS LOW VALUE
1000 users
bash-3.00# ./3_lwlock_acquires.d 19178

             Lock Id            Mode           Count
      BufMappingLock       Exclusive               1
    RelCacheInitLock       Exclusive               1
          SInvalLock       Exclusive               1
       WALInsertLock       Exclusive               3
      CheckpointLock          Shared               9
 CheckpointStartLock          Shared               9
          OidGenLock       Exclusive               9
          XidGenLock       Exclusive               9
    FirstLockMgrLock          Shared              14
       FreeSpaceLock       Exclusive              33
          OidGenLock          Shared              37
          XidGenLock          Shared              44
 CheckpointStartLock       Exclusive             122
       ProcArrayLock          Shared             145

             Lock Id   Combined Time (ns)
    RelCacheInitLock                14300
          SInvalLock                15600
      BufMappingLock                21400
    FirstLockMgrLock               184000
       FreeSpaceLock               366200
       WALInsertLock              1769500
       ProcArrayLock              5076500
          XidGenLock              5898400
          OidGenLock              9244800
 CheckpointStartLock             31077500
      CheckpointLock             91861900

bash-3.00# echo 1050 users - SMALL  INCREASE
1050 users
bash-3.00# ./3_lwlock_acquires.d 19178

             Lock Id            Mode           Count
      BufMappingLock       Exclusive               2
       WALInsertLock       Exclusive               9
      CheckpointLock          Shared              24
          XidGenLock       Exclusive              24
 CheckpointStartLock          Shared              25
          OidGenLock       Exclusive              25
    FirstLockMgrLock          Shared              30
       FreeSpaceLock       Exclusive             100
          OidGenLock          Shared             107
          XidGenLock          Shared             129
 CheckpointStartLock       Exclusive             153
       ProcArrayLock          Shared             400

             Lock Id   Combined Time (ns)
      BufMappingLock                36600
    FirstLockMgrLock               420600
       FreeSpaceLock              2998400
       WALInsertLock              3818300
       ProcArrayLock              8986900
          OidGenLock             18127200
          XidGenLock             18569200
 CheckpointStartLock             44795700
      CheckpointLock            206488400

bash-3.00# echo 1100 users - SMALL DROP AGAIN
1100 users
bash-3.00# ./3_lwlock_acquires.d 19178

             Lock Id            Mode           Count
      BufMappingLock       Exclusive               1
       WALInsertLock       Exclusive               6
      CheckpointLock          Shared              11
          XidGenLock       Exclusive              11
 CheckpointStartLock          Shared              12
          OidGenLock       Exclusive              12
    FirstLockMgrLock          Shared              24
       FreeSpaceLock       Exclusive              39
          OidGenLock          Shared              44
          XidGenLock          Shared              51
 CheckpointStartLock       Exclusive              88
       ProcArrayLock          Shared             171

             Lock Id   Combined Time (ns)
      BufMappingLock                19500
    FirstLockMgrLock               302700
       FreeSpaceLock               511200
       ProcArrayLock              5042300
       WALInsertLock              5592800
 CheckpointStartLock             25009900
          OidGenLock             25231600
          XidGenLock            108045300
      CheckpointLock            379734000

bash-3.00# echo 1150 users  - STEADY AT LOW VALUE
1150 users
bash-3.00# ./3_lwlock_acquires.d 19178

             Lock Id            Mode           Count
        WALWriteLock       Exclusive               1
       WALInsertLock       Exclusive               2
      CheckpointLock          Shared               5
 CheckpointStartLock          Shared               6
          OidGenLock       Exclusive               6
          XidGenLock       Exclusive               6
    FirstLockMgrLock          Shared               8
       FreeSpaceLock       Exclusive              21
          OidGenLock          Shared              26
          XidGenLock          Shared              31
       ProcArrayLock          Shared              93
 CheckpointStartLock       Exclusive             122

             Lock Id   Combined Time (ns)
        WALWriteLock                14900
       WALInsertLock               116900
    FirstLockMgrLock               120600
       FreeSpaceLock              2177800
          XidGenLock              4899200
       ProcArrayLock             20721700
 CheckpointStartLock             27805200
      CheckpointLock             76369300
          OidGenLock            470145800

bash-3.00# echo 1250 users - STEADY AT LOW VALUE
1250 users
bash-3.00# ./3_lwlock_acquires.d 19178

             Lock Id            Mode           Count
      CheckpointLock          Shared               2
 CheckpointStartLock          Shared               2
          OidGenLock       Exclusive               2
       WALInsertLock       Exclusive               2
          XidGenLock       Exclusive               2
       FreeSpaceLock       Exclusive               9
          OidGenLock          Shared              10
          XidGenLock          Shared              12
       ProcArrayLock          Shared              36
 CheckpointStartLock       Exclusive             135

             Lock Id   Combined Time (ns)
       WALInsertLock                39500
       FreeSpaceLock                98600
       ProcArrayLock               318800
          XidGenLock              1379900
          OidGenLock              3437700
      CheckpointLock              9565200
 CheckpointStartLock             56547900

bash-3.00#




In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-07-25 23:24:25
Subject: Re: User concurrency thresholding: where do I look?
Previous:From: Karl DenningerDate: 2007-07-25 21:01:28
Subject: Re: Performance issue with 8.2.3 - "C" application

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group