MySQL pressure test Tool sysbench installation test detailed

If you are evaluating the pressure of a MySQL database, you can use Sysbench to test

The concrete operation out, installs the Sysbench tool first, installs the operation as follows:

Installation Environment

CentOS Release 5.4 (Final)
MySQL 5.1.40 mysql_home=/usr/local/mysql/
Sysbench 0.4.12

Installation steps:

1. Go to http://sourceforge.net/projects/sysbench/to download the latest version of Sysbench 0.4.12

2. Decompression sysbench-0.4.12.tar.gz

Tar--zxvf sysbench-0.4.12.tar.gz

3. Enter the decompression package sysbench-0.4.12 and execute the script autogen.sh

CD sysbench-0.4.12
./autogen.sh

4. The Key trilogy:

Configure&make&make Install

First of all, the./configure command, sysbench default is to support MySQL benchmarking, if no option is required to ensure that the MySQL installation path is the default standard path, headfile located in the/usr/include directory, Libraries is located in the/usr/lib/directory. Because my MySQL is compiled by the source code, installation path is placed under the/usr/local/mysql, so here to add the appropriate options command:

./configure--prefix=/usr/local/mysql--with-mysql=/usr/local/mysql--with-mysql-includes=/usr/local/mysql/ include/mysql/--with-mysql-libs=/usr/local/mysql/lib/mysql/

Note: After repeated testing here at compile time to write the path to the last Include/mysql,lib/mysql

--with-mysql-includes=/usr/local/mysql/include/mysql/
--with-mysql-libs=/usr/local/mysql/lib/mysql/

Because a lot of information on the internet did not mention this layer, at compile time always compile not past, here wasted a lot of energy.

Make
Make install

Install on it, let's take a look at the test

Test Case for Sysbench database:


The first step: Prepare the database, ready to create 1000000 data volume in the MySQL database, the storage engine is InnoDB.


Bin/sysbench--TEST=OLTP \--Test type database OLTP


--mysql-host=127.0.0.1 \--mysql Host


--mysql-port=3421 \--mysql Port


--mysql-user=root \--mysql Login User


--mysql-password= $password \--mysql Password


--mysql-db=test \--mysql Test Database name


--mysql-socket=/var/mysql.sock \--socket Location


--oltp-table-name=test \--mysql Test Table name


--MYSQL-TABLE-ENGINE=INNODB \--mysql table using storage engine


--oltp-table-size=1000000 Prepare--table data quantity


Step Two: Start testing,


Bin/sysbench--TEST=OLTP \


--mysql-host=127.0.0.1 \--mysql Host


--mysql-port=3421 \--mysql Port


--mysql-user=root \--mysql Login User


--mysql-password= $password \--mysql Password


--mysql-db=test \--mysql Test Database name


--mysql-socket=/var/mysql.sock \--socket Location


--oltp-table-name=test \--mysql Test Table name


--MYSQL-TABLE-ENGINE=INNODB \--mysql table using storage engine


--max-requests=100000 \--Number of test requests


--MAX-TIME=600 \--Testing for the longest time


--num-threads=100 run--Number of concurrent threads


Test results:


Sysbench 0.4.12:multi-threaded System Evaluation Benchmark


No DB drivers specified, using MySQL


Running the test with following options:


Number of threads:100


Doing OLTP test.


Running Mixed OLTP Test


Using Special Distribution (iterations, 1 pct of values are returned in PCT cases)


Using "BEGIN" for starting transactions


Using auto_inc on the ID column


Maximum number of requests for OLTP Test was limited to 100000


Threads started!


Done.


OLTP Test Statistics:


Queries performed:


read:1401582


write:500563


other:200225


total:2102370


transactions:100112 (3446.37 per sec.)


Deadlocks:1 (0.03 per sec.)


Read/write requests:1902145 (65481.66 per sec.)


Other operations:200225 (6892.78 per sec.)


Test Execution Summary:


Total time:29.0485s


Total number of events:100112


Total time taken by event execution:2898.7554


Per-request Statistics:


Min:3.52ms


Avg:28.96ms


Max:187.11ms


Approx. Percentile:46.05ms


Threads Fairness:


Events (Avg/stddev): 1001.1200/10.80


Execution Time (Avg/stddev): 28.9876/0.03


Step three: Clean up the test data.


Bin/sysbench--TEST=OLTP \


--mysql-host=127.0.0.1 \--mysql Host


--mysql-port=3421 \--mysql Port


--mysql-user=root \--mysql Login User


--mysql-password= $password \--mysql Password


--mysql-db=test \--mysql Test Database name


--mysql-socket=/var/mysql.sock \--socket Location


--oltp-table-name=test \--mysql Test Table name


--MYSQL-TABLE-ENGINE=INNODB \--mysql table using storage engine


Cleanup


To support custom statements in version 0.5, refer to the links below.


Http://dba.stackexchange.com/questions/39221/stress-test-mysql-with-queries-captured-with-general-log-in-mysql


3. Test Cases, table properties compress the impact of insert speed.


The first step is to prepare:


Create a table structure


Bin/sysbench--TEST=OLTP \--Test type database OLTP


--mysql-host=127.0.0.1 \--mysql Host


--mysql-port=3421 \--mysql Port


--mysql-user=root \--mysql Login User


--mysql-password= $password \--mysql Password


--mysql-db=test \--mysql Test Database name


--mysql-socket=/var/mysql.sock \--socket Location


--oltp-table-name=sbtest \--mysql Test Table name


--MYSQL-TABLE-ENGINE=INNODB \--mysql table using storage engine


--oltp-table-size=1 Prepare--table data quantity


Modify the table name to uncompressed


Rename table Sbtest to uncompressed;


Create the table sbtest again and modify the table name to compressed


Rename table Sbtest to compressed;


Change table to Compressed


ALTER TABLE compressed row_format=compressed;


Step two: Add a record to the table uncompressed table


Bin/sysbench--TEST=OLTP \


--mysql-host=127.0.0.1 \--mysql Host


--mysql-port=3421 \--mysql Port


--mysql-user=root \--mysql Login User


--mysql-password= $password \--mysql Password


--mysql-db=test \--mysql Test Database name


--mysql-socket=/var/mysql.sock \--socket Location


--oltp-table-name=uncompressed \--mysql Test Table name


--MYSQL-TABLE-ENGINE=INNODB \--mysql table using storage engine


--oltp-nontrx-mode=insert \


--oltp-test-mode=nontrx \


--max-requests=1000000 \


--max-time=600 \


--num-threads=100 Run


Test results:


OLTP Test Statistics:


Queries performed:


read:0


write:1001110


other:0


total:1001110


Transactions:1001110 (13617.75 per sec.)


Deadlocks:0 (0.00 per Sec.)


Read/write requests:1001110 (13617.75 per sec.)


Other operations:0 (0.00 per Sec.)


Test Execution Summary:


Total time:73.5151s


Total number of events:1001110


Total time taken by event execution:7346.0036


Per-request Statistics:


Min:0.10ms


Avg:7.34ms


Max:239.05ms


Approx. Percentile:22.00ms


Threads Fairness:


Events (Avg/stddev): 10011.1000/86.80


Execution Time (Avg/stddev): 73.4600/0.00


Description: Total consumption of 73s. Up to 13,617 transactions per second.


Step three: Add a record to the table compressed table


Bin/sysbench--TEST=OLTP \


--mysql-host=127.0.0.1 \--mysql Host


--mysql-port=3421 \--mysql Port


--mysql-user=root \--mysql Login User


--mysql-password= $password \--mysql Password


--mysql-db=test \--mysql Test Database name


--mysql-socket=/var/mysql.sock \--socket Location


--oltp-table-name=compressed \--mysql Test Table name


--MYSQL-TABLE-ENGINE=INNODB \--mysql table using storage engine


--oltp-nontrx-mode=insert \


--oltp-test-mode=nontrx \


--max-requests=1000000 \


--max-time=600 \


--num-threads=100 Run


Test results:


OLTP Test Statistics:


Queries performed:


read:0


write:1000515


other:0


total:1000515


transactions:1000515 (2313.85 per sec.)


Deadlocks:0 (0.00 per Sec.)


Read/write requests:1000515 (2313.85 per sec.)


Other operations:0 (0.00 per Sec.)


Test Execution Summary:


Total time:432.4036s


Total number of events:1000515


Total time taken by event execution:43229.6698


Per-request Statistics:


Min:0.10ms


Avg:43.21ms


Max:730.32ms


Approx. Percentile:133.46ms


Threads Fairness:


Events (Avg/stddev): 10005.1500/109.30


Execution Time (Avg/stddev): 432.2967/0.01


Note: The total consumption of 432s, processing 2313 transactions per second. and uncompressed speed difference 6 times times.


4. Test Cases, table properties compress the effect of composite operation speed.


Step one: Prepare the data and prepare for the 3rd item.


The second step: compound operation test for compressed table.


Bin/sysbench--TEST=OLTP \


--mysql-host=127.0.0.1 \--mysql Host


--mysql-port=3421 \--mysql Port


--mysql-user=root \--mysql Login User


--mysql-password= $password \--mysql Password


--mysql-db=test \--mysql Test Database name


--mysql-socket=/var/mysql.sock \--socket Location


--oltp-table-name=compressed \--mysql Test Table name


--MYSQL-TABLE-ENGINE=INNODB \--mysql table using storage engine


--max-requests=1000000 \


--max-time=600 \


--num-threads=100 Run


OLTP Test Statistics:


Queries performed:


read:14012236


write:5004356


other:2001743


total:21018335


transactions:1000869 (3145.37 per sec.)


Deadlocks:5 (0.02 per Sec.)


Read/write requests:19016592 (59762.20 per sec.)


Other operations:2001743 (6290.75 per sec.)


Test Execution Summary:


Total time:318.2043s


Total number of events:1000869


Total time taken by event execution:31800.5571


Per-request Statistics:


Min:3.19ms


Avg:31.77ms


Max:344.54ms


Approx. Percentile:53.37ms


Threads Fairness:


Events (Avg/stddev): 10008.6900/32.50


Execution Time (Avg/stddev): 318.0056/0.01


Description: Total consumption time 318s, 3,145 transactions per second.


The third step: compound operation test for uncompressed table.


Bin/sysbench--TEST=OLTP \


--mysql-host=127.0.0.1 \--mysql Host


--mysql-port=3421 \--mysql Port


--mysql-user=root \--mysql Login User


--mysql-password= $password \--mysql Password


--mysql-db=test \--mysql Test Database name


--mysql-socket=/var/mysql.sock \--socket Location


--oltp-table-name=uncompressed \--mysql Test Table name


--MYSQL-TABLE-ENGINE=INNODB \--mysql table using storage engine


--oltp-nontrx-mode=insert \


--oltp-test-mode=nontrx \


--max-requests=1000000 \


--max-time=600 \


--num-threads=100 Run


OLTP Test Statistics:


Queries performed:


read:14013370


write:5004769


other:2001908


total:21020047


transactions:1000953 (3389.22 per sec.)


Deadlocks:2 (0.01 per sec.)


Read/write requests:19018139 (64395.20 per sec.)


Other operations:2001908 (6778.44 per sec.)


Test Execution Summary:


Total time:295.3347s


Total number of events:1000953


Total time taken by event execution:29512.3204


Per-request Statistics:


Min:3.37ms


Avg:29.48ms


Max:157.93ms


Approx. Percentile:48.19ms


Threads Fairness:


Events (Avg/stddev): 10009.5300/30.59


Execution Time (Avg/stddev): 295.1232/0.02

Description: Total consumption time 295s, 3,389 transactions per second. In a composite operation, the compressed table will be faster.