- 什么是 pgbench
- 宿主host连接docker的postgres
- pgbench 测试
- pgbench 初始化
- pgbench 测试样例
- pgbench good practices
- pgbench-tools 测试
Postgresql pgbench Study 006 Tips
什么是 pgbench
pgbench是基于tpc-b模型的postgresql测试工具。它属于开源软件，主要为对 PostgreSQL 进行压力测试的一款简单程序, SQL命令可以在一个连接中顺序地执行，通常会开多个数据库 Session, 并且在测试最后形成测试报告，得出每秒平均事务数，pgbench可以测试 select,update,insert,delete 命令，用户可以编写自己的脚本进行测试。
-
TPC：Transactionprocessing Performance Council 事务处理性能委员会
-
TPC-B:TPC-B measures throughput in terms of how many transactions per second a system canperform / 测试一秒可以处理的事务个数 事务：在这个文档中的事务并不是指的数据库层面上的事务，而是指一系列SQL语句。
宿主host连接docker的postgres
测试:
宿主host: centos7_pg (也安装了postgres，所以也有psql客户端)
docker: pgtest01
端口: 宿主5321 <-> docker的postgres端口 映射
[root@centos7_pg ~]# docker exec -it pgtest01 ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 172.17.0.2 netmask 255.255.0.0 broadcast 172.17.255.255
ether 02:42:ac:11:00:02 txqueuelen 0 (Ethernet)
RX packets 31 bytes 2710 (2.6 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 20 bytes 1736 (1.6 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
loop txqueuelen 1000 (Local Loopback)
RX packets 1153 bytes 400117 (390.7 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 1153 bytes 400117 (390.7 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
[root@centos7_pg ~]# netstat -nalp | grep 543
tcp6 0 0 :::5431 :::* LISTEN 2228/docker-proxy
[root@centos7_pg ~]# telnet 172.17.0.2 5431
-bash: telnet: 未找到命令
[root@centos7_pg ~]# yum install telnet -y
已加载插件：fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
~省略~
[root@centos7_pg ~]#su - postgres
[postgres@centos7_pg ~]$ telnet 172.17.0.2 5432
Trying 172.17.0.2...
Connected to 172.17.0.2.
Escape character is '^]'.
^CConnection closed by foreign host.
[postgres@centos7_pg ~]$ psql -h 172.17.0.2 -p 5432 -U postgres -d postgres
Password for user postgres:
You are connected to database "postgres" as user "postgres" on host "172.17.0.2" at port "5432".
psql (10.11, server 10.12)
Type "help" for help.
postgres@postgres=> \db
List of tablespaces
Name | Owner | Location
------------+----------+-------------------
pg_default | postgres |
pg_global | postgres |
tab_01 | postgres | /pgdata/10/tab_01
(3 rows)
postgres@postgres=> \q
[postgres@centos7_pg ~]$ psql -h 127.0.0.1 -p 5431 -U postgres -d postgres
Password for user postgres:
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5431".
psql (10.11, server 10.12)
Type "help" for help.
postgres@postgres=> \db
List of tablespaces
Name | Owner | Location
------------+----------+-------------------
pg_default | postgres |
pg_global | postgres |
tab_01 | postgres | /pgdata/10/tab_01
(3 rows)
postgres@postgres=>
另外postgres用户名密码可以通过下面命令重置
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=#
pgbench 测试
创建 pgbench_test 数据库并测试连接
postgres@postgres=> \db
List of tablespaces
Name | Owner | Location
------------+----------+-------------------
pg_default | postgres |
pg_global | postgres |
tab_01 | postgres | /pgdata/10/tab_01
(3 rows)
postgres@postgres=> create database pgbench_test tablespace tab_01;
CREATE DATABASE
postgres@postgres=> \d
Did not find any relations.
postgres@postgres=> select datname from pg_database;
datname
--------------
postgres
template1
template0
pgbench_test
(4 rows)
postgres@pgbench_test=> \q
[postgres@centos7_pg ~]$ psql -h 172.17.0.2 -p 5432 -U postgres -d pgbench_test
Password for user postgres:
You are connected to database "pgbench_test" as user "postgres" on host "172.17.0.2" at port "5432".
psql (10.11, server 10.12)
Type "help" for help.
postgres@pgbench_test=>
pgbench 初始化
[postgres@centos7_pg ~]$ pgbench --help
pgbench is a benchmarking tool for PostgreSQL.
Usage:
pgbench [OPTION]... [DBNAME]
Initialization options:
-i, --initialize invokes initialization mode
-F, --fillfactor=NUM set fill factor
-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
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)
-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
-R, --rate=NUM target rate in transactions per second
-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
--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
Report bugs to <pgsql-bugs@postgresql.org>.
[postgres@centos7_pg ~]$
初始化数据库中的测试数据，使用pgbench -i进入初始化模式。
pgbench -i --unlogged-tables -s 1 -U postgres -p 5432 -d pgbench_test
以上参数中：
-i表示初始化模式，
–unlogged-tables表示创建没有log的表，
-s 1表示默认的数据的几倍，默认是100000条数据，其他是postgres连接的参数。每次测试默认会清除之前的表，创建完之后可以发现，pgbench_accounts表中有了100000条数据
Initialization options: # 初始化选项
-i, --initialize invokes initialization mode # 初始化数据库
-F, --fillfactor=NUM set fill factor # 设定填充因子
-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类型的表
[postgres@centos7_pg ~]$ time pgbench -i --unlogged-tables -s 1 -h 172.17.0.2 -p 5432 -U postgres -d pgbench_test
Password:
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.12 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
real 0m4.752s
user 0m0.047s
sys 0m0.003s
[postgres@centos7_pg ~]$
[postgres@centos7_pg ~]$ psql -h 172.17.0.2 -p 5432 -U postgres -d pgbench_test
Password for user postgres:
You are connected to database "pgbench_test" as user "postgres" on host "172.17.0.2" at port "5432".
psql (10.11, server 10.12)
Type "help" for help.
postgres@pgbench_test=> \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres
(4 rows)
postgres@pgbench_test=> select count(*) from pgbench_accounts;
count
--------
100000
(1 row)
postgres@pgbench_test=> select count(*) from pgbench_branches;
count
-------
1
(1 row)
postgres@pgbench_test=> select count(*) from pgbench_history;
count
-------
0
(1 row)
postgres@pgbench_test=> select count(*) from pgbench_tellers;
count
-------
10
(1 row)
postgres@pgbench_test=>
压测可以使用 -b 来指定具体的场景:
-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) # pgbench工作线程数
-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 # 测试前vacuum表
--aggregate-interval=NUM aggregate data over NUM seconds # 每隔N秒聚合一次数据
示例:
pgbench -b simple-update@2 -b select-only@7 -b tpcb-like@1
[postgres@centos7_pg ~]$ pgbench -b list
Available builtin scripts:
tpcb-like
simple-update
select-only
[postgres@centos7_pg ~]$
如果用编译方式编译了postgres 可以在下面源代码文件中查看：
src/bin/pgbench/pgbench.c 具体描述:
[postgres@centos7_pg postgresql-10.11]$ pwd
/data/postgresql-10.11
[postgres@centos7_pg postgresql-10.11]$ ls -ltr
total 1188
-rw-r--r--. 1 postgres postgres 1212 Nov 12 06:07 README
-rw-r--r--. 1 postgres postgres 1665 Nov 12 06:07 Makefile
-rw-r--r--. 1 postgres postgres 284 Nov 12 06:07 HISTORY
-rw-r--r--. 1 postgres postgres 3822 Nov 12 06:07 GNUmakefile.in
-rw-r--r--. 1 postgres postgres 1192 Nov 12 06:07 COPYRIGHT
-rw-r--r--. 1 postgres postgres 79645 Nov 12 06:07 configure.in
-rwxr-xr-x. 1 postgres postgres 507965 Nov 12 06:07 configure
-rw-r--r--. 1 postgres postgres 493 Nov 12 06:07 aclocal.m4
drwxrwxrwx. 55 postgres postgres 4096 Nov 12 06:20 contrib
drwxrwxrwx. 3 postgres postgres 107 Nov 12 06:20 doc
drwxrwxrwx. 2 postgres postgres 4096 Nov 12 06:20 config
-rw-r--r--. 1 postgres postgres 73124 Nov 12 06:21 INSTALL
-rwxr-xr-x. 1 postgres postgres 42490 Feb 3 17:25 config.status
-rw-r--r--. 1 postgres postgres 3822 Feb 3 17:25 GNUmakefile
drwxrwxrwx. 16 postgres postgres 4096 Feb 3 17:25 src
-rw-r--r--. 1 postgres postgres 460093 Feb 3 17:25 config.log
[postgres@centos7_pg postgresql-10.11]$ cat src/bin/pgbench/pgbench.c
/*
* pgbench.c
*
* A simple benchmark program for PostgreSQL
* Originally written by Tatsuo Ishii and enhanced by many contributors.
*
* src/bin/pgbench/pgbench.c
* Copyright (c) 2000-2017, PostgreSQL Global Development Group
* ALL RIGHTS RESERVED;
*
* Permission to use, copy, modify, and distribute this software and its
* documentation for any purpose, without fee, and without a written agreement
* is hereby granted, provided that the above copyright notice and this
* paragraph and the following two paragraphs appear in all copies.
*
* IN NO EVENT SHALL THE AUTHOR OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
* DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
* LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
* DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* THE AUTHOR AND DISTRIBUTORS SPECIFICALLY DISCLAIMS ANY WARRANTIES,
* INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
* AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
* ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
* PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
*
*/
build 具体内容:
- 通过 vi
src/bin/pgbench/pgbench.c-> set nu 来查看BuiltinScript模块
410 static const BuiltinScript builtin_script[] =
411 {
412 {
413 "tpcb-like",
414 "<builtin: TPC-B (sort of)>",
415 "\\set aid random(1, " CppAsString2(naccounts) " * :scale)\n"
416 "\\set bid random(1, " CppAsString2(nbranches) " * :scale)\n"
417 "\\set tid random(1, " CppAsString2(ntellers) " * :scale)\n"
418 "\\set delta random(-5000, 5000)\n"
419 "BEGIN;\n"
420 "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
421 "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
422 "UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;\n"
423 "UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;\n"
424 "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
425 "END;\n"
426 },
427 {
428 "simple-update",
429 "<builtin: simple update>",
430 "\\set aid random(1, " CppAsString2(naccounts) " * :scale)\n"
431 "\\set bid random(1, " CppAsString2(nbranches) " * :scale)\n"
432 "\\set tid random(1, " CppAsString2(ntellers) " * :scale)\n"
433 "\\set delta random(-5000, 5000)\n"
434 "BEGIN;\n"
435 "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
436 "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
437 "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
438 "END;\n"
439 },
440 {
441 "select-only",
442 "<builtin: select only>",
443 "\\set aid random(1, " CppAsString2(naccounts) " * :scale)\n"
444 "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
445 }
446 };
可以参考上面写法自定义sql语句来做压力测试,如：tpcb-like
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;
pgbench 测试样例
样例命令:
time pgbench -h 172.17.0.2 -p 5432 -d pgbench_test -U postgres -c 4 -j 2 -T 60 -N
-h 主机名 172.17.0.2 -p 端口 5432 -d 数据库名 pgbench_test -U 连接用户 postgres -c 启动客户端连接 4 -j pgbench的工作线程数 2 -T 执行多久 60(秒) -N 跳过更新pgbench_tellers和pgbench_branches
- 最后结果如下
处理了 68964 的事务 tps大概是 1149
transaction type: <builtin: simple update>
scaling factor: 3
query mode: simple
number of clients: 4
number of threads: 2
duration: 60 s
number of transactions actually processed: 68964
latency average = 3.481 ms
tps = 1149.212946 (including connections establishing)
tps = 1149.314486 (excluding connections establishing)
real 1m2.499s
user 0m3.805s
sys 0m8.594s
[postgres@centos7_pg ~]$
测试过程中top情况
- Top 情况 (CPU资源消耗比较多)
top - 07:53:09 up 5:55, 0 users, load average: 1.69, 0.43, 0.18
Tasks: 18 total, 5 running, 13 sleeping, 0 stopped, 0 zombie
%Cpu(s): 40.1 us, 48.5 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 11.4 si, 0.0 st
KiB Mem : 1882152 total, 937620 free, 200496 used, 744036 buff/cache
KiB Swap: 1572860 total, 1572860 free, 0 used. 1441992 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
775 postgres 20 0 297120 52232 50072 R 14.7 2.8 0:04.22 postgres
773 postgres 20 0 297120 52268 50108 R 14.3 2.8 0:04.21 postgres
774 postgres 20 0 297120 52220 50048 R 14.3 2.8 0:04.22 postgres
776 postgres 20 0 297120 52192 50020 R 14.3 2.8 0:04.26 postgres
29 postgres 20 0 296040 6344 4916 S 3.0 0.3 0:01.11 postgres
1 root 20 0 15116 1524 1312 S 0.0 0.1 0:00.02 docker-entrypoi
11 root 20 0 85404 2176 1640 S 0.0 0.1 0:00.00 su
12 postgres 20 0 296040 17008 15596 S 0.0 0.9 0:00.20 postgres
25 postgres 20 0 148844 1916 528 S 0.0 0.1 0:00.00 postgres
27 postgres 20 0 296184 4680 3172 S 0.0 0.2 0:00.02 postgres
28 postgres 20 0 296040 3444 2012 S 0.0 0.2 0:00.26 postgres
30 postgres 20 0 296588 3164 1428 S 0.0 0.2 0:00.16 postgres
31 postgres 20 0 151100 2184 680 S 0.0 0.1 0:00.26 postgres
32 postgres 20 0 296336 2536 968 S 0.0 0.1 0:00.00 postgres
575 root 20 0 15252 2044 1656 S 0.0 0.1 0:00.03 bash
597 root 20 0 85408 2192 1644 S 0.0 0.1 0:00.00 su
598 postgres 20 0 15252 2024 1648 S 0.0 0.1 0:00.01 bash
778 postgres 20 0 59596 2088 1524 R 0.0 0.1 0:00.00 top
- 前10的top进程
-bash-4.2$ ps -aux --sort=-pcpu|head -10
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 773 14.4 3.0 297120 56756 ? Ss 07:52 0:07 postgres: postgres pgbench_test 172.17.0.1(35194) idle in transaction
postgres 774 14.4 3.0 297120 56708 ? Ss 07:52 0:07 postgres: postgres pgbench_test 172.17.0.1(35196) idle in transaction
postgres 776 14.4 3.0 297120 56944 ? Ss 07:52 0:07 postgres: postgres pgbench_test 172.17.0.1(35200) idle in transaction
postgres 775 14.2 3.0 297120 56984 ? Ss 07:52 0:07 postgres: postgres pgbench_test 172.17.0.1(35198) idle in transaction
root 1 0.0 0.0 15116 1524 ? Ss 02:18 0:00 /bin/bash /usr/local/bin/docker-entrypoint.sh postgres
root 11 0.0 0.1 85404 2176 ? S 02:18 0:00 su - postgres -c /usr/pgsql-10/bin/postgres -D /var/lib/pgsql/10/data -c config_file=/var/lib/pgsql/10/data/postgresql.conf
postgres 12 0.0 0.9 296040 17008 ? Ss 02:18 0:00 /usr/pgsql-10/bin/postgres -D /var/lib/pgsql/10/data -c config_file=/var/lib/pgsql/10/data/postgresql.conf
postgres 25 0.0 0.1 148844 1916 ? Ss 02:18 0:00 postgres: logger process
postgres 27 0.0 0.2 296184 4680 ? Ss 02:18 0:00 postgres: checkpointer process
-bash-4.2$
pgbench good practices
It is very easy to use pgbench to produce completely meaningless numbers. Here are some guidelines to help you get useful results.
In the first place, never believe any test that runs for only a few seconds. Use the -t or -T option to make the run last at least a few minutes, so as to average out noise. In some cases you could need hours to get numbers that are reproducible. It's a good idea to try the test run a few times, to find out if your numbers are reproducible or not.
For the default TPC-B-like test scenario, the initialization scale factor (-s) should be at least as large as the largest number of clients you intend to test (-c); else you'll mostly be measuring update contention. There are only -s rows in the pgbench_branches table, and every transaction wants to update one of them, so -c values in excess of -s will undoubtedly result in lots of transactions blocked waiting for other transactions.
The default test scenario is also quite sensitive to how long it's been since the tables were initialized: accumulation of dead rows and dead space in the tables changes the results. To understand the results you must keep track of the total number of updates and when vacuuming happens. If autovacuum is enabled it can result in unpredictable changes in measured performance.
A limitation of pgbench is that it can itself become the bottleneck when trying to test a large number of client sessions. This can be alleviated by running pgbench on a different machine from the database server, although low network latency will be essential. It might even be useful to run several pgbench instances concurrently, on several client machines, against the same database server.
pgbench-tools 测试
参考如下:
PostgreSQL Benchmarking Toolset
- 创建测试库和报告库
-bash-4.2$ psql -c "select datname from pg_database;"
datname
--------------
postgres
template1
template0
pgbench_test
(4 rows)
-bash-4.2$ createdb pgbench;
-bash-4.2$ createdb results;
-bash-4.2$ psql -c "select datname from pg_database;"
datname
--------------
postgres
template1
template0
pgbench_test
pgbench
results
(6 rows)
-bash-4.2$
-
docker 环境里面用
root用户安装git -
然后用
postgres用户 下载pgbench-tools使用
-bash-4.2$ which git
/usr/bin/which: no git in (/usr/pgsql-10/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin)
-bash-4.2$ yum install git
Loaded plugins: fastestmirror, ovl
ovl: Error while doing RPMdb copy-up:
[Errno 13] Permission denied: '/var/lib/rpm/.dbenv.lock'
You need to be root to perform this command.
-bash-4.2$ exit
logout
[root@0b35109c39c9 /]# yum install git -y
~省略~
Installed:
git.x86_64 0:1.8.3.1-21.el7_7
Dependency Installed:
fipscheck.x86_64 0:1.4.1-6.el7 fipscheck-lib.x86_64 0:1.4.1-6.el7 less.x86_64 0:458-9.el7 libedit.x86_64 0:3.0-12.20121213cvs.el7 openssh.x86_64 0:7.4p1-21.el7
openssh-clients.x86_64 0:7.4p1-21.el7 perl-Error.noarch 1:0.17020-2.el7 perl-Git.noarch 0:1.8.3.1-21.el7_7 perl-TermReadKey.x86_64 0:2.30-20.el7 rsync.x86_64 0:3.1.2-6.el7_6.1
Complete!
[root@0b35109c39c9 ~]# su - postgres
Last login: Fri Apr 10 06:44:36 UTC 2020 on pts/0
-bash-4.2$ mkdir pgbench-tools
-bash-4.2$ cd pgbench-tools/
-bash-4.2$ git clone https://github.com/gregs1104/pgbench-tools.git
Cloning into 'pgbench-tools'...
remote: Enumerating objects: 39, done.
remote: Counting objects: 100% (39/39), done.
remote: Compressing objects: 100% (30/30), done.
remote: Total 573 (delta 11), reused 32 (delta 9), pack-reused 534
Receiving objects: 100% (573/573), 176.30 KiB | 33.00 KiB/s, done.
Resolving deltas: 100% (304/304), done.
-bash-4.2$
-bash-4.2$ which git
/usr/bin/git
-bash-4.2$
也需要安装
[root@0b35109c39c9 ~]# yum install gnuplot -y
- 初始化
-bash-4.2$ ls -tlr
total 164
-rw-r--r-- 1 postgres postgres 12754 Apr 10 08:58 README.md
-rw-r--r-- 1 postgres postgres 2612 Apr 10 08:58 HISTORY
-rw-r--r-- 1 postgres postgres 1482 Apr 10 08:58 COPYRIGHT
-rwxr-xr-x 1 postgres postgres 396 Apr 10 08:58 log-to-csv
-rw-r--r-- 1 postgres postgres 239 Apr 10 08:58 list_orderbytest
-rw-r--r-- 1 postgres postgres 11333 Apr 10 08:58 limited_webreport
-rw-r--r-- 1 postgres postgres 237 Apr 10 08:58 latest_set
-rwxr-xr-x 1 postgres postgres 555 Apr 10 08:58 kill_pg
drwxr-xr-x 2 postgres postgres 48 Apr 10 08:58 init
-rwxr-xr-x 1 postgres postgres 276 Apr 10 08:58 dirty-plot
-rwxr-xr-x 1 postgres postgres 16158 Apr 10 08:58 csv2gnuplot
-rw-r--r-- 1 postgres postgres 1766 Apr 10 08:58 config
-rw-r--r-- 1 postgres postgres 233 Apr 10 08:58 cleanup_singlevalue
-rw-r--r-- 1 postgres postgres 235 Apr 10 08:58 cleanup_fromvalue
-rw-r--r-- 1 postgres postgres 141 Apr 10 08:58 cleanup
-rwxr-xr-x 1 postgres postgres 16374 Apr 10 08:58 benchwarmer
drwxr-xr-x 2 postgres postgres 117 Apr 10 08:58 regress
-rw-r--r-- 1 postgres postgres 16491 Apr 10 08:58 rates_webreport
drwxr-xr-x 2 postgres postgres 284 Apr 10 08:58 plots
-rwxr-xr-x 1 postgres postgres 430 Apr 10 08:58 newset
drwxr-xr-x 2 postgres postgres 93 Apr 10 08:58 templates
-rwxr-xr-x 1 postgres postgres 228 Apr 10 08:58 summary
-rwxr-xr-x 1 postgres postgres 390 Apr 10 08:58 set-times
-rwxr-xr-x 1 postgres postgres 1986 Apr 10 08:58 runset
drwxr-xr-x 2 postgres postgres 4096 Apr 10 08:58 reports
-rwxr-xr-x 1 postgres postgres 8424 Apr 10 08:58 webreport
-rwxr-xr-x 1 postgres postgres 5574 Apr 10 08:58 timed-os-stats
drwxr-xr-x 4 postgres postgres 154 Apr 10 08:58 tests
drwxr-xr-x 11 postgres postgres 87 Apr 10 09:13 results
-bash-4.2$ psql -f init/resultdb.sql -d results
BEGIN
psql:init/resultdb.sql:3: NOTICE: table "testset" does not exist, skipping
DROP TABLE
CREATE TABLE
psql:init/resultdb.sql:9: NOTICE: table "tests" does not exist, skipping
DROP TABLE
CREATE TABLE
psql:init/resultdb.sql:30: NOTICE: table "timing" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE INDEX
psql:init/resultdb.sql:41: NOTICE: table "test_bgwriter" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE FUNCTION
CREATE FUNCTION
COMMIT
-bash-4.2$
- 创建测试结果集
./newset 'Initial Config' - 运行测试
可编辑配置文件config，添加需要测试的用例，默认测试用例是运行一个时长1分钟的只有SELECT功能
运行结果保存在results目录下
./runset
测试用例:
-bash-4.2$ ./runset
VACUUM
Creating new pgbench tables
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.09 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
Run set #1 of 3 with 1 clients scale=1
Running tests using: psql -h localhost -U postgres -p 5432 -d pgbench
Storing results using: psql -h localhost -U postgres -p 5432 -d results
Found standard pgbench tables with prefix=pgbench_
Cleaning up database pgbench
TRUNCATE TABLE
VACUUM
CHECKPOINT
Waiting for checkpoint statistics
INSERT 0 1
This is test 1
Script select.sql executing for 1 concurrent users...
~省略~
- 在另外一个窗口可以并行看测试进度
psql -d results -f reports/report.sql
示例:
-bash-4.2$ cd pgbench-tools/
-bash-4.2$ psql -d results -f reports/report.sql
set | scale | test | script | clients | workers | limit | tps | avg_latency | 90%< | max_latency | trans
-----+-------+------+------------+---------+---------+-------+------+-------------+-------+-------------+--------
1 | 1 | 1 | select.sql | 1 | 1 | | 8419 | 0.119 | 0.151 | 12 | 505117
1 | 1 | 2 | select.sql | 2 | 1 | | 8198 | 0.244 | 0.314 | 27 | 491894
1 | 1 | 3 | select.sql | 4 | 1 | | 0 | | | |
(3 rows)
-bash-4.2$ psql -d results -f reports/fastest.sql
set | script | scale | clients | workers | tps
-----+------------+-------+---------+---------+------
1 | select.sql | 1 | 4 | 1 | 8629
1 | select.sql | 1 | 1 | 1 | 8419
1 | select.sql | 1 | 2 | 1 | 8198
1 | select.sql | 1 | 8 | 1 | 0
(4 rows)
-bash-4.2$ psql -d results -f reports/summary.sql
set | scale | db_size | clients | rate_limit | tps | avg_latency | max_latency | 90%< | runtime
-----+-------+---------+---------+------------+------+-------------+-------------+-------+----------
1 | 1 | 23 MB | 1 | | 8419 | 0.119 | 11.997 | 0.151 | 00:01:00
1 | 1 | 23 MB | 2 | | 8198 | 0.244 | 27.231 | 0.314 | 00:01:00
1 | 1 | 23 MB | 4 | | 8629 | 0.463 | 53.894 | 0.633 | 00:01:00
1 | 1 | 23 MB | 8 | | 0 | | | |
(4 rows)
-bash-4.2$ psql -d results -f reports/bufreport.sql
set | scale | test | clients | tps | chkpts | buf_check | buf_clean | buf_backend | buf_alloc | backend_sync | max_dirty
-----+-------+------+---------+------+--------+-----------+-----------+-------------+-----------+--------------+-----------
1 | 1 | 1 | 1 | 8419 | 0 | 0 | 0 | 0 | 35 | 0 | 8421376
1 | 1 | 2 | 2 | 8198 | 0 | 0 | 0 | 0 | 3 | 0 | 8024064
1 | 1 | 3 | 4 | 8629 | 0 | 0 | 0 | 0 | 6 | 0 | 8298496
1 | 1 | 4 | 8 | 0 | 116 | 11092 | 0 | 73171 | 60028 | 0 |
(4 rows)
-bash-4.2$ psql -d results -f reports/bufsummary.sql
set | scale | clients | tps | chkpts | buf_check | buf_clean | buf_backend | buf_alloc | backend_fsync | max_dirty
-----+-------+---------+------+--------+-----------+-----------+-------------+-----------+---------------+-----------
1 | 1 | 1 | 8419 | 0 | 0 | 0 | 0 | 35 | 0 | 8421376
1 | 1 | 2 | 8198 | 0 | 0 | 0 | 0 | 3 | 0 | 8024064
1 | 1 | 4 | 8629 | 0 | 0 | 0 | 0 | 6 | 0 | 8298496
1 | 1 | 8 | 9026 | 0 | 0 | 0 | 0 | 7 | 0 | 8728576
(4 rows)
-bash-4.2$
- 跑到想要测试的并发客户端数目时候，终止清理下执行中的信息
测试终端: Ctrl + c
This is test 6
Script select.sql executing for 32 concurrent users...
^C
Run set #2 of 3 with 1 clients scale=1
^C
-bash-4.2$
查看终端:
-bash-4.2$ psql -d results -f reports/summary.sql
set | scale | db_size | clients | rate_limit | tps | avg_latency | max_latency | 90%< | runtime
-----+-------+---------+---------+------------+------+-------------+-------------+-------+----------
1 | 1 | 23 MB | 1 | | 8419 | 0.119 | 11.997 | 0.151 | 00:01:00
1 | 1 | 23 MB | 2 | | 8198 | 0.244 | 27.231 | 0.314 | 00:01:00
1 | 1 | 23 MB | 4 | | 8629 | 0.463 | 53.894 | 0.633 | 00:01:00
1 | 1 | 23 MB | 8 | | 9026 | 0.885 | 55.894 | 1.002 | 00:01:00
1 | 1 | 23 MB | 16 | | 8184 | 1.953 | 34.053 | 2.349 | 00:01:00
1 | 1 | 23 MB | 32 | | 0 | | | |
(6 rows)
-bash-4.2$
-bash-4.2$ chmod +x benchwarmer
-bash-4.2$ chmod +x cleanup
-bash-4.2$ ./cleanup
DELETE 1
-bash-4.2$ ./webreport
-bash-4.2$ psql -d results -f reports/summary.sql
set | scale | db_size | clients | rate_limit | tps | avg_latency | max_latency | 90%< | runtime
-----+-------+---------+---------+------------+------+-------------+-------------+-------+----------
1 | 1 | 23 MB | 1 | | 8419 | 0.119 | 11.997 | 0.151 | 00:01:00
1 | 1 | 23 MB | 2 | | 8198 | 0.244 | 27.231 | 0.314 | 00:01:00
1 | 1 | 23 MB | 4 | | 8629 | 0.463 | 53.894 | 0.633 | 00:01:00
1 | 1 | 23 MB | 8 | | 9026 | 0.885 | 55.894 | 1.002 | 00:01:00
1 | 1 | 23 MB | 16 | | 8184 | 1.953 | 34.053 | 2.349 | 00:01:00
(5 rows)
-bash-4.2$
- Tips: 提前安装
gnuplot
-bash-4.2$ ./newset 'Initial Config'
which: no gnuplot in (/usr/pgsql-10/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin)
INSERT 0 1
set | info
-----+----------------
1 | Initial Config
(1 row)
-bash-4.2$
Have a good work&life! 2020/09 via LinHong