DBT2 Benchmark Tool

下載DBT2 Benchmark，編譯安裝，按照README-MYSQL中的步驟來操作。報告的時間是客戶端的經(jīng)過時間，不是在服務(wù)器端的CPU時間。執(zhí)行BENCHMARK()若干次可能是明智的，并且注意服務(wù)器機器的負載有多重來解釋結(jié)果。需要由aclocal、autoheader、autoconf、automake，以及mysql、mysql-libs、mysql-devel。

$ ./congigure --with-mysql

$ make

$ sudo make install 

安裝好datagen，然后生成數(shù)據(jù)，需要先創(chuàng)建對應(yīng)的目錄如/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

生成完數(shù)據(jù)后就可以執(zhí)行scripts/mysql/mysql_load_db.sh腳本將數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中。

benchmark  使用方法：

這里還有一種方法能夠獲取到 mysql 執(zhí)行一個sql 語句或sql 表達式的 時間，并且可以由用戶指定執(zhí)行次數(shù)。通過執(zhí)行大規(guī)模次數(shù)，         才能夠獲得比較穩(wěn)定的 sql 執(zhí)行時間。

select   benchmark(num, exp) ;   其中 num 為執(zhí)行次數(shù)，exp 為希望獲得執(zhí)行時間的表達式 ，不包括 sql 語句

select benchmark(1, to_days(2011-04-07) - to_days(now()) <1 );   執(zhí)行時間大概是 0.0011秒 ，可以計算表達式時間

select benchmark(1, select * from MYTABLE  limit 0,40 );   Mysql  報錯，說明EXP 不能為sql 語句，只能為表達式或函數(shù)

下面是對num 賦予不同的數(shù)值，查看執(zhí)行不同次數(shù)所耗費的時間

select benchmark(100, to_days(2011-04-07) - to_days(now()) <1 );     執(zhí)行時間大概也是 0.0012秒

select benchmark(10000, to_days(2011-04-07) - to_days(now()) <1 );     執(zhí)行時間大概也是 0.0056秒

select benchmark(1000000, to_days(2011-04-07) - to_days(now()) <1 );     執(zhí)行時間大概也是 0.4454秒

為什么執(zhí)行100次和執(zhí)行 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