1、大致了解了oltp.lua脚本的内容，下面就开始测试
2、初始化测试环境

root@pm1:/usr/local/sysbench# sysbench --test=sysbench/tests/db/oltp.lua --mysql-socket=/data/pm1.sock --oltp-table-size=1000000 --mysql-table-engine=innodb --mysql-user=root --mysql-password=111 --mysql-db=test prepare
sysbench 0.5: multi-threaded system evaluation benchmark
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'

3、查看创建的表结构和数据

[root@localhost:test][03:01:02pm] MySQL-> show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned 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=2000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
1 row in set (0.05 sec)
[root@localhost:test][03:01:11pm] MySQL-> select * from sbtest1 limit 10;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 501885 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
| 3 | 495688 | 95969429576-20587925969-20202408199-67602281819-18293380360-38184587501-73192830026-41693404212-56705243222-89212376805 | 09512147864-77936258834-40901700703-13541171421-15205431759 |
| 5 | 514246 | 26283585383-48610978532-72166636310-67148386979-89643583984-06169170732-23477134062-17788128188-73465768032-24619558652 | 21979564480-87492594656-60524686334-78820761788-57684966682 |
| 7 | 393975 | 57481185690-89398636500-16888148413-67987678267-15604944838-94210794401-18107184012-91338377776-83386272438-09451188763 | 35227182905-15234265621-59793845249-15413569710-23749555118 |
| 9 | 500775 | 93482034638-51911042233-95872637268-17943401357-38175578085-45788017606-44041118775-54344399763-72128807465-92228972632 | 27590239742-20204899609-34345212327-79811525340-24267764271 |
| 11 | 498573 | 24310225777-93998284033-46606859421-56148834010-17759122961-78348472702-44986564036-71625391482-12661762212-64721022134 | 43131080328-59298106536-35954612339-97546855884-75769514803 |
| 13 | 504353 | 70862277183-86122137003-79729847560-50337161750-15964469011-48879357028-22541966759-10928901419-99400098250-19200948263 | 00505722282-72931248925-57037623248-81117963809-88658076981 |
| 15 | 497896 | 82571936845-31830426410-85662298479-28456275464-64339136268-26186841165-94168712814-56389105006-66969794071-60071049942 | 13152283289-69561545685-52868757241-04245213425-69280254356 |
| 17 | 503666 | 30259457399-49455699717-43210898264-46300466148-34254750860-44098710066-38295952016-90196077385-22332519290-06484158548 | 40929980986-33813039690-13155419391-97985458477-39771362212 |
| 19 | 503330 | 48090103407-09222928184-34050945574-85418069333-36966673537-23363106719-15284068881-04674238815-26203696337-24037044694 | 01495266405-82925129145-92643983850-90243995398-18709399387 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
10 rows in set (0.04 sec)

4、测试环境是虚拟机

root@pm1:/usr/local/sysbench# sysbench --test=sysbench/tests/db/oltp.lua --mysql-socket=/data/pm1.sock --mysql-user=root --mysql-password=111 \
> --mysql-db=test --oltp-table-size=1000000 --num-threads=4 --oltp-read-only=off --report-interval=10 \
> --rand-type=uniform --max-time=60 --max-requests=0 --percentile=99 run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored
Threads
[ 10s] threads: 4, tps: 128.93, reads: 1812.43, writes: 517.52, response time: 240.97ms (99%), errors: 0.20, reconnects: 0.00
[ 20s] threads: 4, tps: 161.84, reads: 2269.16, writes: 647.86, response time: 288.03ms (99%), errors: 0.30, reconnects: 0.00
[ 30s] threads: 4, tps: 113.60, reads: 1590.90, writes: 454.50, response time: 482.58ms (99%), errors: 0.00, reconnects: 0.00
[ 40s] threads: 4, tps: 106.00, reads: 1484.41, writes: 423.10, response time: 498.13ms (99%), errors: 0.00, reconnects: 0.00
[ 50s] threads: 4, tps: 138.80, reads: 1943.60, writes: 555.90, response time: 304.89ms (99%), errors: 0.10, reconnects: 0.00
[ 60s] threads: 4, tps: 116.60, reads: 1635.29, writes: 466.60, response time: 494.57ms (99%), errors: 0.20, reconnects: 0.00
OLTP test statistics:
queries performed:
read: 107310
write: 30652
other: 15322
total: 153284
transactions: 7657 (127.59 per sec.)
read/write requests: 137962 (2298.98 per sec.)
other operations: 15322 (255.32 per sec.)
ignored errors: 8 (0.13 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0102s
total number of events: 7657
total time taken by event execution: 239.7554s
response time:
min: 6.19ms
avg: 31.31ms
max: 1818.79ms
approx. 99 percentile: 375.85ms
Threads fairness:
events (avg/stddev): 1914.2500/5.63
execution time (avg/stddev): 59.9389/0.02

4.1、每10秒钟报告一次测试结果，线程数4，tps、每秒读、每秒写、99%以上的响应时长统计；
4.2、oltp测试统计，显示了读、写、其他操作（如commit、rollback等）及总共的统计值，还有事物数、读写请求数等等。
5、清除测试数据

root@pm1:/usr/local/sysbench# sysbench --test=sysbench/tests/db/oltp.lua --mysql-socket=/data/pm1.sock --num-threads=4 \
> --mysql-user=root --mysql-password=111 --mysql-db=test cleanup
sysbench 0.5: multi-threaded system evaluation benchmark
Dropping table 'sbtest1'...

在sysbench 0.5里还可以使用其他方式进行测试，如select、insert、delete、update等。

在sbtest1表中插入数据时，涉及到随机值，有2个脚本select_random_points.lua和select_random_ranges.lua没介绍。

ITPub博客

测试软件：sysbench-oltp（3）

最新文章