数据库优化-基准测试(三)

翻译 2015年07月10日 22:02:53

基准测试工具

基准测试工具:mysqlslap

是MySQL官方提供的性能基准测试工具,通过客户端模拟工作负载。
其执行包括三个阶段:
1. 创建表结构和加载数据
2. 运行测试
3. 清理数据

#例子1:--only-print 只输出SQL语句并打印 --auto-generate-sql 代表用系统自己生成的SQL脚本来测试
$ 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

inner join MySQL Profiles 三表内联查询效率优化

今天系统爆出一个慢SQL语句,经过分析确定查询时间过长的原因主要是数据量大三张表内联耗时较长;经过重新设计将不必要的内联逻辑排除,成功的将查询时间降低一个数量级。...
  • mrlichengyi
  • mrlichengyi
  • 2017年06月25日 17:29
  • 512

数据库三范式的简单理解

参考书籍:数据库系统概论第四版-王珊、萨师煊 数据库范式 范式的级别       设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递...
  • zymx14
  • zymx14
  • 2017年04月08日 23:58
  • 1310

关系型数据库三范式

关系数据库、 三大范式
  • niclascage
  • niclascage
  • 2015年10月07日 21:35
  • 1747

数据库的三范式详解

数据库的三范式 1N:关系R中的属性都是不可分割的项. 2N:在1N的基础上,每个非主属性完全函数依赖于码. 3N:在2N的基础上,每一个非主属性既不部分依赖于码也不传递依赖于码....
  • bluend1004
  • bluend1004
  • 2013年08月17日 21:22
  • 948

数据库性能优化详解

1.数据库访问优化法则 要正确的优化SQL,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?而大多数情况性能最慢的设备会是瓶颈点,如下载时网络速度可能会是瓶颈点...
  • yzllz001
  • yzllz001
  • 2017年02月03日 17:38
  • 34685

如何实现数据库优化

如何实现数据库优化
  • gmjabc
  • gmjabc
  • 2016年02月03日 13:43
  • 929

数据库优化之MySQL优化(三):

数据库结构优化 1:选择合适的数据类型 数据类型的选择重点在于合适两个字,如何确定选择的数据类型是否合适? 1.使用可以存下你的数据的最小的数据类型 2.使用简单的数据类型,In...
  • a3060858469
  • a3060858469
  • 2017年11月02日 14:40
  • 136

理解数据库设计的三大范式

为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库,必须满足一定的范式。   ...
  • heicm
  • heicm
  • 2014年10月28日 15:45
  • 1675

数据库优化操作及方案

MySQL数据量大时,优化可以进行的操作:MySQL分库分表,MySQL缓存,MySQL索引,MySQL优化查询语句? 数据库优化方案:缓存;数据库表的大字段剥离;恰当的使用索引;表库的拆分;字段冗余...
  • ShareUs
  • ShareUs
  • 2017年05月12日 12:58
  • 535

CUDA程序优化小记(三)

CUDA全称ComputerUnified Device Architecture(计算机同一设备架构),它的引入为计算机计算速度质的提升提供了可能,从此微型计算机也能有与大型机相当计算的能力。可是不...
  • jiangcaiyang123
  • jiangcaiyang123
  • 2014年01月02日 23:10
  • 2593
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据库优化-基准测试(三)
举报原因:
原因补充:

(最多只允许输入30个字)