测试软件：sysbench-oltp（3）

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

1. 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
   
2. sysbench 0.5: multi-threaded system evaluation benchmark
   
3. 
   
4. Creating table 'sbtest1'...
   
5. Inserting 1000000 records into 'sbtest1'

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

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

4、测试环境是虚拟机

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

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

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

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

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12219480/viewspace-1760173/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12219480/viewspace-1760173/