Mysql Benchmark Test Mysqlslap,sysbench

Baseline Test Basic steps

The design is as simple as possible, and it is clear what type of test to use, what kind of data

For example: reflect the actual situation, with the production environment of data backup, can be logged, access to the volume of playback

If you test performance, you can generate data with test tools.

Execution once and for a short time is meaningless, the average of multiple results. Collect as much information as possible

Benchmark test File Download

Analysis File Download

Start the actual test

Mysqlslap--help Check if the Mysqlslap test tool is installed

Mysqlslap--concurrency=1,50,100,200--iterations=3--number-int-cols=5--number-charcols=5--auto-generate-sql-- Auto-generate-sql-add-autoincrement--engine=myisam,innodb--number-of-queries=10--create-schema=sbtest

Parameter description

--CONCURRENCY=1,50,100,200 Specifies the number of concurrent 1 concurrent, 50 concurrent, 100 concurrent, 200 concurrent

--ITERATIONS=3 Complete 3 Tests

--number-int-cols=5-generated integer rows

--number-charcols=5 varchar column is also 5 rows

--auto-generate-sql automatically generate SQL

--auto-generate-sql-add-autoincrement automatically generate self-increment ID

--engine=myisam,innodb the engine that needs testing

--number-of-queries=10 specifying the query volume 10 times

--create-schema=sbtest Specifying a directory

When you're done, add the following command to run

--only-print View the executed script, do not run the script, take a look at what actually runs

Another Performance test tool Sysbench

The Mysqlslap test scenario does not perform well in testing performance and does not establish an index. CPU, memory, IO powerless

Sysbench supports scripting languages and allows you to customize settings. can be authoritative for testing tools, commonly used. Test method close to InnoDB test mode

Due to video voice errors, only. Also need to check information.

CD svsbench-0.5

CD Tests

CD db

LS *.lua

Sysbench--test=cpu--cpu-max-prime=10000 Test CPU Performance CPU to calculate the maximum conversion value 10000, only test the performance of the single core CPU

Free-m View the amount of operating system memory,

Current virtual machine memory 512M, test file is greater than 512M, here is set to 1G.

Sysbench--test=fileio--file-total-size=1g prepar to file io, test file size 1G. Prepar, start preparing.

All files are added about 1G.

Sysbench--test=fileio--num-threads=8--init-rng=on--file-total-size=1g--file-test-mode=rndrw--report-interval=1 Run

--test=fileio

--num-threads=8 the number of test processes concurrently

--init-rng=on

Size of the--file-total-size=1g file

--FILE-TEST-MODE=RNDRW IO type Random read/write

--report-interval=1 number of seconds interval output statistic results

Run starts running

Each 1-second output statistic results, reads the quantity, writes the quantity, synchronizes the quantity, the response time.

Cpu,io performance test before the new hardware limit

Next, perform a database performance test

1 Create a database, user name and password

Create databases Imooc; creating a Database

Grant Allprivileges on * * to [email protected] ' localhost ' identified by ' 123456 '; Create User name Password permissions

Go to the script directory tests/db ls-l *.lua

Preparing test data

Sysbench--test=./oltp.lua--mysql-table-engine=innodb--oltp-table-size=10000--mysql-db=immooc--mysql-user= Sbtest--mysql-password=123456--oltp-tables-count=10--mysql-socket=/usr/local/mysql/data/mysql.sock prepar

--test=./oltp.lua

--mysql-table-engine=innodb storage due to the situation

--oltp-table-size=10000 size of each table

--MYSQL-DB=IMMOOC DB Name

--mysql-user=sbtest User Name

--mysql-password=123456 Password

Number of--oltp-tables-count=10 OLTP tables

--mysql-socket=/usr/local/mysql/data/mysql.sock MySQL sock file for connection

Prepar Generating Data

Now view database, generate 10 tables, single table data row number 10000 rows

The next Test

Another client runs the System State Collection script bash in the background./get_test_info.sh &

Go to the first client run test sysbench prepar change to Run command

Enter the data collection directory and the data file has been generated.

When the test is complete, stop the collection script.

./analyze.sh analysis of collected data

Mysql Benchmark Test Mysqlslap,sysbench