PostgreSQL Systemtap example : connection close and session duration static

2 minute read

背景

本文是一个systemtap的例子, 用到上一篇文章中讲到的PostgreSQL定制探针.(数据库连接以及断开连接的探针)

http://blog.163.com/digoal@126/blog/static/163877040201391123645546/

接下来要使用这两个探针, 统计数据库每秒的新建连接数, 每秒的断开连接数, 以及断开连接的会话时长的柱状图等信息.  
stap脚本如下 :   
vi test.stp  
global var1, var11, var2, var3, var4, var44;  
  
probe begin {  
  if ($1<1 || $2<1) {  
    println("Please enter $interval and $count larger then one")  
    exit()  
  }  
}  
  
probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("client_conn") {  
  var1 <<< 1  
  var11 <<< 1  
  var2[pid()]=gettimeofday_ms()  
}  
  
probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("client_close") {  
# 因为可能断开的连接是未探测到client_conn的连接, 所以要做一下判断, 忽略未探测到client_conn的断开连接探针.  
  if (var2[pid()] != 0) {  
    var3 <<< (gettimeofday_ms() - var2[pid()])  
    delete var2[pid()]  
  }  
  var4 <<< 1  
  var44 <<< 1  
}  
  
probe timer.s($1) {  
   printf("conn per sec: %d, close per sec: %d\n", @count(var1)/$1, @count(var4)/$1)  
   delete var1  
   delete var4  
}  
  
probe timer.s($2) {  
   printf("end conn per sec: %d, end close per sec: %d\n", @count(var1)/$2, @count(var4)/$2)  
   printf("session times min:%d, max:%d, avg:%d\n", @min(var3), @max(var3), @avg(var3))  
   println("hist_linear : ms")  
   println(@hist_linear(var3,0,5000,50))  
   delete var11  
   delete var44  
   delete var3  
   delete var1  
   delete var2  
   delete var4  
   exit()  
}  
  
测试用到pgbench, 脚本如下 :   
\setrandom seed 1 300  
select pg_sleep(0.01 * :seed);  
  
测试过程 :   
启动pgbench进行测试(测试13秒) :   
pg93@db-172-16-3-39-> pgbench -M extended -f ./test.sql -n -r -C -h 127.0.0.1 -c 16 -j 4 -T 13  
然后启动stap观察从以上脚本得到的数据库每秒的新建连接数, 每秒的断开连接数, 以及断开连接的会话时长的柱状图等信息, 本例采样10秒. 输出如下 :   
[root@db-172-16-3-39 ~]# stap ./test.stp 1 10  
conn per sec: 11, close per sec: 11  
conn per sec: 5, close per sec: 5  
conn per sec: 13, close per sec: 13  
conn per sec: 13, close per sec: 13  
conn per sec: 12, close per sec: 12  
conn per sec: 8, close per sec: 8  
conn per sec: 15, close per sec: 15  
conn per sec: 8, close per sec: 8  
conn per sec: 11, close per sec: 11  
conn per sec: 11, close per sec: 11  
end conn per sec: 0, end close per sec: 0  
session times min:14, max:2994, avg:1429  
hist_linear : ms  
value |-------------------------------------------------- count  
    0 |@@@@@                                              5  
   50 |@@@                                                3  
  100 |@@                                                 2  
  150 |@@                                                 2  
  200 |                                                   0  
  250 |@@                                                 2  
  300 |@@                                                 2  
  350 |@                                                  1  
  400 |                                                   0  
  450 |                                                   0  
  500 |@@@@                                               4  
  550 |                                                   0  
  600 |@                                                  1  
  650 |@@@                                                3  
  700 |@@                                                 2  
  750 |                                                   0  
  800 |@                                                  1  
  850 |@@                                                 2  
  900 |@@                                                 2  
  950 |@@                                                 2  
 1000 |@@                                                 2  
 1050 |@@@                                                3  
 1100 |@                                                  1  
 1150 |@@@                                                3  
 1200 |@@                                                 2  
 1250 |                                                   0  
 1300 |@@@                                                3  
 1350 |                                                   0  
 1400 |@                                                  1  
 1450 |@                                                  1  
 1500 |                                                   0  
 1550 |@                                                  1  
 1600 |@                                                  1  
 1650 |                                                   0  
 1700 |@                                                  1  
 1750 |@@@                                                3  
 1800 |@@@                                                3  
 1850 |@                                                  1  
 1900 |@                                                  1  
 1950 |                                                   0  
 2000 |@                                                  1  
 2050 |@                                                  1  
 2100 |@                                                  1  
 2150 |@@@@                                               4  
 2200 |@@                                                 2  
 2250 |                                                   0  
 2300 |@                                                  1  
 2350 |@@                                                 2  
 2400 |                                                   0  
 2450 |@                                                  1  
 2500 |                                                   0  
 2550 |@@@                                                3  
 2600 |                                                   0  
 2650 |@@@@                                               4  
 2700 |                                                   0  
 2750 |@                                                  1  
 2800 |@                                                  1  
 2850 |@                                                  1  
 2900 |@@@                                                3  
 2950 |@@@@                                               4  
 3000 |                                                   0  
 3050 |                                                   0  
  
测试完后pgbench的输出  
transaction type: Custom query  
scaling factor: 1  
query mode: extended  
number of clients: 16  
number of threads: 4  
duration: 13 s  
number of transactions actually processed: 143  
tps = 9.486990 (including connections establishing)  
tps = 9.559375 (excluding connections establishing)  
statement latencies in milliseconds:  
        0.005455        \setrandom seed 1 300  
        1572.025727     select pg_sleep(0.01 * :seed);  

参考

1. http://blog.163.com/digoal@126/blog/static/163877040201391123645546/

Flag Counter

digoal’s 大量PostgreSQL文章入口