Pgbouncer是什么
Pgbouncer是一个轻量的工具
•数据库在没有任何连接池的情况下，应用程序必须直接访问数据库来建立连接。打开的一个连接的消耗，关闭一个连接的消耗，而这样的消耗，伴随着你打开的连接越多，则消耗的就越多。还有短连接，可能同时并发的连接多，但占用这个连接的时间很短。这就会引起另一个问题，你设置的连接数和突入起来的连接数不匹配的情况，最后就会造成拒绝连接的问题。
•所以这就对数据库的连接提出一个问题，复用，连接的复用对数据库非常重要，这可以降低某些快速连接，快速断开的连接的数据库访问对数据库性能的消耗和产生的一些不必要的麻烦。
使用Pgbouncer的作用
•pgBouncer可以在后端数据库和前端应用间建立连接的桥梁，由pgBouncer去处理和后端数据库的连接关系。
•对客户端连接进行限制，预防过多或者恶意的连接请求。
•主要工作还是每一个数据节点对数据的存取，pgbouncer只是提供给客户端数据节点的分配和链接以及限制客户端连接的数量。整体上实现了对数据的负载均衡
•能够缓存和PostgreSQL的连接，当有连接请求进来的时候，直接分配空闲进程，而不需要PostgreSQL fork出新进程来建立连接，以节省创建新进程，创建连接的资源消耗。
•能够有效提高连接的利用率，避免过多的无效连接，导致数据库消耗资源过大，CPU占用过高。
使用Pgbouncer的优点
•内存消耗低(默认为2k/连接)，因为pgbouncer不需要每次都接受完整的数据包
•可以把不同的数据库连接到一个机器上，而对客户端保持透明
•支持在线的重新配置而无须重启
•pgbouncer 可以限制每一对用户+数据库 到PostgreSQL Cluster的总连接数
Pgbouncer的三种模式
•1.session
会话级连接，在它的连接生命周期内，连接池分配给它一个数据库连接。客户端断开时，数据库连接会放回连接池中。
•2.transaction
事务级别连接，当客户端的每个事务结束时，数据库连接就会重新释放回连接池中，再次执行一个事务时，需要再从连接池中获取一个连接。
•3.statement
每执行完一个SQL时，连接就会重新释放回连接池中，再次执行一个SQL 时，需要再次从连接池中获得连接。这种模式意味着在客户端强制autocomit模式
准备环境
操作系统：centos 7.9（为了方便上网，我VM虚拟机配置2块网卡:1块host-only网卡不配网关;1块nat自动获取IP上网）
数据库版本：pg 14.11 下载地址：PostgreSQL: File Browser
pgbouncer版本：1.22.0 下载地址：PgBouncer Downloads
数据库安装目录：/u01/postgres
数据库PGDATA目录：/u01/postgres/data
pgbouncer安装目录：/u01/pgbouncer
安装pgbouncer
LINUX里下载阿里源。
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
yum makecache
安装依赖，主要是libevent-devel*，别的安装包在安装PG时一般都安装了。
yum -y install tcl tcl-devel uuid-devel perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake gcc* readline-devel bison flex libicu-devel net-tools systemd-devel libevent-devel*
下安装包到/home/postgres目录，赋予安装权限。
chown -R postgres:postgres pgbouncer-1.22.0.tar.gz
mkdir -p /u01/pgbouncer
chown -R postgres:postgres /u01/pgbouncer
su - postgres
cd ~
tar -zxvf pgbouncer-1.22.0.tar.gz
cd pgbouncer-1.22.0
./configure --prefix=/u01/pgbouncer
make && make install
创建测试库
这里使用pgbench工具来准备压测数据库
[postgres@pg01 ~]$ psql
psql (14.11)
Type "help" for help.
postgres=# create user pgbench with password '123456';
CREATE ROLE
postgres=# create database pgbench owner pgbench;
CREATE DATABASE
postgres-# \q
[postgres@pg01 ~]$ pgbench -U pgbench -i pgbench
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.19 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.45 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 0.31 s, vacuum 0.08 s, primary keys 0.06 s).
安装pgbouncer
配置pgbouncer配置文件my.ini
[postgres@pg01 pgbouncer]$ pwd
/u01/pgbouncer
[postgres@pg01 pgbouncer]$ cat my.ini
[databases]
#这行定义了一个 PgBouncer 管理的数据库别名 testdb。host=192.168.1.151 指定了后端 PostgreSQL 数据库服务器的 IP 地址；port=5432 指定了 PostgreSQL 数据库服务器监听的端口号；dbname=postgres 指定了要连接的实际数据库名。
testdb=host=192.168.100.101 port=5432 dbname=pgbench
[pgbouncer]
# 日志文件位置
logfile = /u01/pgbouncer/pgbouncer.log
# pid文件位置
pidfile = /u01/pgbouncer/pgbouncer.pid
# 监听的地址
listen_addr = 0.0.0.0
# 监听的端口,pgbouncer对外服务的端口
listen_port = 6432
#采用md5认证
auth_type = md5
# 认证的配置文件配置
auth_file = /u01/pgbouncer/userlist.txt
#指定具有 PgBouncer 管理员权限的用户列表，拥有权限可以执行管理命令，如;重载配置、暂停/恢复池等。
admin_users = postgres
# 池的模式，默认session级别
pool_mode = session
#连接释放回池子时，PgBouncer 将在服务器上执行此 SQL 命令来清除会话状态。
#DISCARD ALL 是 PostgreSQL 中的一个 SQL 命令，它用于清除当前会话的所有设置和临时资源，包括：关闭所有打开的临时表，重置会话级别的配置设置为默认值，释放所有保存的计划查询（prepared statements），关闭所有光标（cursors），释放所有临时变量（temporary variables）
server_reset_query = DISCARD ALL
max_client_conn = 200
min_pool_size=2
default_pool_size = 20
max_db_connections=200
max_user_connections=200
查看密码
[postgres@pg01 pgbouncer]$ psql
psql (14.11)
Type "help" for help.
postgres=# select usename, passwd from pg_shadow order by 1;
usename | passwd
----------+---------------------------------------------------------------------------------------------------------------------------------------
pgbench | SCRAM-SHA-256$4096:fLC3qN96yKBr9jdPETgz+w==$0YBE3eNhw5UPMWxfERKpoE7naVNtPa9w2Xt9UR077ZE=:KxwO8NyS15WkEQdvnAoY0CJ26gt+VJ0B+9VttZt7muo=
postgres |
(2 rows)
创建userlist.txt
[postgres@pg01 pgbouncer]$ cat userlist.txt
"pgbench" "SCRAM-SHA-256$4096:fLC3qN96yKBr9jdPETgz+w==$0YBE3eNhw5UPMWxfERKpoE7naVNtPa9w2Xt9UR077ZE=:KxwO8NyS15WkEQdvnAoY0CJ26gt+VJ0B+9VttZt7muo="
pg_hba.conf配置
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
#增加192.168.100.1/24
host all all 192.168.100.0/24 scram-sha-256
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
修改postgres.conf
[postgres@pg01 data]$ cat postgresql.conf |grep listen_address
listen_addresses = '*' # what IP address(es) to listen on;
重启pg
pg_ctl restart
启动连接池
/u01/pgbouncer/bin/pgbouncer --daemon /u01/pgbouncer/my.ini
重启动连接池
/u01/pgbouncer/bin/pgbouncer --reboot --daemon /u01/pgbouncer/my.ini
登录连接池测试
psql -p 6432 -h 192.168.100.101 -U pgbench -d testdb
单机压测
进行压测
为保证测试准确性，我这里每测试一次，就重启下系统，避免缓存影响数据。
[postgres@pg01 ~]$ pgbench -M extended -v -r -P 1 -S -C -c 100 -j 100 -T 60 -p 5432 -Upostgres pgbench
pgbench (14.11)
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 1.1 s, 398.6 tps, lat 12.651 ms stddev 15.406
progress: 2.0 s, 624.1 tps, lat 19.031 ms stddev 22.445
progress: 3.1 s, 491.7 tps, lat 33.322 ms stddev 45.258
progress: 4.0 s, 881.5 tps, lat 16.985 ms stddev 16.042
progress: 5.1 s, 1049.8 tps, lat 12.410 ms stddev 8.266
progress: 6.0 s, 988.2 tps, lat 13.198 ms stddev 10.721
progress: 7.1 s, 964.6 tps, lat 17.190 ms stddev 17.703
progress: 8.1 s, 1026.0 tps, lat 13.753 ms stddev 10.368
progress: 9.0 s, 1035.6 tps, lat 15.987 ms stddev 12.954
progress: 10.1 s, 903.3 tps, lat 17.843 ms stddev 18.549
progress: 11.0 s, 1029.1 tps, lat 13.976 ms stddev 13.646
progress: 12.1 s, 1089.8 tps, lat 15.138 ms stddev 10.753
progress: 13.0 s, 1055.9 tps, lat 13.770 ms stddev 11.205
progress: 14.0 s, 1130.9 tps, lat 11.905 ms stddev 7.755
progress: 15.0 s, 1139.3 tps, lat 13.270 ms stddev 8.982
progress: 16.0 s, 1100.0 tps, lat 14.051 ms stddev 9.376
progress: 17.0 s, 943.9 tps, lat 16.092 ms stddev 23.771
progress: 18.0 s, 1169.3 tps, lat 12.085 ms stddev 8.515
progress: 19.0 s, 1118.8 tps, lat 12.805 ms stddev 9.167
progress: 20.0 s, 1129.3 tps, lat 13.549 ms stddev 7.904
progress: 21.0 s, 1133.3 tps, lat 12.611 ms stddev 7.614
progress: 22.0 s, 1149.4 tps, lat 11.205 ms stddev 7.502
progress: 23.0 s, 1134.8 tps, lat 13.831 ms stddev 8.509
progress: 24.0 s, 1152.6 tps, lat 13.689 ms stddev 8.417
progress: 25.0 s, 1140.4 tps, lat 13.548 ms stddev 7.302
progress: 26.0 s, 1142.6 tps, lat 13.947 ms stddev 8.097
progress: 27.0 s, 1138.1 tps, lat 14.794 ms stddev 8.086
progress: 28.0 s, 1149.0 tps, lat 13.207 ms stddev 7.852
progress: 29.0 s, 1140.6 tps, lat 12.624 ms stddev 7.527
progress: 30.1 s, 1053.6 tps, lat 12.696 ms stddev 8.408
progress: 31.1 s, 1001.6 tps, lat 14.090 ms stddev 8.744
progress: 32.0 s, 978.1 tps, lat 14.311 ms stddev 9.989
progress: 33.1 s, 984.6 tps, lat 13.793 ms stddev 8.887
progress: 34.1 s, 1005.3 tps, lat 14.813 ms stddev 9.267
progress: 35.1 s, 1004.5 tps, lat 13.660 ms stddev 8.717
progress: 36.0 s, 933.0 tps, lat 12.334 ms stddev 7.954
progress: 37.0 s, 954.1 tps, lat 14.093 ms stddev 8.304
progress: 38.0 s, 1021.7 tps, lat 7.960 ms stddev 6.513
progress: 39.0 s, 1029.2 tps, lat 14.038 ms stddev 9.085
progress: 40.0 s, 1014.4 tps, lat 12.134 ms stddev 6.920
progress: 41.1 s, 1021.6 tps, lat 12.856 ms stddev 8.041
progress: 42.0 s, 1075.6 tps, lat 14.512 ms stddev 8.528
progress: 43.1 s, 1042.4 tps, lat 11.737 ms stddev 7.131
progress: 44.1 s, 1031.7 tps, lat 14.037 ms stddev 8.868
progress: 45.0 s, 1030.3 tps, lat 12.778 ms stddev 7.576
progress: 46.1 s, 1042.6 tps, lat 14.761 ms stddev 10.188
progress: 47.1 s, 1023.7 tps, lat 13.342 ms stddev 8.992
progress: 48.1 s, 1009.5 tps, lat 12.510 ms stddev 8.522
progress: 49.0 s, 1053.4 tps, lat 11.295 ms stddev 8.453
progress: 50.0 s, 1028.9 tps, lat 12.323 ms stddev 8.568
progress: 51.0 s, 1054.7 tps, lat 12.762 ms stddev 8.436
progress: 52.0 s, 1018.9 tps, lat 14.034 ms stddev 9.024
progress: 53.1 s, 1039.5 tps, lat 15.285 ms stddev 9.152
progress: 54.0 s, 1047.8 tps, lat 11.740 ms stddev 6.779
progress: 55.1 s, 1029.7 tps, lat 13.368 ms stddev 9.154
progress: 56.0 s, 1031.2 tps, lat 13.925 ms stddev 9.217
progress: 57.1 s, 1063.1 tps, lat 14.999 ms stddev 9.707
progress: 58.1 s, 1047.1 tps, lat 12.350 ms stddev 8.087
progress: 59.1 s, 1048.9 tps, lat 13.128 ms stddev 7.986
progress: 60.0 s, 1060.0 tps, lat 12.589 ms stddev 8.123
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 100
number of threads: 100
duration: 60 s
number of transactions actually processed: 61373
latency average = 13.711 ms
latency stddev = 11.108 ms
average connection time = 84.128 ms
tps = 1021.359924 (including reconnection times)
statement latencies in milliseconds:
0.002 \set aid random(1, 100000 * :scale)
13.711 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
session模式压测
重启服务器、重启PG、重启连接池
测试结果
[postgres@pg01 pgbouncer]$ cat my.ini |grep mode
pool_mode = session
[postgres@pg01 ~]$ pgbench -M extended -v -r -P 1 -S -C -c 100 -j 100 -T 60 -p 6432 -Upgbench testdb
Password:
pgbench (14.11)
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 1.0 s, 1374.4 tps, lat 51.924 ms stddev 13.187
progress: 2.0 s, 1517.7 tps, lat 49.955 ms stddev 7.387
progress: 3.0 s, 1602.2 tps, lat 44.645 ms stddev 6.399
progress: 4.0 s, 1539.8 tps, lat 47.779 ms stddev 7.759
progress: 5.0 s, 1580.9 tps, lat 46.602 ms stddev 6.323
progress: 6.0 s, 1572.3 tps, lat 47.076 ms stddev 6.179
progress: 7.0 s, 1565.9 tps, lat 48.616 ms stddev 6.718
progress: 8.0 s, 1596.6 tps, lat 47.884 ms stddev 5.808
progress: 9.0 s, 1609.7 tps, lat 46.839 ms stddev 5.900
progress: 10.0 s, 1606.7 tps, lat 47.622 ms stddev 5.726
progress: 11.0 s, 1608.3 tps, lat 46.798 ms stddev 6.432
progress: 12.0 s, 1540.1 tps, lat 49.928 ms stddev 7.225
progress: 13.0 s, 1585.9 tps, lat 47.762 ms stddev 6.301
progress: 14.0 s, 1494.0 tps, lat 51.886 ms stddev 10.990
progress: 15.0 s, 1514.4 tps, lat 49.679 ms stddev 8.255
progress: 16.0 s, 1549.8 tps, lat 48.412 ms stddev 7.108
progress: 17.0 s, 1401.2 tps, lat 53.271 ms stddev 6.791
progress: 18.0 s, 1347.0 tps, lat 55.504 ms stddev 7.737
progress: 19.0 s, 1318.7 tps, lat 57.148 ms stddev 7.253
progress: 20.0 s, 1378.6 tps, lat 54.848 ms stddev 6.820
progress: 21.0 s, 1343.1 tps, lat 56.653 ms stddev 9.820
progress: 22.0 s, 1402.3 tps, lat 55.610 ms stddev 6.525
progress: 23.0 s, 1314.9 tps, lat 58.170 ms stddev 7.918
progress: 24.0 s, 1274.7 tps, lat 60.642 ms stddev 6.874
progress: 25.0 s, 1320.0 tps, lat 58.357 ms stddev 6.742
progress: 26.0 s, 1318.9 tps, lat 57.537 ms stddev 6.574
progress: 27.0 s, 1314.2 tps, lat 57.850 ms stddev 8.741
progress: 28.0 s, 1317.4 tps, lat 58.591 ms stddev 7.809
progress: 29.0 s, 1332.3 tps, lat 56.925 ms stddev 8.142
progress: 30.0 s, 1369.4 tps, lat 54.325 ms stddev 7.220
progress: 31.0 s, 1366.4 tps, lat 54.931 ms stddev 7.282
progress: 32.0 s, 1336.9 tps, lat 56.411 ms stddev 8.259
progress: 33.0 s, 1296.2 tps, lat 58.523 ms stddev 7.839
progress: 34.0 s, 1265.5 tps, lat 60.896 ms stddev 8.377
progress: 35.0 s, 1282.2 tps, lat 58.186 ms stddev 8.921
progress: 36.0 s, 1088.9 tps, lat 68.901 ms stddev 13.036
progress: 37.0 s, 1055.4 tps, lat 71.581 ms stddev 12.162
progress: 38.0 s, 853.7 tps, lat 90.080 ms stddev 14.280
progress: 39.0 s, 999.8 tps, lat 76.673 ms stddev 14.878
progress: 40.0 s, 1275.1 tps, lat 58.898 ms stddev 7.411
progress: 41.0 s, 1330.5 tps, lat 57.027 ms stddev 9.602
progress: 42.0 s, 1281.3 tps, lat 59.057 ms stddev 7.763
progress: 43.0 s, 1337.6 tps, lat 58.174 ms stddev 7.467
progress: 44.0 s, 1289.8 tps, lat 58.239 ms stddev 8.077
progress: 45.0 s, 1304.2 tps, lat 58.475 ms stddev 8.294
progress: 46.0 s, 1301.8 tps, lat 56.439 ms stddev 7.816
progress: 47.0 s, 1313.1 tps, lat 59.291 ms stddev 6.721
progress: 48.0 s, 1360.2 tps, lat 55.774 ms stddev 7.343
progress: 49.0 s, 1380.5 tps, lat 55.142 ms stddev 6.441
progress: 50.0 s, 1377.6 tps, lat 55.156 ms stddev 6.345
progress: 51.0 s, 1345.1 tps, lat 57.189 ms stddev 8.674
progress: 52.0 s, 1349.3 tps, lat 55.513 ms stddev 8.389
progress: 53.0 s, 1374.7 tps, lat 55.630 ms stddev 6.508
progress: 54.0 s, 1326.6 tps, lat 56.615 ms stddev 6.355
progress: 55.0 s, 1308.9 tps, lat 57.960 ms stddev 8.213
progress: 56.0 s, 1347.5 tps, lat 54.044 ms stddev 7.584
progress: 57.0 s, 1270.2 tps, lat 58.542 ms stddev 8.646
progress: 58.0 s, 1333.3 tps, lat 58.468 ms stddev 6.370
progress: 59.0 s, 1387.6 tps, lat 54.851 ms stddev 6.421
progress: 60.0 s, 1406.4 tps, lat 54.650 ms stddev 7.404
transaction type: <builtin: select only>
scaling factor: 1
query mode: exte
ded
number of clients: 100
number of threads: 100
duration: 60 s
number of transactions actually processed: 82195
latency average = 55.237 ms
latency stddev = 10.410 ms
average connection time = 17.753 ms
tps = 1369.756337 (including reconnection times)
statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
55.236 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
transaction模式压测
重启服务器、重启PG、重启连接池
[postgres@pg01 pgbouncer]$ cat my.ini |grep mode
pool_mode = transaction
[postgres@pg01 pgbouncer]$ pgbench -M extended -v -r -P 1 -S -C -c 100 -j 100 -T 60 -p 6432 -Upgbench testdb
Password:
pgbench (14.11)
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 1.0 s, 1698.8 tps, lat 26.435 ms stddev 12.621
progress: 2.0 s, 1840.2 tps, lat 17.355 ms stddev 8.281
progress: 3.0 s, 1829.6 tps, lat 17.060 ms stddev 8.066
progress: 4.0 s, 1894.7 tps, lat 16.395 ms stddev 8.575
progress: 5.0 s, 1821.2 tps, lat 17.171 ms stddev 10.170
progress: 6.0 s, 1889.1 tps, lat 16.247 ms stddev 8.437
progress: 7.1 s, 1719.1 tps, lat 17.322 ms stddev 9.779
progress: 8.0 s, 1853.7 tps, lat 15.251 ms stddev 8.863
progress: 9.0 s, 1887.8 tps, lat 15.616 ms stddev 8.661
progress: 10.0 s, 1857.1 tps, lat 16.234 ms stddev 9.152
progress: 11.0 s, 1907.5 tps, lat 16.013 ms stddev 8.828
progress: 12.0 s, 1893.5 tps, lat 16.282 ms stddev 7.555
progress: 13.0 s, 1892.2 tps, lat 16.659 ms stddev 8.492
progress: 14.0 s, 1920.9 tps, lat 15.195 ms stddev 8.742
progress: 15.0 s, 1914.0 tps, lat 16.291 ms stddev 8.557
progress: 16.0 s, 1900.6 tps, lat 16.202 ms stddev 8.801
progress: 17.0 s, 1742.8 tps, lat 17.418 ms stddev 9.322
progress: 18.0 s, 1523.2 tps, lat 20.844 ms stddev 11.975
progress: 19.0 s, 1470.9 tps, lat 21.520 ms stddev 11.526
progress: 20.0 s, 1324.6 tps, lat 22.720 ms stddev 12.348
progress: 21.0 s, 1221.9 tps, lat 25.313 ms stddev 12.761
progress: 22.0 s, 1301.0 tps, lat 24.390 ms stddev 13.980
progress: 23.0 s, 1611.7 tps, lat 18.586 ms stddev 9.989
progress: 24.0 s, 1589.7 tps, lat 18.623 ms stddev 11.568
progress: 25.0 s, 1559.6 tps, lat 18.694 ms stddev 10.168
progress: 26.0 s, 1610.6 tps, lat 20.280 ms stddev 12.089
progress: 27.0 s, 1631.7 tps, lat 18.808 ms stddev 10.403
progress: 28.0 s, 1612.0 tps, lat 18.421 ms stddev 10.222
progress: 29.1 s, 1630.3 tps, lat 18.474 ms stddev 12.375
progress: 30.0 s, 1630.0 tps, lat 19.685 ms stddev 9.705
progress: 31.0 s, 1633.3 tps, lat 17.931 ms stddev 9.855
progress: 32.0 s, 1606.1 tps, lat 17.814 ms stddev 10.920
progress: 33.1 s, 1604.8 tps, lat 18.985 ms stddev 10.397
progress: 34.0 s, 1636.7 tps, lat 18.589 ms stddev 11.035
progress: 35.0 s, 1672.4 tps, lat 18.389 ms stddev 11.320
progress: 36.0 s, 1608.4 tps, lat 18.681 ms stddev 9.953
progress: 37.0 s, 1631.3 tps, lat 18.355 ms stddev 10.851
progress: 38.0 s, 1621.6 tps, lat 19.922 ms stddev 11.768
progress: 39.0 s, 1642.5 tps, lat 18.484 ms stddev 9.613
progress: 40.0 s, 1642.0 tps, lat 19.190 ms stddev 10.295
progress: 41.0 s, 1654.0 tps, lat 19.148 ms stddev 9.792
progress: 42.0 s, 1622.3 tps, lat 18.274 ms stddev 9.420
progress: 43.0 s, 1655.9 tps, lat 19.360 ms stddev 10.837
progress: 44.0 s, 1649.8 tps, lat 17.499 ms stddev 10.159
progress: 45.0 s, 1623.6 tps, lat 18.424 ms stddev 9.582
progress: 46.1 s, 1613.6 tps, lat 19.539 ms stddev 10.398
progress: 47.0 s, 1568.4 tps, lat 20.409 ms stddev 10.440
progress: 48.0 s, 1029.3 tps, lat 34.545 ms stddev 19.935
progress: 49.0 s, 957.1 tps, lat 33.013 ms stddev 18.065
progress: 50.0 s, 1431.3 tps, lat 21.680 ms stddev 12.274
progress: 51.0 s, 1483.8 tps, lat 20.620 ms stddev 10.697
progress: 52.0 s, 1560.1 tps, lat 20.074 ms stddev 10.590
progress: 53.0 s, 1504.2 tps, lat 20.237 ms stddev 10.571
progress: 54.0 s, 1580.1 tps, lat 19.989 ms stddev 10.554
progress: 55.0 s, 1617.3 tps, lat 18.042 ms stddev 11.258
progress: 56.0 s, 1478.5 tps, lat 20.900 ms stddev 10.628
progress: 57.0 s, 1411.3 tps, lat 21.240 ms stddev 12.033
progress: 58.0 s, 1532.7 tps, lat 19.816 ms stddev 10.575
progress: 59.0 s, 1592.8 tps, lat 19.344 ms stddev 11.468
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 100
number of threads: 100
duration: 60 s
number of transactions actually processed: 97713
latency average = 19.009 ms
latency stddev = 11.006 ms
average connection time = 42.386 ms
tps = 1627.826702 (including reconnection times)
statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
19.008 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
statement模式压测
重启服务器、重启PG、重启连接池
[postgres@pg01 pgbouncer]$ cat my.ini |grep mode
pool_mode = statement
[postgres@pg01 pgbouncer]$ pgbench -M extended -v -r -P 1 -S -C -c 100 -j 100 -T 60 -p 6432 -Upgbench testdb
Password:
pgbench (14.11)
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 1.0 s, 1602.6 tps, lat 27.205 ms stddev 17.764
progress: 2.0 s, 1805.9 tps, lat 17.553 ms stddev 9.500
progress: 3.0 s, 1818.8 tps, lat 16.612 ms stddev 9.813
progress: 4.0 s, 1852.3 tps, lat 16.123 ms stddev 9.757
progress: 5.0 s, 1823.2 tps, lat 16.037 ms stddev 9.819
progress: 6.0 s, 1806.5 tps, lat 15.899 ms stddev 9.033
progress: 7.1 s, 1860.7 tps, lat 16.548 ms stddev 9.561
progress: 8.0 s, 1881.7 tps, lat 16.202 ms stddev 9.365
progress: 9.0 s, 1811.6 tps, lat 16.441 ms stddev 10.011
progress: 10.0 s, 1830.5 tps, lat 16.508 ms stddev 9.503
progress: 11.0 s, 1610.5 tps, lat 18.600 ms stddev 9.667
progress: 12.0 s, 1561.5 tps, lat 19.717 ms stddev 10.577
progress: 13.1 s, 1514.3 tps, lat 20.092 ms stddev 11.097
progress: 14.0 s, 1534.3 tps, lat 19.217 ms stddev 11.406
progress: 15.0 s, 1469.5 tps, lat 21.063 ms stddev 10.412
progress: 16.0 s, 1456.2 tps, lat 21.168 ms stddev 9.600
progress: 17.0 s, 1555.2 tps, lat 19.638 ms stddev 10.080
progress: 18.0 s, 1530.5 tps, lat 19.070 ms stddev 11.152
progress: 19.0 s, 1586.2 tps, lat 19.541 ms stddev 9.956
progress: 20.0 s, 1550.5 tps, lat 20.693 ms stddev 10.862
progress: 21.0 s, 1554.0 tps, lat 20.334 ms stddev 11.422
progress: 22.0 s, 1529.1 tps, lat 19.263 ms stddev 10.065
progress: 23.0 s, 1513.2 tps, lat 20.228 ms stddev 12.398
progress: 24.0 s, 1430.2 tps, lat 21.292 ms stddev 12.653
progress: 25.0 s, 1491.1 tps, lat 21.218 ms stddev 10.398
progress: 26.0 s, 1437.9 tps, lat 21.398 ms stddev 11.308
progress: 27.0 s, 1479.0 tps, lat 20.801 ms stddev 10.331
progress: 28.1 s, 1432.7 tps, lat 23.129 ms stddev 11.310
progress: 29.0 s, 1571.4 tps, lat 19.641 ms stddev 9.946
progress: 30.0 s, 1570.7 tps, lat 20.460 ms stddev 11.428
progress: 31.0 s, 1296.5 tps, lat 24.401 ms stddev 16.838
progress: 32.0 s, 1539.7 tps, lat 19.380 ms stddev 10.827
progress: 33.0 s, 1561.9 tps, lat 19.079 ms stddev 9.798
progress: 34.0 s, 1519.1 tps, lat 21.068 ms stddev 10.891
progress: 35.0 s, 1552.8 tps, lat 17.830 ms stddev 10.141
progress: 36.0 s, 1544.6 tps, lat 20.283 ms stddev 10.862
progress: 37.0 s, 1515.9 tps, lat 20.408 ms stddev 12.286
progress: 38.0 s, 1600.0 tps, lat 18.350 ms stddev 10.859
progress: 39.0 s, 1571.0 tps, lat 18.949 ms stddev 10.080
progress: 40.0 s, 1555.2 tps, lat 20.033 ms stddev 10.079
progress: 41.0 s, 1545.7 tps, lat 19.124 ms stddev 10.905
progress: 42.0 s, 1538.3 tps, lat 19.910 ms stddev 10.428
progress: 43.0 s, 1553.7 tps, lat 19.758 ms stddev 10.380
progress: 44.1 s, 1592.9 tps, lat 19.860 ms stddev 10.743
progress: 45.0 s, 1564.8 tps, lat 18.937 ms stddev 10.729
progress: 46.0 s, 1546.1 tps, lat 19.712 ms stddev 10.236
progress: 47.0 s, 1567.5 tps, lat 19.423 ms stddev 11.330
progress: 48.0 s, 1554.4 tps, lat 19.683 ms stddev 11.022
progress: 49.0 s, 1559.9 tps, lat 19.046 ms stddev 10.239
progress: 50.0 s, 1584.9 tps, lat 19.466 ms stddev 9.788
progress: 51.0 s, 1576.7 tps, lat 18.485 ms stddev 10.541
progress: 52.0 s, 1541.3 tps, lat 20.258 ms stddev 10.231
progress: 53.0 s, 1592.3 tps, lat 18.891 ms stddev 10.064
progress: 54.0 s, 1560.7 tps, lat 19.457 ms stddev 11.445
progress: 55.0 s, 1558.4 tps, lat 18.374 ms stddev 10.459
progress: 56.0 s, 1545.9 tps, lat 21.086 ms stddev 11.206
progress: 57.1 s, 1549.1 tps, lat 19.614 ms stddev 13.008
progress: 58.0 s, 1560.3 tps, lat 19.157 ms stddev 10.450
progress: 59.0 s, 1586.4 tps, lat 19.217 ms stddev 9.599
progress: 60.0 s, 1562.5 tps, lat 19.055 ms stddev 9.634
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 100
number of threads: 100
duration: 60 s
number of transactions actually processed: 95078
latency average = 19.390 ms
latency stddev = 10.984 ms
average connection time = 43.745 ms
tps = 1583.468666 (including reconnection times)
statement latencies in milliseconds:
0.014 \set aid random(1, 100000 * :scale)
19.375 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
也欢迎关注我的公众号【徐sir的IT之路】，一起学习！
————————————————————————————
公众号：徐sir的IT之路
CSDN ：https://blog.csdn.net/xxddxhyz?type=blog
墨天轮：https://www.modb.pro/u/3605
PGFANS：https://www.pgfans.cn/user/home?userId=5568
————————————————————————————
image.png
最后修改时间：2024-04-06 13:57:00
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
