PostgreSQL Systemtap example : connection close and session duration static
背景
本文是一个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/