常用基准测试工具之mysqlslap

mysqlslap基准测试工具介绍

mysqlslap常用参数说明



mysqlslap演示

1 ，系统中是否安装mysqlslap工具

[root@localhost ~]# mysqlslap --help

2， 运行mysqlslap测试命令

[root@localhost script]# mysqlslap --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=sbtest
 
# 分析：
# --concurrency=1,50,100,200   指定我们的并发数量为1单线程，50个并发，100个并发和200个并发
# --iterations=3               测试完成三次
# --number-int-cols=5          我们生成的整数行（int）可以定义成五行数量
# --number-char-cols=5         我们生成的字母行（char）可以定义成五行数量
# --auto-generate-sql          指定由系统自动成功SQL进行测试
# --auto-generate-sql-add-autoincrement 自动生成的SQL自增ID
# --engine=myisam,innodb       指定表的存储引擎为myisam和innodb
# -number-of-queries=10        每个查询量为10
# --create-schema=sbtest       测试在sbtest下进行   

3， 测试结果

Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.001 seconds
        Minimum number of seconds to run all queries: 0.001 seconds
        Maximum number of seconds to run all queries: 0.001 seconds
        Number of clients running queries: 1
        Average number of queries per client: 10

Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.075 seconds
        Minimum number of seconds to run all queries: 0.074 seconds
        Maximum number of seconds to run all queries: 0.076 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.165 seconds
        Minimum number of seconds to run all queries: 0.159 seconds
        Maximum number of seconds to run all queries: 0.176 seconds
        Number of clients running queries: 100
        Average number of queries per client: 0

Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.345 seconds
        Minimum number of seconds to run all queries: 0.339 seconds
        Maximum number of seconds to run all queries: 0.348 seconds
        Number of clients running queries: 200
        Average number of queries per client: 0

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.017 seconds
        Minimum number of seconds to run all queries: 0.015 seconds
        Maximum number of seconds to run all queries: 0.019 seconds
        Number of clients running queries: 1
        Average number of queries per client: 10

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.174 seconds
        Minimum number of seconds to run all queries: 0.153 seconds
        Maximum number of seconds to run all queries: 0.216 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.392 seconds
        Minimum number of seconds to run all queries: 0.386 seconds
        Maximum number of seconds to run all queries: 0.401 seconds
        Number of clients running queries: 100
        Average number of queries per client: 0

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.731 seconds
        Minimum number of seconds to run all queries: 0.623 seconds
        Maximum number of seconds to run all queries: 0.846 seconds
        Number of clients running queries: 200
        Average number of queries per client: 0

mysqlslap先执行MyISAM的测试测试根据测试场景不同，分别有4次结果，然后接下来会进行InnoDB的测试，同样有四次测试结果

问题：在单线程环境下，InnoDB与MyISAM那个性能好？

答：理论上是InnoDB

解析：从数据可以看到，在单并发情况下，每个SQL只执行一次查询，myisam的执行时间却快于innodb，myisam引擎由于不支持事务，所以在单独的读环境或者写环境下可能性能会好于innodb，但是在读写混合环境，innodb的性能会好于myisam，特别是数据量越多越明显

问题：在多线程程环境下，InnoDB与MyISAM那个性能好？

答：理论上是InnoDB
解析：上述测试结果中的200并发中，…，说明在这种混合的查询压力下，InnoDB性能要好的。这也就是前面我们为什么要推荐大家使用InnoDB的一个原因。

4 ,上面我们只是进行了查询，但是我们并不知道这个压力测试的具体执行的查询是什么，刚才我们介绍了–only -print这个参数，可以通过这个命令行查看具体的脚本内容，最后对sbtest做了一个清楚操作

[root@localhost script]# mysqlslap --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=sbtest --only-print

生成到指定脚本中

[root@localhost script]# mysqlslap --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=sbtest --only-print > 1.sql

mysqlslap常用参数说明总结