MySQL Stress test---sysbench

I. Introduction of Sysbench

Sysbenchis an open-source multi-Threading Performance Testingtool that can performCpu/Memory/Thread/Io/Databaseand other aspects of performance testing. Databasecurrently supportsMysql/Oracle/postgresql.

Second, sysbench installation

Download sysbench-0.4.12.tar.gz

TAR-XVF sysbench-0.4.12.tar.gz

If MySQL is not installed in the standard directory, then you need to specify the path of MySQL.

Compile and install:

./configure--with-mysql-includes=/usr/local/mysqlndb/include--with-mysql-libs=/usr/local/mysql/libmakemake Install

Third, testing

The test is divided into prepare and run.

ndbcluster Test Prep is

Sysbench--test=oltp--mysql-table-engine=ndbcluster--mysql-db=ndb--oltp-table-size=100--mysql-user=root-- Mysql-password= ""--mysql-socket=/usr/local/mysql/tmp/mysql.sock--db-driver=mysql--num-threads=16 Prepare

Manually write a stored procedure to enter data:

DELIMITER $ $DROP PROCEDURE test.wuxin_erp_insert $ $CREATE PROCEDURE test.wuxin_erp_insert () begindeclare i INT; SET i=0; While i< 100000 doreplace into Ndb.sbtest SELECT i,0, ', ' qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt '; END while; COMMIT; end$ $DELIMITER;

Input data:

Call Test.wuxin_erp_insert ();

Test the thread separately as 8,16,32.

The following is tested with the number of threads 8

Number of concurrent Threads: 8

Sysbench --test=oltp --mysql-table-engine=ndbcluster --mysql-db=ndb --oltp-table-size= 100000 --mysql-user=root --mysql-password= ""  --mysql-socket=/usr/local/mysql/tmp/mysql.sock  --db-driver=mysql --num-threads=8 run test Results:warning: preparing of  "BEGIN"  is  unsupported, using emulation (last message repeated 7 times) Running  the test with following options:number of threads: 8doing oltp  Test. running mixed oltp testusing special distribution  (12 iterations,   1 pct of values are returned in 75 pct cases) Using  " BEGIN " for starting transactionsusing auto_inc on the id columnmaximum  number of requests for oltp test is limited to 10000threads  started! Done.oltp test statistics:    queries performed:         read:                             140000         write:                            50000         other:                            20000         total:                            210000     transactions:                         10000   (143.72 per sec.)     deadlocks:                            0        (0.00 per sec.)     read/write requests:                  190000  (2730.70 per sec.)     other operations:                     20000   (287.44 per sec.) test execution summary:    total time:                           69.5791s     total number of events:               10000    total time taken by event execution:  556.3746    per-request statistics:          min:                             0.0201s          avg:                             0.0556s          max:                             0.3690s          approx.  95 percentile:          0.0766sThreads fairness:    events  (Avg/stddev):            1250.0000/8.28    execution  time  (Avg/stddev):    69.5468/0.01

InnoDB Test Prep is

Sysbench--test=oltp--mysql-table-engine=innodb--mysql-db=test--oltp-table-size=100000--mysql-user=root-- Mysql-password= ""--mysql-socket=/usr/local/mysql/tmp/mysql.sock--db-driver=mysql--num-threads=8 Prepare

Test the thread separately as 8,16,32.

The following tests are performed with the number of threads 8:

Start Testing 8 Threads

sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=test --oltp-table-size=100000  --mysql-user=root --mysql-password= ""  --mysql-socket=/usr/local/mysql/tmp/mysql.sock -- Db-driver=mysql --num-threads=8 run test Results:warning: preparing of  "BEGIN"  is  Unsupported, using emulation (last message repeated 7 times) Running the  test with following options:number of threads: 8doing oltp test. running mixed oltp testusing special distribution  (12 iterations,   1 pct of values are returned in 75 pct cases) Using  " BEGIN " for starting transactionsusing auto_inc on the id columnmaximum  number of requests for oltp test is limited to 10000threads  started! Done.oltp test statistics:    queries performed:         read:                             140000         write:                            50000         other:                            20000         total:                            210000     transactions:                         10000   (771.84 per sec.)     deadlocks:                            0        (0.00 per sec.)     read/write requests:                  190000  (14664.87 per sec.)     other operations:                     20000   (1543.67 per sec.) test execution summary:    total time:                           12.9561s     total number of events:               10000    total time taken by event execution:  103.5427    per-request statistics:          min:                             0.0023s          avg:                             0.0104s          max:                             0.3084s          approx.  95 percentile:          0.0185sThreads fairness:    events  (Avg/stddev):            1250.0000/9.50    execution time   (Avg/stddev):    12.9428/0.01

This article is from the "Notes" blog, so be sure to keep this source http://sunflower2.blog.51cto.com/8837503/1670601

MySQL Stress test---sysbench