sysbench测试方法

1.bulk_insert

批量插入测试.

# sysbench bulk_insert --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=sbtest --tables=30 --table-size=1000000 --threads=64 --time=60 --report-interval=10 run

下面是 bulk_insert 场景下创建的测试表.

mysql> show create table sbtest.sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL,
  `k` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

测试对应的 SQL 语句如下：

INSERT INTO sbtest1 VALUES(?, ?),(?, ?),(?, ?),(?, ?)...

 

2.oltp_delete

删除测试

# sysbench oltp_delete --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=sbtest --tables=30 --table-size=1000000 --threads=64 --time=60 --report-interval=10 run

基于主键进行删除.测试对应的 SQL 语句如下：

DELETE FROM sbtest1 WHERE id=?

 

3.oltp_insert
插入测试.

# sysbench oltp_insert --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=sbtest --tables=30 --table-size=1000000 --threads=64 --time=60 --report-interval=10 run

 

测试对应的 SQL 语句如下：

INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)

 

4.oltp_point_select
基于主键进行查询

# sysbench oltp_point_select --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=sbtest --tables=30 --table-size=1000000 --threads=64 --time=60 --report-interval=10 run

测试对应的 SQL 语句如下：

SELECT c FROM sbtest1 WHERE id=?

 

5.oltp_read_only
只读测试

# sysbench oltp_read_only --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=sbtest --tables=30 --table-size=1000000 --threads=64 --time=60 --report-interval=10 run

 

测试对应的 SQL 语句如下:

SELECT c FROM sbtest1 WHERE id=? # 默认会执行 10 次，由 --point_selects 选项控制.
SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?
SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?
SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c
SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c

 

6.oltp_read_write
读写测试

测试对应的 SQL 语句如下：

SELECT c FROM sbtest1 WHERE id=? # 默认会执行 10 次，由 --point_selects 选项控制.
SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?
SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?
SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c
SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c
UPDATE sbtest1 SET k=k+1 WHERE id=?
UPDATE sbtest1 SET c=? WHERE id=?
DELETE FROM sbtest1 WHERE id=?
INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)

 

7.oltp_update_index
基于主键进行更新，更新的是索引字段

# sysbench oltp_update_index --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=sbtest --tables=30 --table-size=1000000 --threads=64 --time=60 --report-interval=10 run

 

测试对应的 SQL 语句如下：
UPDATE sbtest1 SET k=k+1 WHERE id=?

 

8.oltp_update_non_index
基于主键进行更新，更新的是非索引字段

# sysbench oltp_update_non_index --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=sbtest --tables=30 --table-size=1000000 --threads=64 --time=60 --report-interval=10 run

 

测试对应的 SQL 语句如下：

UPDATE sbtest1 SET c=? WHERE id=?

 

9.oltp_write_only
只写测试

# sysbench oltp_write_only --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=sbtest --tables=30 --table-size=1000000 --threads=64 --time=60 --report-interval=10 run

 

测试对应的 SQL 语句如下：

UPDATE sbtest1 SET k=k+1 WHERE id=?
UPDATE sbtest1 SET c=? WHERE id=?
DELETE FROM sbtest1 WHERE id=?
INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)

 

10.select_random_points
基于索引进行随机查询

 

# sysbench select_random_points --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=sbtest --tables=30 --table-size=1000000 --threads=64 --time=60 --report-interval=10 run

测试对应的 SQL 语句如下：

SELECT id, k, c, pad
FROM sbtest1
WHERE k IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

 

11.select_random_ranges
基于索引进行随机范围查询

# sysbench select_random_ranges --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=sbtest --tables=30 --table-size=1000000 --threads=64 --time=60 --report-interval=10 run

 

测试对应的 SQL 语句如下：

SELECT count(k)
FROM sbtest1
WHERE k BETWEEN ? AND ? OR k BETWEEN ? AND ? OR k BETWEEN ? AND ? OR k BETWEEN ? AND ? OR k BETWEEN ? AND ? OR k BETWEEN ? AND ? OR k BETWEEN ? AND ? OR k BETWEEN ? AND ? OR k BETWEEN ? AND ? OR k BETWEEN ? AND ?

 

-- The End --