mysql tps 测试_初步测试磁盘IOPS和mysql-TPS

测试磁盘IOPS，分为prepare run  cleanup三个阶段：

[root@localhost ~]# sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw prepare

sysbench 0.5:  multi-threaded system evaluation benchmark

等待执行后，会在当前目录产生test_file.0等测试数据文件；

[root@localhost ~]# ll

total 3145752

-rw-------. 1 root root    1232 Apr 10  2013 anaconda-ks.cfg

-rw-r--r--. 1 root root    9064 Apr 10  2013 install.log

-rw-r--r--. 1 root root    3091 Apr 10  2013 install.log.syslog

drwxr-xr-x  3 root root    4096 Feb 10 16:31 mysql

-rw-------  1 root root 25165824 Feb 11 00:14 test_file.0

-rw-------  1 root root 25165824 Feb 11 00:14 test_file.1

……

-rw-------  1 root root 25165824 Feb 11 00:14 test_file.108

-rw-------  1 root root 25165824 Feb 11 00:14 test_file.109

产生129个对应的测试文件；由--file-total-size=3G来决定；

[root@localhost ~]# sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrwrun

sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 16

Random number generator seed is 0 and will be ignored

Threads started!

Operations performed:  6006 reads, 3994 writes, 12800 Other = 22800 Total

Read 93.844Mb  Written 62.406Mb  Total transferred 156.25Mb  (5.8673Mb/sec)

375.51 Requests/sec executed

Test execution summary:

total time:                          26.6308s

total number of events:              10000

total time taken by event execution: 412.3393s

per-request statistics:

min:                                  0.02ms

avg:                                41.23ms

max:                                774.07ms

approx.  95 percentile:            178.47ms

Threads fairness:

events (avg/stddev):          625.0000/32.18

execution time (avg/stddev):  25.7712/0.19

375.51 Requests/sec executed 即磁盘IOPS这里测试的结果是375.51

清除测试数据：

[root@localhost ~]# sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrwcleanup

sysbench 0.5:  multi-threaded system evaluation benchmark

[root@localhost ~]# ll

total 24

-rw-------. 1 root root 1232 Apr 10  2013 anaconda-ks.cfg

-rw-r--r--. 1 root root 9064 Apr 10  2013 install.log

-rw-r--r--. 1 root root 3091 Apr 10  2013 install.log.syslog

drwxr-xr-x  3 root root 4096 Feb 10 16:31 mysql

下面测试mysql数据库的性能指标之一：tps

下面是测试mysql的常用命令参数：(这是阿里云RDS测试报告上面给出的注释，借用一下，在此说明)

/usr/local/sysbench-0.5/bin/sysbench

--mysql-host=test.mysql.rds.aliyuncs.com    #数据库host

--mysql-port=3306                          #数据库端口

--mysql-user=your_username                  #数据库用户名

--mysql-password=your_password              #数据库密码

--mysql-db=your_db_for_test                #数据库名

--oltp-tables-count=10                      #模拟的表的个数，规格越高该值越大

--oltp-table-size=6000000                  #模拟的每张表的行数，规格越高该值越大

--num-threads=50                            #模拟的并发数量，规格越高该值越大

--max-requests=100000000              #最大请求次数

--max-time=20                            #最大测试时间(与--max-requests只要有一个超过，则退出)

--report-interval=1                        #每1秒打印一次当前的QPS等值

--test=/tmp/sysbench-0.5/sysbench/tests/db/oltp.lua 

    #选用的测试脚本(lua)，此脚本可以从sysbench-0.5源代码文件目录下找

[prepare | run | cleanup]            #prepare准备数据，run执行测试，cleanup清理数据

--测试过程分为[prepare | run | cleanup]    3个步骤 注意看下面的命令最后一个单词

下面是在本地虚拟机上面做的初步测试，首先创建表：

[root@localhost sysbench-0.5]# sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test --oltp-tables-count=10 --oltp-table-size=600000 --num-threads=50 --max-requests=100000000 --max-time=20 --report-interval=1 --test=/root/mysql/sysbench-0.5/sysbench/tests/db/oltp.lua prepare

sysbench 0.5:  multi-threaded system evaluation benchmark

Creating table 'sbtest1'...

Inserting 600000 records into 'sbtest1'

Creating table 'sbtest2'...

Inserting 600000 records into 'sbtest2'

Creating table 'sbtest3'...

Inserting 600000 records into 'sbtest3'

Creating table 'sbtest4'...

Inserting 600000 records into 'sbtest4'

Creating table 'sbtest5'...

Inserting 600000 records into 'sbtest5'

Creating table 'sbtest6'...

Inserting 600000 records into 'sbtest6'

Creating table 'sbtest7'...

Inserting 600000 records into 'sbtest7'

Creating table 'sbtest8'...

Inserting 600000 records into 'sbtest8'

Creating table 'sbtest9'...

Inserting 600000 records into 'sbtest9'

Creating table 'sbtest10'...

Inserting 600000 records into 'sbtest10'

插入数据的过程中 ，可以这样监控插入的数据速度：

[root@localhost ~]# mysqladmin -uroot -proot extended-status -r -i1|grep Com_insert

| Com_insert                        | 4764        |

| Com_insert_select                | 0          |

| Com_insert                        | 2          |

| Com_insert_select                | 0          |

| Com_insert                        | 1          |

| Com_insert_select                | 0          |

| Com_insert                        | 2          |

| Com_insert_select                | 0          |

| Com_insert                        | 2          |

| Com_insert_select                | 0          |

| Com_insert                        | 2          |

| Com_insert_select                | 0          |

| Com_insert                        | 1          |

| Com_insert_select                | 0          |

| Com_insert                        | 2          |

| Com_insert_select                | 0          |

| Com_insert                        | 2          |

插入完成后，确认表的数量和表的记录数：

[root@localhost sysbench-0.5]#

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| sbtest1        |

| sbtest10      |

| sbtest2        |

| sbtest3        |

| sbtest4        |

| sbtest5        |

| sbtest6        |

| sbtest7        |

| sbtest8        |

| sbtest9        |

+----------------+

10 rows in set (0.06 sec)

mysql> select count(*) from sbtest1;

+----------+

| count(*) |

+----------+

|  600000 |

+----------+

1 row in set (0.43 sec)

mysql> select count(*) from sbtest10;

+----------+

| count(*) |

+----------+

|  600000 |

+----------+

1 row in set (0.21 sec)

mysql>

mysql> desc sbtest1;

+-------+------------------+------+-----+---------+----------------+

| Field | Type            | Null | Key | Default | Extra          |

+-------+------------------+------+-----+---------+----------------+

| id    | int(10) unsigned | NO  | PRI | NULL    | auto_increment |

| k    | int(10) unsigned | NO  | MUL | 0      |                |

| c    | char(120)        | NO  |    |        |                |

| pad  | char(60)        | NO  |    |        |                |

+-------+------------------+------+-----+---------+----------------+

4 rows in set (0.05 sec)

开始测试：

[root@localhost sysbench-0.5]# sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test --oltp-tables-count=10 --oltp-table-size=600000 --num-threads=50 --max-requests=100000000 --max-time=20 --report-interval=1 --test=/root/mysql/sysbench-0.5/sysbench/tests/db/oltp.lua run

sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 50

Report intermediate results every 1 second(s)

Random number generator seed is 0 and will be ignored

Threads started!

[  1s] threads: 50, tps: 0.00, reads/s: 674.46, writes/s: 4.00

[  2s] threads: 50, tps: 50.03, reads/s: 452.27, writes/s: 196.12

[  3s] threads: 50, tps: 44.00, reads/s: 432.00, writes/s: 185.00

[  4s] threads: 50, tps: 34.00, reads/s: 680.00, writes/s: 127.00

[  5s] threads: 50, tps: 26.00, reads/s: 355.99, writes/s: 143.00

[  6s] threads: 50, tps: 36.00, reads/s: 636.00, writes/s: 116.00

[  7s] threads: 50, tps: 46.00, reads/s: 548.02, writes/s: 173.00

[  8s] threads: 50, tps: 41.00, reads/s: 495.99, writes/s: 170.00

[  9s] threads: 50, tps: 45.00, reads/s: 608.02, writes/s: 174.01

[  10s] threads: 50, tps: 42.00, reads/s: 431.99, writes/s: 170.00

[  11s] threads: 50, tps: 13.00, reads/s: 554.99, writes/s: 93.00

[  12s] threads: 50, tps: 44.00, reads/s: 459.01, writes/s: 139.00

[  13s] threads: 50, tps: 21.00, reads/s: 398.02, writes/s: 85.00

[  14s] threads: 50, tps: 36.00, reads/s: 313.97, writes/s: 155.99

[  15s] threads: 50, tps: 43.00, reads/s: 588.01, writes/s: 181.00

[  16s] threads: 50, tps: 27.00, reads/s: 500.98, writes/s: 95.00

[  17s] threads: 50, tps: 24.00, reads/s: 357.01, writes/s: 81.00

[  18s] threads: 50, tps: 43.00, reads/s: 528.00, writes/s: 189.00

[  19s] threads: 50, tps: 25.00, reads/s: 346.01, writes/s: 103.00

[  20s] threads: 50, tps: 31.00, reads/s: 518.00, writes/s: 130.00

OLTP test statistics:

queries performed:

read:                            10094

write:                          2884

other:                          1442

total:                          14420

transactions:                        721    (34.55 per sec.)

deadlocks:                          0      (0.00 per sec.)

read/write requests:                12978  (621.95 per sec.)

other operations:                    1442  (69.11 per sec.)

Test execution summary:

total time:                          20.8665s

total number of events:              721

total time taken by event execution: 1016.7021s

per-request statistics:

min:                                654.17ms

avg:                              1410.13ms

max:                              2471.19ms

approx.  95 percentile:            2012.21ms

Threads fairness:

events (avg/stddev):          14.4200/0.87

execution time (avg/stddev):  20.3340/0.29

清除测试数据：

[root@localhost sysbench-0.5]# sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test --oltp-tables-count=10 --oltp-table-size=600000 --num-threads=50 --max-requests=100000000 --max-time=20 --report-interval=1 --test=/root/mysql/sysbench-0.5/sysbench/tests/db/oltp.lua cleanup

sysbench 0.5:  multi-threaded system evaluation benchmark

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

Dropping table 'sbtest4'...

Dropping table 'sbtest5'...

Dropping table 'sbtest6'...

Dropping table 'sbtest7'...

Dropping table 'sbtest8'...

Dropping table 'sbtest9'...

Dropping table 'sbtest10'...

清除数据，删除之前产生的表；

具体测试的结果的含义慢慢再研究

相关阅读：