pgbouncer连接池设置与压力测试的最大连接数测试

pgbouncer中间件，通过设置so_reuseport=1，配置两个pgbouncer的systemctl服务，启动两个pgbouncer的进程。
如下两个进程的配置文件分别是pgbouncer.ini和pgbouncer2.ini，default_pool_size=30，意味着最多支持60个客户端连接，超过这个连接数之后就开始排队（即便是数据库的最大连接数远超60），验证一下这个设置。

可以看到当线程数超过64的时候，出现了NOTICE:  No server connection available in postgres backend, client being queued异常。
但是这里的No server connection available并不是达到了数据库的最大连接数的设置的值，也不是报错，而是受前端的pgbouncer连接池中设置的最大值限制，没有连接可用，然后排队等待可用连接。

1. root@ubuntu12:/usr/local/pgbouncer/bin# pgbench -r -T60 demo_db -U demo_user  -p 8888 -c8 -j8
2. Password:
3. pgbench (16.4)
4. starting vacuum...end.
5. transaction type: <builtin: TPC-B (sort of)>
6. scaling factor: 10
7. query mode: simple
8. number of clients: 8
9. number of threads: 8
10. maximum number of tries: 1
11. duration: 60 s
12. number of transactions actually processed: 76925
13. number of failed transactions: 0 (0.000%)
14. latency average = 6.239 ms
15. initial connection time = 11.821 ms
16. tps = 1282.170948 (without initial connection time)
17. statement latencies in milliseconds and failures:
18.          0.001           0  \set aid random(1, 100000 * :scale)
19.          0.000           0  \set bid random(1, 1 * :scale)
20.          0.000           0  \set tid random(1, 10 * :scale)
21.          0.000           0  \set delta random(-5000, 5000)
22.          0.534           0  BEGIN;
23.          0.709           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
24.          0.737           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
25.          0.846           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
26.          1.261           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
27.          0.624           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
28.          1.527           0  END;
29. root@ubuntu12:/usr/local/pgbouncer/bin#
30. root@ubuntu12:/usr/local/pgbouncer/bin#
31. root@ubuntu12:/usr/local/pgbouncer/bin# pgbench -r -T60 demo_db -U demo_user  -p 8888 -c32 -j32
32. Password:
33. pgbench (16.4)
34. starting vacuum...end.
35. NOTICE:  No server connection available in postgres backend, client being queued
36. NOTICE:  No server connection available in postgres backend, client being queued
37. transaction type: <builtin: TPC-B (sort of)>
38. scaling factor: 10
39. query mode: simple
40. number of clients: 32
41. number of threads: 32
42. maximum number of tries: 1
43. duration: 60 s
44. number of transactions actually processed: 93419
45. number of failed transactions: 0 (0.000%)
46. latency average = 20.549 ms
47. initial connection time = 28.982 ms
48. tps = 1557.281163 (without initial connection time)
49. statement latencies in milliseconds and failures:
50.          0.001           0  \set aid random(1, 100000 * :scale)
51.          0.000           0  \set bid random(1, 1 * :scale)
52.          0.000           0  \set tid random(1, 10 * :scale)
53.          0.000           0  \set delta random(-5000, 5000)
54.          2.492           0  BEGIN;
55.          1.250           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
56.          1.657           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
57.          3.656           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
58.          7.347           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
59.          1.015           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
60.          3.126           0  END;
61. root@ubuntu12:/usr/local/pgbouncer/bin#
62. root@ubuntu12:/usr/local/pgbouncer/bin#
63. root@ubuntu12:/usr/local/pgbouncer/bin# pgbench -r -T60 demo_db -U demo_user  -p 8888 -c64 -j64
64. Password:
65. pgbench (16.4)
66. starting vacuum...end.
67. NOTICE:  No server connection available in postgres backend, client being queued
68. NOTICE:  No server connection available in postgres backend, client being queued
69. NOTICE:  No server connection available in postgres backend, client being queued
70. NOTICE:  No server connection available in postgres backend, client being queued
71. NOTICE:  No server connection available in postgres backend, client being queued
72. NOTICE:  No server connection available in postgres backend, client being queued
73. NOTICE:  No server connection available in postgres backend, client being queued
74. NOTICE:  No server connection available in postgres backend, client being queued
75. NOTICE:  No server connection available in postgres backend, client being queued
76. NOTICE:  No server connection available in postgres backend, client being queued
77. NOTICE:  No server connection available in postgres backend, client being queued
78. NOTICE:  No server connection available in postgres backend, client being queued
79. NOTICE:  No server connection available in postgres backend, client being queued
80. NOTICE:  No server connection available in postgres backend, client being queued
81. NOTICE:  No server connection available in postgres backend, client being queued
82. NOTICE:  No server connection available in postgres backend, client being queued
83. NOTICE:  No server connection available in postgres backend, client being queued
84. NOTICE:  No server connection available in postgres backend, client being queued
85. NOTICE:  No server connection available in postgres backend, client being queued
86. NOTICE:  No server connection available in postgres backend, client being queued
87. NOTICE:  No server connection available in postgres backend, client being queued
88. NOTICE:  No server connection available in postgres backend, client being queued
89. NOTICE:  No server connection available in postgres backend, client being queued
90. NOTICE:  No server connection available in postgres backend, client being queued
91. NOTICE:  No server connection available in postgres backend, client being queued
92. NOTICE:  No server connection available in postgres backend, client being queued
93. NOTICE:  No server connection available in postgres backend, client being queued
94. NOTICE:  No server connection available in postgres backend, client being queued
95. NOTICE:  No server connection available in postgres backend, client being queued
96. NOTICE:  No server connection available in postgres backend, client being queued
97. NOTICE:  No server connection available in postgres backend, client being queued
98. NOTICE:  No server connection available in postgres backend, client being queued
99. NOTICE:  No server connection available in postgres backend, client being queued
100. NOTICE:  No server connection available in postgres backend, client being queued
101. transaction type: <builtin: TPC-B (sort of)>
102. scaling factor: 10
103. query mode: simple
104. number of clients: 64
105. number of threads: 64
106. maximum number of tries: 1
107. duration: 60 s
108. number of transactions actually processed: 93579
109. number of failed transactions: 0 (0.000%)
110. latency average = 41.024 ms
111. initial connection time = 67.205 ms
112. tps = 1560.068618 (without initial connection time)
113. statement latencies in milliseconds and failures:
114.          0.001           0  \set aid random(1, 100000 * :scale)
115.          0.000           0  \set bid random(1, 1 * :scale)
116.          0.000           0  \set tid random(1, 10 * :scale)
117.          0.000           0  \set delta random(-5000, 5000)
118.         23.016           0  BEGIN;
119.          1.245           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
120.          1.818           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
121.          3.676           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
122.          7.185           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
123.          1.111           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
124.          2.951           0  END;
125. root@ubuntu12:/usr/local/pgbouncer/bin#

复制代码

 

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！