DBT2 Benchmark Tool

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

$ ./congigure --with-mysql

$ make

$ sudo make install 

安裝好datagen，然后生成數(shù)據(jù)，需要先創(chuàng)建對(duì)應(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ù)庫(kù)中。

benchmark  使用方法：

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

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

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

select benchmark(1, select * from MYTABLE  limit 0,40 );   Mysql  報(bào)錯(cuò)，說(shuō)明EXP 不能為sql 語(yǔ)句，只能為表達(dá)式或函數(shù)

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

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

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

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

為什么執(zhí)行100次和執(zhí)行 1次的時(shí)間耗費(fèi)差不多呢？ 這是因?yàn)榭赡鼙蛔⑷肓司彺娴木壒�，因此benchmark 不能用來(lái)完成一種實(shí)際的  基準(zhǔn)測(cè)試。

英文介紹：

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