DBT2 Benchmark Tool

下载DBT2 Benchmark，编译安装，按照README-MYSQL中的步骤来操作。报告的时间是客户端的经过时间，不是在服务器端的CPU时间。执行BENCHMARK()若干次可能是明智的，并且注意服务器机器的负载有多重来解释结果。需要由aclocal、autoheader、autoconf、automake，以及mysql、mysql-libs、mysql-devel。

$ ./congigure --with-mysql

$ make

$ sudo make install 

安装好datagen，然后生成数据，需要先创建对应的目录如/tmp/dbt2-w3。

$ datagen -w 3 -d  /tmp/dbt2-w3 --mysql

warehouses = 3
first warehouse = 1
districts = 10
customers = 3000
items = 100000
orders = 3000
stock = 100000
new_orders = 900

生成完数据后就可以执行scripts/mysql/mysql_load_db.sh脚本将数据导入到数据库中。

benchmark  使用方法：

这里还有一种方法能够获取到 mysql 执行一个sql 语句或sql 表达式的 时间，并且可以由用户指定执行次数。通过执行大规模次数，         才能够获得比较稳定的 sql 执行时间。

select   benchmark(num, exp) ;   其中 num 为执行次数，exp 为希望获得执行时间的表达式 ，不包括 sql 语句

select benchmark(1, to_days(2011-04-07) - to_days(now()) <1 );   执行时间大概是 0.0011秒 ，可以计算表达式时间

select benchmark(1, select * from MYTABLE  limit 0,40 );   Mysql  报错，说明EXP 不能为sql 语句，只能为表达式或函数

下面是对num 赋予不同的数值，查看执行不同次数所耗费的时间

select benchmark(100, to_days(2011-04-07) - to_days(now()) <1 );     执行时间大概也是 0.0012秒

select benchmark(10000, to_days(2011-04-07) - to_days(now()) <1 );     执行时间大概也是 0.0056秒

select benchmark(1000000, to_days(2011-04-07) - to_days(now()) <1 );     执行时间大概也是 0.4454秒

为什么执行100次和执行 1次的时间耗费差不多呢？ 这是因为可能被注入了缓存的缘故，因此benchmark 不能用来完成一种实际的  基准测试。

英文介绍：

The DBT2 Benchmark Tool can be used to run automated benchmarks for MySQL and MySQL Cluster. It supports three types of benchmarks:

DBT2

SysBench

flexAsynch

It has been primarily used on Linux x86_64 platforms, but occasional benchmarks have also been run on Solaris and Windows. It can be used to test MySQL based on MySQL 5.6 and MySQL 5.7 Releases (older versions might work as well but isn't maintained any more).

DBT2 is an open source benchmark that mimics an OLTP application for a company owning large amounts of warehouses. It contains transactions to handle New Orders, Order Entry, Order Status, Payment and Stock handling. The transactions are a mix of read and write transactions. Using MySQL the benchmark tests a single MySQL Server instance. Using MySQL Cluster the benchmark tool can drive large distributed tests with many MySQL Cluster Data nodes and MySQL Server instances. The DBT2 Benchmark Tool provides scripts to automate execution of these benchmarks.

The DBT2 tarball also contains a benchmark tool using PowerShell on Windows to run sysbench on Windows. There is also scripts mimicing top on Windows. Finally there is also a set of simple scripts to use the perf tool on Linux.

FlexAsynch is a benchmark specifically developed to test scalability of MySQL Cluster. It is found in any MySQL Cluster source tarball under storage/ndb/test/ndbapi. The features required to run it in this parallel manner requires a MySQL Cluster 7.x version released after the 15th of October 2011. The DBT2 Benchmark Tool can be used to run distributed tests with many MySQL Cluster Data nodes and many flexAsynch benchmark programs in a completely automated fashion. The latest version of flexAsynch mainly exists in the source tree for the 2 most recent versions.

MySQL Server Version: 5.6 and later
MySQL Cluster Version: MySQL Cluster 7.3 and later