MySQL Benchmark Test --- sysbench

MySQL benchmark Test-sysbench 1. When you benchmark the overall website performance, you can use a variety of tools, such as the well-known AB
(Apache load), http_load and other tools. Here we do not care about their use. If you want to know, you can go
Http://en.wikipedia.org/wiki/apachebench. Www.2cto.com focuses on how to conduct MySQL benchmark tests. There are also many tools available for us to choose from, such as mysqlslap,
Sysbench, Super Smack, etc. The mysqlslap MySQL official website provides an introduction, Super Smack
Sysbench is a powerful tool for server stress testing. It is a great tool for MySQL Benchmark Testing. Sysbench is an open-source multi-thread performance testing tool that can execute CPU/memory/thread/IO/Database
Performance testing. The database currently supports MySQL, Oracle, and PostgreSQL. In this test, the sysbench version is 0.4.12, And the MySQL version is 5.1.61. the ubuntu version is: 10.10 The basic usage of sysbench can be easily seen through man sysbench or sysbench -- help: sysbench [general-options]... -- test = <test-name> [test-options]... command general-options for the moment, test-name includes fileio/cpu/memory/threads/mutex/oltp testing,
Command includes prepare/run/cleanup/help/version and so on (1) Test the cpu test sysbench by finding the maximum prime number. The command is as follows: root @ ubuntu :~ # Sysbench -- test = cpu -- cpu-max-prime = 1500 run from total time: a row shows that 1500 s (2) is used to obtain the maximum prime number less than 3.2214) for fileio testing, you must first generate a file, and then test the fileio performance to generate a file: sysbench -- test = fileio prepare. When you use the fileio command, there will be many parameters that can be used
-- File-block-size specifies the size of a single file. You can use -- file-total-size to specify the size of the entire file.
Use the default parameter. The size of a single file is 16 MB, and the size of the entire file is 2 GB. As shown in the following figure: a total of 128 files are generated. Read/write files sysbench -- test = fileio -- file-test-mode = rndrw run,
As shown below: in this report, there are two parameters that deserve our attention, namely, the number of requests per second and the total throughput.
It is 785.16 Requests/sec and 12.268 Mb/sec. These two parameters are very helpful for evaluating disk performance. Complete the test
After the test, you need to delete the test data: sysbench -- test = fileio cleanup (3) oltp test first generates the data: sysbench -- test = oltp -- oltp-table-size = 2000000 -- oltp-table-name = tt-
-Mysql-db = test -- mysql-user = root -- msyql-password = 123 prepare. The test username is root.
Password: 123 after the data is generated, perform the test and use eight threads for the test, as shown below: The above data is very helpful for the analysis of multi-thread transaction processing, such as the transaction President count, transaction volume per second, etc. sysbench is similar to other tests... 2. For MySQL performance analysis, there are many good commands in the server for our use, such
Show status/show processlist/show innodb status in show status, "Select %" generally refers to different types of connection execution plans, "Handler %" generally refers to the storage
Sort information. "Sort %" generally refers to sorting information, and "Created %" generally refers to information about creating a temporary table.
You can also use mysqlreport to print statistics. The show processlist also prints related parameters,
In this case, Innotop can be used instead for convenient analysis, or mytop can be used for analysis. If it is used in session, you must
Add the session parameter, that is, show session status. Use show processlist to locate the mysql connection and
This function is generally used together with prep netstat psof ps. Use tcpdump to capture packets for analysis
Traffic. The powerful functions of innotop and tcpdump will be analyzed later.