签到成功

知道了

CNDBA社区CNDBA社区

PostgreSQL 学习笔记（18） -- PG 性能压测工具 sysbench 使用说明

2022-12-16 22:08 2141 0 原创 PostgreSQL
作者： dave
本文链接：http://www.cndba.cn/dave/article/116397

sysbench是一款开源的多线程性能压测工具，可以对CPU、内存、线程、IO、数据库等进行性能压力测试。

在之前的博客我们看了使用sysbench 对mongodb 和 TDSQL 的压测，如下：

MongoDB 性能测试工具 sysbench-mongodb
https://www.cndba.cn/dave/article/107997

TDSQL 集群 使用 sysbench 进行性能压测 使用案例
https://www.cndba.cn/dave/article/4628

本篇我们看下sysbench 对postgresql 的压测。

http://www.cndba.cn/dave/article/116397

1 Sysbench 安装

我们这里采用源码安装。

安装依赖包：

[dave@www.cndba.cn ~]# yum -y install make automake libtool pkgconfig libaio-develhttp://www.cndba.cn/dave/article/116397

Git 克隆慢，直接从github上下载zip包在解压缩：

http://www.cndba.cn/dave/article/116397

https://codeload.github.com/akopytov/sysbench/zip/refs/heads/master

http://www.cndba.cn/dave/article/116397
http://www.cndba.cn/dave/article/116397

[dave@www.cndba.cn postgres]# pwd
/data/postgres
[dave@www.cndba.cn postgres]# ls
data  sysbench-master.zip
[dave@www.cndba.cn postgres]# unzip sysbench-master.zip
[dave@www.cndba.cn postgres]# cd sysbench-master/
[dave@www.cndba.cn sysbench-master]# ./autogen.sh
[dave@www.cndba.cn sysbench-master]# ./configure --with-pgsql --with-pgsql-includes=/usr/pgsql-14/include --with-pgsql-libs=/usr/pgsql-14/lib --without-mysql
[dave@www.cndba.cn sysbench-master]# make && make install

这里可能会报如下错误：http://www.cndba.cn/dave/article/116397

http://www.cndba.cn/dave/article/116397

PostgreSQL 编译 sysbench 报fatal error: libpq-fe.h 错误解决方法
https://www.cndba.cn/dave/article/116396

http://www.cndba.cn/dave/article/116397

查看sysbench版本

[dave@www.cndba.cn sysbench-master]# sysbench --version
sysbench 1.1.0
[dave@www.cndba.cn sysbench-master]#

查看语法：

[dave@www.cndba.cn sysbench-master]# sysbench --help
Usage:
  sysbench [options]... [testname] [command]

Commands implemented by most tests: prepare run cleanup help

General options:
  --threads=N                     number of threads to use [1]
  --events=N                      limit for total number of events [0]
  --time=N                        limit for total execution time in seconds [10]
  --warmup-time=N                 execute events for this many seconds with statistics disabled before the actual benchmark run with statistics enabled [0]
  --forced-shutdown=STRING        number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
  --thread-stack-size=SIZE        size of stack per thread [64K]
  --thread-init-timeout=N         wait time in seconds for worker threads to initialize [30]
  --rate=N                        average transactions rate. 0 for unlimited rate [0]
  --report-interval=N             periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
  --report-checkpoints=[LIST,...] dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []
  --debug[=on|off]                print more debugging info [off]
  --validate[=on|off]             perform validation checks where possible [off]
  --help[=on|off]                 print help and exit [off]
  --version[=on|off]              print version and exit [off]
  --config-file=FILENAME          File containing command line options
  --luajit-cmd=STRING             perform LuaJIT control command. This option is equivalent to 'luajit -j'. See LuaJIT documentation for more information

Pseudo-Random Numbers Generator options:
  --rand-type=STRING   random numbers distribution {uniform, gaussian, pareto, zipfian} to use by default [uniform]
  --rand-seed=N        seed for random number generator. When 0, the current time is used as an RNG seed. [0]
  --rand-pareto-h=N    shape parameter for the Pareto distribution [0.2]
  --rand-zipfian-exp=N shape parameter (exponent, theta) for the Zipfian distribution [0.8]

Log options:
  --verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]

  --percentile=N       percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
  --histogram[=on|off] print latency histogram in report [off]

General database options:

  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers)
  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
  --db-debug[=on|off] print database-specific debug information [off]


Compiled-in database drivers:
  pgsql - PostgreSQL driver

pgsql options:
  --pgsql-host=STRING     PostgreSQL server host [localhost]
  --pgsql-port=N          PostgreSQL server port [5432]
  --pgsql-user=STRING     PostgreSQL user [sbtest]
  --pgsql-password=STRING PostgreSQL password []
  --pgsql-db=STRING       PostgreSQL database name [sbtest]
  --pgsql-sslmode=STRING  PostgreSQL SSL mode (disable, allow, prefer, require, verify-ca, verify-full) [prefer]

Compiled-in tests:
  fileio - File I/O test
  cpu - CPU performance test
  memory - Memory functions speed test
  threads - Threads subsystem performance test
  mutex - Mutex performance test

See 'sysbench <testname> help' for a list of options for each test.

[dave@www.cndba.cn sysbench-master]#

2 使用示例

2.1 选择压测脚本

安装目录的src/lua 下有多种不同类型的压测脚本，可以根据需要选择：

[dave@www.cndba.cn lua]# pwd
/data/postgres/sysbench-master/src/lua
[dave@www.cndba.cn lua]# ll
total 132
-rwxr-xr-x. 1 root root  1446 Dec  9  2021 bulk_insert.lua
-rw-r--r--. 1 root root  1307 Dec  9  2021 empty-test.lua
drwxr-xr-x. 2 root root  4096 Dec 16 21:02 internal
-rw-r--r--. 1 root root 25493 Dec 16 20:41 Makefile
-rw-r--r--. 1 root root  1219 Dec  9  2021 Makefile.am
-rw-r--r--. 1 root root 24760 Dec 16 20:37 Makefile.in
-rw-r--r--. 1 root root 14878 Dec  9  2021 oltp_common.lua
-rwxr-xr-x. 1 root root  1312 Dec  9  2021 oltp_delete.lua
-rwxr-xr-x. 1 root root  2437 Dec  9  2021 oltp_insert.lua
-rwxr-xr-x. 1 root root  1287 Dec  9  2021 oltp_point_select.lua
-rwxr-xr-x. 1 root root  1671 Dec  9  2021 oltp_read_only.lua
-rwxr-xr-x. 1 root root  1846 Dec  9  2021 oltp_read_write.lua
-rwxr-xr-x. 1 root root  1139 Dec  9  2021 oltp_update_index.lua
-rwxr-xr-x. 1 root root  1149 Dec  9  2021 oltp_update_non_index.lua
-rwxr-xr-x. 1 root root  1462 Dec  9  2021 oltp_write_only.lua
-rw-r--r--. 1 root root  1631 Dec  9  2021 prime-test.lua
-rwxr-xr-x. 1 root root  1955 Dec  9  2021 select_random_points.lua
-rwxr-xr-x. 1 root root  2154 Dec  9  2021 select_random_ranges.lua
[dave@www.cndba.cn lua]#

每个脚本的使用可以查看help：http://www.cndba.cn/dave/article/116397

[dave@www.cndba.cn lua]# sysbench oltp_common.lua help
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

oltp_common.lua options:
  --auto_inc[=on|off]           Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]
  --create_secondary[=on|off]   Create a secondary index in addition to the PRIMARY KEY [on]
  --create_table_options=STRING Extra CREATE TABLE options []
  --delete_inserts=N            Number of DELETE/INSERT combinations per transaction [1]
  --distinct_ranges=N           Number of SELECT DISTINCT queries per transaction [1]
  --index_updates=N             Number of UPDATE index queries per transaction [1]
  --mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb]
  --non_index_updates=N         Number of UPDATE non-index queries per transaction [1]
  --order_ranges=N              Number of SELECT ORDER BY queries per transaction [1]
  --pgsql_variant=STRING        Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is 'redshift'. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0
  --point_selects=N             Number of point SELECT queries per transaction [10]
  --range_selects[=on|off]      Enable/disable all range SELECT queries [on]
  --range_size=N                Range size for range SELECT queries [100]
  --reconnect=N                 Reconnect after every N events. The default (0) is to not reconnect [0]
  --secondary[=on|off]          Use a secondary index in place of the PRIMARY KEY [off]
  --simple_ranges=N             Number of simple range SELECT queries per transaction [1]
  --skip_trx[=on|off]           Don't start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
  --sum_ranges=N                Number of SELECT SUM() queries per transaction [1]
  --table_size=N                Number of rows per table [10000]
  --tables=N                    Number of tables [1]

[dave@www.cndba.cn lua]#

2.2 性能压测

sysbench的压测的每个lua脚本都有三个步骤：

http://www.cndba.cn/dave/article/116397

  1. prepare
  2. run
  3. cleanup

prepare是为测试提前准备数据，run是执行正式的测试，cleanup是在测试完成后对数据库进行清理。

我们这里以oltp_common.lua 脚本为例进行演示。

2.2.1 加载数据（prepare）

使用sysben压入测试数据：我们这里在cndba库中创建10张表，每张表10000条记录。

[dave@www.cndba.cn yum.repos.d]# su - postgres
Last login: Fri Dec 16 14:45:26 CST 2022 on pts/0
-bash-4.2$ psql -h localhost
psql (14.6)
Type "help" for help.

postgres=# /l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 cndba     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 dave      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 ustc      | cndba    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)

postgres=#



[dave@www.cndba.cn lua]# sysbench /data/postgres/sysbench-master/src/lua/oltp_common.lua --db-driver=pgsql --pgsql-host=127.0.0.1  --pgsql-port=5432  --pgsql-user=postgres  --pgsql-password=postgres  --pgsql-db=cndba  --tables=10  --table-size=10000 --threads=10 prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest8'...
Creating table 'sbtest3'...
Creating table 'sbtest7'...
Creating table 'sbtest1'...
Creating table 'sbtest2'...
Creating table 'sbtest9'...
Creating table 'sbtest4'...
Creating table 'sbtest6'...
Creating table 'sbtest10'...
Creating table 'sbtest5'...
Inserting 10000 records into 'sbtest6'
Inserting 10000 records into 'sbtest3'
Inserting 10000 records into 'sbtest1'
Inserting 10000 records into 'sbtest9'
Inserting 10000 records into 'sbtest10'
Inserting 10000 records into 'sbtest5'
Inserting 10000 records into 'sbtest2'
Inserting 10000 records into 'sbtest4'
Inserting 10000 records into 'sbtest8'
Inserting 10000 records into 'sbtest7'
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest9'...
[dave@www.cndba.cn lua]#

2.2.2 压测（run）

这里可以根据需要选择不同的lua脚本，使用不同的参数：

[dave@www.cndba.cn lua]# sysbench oltp_read_write.lua --db-driver=pgsql --pgsql-host=127.0.0.1  --pgsql-port=5432  --pgsql-user=postgres  --pgsql-password=postgres  --pgsql-db=cndba  --tables=10  --table-size=10000 --threads=10  --report-interval=5 --time=20 run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 10
Report intermediate results every 5 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 5s ] thds: 10 tps: 434.99 qps: 8722.43 (r/w/o: 6110.48/1739.97/871.98) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 10 tps: 444.20 qps: 8889.30 (r/w/o: 6222.67/1778.22/888.41) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 10 tps: 318.58 qps: 6374.11 (r/w/o: 4461.46/1275.50/637.15) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 10 tps: 342.44 qps: 6844.04 (r/w/o: 4790.39/1368.77/684.88) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            107968
        write:                           30848
        other:                           15424
        total:                           154240
    transactions:                        7712   (384.67 per sec.)
    queries:                             154240 (7693.43 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      384.6713
    time elapsed:                        20.0483s
    total number of events:              7712

Latency (ms):
         min:                                    6.92
         avg:                                   25.97
         max:                                  134.12
         95th percentile:                       35.59
         sum:                               200246.45

Threads fairness:
    events (avg/stddev):           771.2000/3.63
    execution time (avg/stddev):   20.0246/0.01

[dave@www.cndba.cn lua]#

2.2.3 删除压测数据（cleanup）

[dave@www.cndba.cn lua]# sysbench oltp_read_write.lua --db-driver=pgsql --pgsql-host=127.0.0.1  --pgsql-port=5432  --pgsql-user=postgres  --pgsql-password=postgres  --pgsql-db=cndba  --tables=10  --table-size=10000 --threads=10  --report-interval=5 --time=20 cleanup
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...
[dave@www.cndba.cn lua]#

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2261
    原创
  • 3
    翻译
  • 578
    转载
  • 191
    评论
  • 访问：7971694次
  • 积分：4346
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ