PostgreSQL DBA(8) - sysbench簡(jiǎn)介

pgbench是一款開源的基準(zhǔn)測(cè)試工具，支持多線程和多種數(shù)據(jù)庫(kù)（包括MySQL、PostgreSQL等）；通過測(cè)試可衡量操作系統(tǒng)的CPU、磁盤IO、內(nèi)存、POSIX線程性能和數(shù)據(jù)庫(kù)性能。

一、安裝

下載sysbench,當(dāng)前最新版本是1.0.15

cd /tmp/install
wget https://github.com/akopytov/sysbench/archive/1.0.15.zip -O "sysbench-1.0.15.zip"
unzip sysbench-1.0.15.zip
cd sysbench-1.0.15

執(zhí)行安裝

yum install automake libtool mysql-devel postgresql-devel –y
./autogen.sh
./configure --with-pgsql --with-pgsql-includes=/appdb/atlasdb/pg11beta2/include  --with-pgsql-libs=/appdb/atlasdb/pg11beta2/lib
make
make install 

安裝成功

[root@localhost sysbench-1.0.15]# sysbench --version
sysbench 1.0.15

二、參數(shù)說明

通過sysbench --help命令可以查看詳細(xì)的參數(shù)設(shè)置.

[root@localhost include]# 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]
...  

其中:
oltp-tables-count
生成測(cè)試數(shù)據(jù)表數(shù)量

--threads=N
sysbench使用的工作線程數(shù)

--time=N
執(zhí)行時(shí)長(zhǎng),以秒為單位

--report-interval=N
報(bào)告間隔,以秒為單位

--config-file=FILENAME
配置文件

--db-driver=STRING
數(shù)據(jù)庫(kù)驅(qū)動(dòng)mysql/pgsql

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]

三、基本使用

連接數(shù)據(jù)庫(kù),創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)sysbench

$createdb sysbench

準(zhǔn)備階段
執(zhí)行以下命令,sysbench執(zhí)行自帶的測(cè)試腳本

sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--oltp-tables-count=10 --oltp-table-size=500000 \
--db-driver=pgsql --pgsql-host=192.168.26.103 --pgsql-port=5432 \
--pgsql-user=atlasdb --pgsql-db=sysbench \
prepare

命令輸出

Creating table 'sbtest1'...
Inserting 500000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 500000 records into 'sbtest2'
Creating secondary indexes on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 500000 records into 'sbtest3'
Creating secondary indexes on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 500000 records into 'sbtest4'
Creating secondary indexes on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 500000 records into 'sbtest5'
Creating secondary indexes on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 500000 records into 'sbtest6'
Creating secondary indexes on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 500000 records into 'sbtest7'
Creating secondary indexes on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 500000 records into 'sbtest8'
Creating secondary indexes on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 500000 records into 'sbtest9'
Creating secondary indexes on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 500000 records into 'sbtest10'
Creating secondary indexes on 'sbtest10'..

運(yùn)行基準(zhǔn)測(cè)試

sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--report-interval=5 \
--threads=4 --time=120 --db-driver=pgsql --pgsql-host=192.168.26.103 --pgsql-port=5432 \
--pgsql-user=atlasdb --pgsql-db=sysbench \
run

執(zhí)行完畢,4線程的執(zhí)行過程和結(jié)果如下:

sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

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


Initializing worker threads...

Threads started!

[ 5s ] thds: 4 tps: 107.50 qps: 2160.56 (r/w/o: 1513.97/430.79/215.80) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 4 tps: 80.79 qps: 1611.00 (r/w/o: 1126.26/323.16/161.58) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 105.45 qps: 2114.74 (r/w/o: 1482.66/421.19/210.89) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
...
[ 120s ] thds: 4 tps: 88.00 qps: 1762.33 (r/w/o: 1232.95/353.39/175.99) lat (ms,95%): 77.19 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            181496
        write:                           51856
        other:                           25928
        total:                           259280
    transactions:                        12964  (108.00 per sec.)
    queries:                             259280 (2160.08 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0312s
    total number of events:              12964

Latency (ms):
         min:                                   18.34
         avg:                                   37.02
         max:                                  122.90
         95th percentile:                       58.92
         sum:                               479962.76

Threads fairness:
    events (avg/stddev):           3241.0000/6.96
    execution time (avg/stddev):   119.9907/0.01

四、參考資料

How to Benchmark Performance of MySQL & MariaDB using SysBench
How to Benchmark PostgreSQL Performance Using Sysbench