安装

1
2
3
4
5
6

# Debian/Ubuntu
$ curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
$ sudo apt -y install sysbench
# RHEL/CentOS:
$ curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
$ sudo yum -y install sysbench

参数

  • –threads:执行任务的线程数
  • –time：持续时间：5min，300s,默认为 10
  • –mysql-host:指定mysql的服务器，默认是localhost;
  • –mysql-port:指定mysql的服务器端口，默认是3306；
  • –mysql-socket:指定mysql服务器的socket;
  • –mysql-user:指定mysql的登陆用户，默认是sbtest;
  • –mysql-password:指定登陆密码，默认是空；
  • –mysql-db:指定测试的数据库，默认是sbtest;
  • –tables:指定压测过程中涉及到的表的数量
  • –table_size:指定被压测的表 单表的行数
  • –mysql-table-engine:指定测试表的存储引擎，默认是innodb;
  • –mysql-engine-trx:指定存储引擎是否为事务性的，默认是auto;
  • –mysql-ssl:指定是否使用SSL连接，默认是off;
  • –mysql-create-options:指定创建表的附加选项。
  • prepar/cleanup/run：初始化/清除/执行任务

进行压测

  • 压测基于 sysbench 内置的压测脚本
  • 使用 yum/apt 安装时，脚本位于 /usr/share/sysbench/ 目录下

初始化数据

  • 创建测试database(压测脚本不会自动生成)
1

CREATE DATABASE `sbtest` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
  • 创建测试用户
1

GRANT ALL PRIVILEGES ON `sbtest`.* TO 'sbtest_user'@'127.0.0.1' IDENTIFIED BY "sysbenctPasswd";
  • 生成初始数据
    • 五个线程同时向五个表中写入50000000条数据、用于后期压测使用
    • 为加快写入速度，可调整 MySQL 配置 innodb_flush_log_at_trx_commit、sync_binlog 为 0 以加快数据写入速度。初始化数据后应及时改回之前配置，避免服务运行状态异常导致数据丢失及压测结果不准确
1
2
3
4

$ sysbench /usr/share/sysbench/oltp_common.lua \
--mysql-host=127.0.0.1 --mysql-port=3306 \
--mysql-user=sbtest_user --mysql-password=sysbenctPasswd  \
--tables=5 --threads=5 --table_size=50000000 prepar
  • 清理初始数据(压测完或需重新初始化数据时执行)
1
2
3
4

$ sysbench /usr/share/sysbench/oltp_common.lua \
--mysql-host=127.0.0.1 --mysql-port=3306 \
--mysql-user=sbtest_user --mysql-password=sysbenctPasswd  \
--tables=5 --threads=5 --table_size=50000000 cleanup

执行压测任务

  • 执行压测任务
1
2
3
4
5

sysbench "$MYSQL_PRESSURE_LUA_PATH/$lua_name" \
--mysql-host="$MYSQL_PRESSURE_HOST" --mysql-port="$MYSQL_PRESSURE_PORT" \
--mysql-user="$MYSQL_PRESSURE_USER" --mysql-password="$MYSQL_PRESSURE_PASS" \
--tables="$MYSQL_PRESSURE_TABLES"  --table_size="$MYSQL_PRESSURE_SIZE"\
--threads="$threads_nu" --time="$SYSBENCH_RUNTIME" run

oltp_point_select.lua

  • 模拟随机单点查询
1

SELECT c FROM sbtest2 WHERE id=?;

select_random_points.lua

  • 模拟使用 IN 的多值单点查询
1
2
3

SELECT id, k, c, pad
          FROM sbtest1
          WHERE k IN (24924832, 25087683, 25159148, 24997665, 24911383, 20377687, 25015821, 25049114, 25035621, 24965724);

select_random_ranges.lua

  • 模拟随机范围查询计数
1
2
3
4
5

SELECT count(k)
          FROM sbtest1
          WHERE k BETWEEN 25070100 AND 25070105 OR k BETWEEN 22558415 AND 22558420 OR k BETWEEN 35407206 AND 35407211 OR k BETWEEN 
25066098 AND 25066103 OR k BETWEEN 22850341 AND 22850346 OR k BETWEEN 25099353 AND 25099358 OR k BETWEEN 25236756 AND 25236761 OR k 
BETWEEN 25149252 AND 25149257 OR k BETWEEN 24963215 AND 24963220 OR k BETWEEN 24910356 AND 24910361;

oltp_read_only.lua

  • 模拟简单范围查询、排序等
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

BEGIN;
SELECT c FROM sbtest3 WHERE id=25027662;
SELECT c FROM sbtest3 WHERE id=24972830;
SELECT c FROM sbtest3 WHERE id=25011357;
SELECT c FROM sbtest3 WHERE id=26473428;
SELECT c FROM sbtest3 WHERE id=25169072;
SELECT c FROM sbtest3 WHERE id=25599162;
SELECT c FROM sbtest3 WHERE id=25215258;
SELECT c FROM sbtest3 WHERE id=25135004;
SELECT c FROM sbtest3 WHERE id=15318077;
SELECT c FROM sbtest3 WHERE id=24929190;
SELECT c FROM sbtest2 WHERE id BETWEEN 25193928 AND 25194027;
SELECT SUM(k) FROM sbtest2 WHERE id BETWEEN 24954468 AND 24954567;
SELECT c FROM sbtest3 WHERE id BETWEEN 29772619 AND 29772718 ORDER BY c;
SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 25222604 AND 25222703 ORDER BY c;
COMMIT;

oltp_update_index.lua

  • 模拟更新有索引的行
1

UPDATE sbtest2 SET k=k+1 WHERE id=?;

oltp_update_non_index.lua

  • 模拟更新没有索引的行
1

UPDATE sbtest2 SET c='65005986283-75587592944-69736716784-24444679855-07718114372-29618851013-62600149221-79922322499-58948208651-69771638372' WHERE id=?;

oltp_insert.lua

  • 模拟 insert 场景
1

INSERT INTO sbtest2 (id, k, c, pad) VALUES (0, 21755014, '81900825785-38858904366-01760249189-66716117297-19511612870-40623060589-37134387164-46875860991-11150404529-34947084548', '87514976009-20471555911-65580581687-69021928326-49523462365');

oltp_delete.lua

  • 模拟 DELETE 场景
1

DELETE FROM sbtest1 WHERE id=25212803;

oltp_write_only.lua

  • 模拟随机 INSERT、UPDATE、DELETE 场景
1
2
3
4
5
6

BEGIN;
UPDATE sbtest3 SET k=k+1 WHERE id=21411421;
UPDATE sbtest2 SET c='46557627079-24216038707-77144886082-03798754391-04913866155-49213610816-51408593973-44416007788-68553477771-13071228862' WHERE id=24986160;
DELETE FROM sbtest2 WHERE id=24923337;
INSERT INTO sbtest2 (id, k, c, pad) VALUES (24923337, 24987009, '55732667279-89797104310-36426039501-69870057557-34468409073-00696468895-40259877671-17247163228-49605849682-20787559137', '47513226371-65062081465-48554340257-46316370410-46186896273');
COMMIT;

oltp_read_write.lua

  • 模拟 事务场景下的读写混合 场景
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

BEGIN;
SELECT c FROM sbtest1 WHERE id=24916337;
SELECT c FROM sbtest1 WHERE id=25187026;
SELECT c FROM sbtest1 WHERE id=25222476;
SELECT c FROM sbtest1 WHERE id=28826983;
SELECT c FROM sbtest1 WHERE id=28302899;
SELECT c FROM sbtest1 WHERE id=29382130;
SELECT c FROM sbtest1 WHERE id=25055983;
SELECT c FROM sbtest1 WHERE id=25738659;
SELECT c FROM sbtest1 WHERE id=25134268;
SELECT c FROM sbtest1 WHERE id=24890898;
SELECT c FROM sbtest3 WHERE id BETWEEN 25120044 AND 25120143;
SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN 24976550 AND 24976649;
SELECT c FROM sbtest1 WHERE id BETWEEN 26563175 AND 26563274 ORDER BY c;
SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 25223198 AND 25223297 ORDER BY c;
UPDATE sbtest2 SET k=k+1 WHERE id=25203271;
UPDATE sbtest2 SET c='08709089640-33589298319-33669599045-39426819139-70290936772-56393732641-11795453171-88742367845-12381439130-51978335201' WHERE id=25236539;
DELETE FROM sbtest3 WHERE id=25013777;
INSERT INTO sbtest3 (id, k, c, pad) VALUES (25013777, 25150157, '20594818910-34673649338-61853014023-10600518298-72529947470-50971987694-20395003019-71793588885-27872548458-58160312415', '87691934240-56489183654-31291221252-89812884374-80848601593');
COMMIT;

返回

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

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


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            381542     # 读总数
        write:                           0          # 写总数
        other:                           54506      # 其他操作
        total:                           436048     # 总共
    transactions:                        27253  (454.20 per sec.)   # 总事务数
    queries:                             436048 (7267.23 per sec.)  # 执行总sql数
    ignored errors:                      0      (0.00 per sec.)     # 未知错误数
    reconnects:                          0      (0.00 per sec.)     # 其他操作数

General statistics:
    total time:                          60.0004s
    total number of events:              27253

Latency (ms):
         min:                                    1.29
         avg:                                    2.20
         max:                                   15.22
         95th percentile:                        2.97
         sum:                                59977.15

Threads fairness:
    events (avg/stddev):           27253.0000/0.00  # 每个线程完成event数/标准差
    execution time (avg/stddev):   59.9771/0.00     # 每个线程平均总耗时/标准差