postgresql自带提供了一款轻量级的压力测试工具:pgbench ,可自行编写脚本,按自己的需求对数据库进行性能压力测试。
这个工具是作者Greg Smith在使用原生pgbench过程中,发现每次运行产生的结果都不一致,而这个工具就正好解决了问题,解决方案:每个测试用例都运行N次,并且这些用例比通常用要更多的事务,最后将这些测试结果计算出一个平均值。
参考:
https://www.postgresql.org/docs/current/pgbench.html
pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions, and then calculates the average transaction rate (transactions per second). By default, pgbench tests a scenario that is loosely based on TPC-B, involving five SELECT, UPDATE, and INSERT commands per transaction. However, it is easy to test other cases by writing your own transaction script files.
pgbench是一个用于在PostgreSQL数据库中运行基准测试的简单程序。pgbench在多个并发的数据库会话中反复运行一系列相同的SQL命令,并计算事务执行的平均速率(每秒执行的事务个数)。 pgbench默认测试的是一种基于TPC-B的松散的测试,即一个事务中包括5个SELECT,UPDATE和INSERT语句。同时允许基于开发者自己书写的事务脚本文件进行其他场景的测试。
pgbench -i [option...] [dbname]
pgbench [OPTIONS]... [DBNAME]
说明:
Initialization options: # 初始化选项
-i, --initialize invokes initialization mode # 初始化数据库
-F, --fillfactor=NUM set fill factor # 设定填充因子(填充因子-对表的填充因子,类似oracle的pctfree作用)
-n, --no-vacuum do not run VACUUM after initialization # 完成后不收缩
-q, --quiet quiet logging (one message each 5 seconds) # 静默模式
-s, --scale=NUM scaling factor # 规模因子(比例因子-控制表的数据大小)
--foreign-keys create foreign key constraints between tables # 在表间创建外键
--index-tablespace=TABLESPACE
create indexes in the specified tablespace # 在指定表空间创建索引
--tablespace=TABLESPACE create tables in the specified tablespace # 在指定表空间创建表
--unlogged-tables create tables as unlogged tables # 创建unlogged类型的表
Options to select what to run:
-b, --builtin=NAME[@W] add builtin script NAME weighted at W (default: 1)
(use "-b list" to list available scripts)
-f, --file=FILENAME[@W] add script FILENAME weighted at W (default: 1)
-N, --skip-some-updates skip updates of pgbench_tellers and pgbench_branches
(same as "-b simple-update")
-S, --select-only perform SELECT-only transactions
(same as "-b select-only")
Benchmarking options:
-c, --client=NUM number of concurrent database clients (default: 1) # 模拟客户端数
-C, --connect establish new connection for each transaction # 为每个事务启用新链接
-D, --define=VARNAME=VALUE
define variable for use by custom script # 用户脚本定义的自定义变量
-j, --jobs=NUM number of threads (default: 1) # 工作线程数
-l, --log write transaction times to log file # 记录每个事务的时间
-L, --latency-limit=NUM count transactions lasting more than NUM ms as late # 标记超时
-M, --protocol=simple|extended|prepared
protocol for submitting queries (default: simple) # 使用的查询协议,默认simple,老高建议使用prepared比较接近实际需求
-n, --no-vacuum do not run VACUUM before tests # 测试前不收缩表
-P, --progress=NUM show thread progress report every NUM seconds # # 每隔$$秒输出一次线程进度报告
-r, --report-latencies report average latency per command # 报告每个sql的平均执行延迟
-R, --rate=NUM target rate in transactions per second # 目标TPS
-s, --scale=NUM report this scale factor in output # 在输出中报告规模因子
-t, --transactions=NUM number of transactions each client runs (default: 10) # 每个客户端执行的事务数
-T, --time=NUM duration of benchmark test in seconds # 测试执行时间
-v, --vacuum-all vacuum all four standard tables before tests # 测试前收缩表
--aggregate-interval=NUM aggregate data over NUM seconds # 每隔N秒聚合一次数据
--log-prefix=PREFIX prefix for transaction time log file
(default: "pgbench_log")
--progress-timestamp use Unix epoch timestamps for progress
--sampling-rate=NUM fraction of transactions to log (e.g., 0.01 for 1%)
Common options:
-d, --debug print debugging output
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=USERNAME connect as specified database user
-V, --version output version information, then exit
-?, --help show this help, then exit
1、环境准备
1.1、创建测试用户
psql -U postgres -p 5432
》CREATE ROLE pgbench LOGIN
ENCRYPTED PASSword 'pgbench'
SUPERUSER INHERIT CREATEDB NOCREATEROLE REPLICATION;
1.2、创建测试表空间
CREATE TABLESPACE pgbench_tbs
OWNER pgbench
LOCATION '/data/pgdata/pgbench';
1.3、创建测试库pg_bench
CREATE DATABASE pgbench
WITH OWNER = pgbench
ENCODING = 'UTF8'
TABLESPACE = pgbench_tbs;
2、初始化环境
使用命令:pgbench -i [ other-options ] dbname
创建测试表,并填充数据5000w笔记录
pgbench -p 5432 -U postgres -i -F 100 -s 500 pgbench
3、默认脚本压力测试
pg自带压测脚本分别执行了insert,update,select,可有效测试数据库吞吐能力,和并发效率
3.1、单个session压测20s
pgbench -p 5432 -U pgbench -c 1 -T 20 -r pgbench
3.2、30个session压测20s
pgbench -p 5432 -U pgbench -c 30 -T 20 -r pgbench