数据库优化-基准测试（三）

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了数据库优化-基准测试（三），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9576字，纯文字阅读大概需要14分钟。

内容图文

$ mysqlslap --only-print --auto-generate-sql DROP SCHEMA IF EXISTS `mysqlslap`; CREATE SCHEMA `mysqlslap`; use mysqlslap; CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128)); INSERT INTO t1 VALUES (...); INSERT INTO t1 VALUES (...); .... SELECT intcol1,charcol1 FROM t1; INSERT INTO t1 VALUES (...); SELECT intcol1,charcol1 FROM t1; INSERT INTO t1 VALUES (...); ... DROP SCHEMA IF EXISTS `mysqlslap`;

#例子2：只输出SQL语句，对命令计数并打印
$ mysqlslap --only-print --auto-generate-sql | awk ‘{print $1" "$2}‘ | sort | uniq -c | sort -n -r
105 INSERT INTO
5 SELECT intcol1,charcol1
2 DROP SCHEMA
1 use mysqlslap;
1 CREATE TABLE
1 CREATE SCHEMA

说明：

• 默认，创建和删除数据库mysqlslap
• 可通过–create-schema=value指定一个不同的数据库名称
• 在数据加载阶段每个线程执行100个INSERT语句（使用–auto-generate-sql-write-number）
• 在测试阶段每个线程执行许多INSERT和SELECT语句（使用–auto-generate-sql-loadtype）

#例子3：计数INSERT/SELECT语句，--number-of-queries 代表总共要运行多少次查询
$ mysqlslap --only-print --auto-generate-sql --number-of-queries=100 | awk ‘{print $1}‘ | egrep ‘(INSERT|SELECT)‘ | sort | uniq -c | sort -n -r
154 INSERT INTO
45 SELECT intcol1,charcol1

#例子4：计数INSERT/SELECT语句，--concurrency 代表并发数量
$ mysqlslap --only-print --auto-generate-sql --concurrency=5
--number-of-queries=100 | awk ‘{print $1}‘ | egrep ‘(INSERT|
SELECT)‘ | sort | uniq -c | sort -n -r
154 INSERT INTO
45 SELECT intcol1,charcol1

#例子5：计数INSERT/SELECT语句，--iterations 代表要运行这些测试多少次
$ mysqlslap --only-print --auto-generate-sql --iteration=5
--number-of-queries=100 | awk ‘{print $1}‘ | egrep ‘(INSERT|
SELECT)‘ | sort | uniq -c | sort -n -r
770 INSERT INTO
225 SELECT intcol1,charcol1

#例子6：计数INSERT/SELECT语句，--auto-generate-sql-write-number 每个线程生成write sql语句数量
$ mysqlslap --only-print --auto-generate-sql --number-of-queries=100 --auto-generate-sql-write-number=10000 | awk
‘{print $1}‘ | egrep ‘(INSERT|SELECT)‘ | sort | uniq -c |
sort -n -r
10054 INSERT
45 SELECT

#例子7：计数INSERT/SELECT语句，--auto-generate-sql-load-type=read 代表要测试的是读还是写还是两者混合的
$ mysqlslap --only-print --auto-generate-sql --number-of-queries=100 –auto-generate-sql-write-number=10000
--auto-generate-sql-load-type=read | awk ‘{print $1}‘ |
egrep ‘(INSERT|SELECT)‘ | sort | uniq -c | sort -n -r
9999 INSERT
100 SELECT

#例子8：并发数1,2,3次执行性能比较，共执行查询100次，生成100000个INSERT语句
$ mysqlslap --auto-generate-sql --concurrency=1,2,3 –-iteration=10 \
--number-of-queries=100 –auto-generate-sql-write-number=100000
Benchmark
Average number of seconds to run all queries: 4.522 seconds
Minimum number of seconds to run all queries: 4.216 seconds
Maximum number of seconds to run all queries: 4.648 seconds
Number of clients running queries: 1
Average number of queries per client: 100
Benchmark
Average number of seconds to run all queries: 3.025 seconds
Minimum number of seconds to run all queries: 2.737 seconds
Maximum number of seconds to run all queries: 3.227 seconds
Number of clients running queries: 2
Average number of queries per client: 50
Benchmark
Average number of seconds to run all queries: 2.618 seconds
Minimum number of seconds to run all queries: 2.338 seconds
Maximum number of seconds to run all queries: 2.746 seconds
Number of clients running queries: 3
Average number of queries per client: 33

#例子9：比较myisam,innodb引擎性能，并发执行数4，共执行100次查询
$ mysqlslap --auto-generate-sql –-concurrency=4 \
--engine=myisam,innodb --number-of-queries=100 \
--iteration=10 --auto-generate-sql-write-number=100000
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 2.232 seconds
Minimum number of seconds to run all queries: 2.003 seconds
Maximum number of seconds to run all queries: 2.319 seconds
Number of clients running queries: 4
Average number of queries per client: 25
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 5.332 seconds
Minimum number of seconds to run all queries: 5.314 seconds
Maximum number of seconds to run all queries: 5.370 seconds
Number of clients running queries: 4
Average number of queries per client: 25

#例子10：比较myisam,innodb引擎性能，并发执行数4，共执行1000次查询
$ mysqlslap --auto-generate-sql --concurrency=4 \
--engine=myisam,innodb --number-of-queries=1000 \
--iteration=10 --auto-generate-sql-write-number=10000
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 2.059 seconds
Minimum number of seconds to run all queries: 1.937 seconds
Maximum number of seconds to run all queries: 2.169 seconds
Number of clients running queries: 4
Average number of queries per client: 250
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 5.604 seconds
Minimum number of seconds to run all queries: 5.560 seconds
Maximum number of seconds to run all queries: 5.659 seconds
Number of clients running queries: 4
Average number of queries per client: 250

#例子11：比较myisam,innodb引擎性能，并发执行数4，共执行1000次查询
#concurrent_insert=0 无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都不允许并发INSERT
SET GLOBAL concurrent_insert=0;
$ mysqlslap --auto-generate-sql –-concurrency=4 –engine=myisam,innodb \
--number-of-queries=1000 --iteration=10 --auto-generate-sql-write-number=10000
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 3.256 seconds
Minimum number of seconds to run all queries: 3.210 seconds
Maximum number of seconds to run all queries: 3.317 seconds
Number of clients running queries: 4
Average number of queries per client: 250
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 5.615 seconds
Minimum number of seconds to run all queries: 5.585 seconds
Maximum number of seconds to run all queries: 5.699 seconds
Number of clients running queries: 4
Average number of queries per client: 250
SET GLOBAL concurrent_insert=1;

#例子12：比较memory,myisam,innodb引擎性能，并发执行数4，共执行1000次查询
#--auto-generate-sql-add-autoincrement 代表对生成的表自动添加auto_increment列
mysqlslap --auto-generate-sql -–concurrency=4 \
--engine=memory,myisam,innodb --iteration=10 \
--number-of-queries=1000 –auto-generate-sql-write-number=10000 --auto-generate-sql-add-autoincrement
CREATE TABLE `t1` (id serial,intcol1 INT(32) ,charcol1 VARCHAR(128));
Running for engine memory
Average number of seconds to run all queries: 0.035 seconds
Running for engine myisam
Average number of seconds to run all queries: 0.038 seconds
Running for engine innodb
Average number of seconds to run all queries: 0.032 seconds

InnoDB比MEMORY和MyISAM更快？

#例子13：增加测试的持续时间。比较memory,myisam,innodb引擎性能，并发执行数4，共执行10000次查询
$ mysqlslap --auto-generate-sql --concurrency=4
--engine=memory,myisam,innodb --iteration=10 \
--number-of-queries=10000 --auto-generate-sql-writenumber=
10000 --auto-generate-sql-add-autoincrement
Running for engine memory
Average number of seconds to run all queries: 0.430 seconds
Running for engine myisam
Average number of seconds to run all queries: 0.467 seconds
Running for engine innodb
Average number of seconds to run all queries: 0.327 seconds

结果InnoDB胜！

#例子14：增加更多字段。比较memory,myisam,innodb引擎性能，并发执行数4，共执行10000次查询
#--auto-generate-sql, -a 自动生成测试表和数据
#--concurrency=N, -c N 表示并发量，模拟多少个客户端同时执行select
#-number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
#--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
#--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
$ mysqlslap mysqlslap -a -c4 -y4 -x4
--engine=memory,myisam,innodb -i10 --number-ofqueries=
10000 --auto-generate-sql-write-number=10000
--auto-generate-sql-add-autoincrement
Running for engine memory
Average number of seconds to run all queries: 0.504 seconds
Running for engine myisam
Average number of seconds to run all queries: 0.526 seconds
Running for engine innodb
Average number of seconds to run all queries: 0.468 seconds

InnoDB仍然获胜，但比上次慢了些。

#例子15：增加并发度。比较memory,myisam,innodb引擎性能，并发执行数8，共执行10000次查询
$ mysqlslap mysqlslap -a -c8 -y4 -x4
--engine=memory,myisam,innodb -i10 --number-ofqueries=
10000 --auto-generate-sql-write-number=10000
--auto-generate-sql-add-autoincrement
Running for engine memory
Average number of seconds to run all queries: 0.526 seconds
Running for engine myisam
Average number of seconds to run all queries: 0.596 seconds
Running for engine innodb
Average number of seconds to run all queries: 0.657 seconds

结果InnoDB最慢。

#例子16：工作负载，读取主键。比较memory,myisam,innodb引擎性能，并发执行数8，共执行10000次查询
#--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)
$ mysqlslap mysqlslap --auto-generate-sql-load-type=key -a
-c8 -y4 -x4 --engine=memory,myisam,innodb -i10 --number-ofqueries=
10000 --auto-generate-sql-write-number=10000 –autogenerate-
sql-add-autoincrement
Running for engine memory
Average number of seconds to run all queries: 0.278 seconds
Running for engine myisam
Average number of seconds to run all queries: 0.328 seconds
Running for engine innodb
Average number of seconds to run all queries: 0.324 seconds

数据库优化-基准测试（三）

标签：数据库

本文系统来源：http://blog.csdn.net/wilbertzhou/article/details/46835513

内容总结

以上是互联网集市为您收集整理的数据库优化-基准测试（三）全部内容，希望文章能够帮你解决数据库优化-基准测试（三）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---