ubuntu安装

##前提：先安装PostgreSQL和PostgreSQL-devel，如果软件仓库中版本太低，就直接编译安装
wget https://github.com/akopytov/sysbench/archive/1.0.20.tar.gz
sudo apt install -y  automake
sudo apt install -y  libtool
sudo apt install -y  pkg-config
sudo apt install -y libmysqlclient-dev
mv 1.0.20.tar.gz sysbench.tar.gz
tar -xzvf sysbench.tar.gz
cd sysbench-1.0.20/
./autogen.sh
./configure --with-pgsql --with-pgsql-includes=/mnt/d/work/ubuntu/app/postgresql/include --with-pgsql-libs=/mnt/d/work/ubuntu/app/postgresql/lib 
make
sudo make install

export LD_LIBRARY_PATH=/mnt/d/work/ubuntu/app/postgresql/lib/:$LD_LIBRARY_PATH
sysbench --help

centos安装

没有测试

##前提：先安装PostgreSQL和PostgreSQL-devel，如果软件仓库中版本太低，就直接编译安装
yum install gcc gcc-c++ automake make libtool mysql-community-devel mysql-devel
yum install automake libtool –y
yum -y install postgresql postgresql-devel

wget https://github.com/akopytov/sysbench/archive/1.0.20.tar.gz
mv 1.0.20.tar.gz sysbench.tar.gz
tar -xzvf sysbench.tar.gz
cd sysbench-1.0.20/
./autogen.sh
./configure --with-pgsql --with-pgsql-includes=/usr/pgsql-10/include --with-pgsql-libs=/usr/pgsql-10/lib
make -j 2
sudo make install

使用

压测PostgreSQL

##准备数据
sysbench --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=test --table_size=25000 --tables=250 --events=0 --time=600 oltp_read_only prepare

##运行workload oltp_read_only/oltp_write_only/oltp_read_write
sysbench --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=test --table_size=25000 --tables=250 --events=0 --time=600 --threads=2 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=1 oltp_read_only run

##清理
sysbench --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=test --table_size=25000 --tables=250 --events=0 --time=600 --threads=2 --percentile=95 --range_selects=0 oltp_read_only cleanup

指定lua脚本

sysbench --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=test --oltp-table-size=10000 --threads=10 --time=120 --events=0 --report-interval=10 --percentile=99 ./sysbench-1.0.20/tests/include/oltp_legacy/oltp.lua prepare

sysbench --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=test --oltp-table-size=10000 --threads=10 --time=120 --events=0 --report-interval=10 --percentile=99 ./sysbench-1.0.20/tests/include/oltp_legacy/oltp.lua run

sysbench --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=test --oltp-table-size=10000 --threads=10 --time=120 --events=0 --report-interval=10 --percentile=99 ./sysbench-1.0.20/tests/include/oltp_legacy/oltp.lua cleanup

压测mysql

##准备数据
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600 oltp_read_only prepare

##运行workload
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600 --threads=XXX --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=1 oltp_read_only run

##清理
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600 --threads=XXX --percentile=95 --range_selects=0 oltp_read_only cleanup

输出结果解析

-- 每10秒钟报告一次测试结果，tps、每秒读、每秒写、99%以上的响应时长统计    
[ 100s ] thds: 10 tps: 967.59 qps: 19659.36 (r/w/o: 13785.63/3868.85/2004.88) lat (ms,99%): 22.28 err/s: 17.10 reconn/s: 0.00
[ 110s ] thds: 10 tps: 727.29 qps: 14821.11 (r/w/o: 10396.20/2913.34/1511.57) lat (ms,99%): 31.94 err/s: 15.30 reconn/s: 0.00
[ 120s ] thds: 10 tps: 570.63 qps: 11608.69 (r/w/o: 8140.82/2280.92/1186.96) lat (ms,99%): 27.66 err/s: 11.10 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            1093134   -- 读总数
        write:                           305474    -- 写总数
        other:                           159493    -- 其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作，例如COMMIT等)
        total:                           1558101   -- 全部总数
    transactions:                        76350  (636.18 per sec.)     -- 总事务数(每秒事务数)
    queries:                             1558101 (12982.76 per sec.)  -- 查询总数（每秒查询数)
    ignored errors:                      1731   (14.42 per sec.)      -- 忽略的错误数
    reconnects:                          0      (0.00 per sec.)       -- 重连次数

General statistics:
    total time:                          120.0115s     -- 总耗时
    total number of events:              76350         -- 共发生多少事务

Latency (ms):
         min:                                    3.18  -- 最小耗时
         avg:                                   15.71  -- 平均耗时
         max:                                 2019.94  -- 最长耗时
         99th percentile:                       38.25  -- 超过99%平均耗时
         sum:                              1199632.83  -- 总耗时

Threads fairness:
    events (avg/stddev):           7635.0000/378.32  --标准差：标准偏差越小，这些值偏离平均值就越少，反之亦然
    execution time (avg/stddev):   119.9633/0.00  

作图

作图可以参考：https://www.jianshu.com/p/5250abb16fc3