有一天我想要搜尋 google://mysql 壓力測試 的時候
發現盡是一些 mysqlslap 的介紹工具，但我又不想用…
直到我找到了 sysbench
是一套老牌的壓力測試工具，如果有興趣研究 lua 語法的話
也可以碰出很多新滋味
安裝方式 (OS: Debian)
# aptitude install sysbench
安裝後確認
# dpkg -l | grep sysbench
ii sysbench 1.0.11-1.jessie amd64 Cross-platform and multi-threaded benchmark tool
# whereis sysbench
sysbench: /usr/bin/sysbench /usr/share/sysbench
lua 語法路徑如下
# ls -la /usr/share/sysbench
total 72
drwxr-xr-x 3 root root 4096 Jan 14 12:12 .
drwxr-xr-x 104 root root 4096 Jan 14 12:12 ..
-rwxr-xr-x 1 root root 1452 Dec 15 20:08 bulk_insert.lua
-rw-r--r-- 1 root root 13816 Dec 15 20:08 oltp_common.lua
-rwxr-xr-x 1 root root 1290 Dec 15 20:08 oltp_delete.lua
-rwxr-xr-x 1 root root 2415 Dec 15 20:08 oltp_insert.lua
-rwxr-xr-x 1 root root 1265 Dec 15 20:08 oltp_point_select.lua
-rwxr-xr-x 1 root root 1649 Dec 15 20:08 oltp_read_only.lua
-rwxr-xr-x 1 root root 1824 Dec 15 20:08 oltp_read_write.lua
-rwxr-xr-x 1 root root 1118 Dec 15 20:08 oltp_update_index.lua
-rwxr-xr-x 1 root root 1127 Dec 15 20:08 oltp_update_non_index.lua
-rwxr-xr-x 1 root root 1440 Dec 15 20:08 oltp_write_only.lua
-rwxr-xr-x 1 root root 1919 Dec 15 20:08 select_random_points.lua
-rwxr-xr-x 1 root root 2118 Dec 15 20:08 select_random_ranges.lua
drwxr-xr-x 4 root root 4096 Jan 14 12:12 tests
我的用法長這樣
sysbench --db-driver=mysql --mysql-user=username --mysql-password=password \
--mysql-db=test \
--range_size=1000 --table_size=100000 --tables=10 \
--threads=1 (幾個 core 就開幾個 thread) --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua prepare
因為 DB: test 在 Mysql 5.7 不是預設會產生，所以自己來
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.02 sec)
接著 prepare 就會生成測試資料
root@pxc-1:~# sysbench --db-driver=mysql --mysql-user=username --mysql-password=password --mysql-db=test --range_size=1000 --table_size=100000 --tables=10 --threads=4 --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua prepare
sysbench 1.0.11 (using bundled LuaJIT 2.1.0-beta2)
Initializing worker threads...
Creating table 'sbtest3'...
Creating table 'sbtest2'...
Creating table 'sbtest1'...
Creating table 'sbtest4'...
Inserting 100000 records into 'sbtest3'
Inserting 100000 records into 'sbtest2'
Inserting 100000 records into 'sbtest1'
Inserting 100000 records into 'sbtest4'
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest6'...
Creating table 'sbtest7'...
Inserting 100000 records into 'sbtest6'
Creating a secondary index on 'sbtest1'...
Inserting 100000 records into 'sbtest7'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Creating table 'sbtest8'...
Inserting 100000 records into 'sbtest5'
Inserting 100000 records into 'sbtest8'
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest10'...
Inserting 100000 records into 'sbtest10'
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest9'...
Inserting 100000 records into 'sbtest9'
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest9'...
接著就可以執行 run 來測試一下效果
root@pxc-1:~# sysbench --db-driver=mysql --mysql-user=username --mysql-password=password --mysql-db=test --range_size=1000 --table_size=100000 --tables=10 --threads=4 --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua run
sysbench 1.0.11 (using bundled LuaJIT 2.1.0-beta2)
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: 18718
write: 0
other: 2674
total: 21392
transactions: 1337 (133.42 per sec.)
queries: 21392 (2134.75 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0189s
total number of events: 1337
Latency (ms):
min: 6.53
avg: 29.95
max: 811.26
95th percentile: 29.19
sum: 40040.04
Threads fairness:
events (avg/stddev): 334.2500/1.48
execution time (avg/stddev): 10.0100/0.01
生成的資料表大概長這樣
mysql> SHOW CREATE TABLE sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
檢查一下內容物有多亂，一推數字，沒有文字或符號一起參亂
mysql> SELECT * FROM sbtest1 LIMIT 1\G
*************************** 1. row ***************************
id: 1
k: 80178
c: 18034632456-32298647298-82351096178-60420120042-90070228681-93395382793-96740777141-18710455882-88896678134-41810932745
pad: 43683718329-48150560094-43449649167-51455516141-06448225399
1 row in set (0.00 sec)
INDEX 則是這樣下的
mysql> SHOW INDEX FROM sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 98712
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: sbtest1
Non_unique: 1
Key_name: k_1
Seq_in_index: 1
Column_name: k
Collation: A
Cardinality: 63604
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
接著來看看 lua 到底都做了些什麼事情
# cat /usr/share/sysbench/oltp_read_only.lua
#!/usr/bin/env sysbench
-- Copyright (C) 2006-2017 Alexey Kopytov <akopytov@gmail.com>
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
-- ----------------------------------------------------------------------
-- Read-Only OLTP benchmark
-- ----------------------------------------------------------------------
require("oltp_common")
function prepare_statements()
prepare_point_selects()
if not sysbench.opt.skip_trx then
prepare_begin()
prepare_commit()
end
if sysbench.opt.range_selects then
prepare_simple_ranges()
prepare_sum_ranges()
prepare_order_ranges()
prepare_distinct_ranges()
end
end
下略
執行的部分程式區段看起來單純
尤其是看到 require(“oltp_common”)
判斷工作的活應該也都寫在 oltp_common 上
總結: 會用就好
