Database optimization-Benchmark test (v)

Benchmark Test Tool: DBT2

DBT2 is an OLTP transactional performance testing tool. It simulates a wholesale vendor, multiple employees accessing the database, updating customer information and checking inventory.
DBT2 is a good implementation of the TPC ' s TPC-C benchmark specification, which is one of MySQL's most popular benchmark tools, but its documentation is scarce.

DBT2: Installing the Perl module

Perl modules required by DBT2:

Statistics::DescriptiveTest::ParserTest::Reporter

How to install the Perl module:

sudosudosudo cpan Test::Reporter

Note: If these packages are missing, it will not return any compilation errors.

DBT2: Download and compile

:
http://sourceforge.net/projects/osdldbt/files/dbt2/

$ wget http://downloads.sourceforge.net/project/osdldbt/dbt2/0.40/dbt2-0.40.tar.gz$ tar -zxf dbt2-0.40.tar.gz$ cd dbt2-0.40$ ./configure –with-mysql[=/usr/local/mysql]$ make

Note: If you do not install MySQL or its variants through the Package Manager (RPM, DEB), you can specify a –mysql path.
For example: Unpack the MySQL tar package in/usr/local/mysql.

DBT2: Running the test

DBT2 tests include the following stages:

• Generate Data
• Loading data
• Run benchmark Tests

1. Generating data

Generate the data used by the test through DataGen.
How to use:

datagen-w # [-C #] [-I #] [-O #] [-S #] [-N #] [-D <str>]-w #: Warehouse table (warehouse) cardinality  -c #: Client table (Customer) cardinality, default-  class= "Ruby" >i #: Commodity table (item) cardinality, default 100000  -o #: Order form cardinality, default " "- n #: New Order table (New-order) base, default  -d <path> :  output path to data files --sapdb :  sap  db  data format --pgsql :  Data format for span class= "hljs-constant" >postgresql  --mysql :  mysql  data format   

For example:

$ mkdir /tmp/dbt2-w10-w10-d /tmp/dbt2-w10 –mysql

2. Loading data

Load data using build_db.sh.
Options:

-d <database name>-f <path to dataset files>-g (generate data files)-s <database socket>-h <database host>-u <database user>-p <database password> *-e <storage engine: [MYISAM|INNODB]. (default INNODB)>-l <to use LOCAL keyword while loading dataset> *-v <verbose>-w <scale factor>

Example:

build_db.-w3-d-f /tmp/dbt2-w3-s /var/run/mysqld/mysqld.-h-u-p-e-v –l

Problems in use:

(1) 文档说使用scripts/mysql/mysql_load_db.sh，但这个脚本不存在。取而代之，使用script/mysql/build_db.sh。(2whilegetopts"d:h:f:gs:e:m:u:p:vlw:"whilegetopts"d:h:f:gs:e:m:u:p:vw:"选项。数据库密码不是通过-p参数而是通过-x接收。

3. Running the data

Run the test using run_workload.sh.
Usage:

run_workload.sh -c <numberofof test> -w <numberof warehouses>

Other options:

-d <database name. (default dbt2)> *-h <database host name. (default localhost)> *-l <database port number>-o <enable oprofile data collection>-s <delay of starting of new threads in milliseconds>-n <no thinking or keying time (default no)>-u <database user>-x <database password>-z <comments for the test>

Problems in use:

(1) 文档说使用scripts/run_mysql.sh，但这个脚本不存在！取而代之使用scripts/run_workload.sh。(2) 选项:-h 不是指定数据库的主机名，而是会打印帮助！使用-H代替-d 不是数据库名称，而是测试持续的秒数运行会失败，除非运行下面的命令：$ export USE_PGPOOL=0(3) 在脚本中Socket /tmp/mysql.sock是硬编码的。如果你的服务器的socket在不同位置，可以简单地创建一个symlink /tmp/mysql.sock 到你的socket位置 , 例如：$ ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock

Example:

./run_workload.-c10-d900-n-w10-s60-u-x password

Benchmark Test Tool: Tpcc-mysql

Tpcc-mysql is an OLTP transactional performance testing tool, a benchmark tool developed by Percona. It generates TPC-C workloads.

Acquisition of Tpcc-mysql Software

Launchpad Location:
Https://code.launchpad.net/~perconadev/perconatools/tpcc-mysql
Get branches:

$ bzr branch lp:~percona-dev/perconatools/tpcc-mysql

Compile:

$ cd tpcc-mysql/src$ make$ cd ..

Test of Tpcc-mysql 1. Prepare the database

create tpcc10mysql tpcc10 < create_table.sqlmysql tpcc10 < add_fkey_idx.sql

2. Loading data

./tpcc_load localhost tpcc10 root pass 10选项:- hostname- dbname- dbuser- dbpass- number of warehouses

3. Start testing

tpcc_start -h localhost -d tpcc10 -u root -p pass-w 10 -c 32 -r 10 -l 600选项:-h : hostname-d : dbname-u : dbuser-p : password-w : warehouses-c : connections-r : rampup (warmup time)-l : benchmark duration在 README里的不正确用法: tpcc_start localhost tpcc1000 root "" 1000 32 10 10800

4. Test results

Database optimization-Benchmark test (v)