用mysqlslap进行mysql压力测试 mysqlslap官方文档 概述和测试环境 压测的目的是为了尽量模拟真实情况。测试的表都是由10个int型字段和10个字符串型字段组成。每个测试项目都测试了myisam和innodb两个引擎。测试的方法都是用两个线程并发,一共跑10000个请求。 压测的机器用的是自己的笔记本。CPU是Intel(R) Core(TM)2 Duo CPU P8400, 4G内存, SATA硬盘。 mysql服务器端和压测的客户端跑在同一台机器上。 用到的mysqlslap参数解释如下: --concurrency=2 两个线程 --iterations=1 执行一次 --number-int-cols=10 10个int型字段 --number-char-cols=10 10个字符串字段 -a 等同于--auto-generate-sql --auto-generate-sql-add-autoincrement 创建auto increment的主键 --auto-generate-sql-secondary-indexes=2 创建2列索引 --auto-generate-sql-guid-primary 创建guid作为主键 --number-of-queries=10000 所有并发线程的请求次数和 --auto-generate-sql-load-type=write/key/read/update/mixed 测试的query类型 --engine=myisam,innodb 存储引擎 写性能测试 写性能测试1,不开binlog, guid做主键, 无索引 测试命令: mysqlslap --concurrency=2\ --iterations=1\ --number-int-cols=10\ --number-char-cols=10\ -a\ --auto-generate-sql-guid-primary\ --number-of-queries=10000\ --auto-generate-sql-load-type=write\ --engine=myisam,innodb\ 结果: Benchmark Running for engine myisam Average number of seconds to run all queries: 6.110 seconds Minimum number of seconds to run all queries: 6.110 seconds Maximum number of seconds to run all queries: 6.110 seconds Number of clients running queries: 2 Average number of queries per client: 5000 Benchmark Running for engine innodb Average number of seconds to run all queries: 10.291 seconds Minimum number of seconds to run all queries: 10.291 seconds Maximum number of seconds to run all queries: 10.291 seconds Number of clients running queries: 2 Average number of queries per client: 5000 用myisam每秒处理1639次。 用innodb每秒处理971次。 真实环境中一般会用binlog做数据备份与同步,所以性能会有所下降,有binlog的情况请继续看后面的实验。 写性能测试2, 开binlog, guid做主键, 无索引 测试命令与“写性能测试1”相同, 结果如下: Benchmark Running for engine myisam Average number of seconds to run all queries: 14.809 seconds Minimum number of seconds to run all queries: 14.809 seconds Maximum number of seconds to run all queries: 14.809 seconds Number of clients running queries: 2 Average number of queries per client: 5000 Benchmark Running for engine innodb Average number of seconds to run all queries: 26.721 seconds Minimum number of seconds to run all queries: 26.721 seconds Maximum number of seconds to run all queries: 26.721 seconds Number of clients running queries: 2 Average number of queries per client: 5000 myisam每秒处理675次 innodb每秒处理374次 写性能测试3, 开binlog, guid做主键, 有索引 现实情况中,有索引的情况较多。采用 --auto-generate-sql-secondary-indexes=2 创建两列索引。 命令如下: mysqlslap --concurrency=2\ --iterations=1\ --number-int-cols=10\ --number-char-cols=10\ -a\ --auto-generate-sql-guid-primary\ --auto-generate-sql-secondary-indexes=2\ --number-of-queries=10000\ --auto-generate-sql-load-type=write\ --engine=myisam,innodb\ 结果如下: Benchmark Running for engine myisam Average number of seconds to run all queries: 16.693 seconds Minimum number of seconds to run all queries: 16.693 seconds Maximum number of seconds to run all queries: 16.693 seconds Number of clients running queries: 2 Average number of queries per client: 5000 Benchmark Running for engine innodb Average number of seconds to run all queries: 30.418 seconds Minimum number of seconds to run all queries: 30.418 seconds Maximum number of seconds to run all queries: 30.418 seconds Number of clients running queries: 2 Average number of queries per client: 5000 myisam每秒处理599次。 innodb每秒处理329次。 写性能测试4,开binglog,auto increment主键,有索引 命令如下: mysqlslap --concurrency=2\ --iterations=1\ --number-int-cols=10\ --number-char-cols=10\ -a\ --auto-generate-sql-add-autoincrement\ --auto-generate-sql-secondary-indexes=2\ --number-of-queries=10000\ --auto-generate-sql-load-type=write\ --engine=myisam,innodb\ 结果如下: Benchmark Running for engine myisam Average number of seconds to run all queries: 16.785 seconds Minimum number of seconds to run all queries: 16.785 seconds Maximum number of seconds to run all queries: 16.785 seconds Number of clients running queries: 2 Average number of queries per client: 5000 Benchmark Running for engine innodb Average number of seconds to run all queries: 28.809 seconds Minimum number of seconds to run all queries: 28.809 seconds Maximum number of seconds to run all queries: 28.809 seconds Number of clients running queries: 2 Average number of queries per client: 5000 myisam每秒处理595次。 innodb每秒处理347次。 读性能测试 在真实的情况中,我们最主要的读操作其实就是用主键去查找表中的一行。 我主要是对这种行为进行测试。所以采用的load-type = key ,而不是read。 read是对全表进行读取,可是实际上这种情况很少出现。 读性能测试1,guid主键 命令如下: mysqlslap --concurrency=2\ --iterations=1\ --number-int-cols=10\ --number-char-cols=10\ -a\ --auto-generate-sql-guid-primary\ --auto-generate-sql-unique-query-number=10000\ --auto-generate-sql-load-type=key\ --number-of-queries=10000\ --engine=myisam,innodb\ 结果如下: Benchmark Running for engine myisam Average number of seconds to run all queries: 4.215 seconds Minimum number of seconds to run all queries: 4.215 seconds Maximum number of seconds to run all queries: 4.215 seconds Number of clients running queries: 2 Average number of queries per client: 5000 Benchmark Running for engine innodb Average number of seconds to run all queries: 3.917 seconds Minimum number of seconds to run all queries: 3.917 seconds Maximum number of seconds to run all queries: 3.917 seconds Number of clients running queries: 2 Average number of queries per client: 5000 myisam每秒处理2372次 innodb每秒处理2553次 读性能测试2,auto increment主键 命令如下 mysqlslap --concurrency=1\ --iterations=1\ --number-int-cols=10\ --number-char-cols=10\ -a\ --auto-generate-sql-add-autoincrement\ --auto-generate-sql-unique-query-number=10000\ --auto-generate-sql-load-type=key\ --number-of-queries=10000\ --engine=myisam,innodb\ 结果如下: Benchmark Running for engine myisam Average number of seconds to run all queries: 4.555 seconds Minimum number of seconds to run all queries: 4.555 seconds Maximum number of seconds to run all queries: 4.555 seconds Number of clients running queries: 1 Average number of queries per client: 10000 Benchmark Running for engine innodb Average number of seconds to run all queries: 4.402 seconds Minimum number of seconds to run all queries: 4.402 seconds Maximum number of seconds to run all queries: 4.402 seconds Number of clients running queries: 1 Average number of queries per client: 10000 myisam每秒处理2195次。 innodb每秒处理2273次。 实验结果
结论 1 在开启了binlog后, mysql写性能下降60% 2 myisam与innodb相比,写的速度更快(快80%), 读的速度差不多。 转载请保留固定链接: https://linuxeye.com/database/1269.html |