任何数据库上线前，都需要有相关的基准压测，伴随着业务的选型，也是一个很好的参考。那么在压测过程中，需要监控数据库的性能。那么PG是支持很多插件的数据库，我们今天简单的讲几个插件的安装。
第一：pg_stat_monitor
unzip pg_stat_monitor-main.zip
mv pg_stat_monitor-main /usr/local/postgresql/contrib/
cd /usr/local/postgresql/contrib/
cd ..
./configure --prefix=/usr/local/pgsql/
cd contrib/pg_stat_monitor-main/
ll
make
make install
配置文件修改
shared_preload_libraries = 'pg_stat_statements,pg_stat_monitor’
CREATE EXTENSION pg_stat_monitor;- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
第二：pg_stat_statements
编译时安装，需要软件目录执行
cd /usr/local/postgresql/
make world
make install-world
这个时候在/usr/local/postgresql/contrib/会有很多东西出来
[postgres@pg01 contrib]$ ls -ltar
total 96
-rw-r--r-- 1 1107 1107 1131 Feb 6 05:41 README
-rw-r--r-- 1 1107 1107 1438 Feb 6 05:41 meson.build
-rw-r--r-- 1 1107 1107 1543 Feb 6 05:41 Makefile
-rw-r--r-- 1 1107 1107 85 Feb 6 05:41 contrib-global.mk
drwxrwxrwx 2 1107 1107 163 Feb 6 05:50 sslinfo
drwxrwxrwx 4 1107 1107 187 Feb 6 05:51 xml2
drwxrwxrwx 4 1107 1107 175 Feb 6 05:51 ltree_plpython
drwxrwxrwx 4 1107 1107 219 Feb 6 05:51 bool_plperl
drwxrwxrwx 4 1107 1107 4096 Feb 6 05:51 pgcrypto
drwxrwxrwx 4 1107 1107 175 Feb 6 05:51 jsonb_plpython
drwxrwxrwx 4 1107 1107 224 Feb 6 05:51 jsonb_plperl
drwxrwxrwx 2 1107 1107 114 Feb 6 05:51 intagg
drwxrwxrwx 4 1107 1107 178 Feb 6 05:51 hstore_plpython
drwxrwxrwx 4 1107 1107 229 Feb 6 05:51 hstore_plperl
drwxrwxrwx 4 1107 1107 187 Feb 6 05:51 uuid-ossp
drwxrwxrwx 4 1107 1107 327 Feb 6 05:51 sepgsql
drwxrwxrwx 3 1107 1107 47 Feb 6 05:51 start-scripts
drwxrwxrwx 2 1107 1107 4096 Mar 22 15:41 spi
drwxrwxrwx 62 1107 1107 4096 Mar 26 16:05 .
drwxr-xr-x 6 postgres postgres 313 Mar 26 16:07 ..
drwxr-xr-x 9 root root 4096 Mar 26 16:07 pg_stat_monitor-main
drwxrwxrwx 4 1107 1107 4096 Mar 26 16:31 pg_stat_statements
drwxrwxrwx 4 1107 1107 288 Mar 26 18:14 adminpack
drwxrwxrwx 5 1107 1107 4096 Mar 26 18:14 amcheck
drwxrwxrwx 2 1107 1107 102 Mar 26 18:14 auth_delay
drwxrwxrwx 3 1107 1107 135 Mar 26 18:14 auto_explain
drwxrwxrwx 4 1107 1107 182 Mar 26 18:14 basic_archive
drwxrwxrwx 3 1107 1107 156 Mar 26 18:14 basebackup_to_shell
drwxrwxrwx 5 1107 1107 4096 Mar 26 18:14 bloom
drwxrwxrwx 4 1107 1107 288 Mar 26 18:14 btree_gin
drwxrwxrwx 5 1107 1107 4096 Mar 26 18:15 btree_gist
drwxrwxrwx 4 1107 1107 4096 Mar 26 18:15 citext
drwxrwxrwx 5 1107 1107 4096 Mar 26 18:15 cube
drwxrwxrwx 4 1107 1107 259 Mar 26 18:15 dblink
drwxrwxrwx 4 1107 1107 190 Mar 26 18:15 dict_int
drwxrwxrwx 4 1107 1107 220 Mar 26 18:15 dict_xsyn
drwxrwxrwx 4 1107 1107 250 Mar 26 18:15 earthdistance
drwxrwxrwx 5 1107 1107 202 Mar 26 18:15 file_fdw
drwxrwxrwx 4 1107 1107 4096 Mar 26 18:15 fuzzystrmatch
drwxrwxrwx 5 1107 1107 4096 Mar 26 18:15 hstore
drwxrwxrwx 6 1107 1107 4096 Mar 26 18:15 intarray
drwxrwxrwx 4 1107 1107 298 Mar 26 18:15 isn
drwxrwxrwx 4 1107 1107 203 Mar 26 18:15 lo
drwxrwxrwx 5 1107 1107 4096 Mar 26 18:15 ltree
drwxrwxrwx 3 1107 1107 120 Mar 26 18:15 oid2name
drwxrwxrwx 2 1107 1107 165 Mar 26 18:15 old_snapshot
drwxrwxrwx 4 1107 1107 4096 Mar 26 18:15 pageinspect
drwxrwxrwx 4 1107 1107 156 Mar 26 18:15 passwordcheck
drwxrwxrwx 4 1107 1107 4096 Mar 26 18:15 pg_buffercache
drwxrwxrwx 4 1107 1107 327 Mar 26 18:15 pg_freespacemap
drwxrwxrwx 3 1107 1107 288 Mar 26 18:15 pg_prewarm
drwxrwxrwx 4 1107 1107 204 Mar 26 18:15 pg_surgery
drwxrwxrwx 5 1107 1107 4096 Mar 26 18:15 pg_trgm
drwxrwxrwx 4 1107 1107 266 Mar 26 18:15 pgrowlocks
drwxrwxrwx 4 1107 1107 4096 Mar 26 18:15 pgstattuple
drwxrwxrwx 4 1107 1107 285 Mar 26 18:15 pg_visibility
drwxrwxrwx 4 1107 1107 273 Mar 26 18:15 pg_walinspect
drwxrwxrwx 4 1107 1107 4096 Mar 26 18:15 postgres_fdw
drwxrwxrwx 5 1107 1107 4096 Mar 26 18:15 seg
drwxrwxrwx 5 1107 1107 226 Mar 26 18:15 tablefunc
drwxrwxrwx 4 1107 1107 167 Mar 26 18:15 tcn
drwxrwxrwx 6 1107 1107 198 Mar 26 18:15 test_decoding
drwxrwxrwx 4 1107 1107 225 Mar 26 18:15 tsm_system_rows
drwxrwxrwx 4 1107 1107 225 Mar 26 18:15 tsm_system_time
drwxrwxrwx 4 1107 1107 276 Mar 26 18:15 unaccent
drwxrwxrwx 3 1107 1107 120 Mar 26 18:15 vacuumlo
配置文件修改
shared_preload_libraries = 'pg_stat_statements,pg_stat_monitor'- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
第三：pg_profile安装
pg_profile 这个插件从工作原理上来说，是依赖于如下的基础指标数据
pg_profile 这个插件主要是由PGPSQL和HTML 2种开发语言组成， 所以不需要C语言的 make & make install 的编译安装。Github 链接地址： https://github.com/zubkov-andrei/pg_profile
Root用户# tar xzf pg_profile--4.4.tar.gz --directory /usr/local/pgsql/share/extension
登录PG库
postgres=# CREATE EXTENSION dblink;
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# CREATE EXTENSION pg_profile;
postgres=# CREATE EXTENSION dblink;
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# CREATE SCHEMA profile;
postgres=# CREATE EXTENSION pg_profile SCHEMA profile;
postgres=# ALTER EXTENSION pg_profile UPDATE;- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
然后这样就可以通过pgbench简单的压测一下数据
create database pgbench
pgbench -i -s 5 pgbench --初始化，将在pgbench_accounts表中创建 500,000行。
pgbench -r -j2 -c4 -t60 pgbench --基准测试1，并行工作线程数2，客户端数量4，每客户端事务数60
pgbench -r -j2 -c10 -T10 pgbench --基准测试2，并行工作线程数2，客户端数量10，运行时间1分钟
-r 在基准结束后，报告平均的每个命令的每语句等待时间(从客户端的角度来说是执行时间)。
-j pgbench中的工作者线程数量。在多 CPU 机器上使用多于一个线程会有用。客户端会尽可能均匀地分布到可用的线程上。默认为 1。
-c 模拟的客户端数量，也就是并发数据库会话数量。默认为 1。
-t 每个客户端运行的事务数量。默认为 10。
-T 运行测试这么多秒，而不是为每个客户端运行固定数量的事务。
注意： -t和-T是互斥的。
[postgres@pg01 ~]$ pgbench -i -s 5 pgbench
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
creating tables...
10000 tuples done.
20000 tuples done.
30000 tuples done.
40000 tuples done.
50000 tuples done.
60000 tuples done.
70000 tuples done.
80000 tuples done.
90000 tuples done.
100000 tuples done.
110000 tuples done.
120000 tuples done.
130000 tuples done.
140000 tuples done.
150000 tuples done.
160000 tuples done.
170000 tuples done.
180000 tuples done.
190000 tuples done.
200000 tuples done.
210000 tuples done.
220000 tuples done.
230000 tuples done.
240000 tuples done.
250000 tuples done.
260000 tuples done.
270000 tuples done.
280000 tuples done.
290000 tuples done.
300000 tuples done.
310000 tuples done.
320000 tuples done.
330000 tuples done.
340000 tuples done.
350000 tuples done.
360000 tuples done.
370000 tuples done.
380000 tuples done.
390000 tuples done.
400000 tuples done.
410000 tuples done.
420000 tuples done.
430000 tuples done.
440000 tuples done.
450000 tuples done.
460000 tuples done.
470000 tuples done.
480000 tuples done.
490000 tuples done.
500000 tuples done.
set primary key...
vacuum...done.
[postgres@pg01 ~]$ pgbench -r -j2 -c10 -T10 pgbenchdb
Connection to database "pgbenchdb" failed:
connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: database "pgbenchdb" does not exist
[postgres@pg01 ~]$ pgbench -r -j2 -c10 -T10 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 5
query mode: simple
number of clients: 10
number of threads: 2
duration: 10 s
number of transactions actually processed: 35776
tps = 3576.578529 (including connections establishing)
tps = 3587.204201 (excluding connections establishing)
statement latencies in milliseconds:
0.002918 \set nbranches 1 * :scale
0.001496 \set ntellers 10 * :scale
0.001507 \set naccounts 100000 * :scale
0.001681 \setrandom aid 1 :naccounts
0.001442 \setrandom bid 1 :nbranches
0.001472 \setrandom tid 1 :ntellers
0.001501 \setrandom delta -5000 5000
0.144608 BEGIN;
0.345508 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.242933 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.397424 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.821400 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.219026 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.592674 END;
[postgres@pg01 ~]$ pgbench -h 127.0.0.1 -p 5432 -r -j2 -c10 -T120 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 5
query mode: simple
number of clients: 10
number of threads: 2
duration: 120 s
number of transactions actually processed: 378871
tps = 3157.167880 (including connections establishing)
tps = 3157.943015 (excluding connections establishing)
statement latencies in milliseconds:
0.003607 \set nbranches 1 * :scale
0.001526 \set ntellers 10 * :scale
0.001567 \set naccounts 100000 * :scale
0.001784 \setrandom aid 1 :naccounts
0.001458 \setrandom bid 1 :nbranches
0.001531 \setrandom tid 1 :ntellers
0.001582 \setrandom delta -5000 5000
0.182651 BEGIN;
0.377843 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.289790 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.474347 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.920400 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.266002 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.629612 END;- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
- 108.
- 109.
- 110.
- 111.
- 112.
- 113.
- 114.
- 115.
- 116.
- 117.
- 118.
- 119.
- 120.
- 121.
- 122.
- 123.
- 124.
- 125.
- 126.
- 127.
- 128.
- 129.
- 130.
- 131.
然后就可以进行收集信息了，类似于Oracle的AWR
postgres=# select * from take_sample(); --就是建立一个快照
server | result | elapsed
--------+--------+-------------
local | OK | 00:00:01.37
(1 row)
postgres=# select show_samples(); --查看生成的快照，以及时间
show_samples
-----------------------------------
(1,"2024-03-26 18:19:18+08",t,,,)
(2,"2024-03-26 18:20:16+08",t,,,)
(3,"2024-03-26 20:22:00+08",t,,,)
(3 rows)
[postgres@pg01 ~]$ psql -d postgres -Aqtc "SELECT get_report('local',2,3)" -o pgbench_report.html
[postgres@pg01 ~]$ sz pgbench_report.html- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
生成的报告就是这样的，可以比较直观的来检测相关性能指标。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
