MySQL pressure test Tool sysbench installation test detailed

Source: Internet
Author: User
Tags mysql host prepare socket table name mysql database mysql login


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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.