MySQL使用Sysbench性能测试

安装

apt-get update
apt-get install sysbench -y

准备

创建用来测试的用户和数据库

CREATE DATABASE mytest1;
CREATE USER 'sysbench'@'localhost' IDENTIFIED BY 'password123';
GRANT ALL PRIVILEGES ON mytest1.* TO 'sysbench'@'localhost';

创建准备命令

sysbench /usr/share/sysbench/oltp_read_write.lua \
--db-driver=mysql \
--mysql-db=mytest1 \
--mysql-user=sysbench \
--mysql-password='password123' \
--mysql-socket=/run/mysqld/mysqld.sock \
--tables=20 \
--table-size=100000 \
prepare

--tables=20表示创建20个表，--table-size=100000每个表10万行数据

运行测试

sysbench /usr/share/sysbench/oltp_read_write.lua \
--db-driver=mysql \
--mysql-db=mytest1 \
--mysql-user=sysbench \
--mysql-password='password123' \
--mysql-socket=/run/mysqld/mysqld.sock \
--tables=20 \
--table-size=100000 \
--time=180 \
--threads=4 \
run

--time=180表示运行180秒，--threads=4表示启用4个线程

结果

sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 4
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            1315286
        write:                           375796
        other:                           187898
        total:                           1878980
    transactions:                        93949  (521.90 per sec.) -- TPS
    queries:                             1878980 (10437.93 per sec.)-- QPS
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          180.0114s
    total number of events:              93949

Latency (ms):
         min:                                    2.45
         avg:                                    7.66
         max:                                  113.88
         95th percentile:                       12.75 -- 95%的请求在12.75的延迟内
         sum:                               719576.49

Threads fairness:
    events (avg/stddev):           23487.2500/216.02
    execution time (avg/stddev):   179.8941/0.00

以上结果来自 4 核 8G 内存的 GreenCloudVPS 服务器测试结果

清理

sysbench /usr/share/sysbench/oltp_read_write.lua \
--db-driver=mysql \
--mysql-db=mytest1 \
--mysql-user=sysbench \
--mysql-password='password123' \
--mysql-socket=/var/run/mysqld/mysqld.sock \
--tables=20 \
--table-size=100000 \
cleanup

完成后删除测试数据