Sysbench a detailed tutorial on Mysql stress testing _mysql

Objective

In the overall performance of the site benchmark, you can use a variety of tools, such as the famous AB (Apache bench), http_load and other tools. Here we are not concerned about their use, if you want to know, you can find the answer online.

Focus on how the MySQL benchmark test, there are many tools for us to choose from, such as Mysqlslap, Sysbench, Super smack, which Mysqlslap's use of the MySQL official website gave the introduction, Super Smack is a powerful tool for server stress testing, so sysbench is a pretty good tool for our MySQL benchmark test.

Sysbench

Sysbench is an open source multithreaded performance testing tool that can perform performance testing on cpu/memory/thread/io/databases. The database currently supports mysql/oracle/postgresql;

It mainly includes the following kinds of tests:

1, CPU performance

2. Disk IO Performance

3. Performance of Dispatcher Program

4, memory allocation and transmission speed

5, POSIX threading performance

6. Database performance (OLTP benchmark)

Sysbench's database OLTP tests support MySQL, PostgreSQL, and Oracle, which are currently used primarily for Linux operating systems, and open source communities have migrated sysbench to Windows and supported the benchmark for SQL Server.

Nonsense don't say much, start.

First, Sysbench installation

MySQL version: mysql-community-server-5.6.29

Os:centos 6.7 x86_64

Sysbench 0.5 has some changes compared to the 0.4 version, including an OLTP test combined with a LUA script and a few more hidden options, which this article will involve getting part of.

Install the build dependencies first
$ sudo yum install gcc gcc-c++ automake make Libtool mysql-community-devel
$ cd/tmp && git c Lone Https://github.com/akopytov/sysbench.git
$ cd/tmp/sysbench &&/autogen.sh
$/configure- prefix=/usr/local/sysbench-0.5
$./make && sudo make install
//0.5 version requires Oltp.lua test script
// If the RPM package is installed, the
$ cd/usr/local/sysbench && sudo mkdir-p share/tests can be found under/usr/share/doc/sysbench/tests/db/ /db
$ cp/tmp/sysbench/sysbench/tests/db/*.lua share/tests/db/
$/bin/sysbench--version
sysbench 0.5

If you need to test PostgreSQL, Oracle, you need to add –with-oracle or configure the –with-pgsql parameter

Second, use Sysbench to the MySQL pressure test

2.1 Read-only examples

./bin/sysbench--test=./share/tests/db/oltp.lua \
--mysql-host=10.0.201.36--mysql-port=8066--mysql-user= Ecuser--mysql-password=ecuser \
--mysql-db=dbtest1a--oltp-tables-count=10--oltp-table-size=500000
report-interval=10--oltp-dist-type=uniform--rand-init=on--max-requests=0 \
--oltp-test-mode=nontrx-- Oltp-nontrx-mode=select \
--oltp-read-only=on--oltp-skip-trx=on \
--max-time=120--num-threads=12 \
[ Prepare|run|cleanup]

Note that the last line, before a test begins, requires prepare to prepare tables and data, run performs real pressure tests, and cleanup is used to purge data and tables. The actual Prepare table structure:

mysql> desc dbtest1a.sbtest1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra     |
+-------+------------------+------+-----+---------+----------------+
| ID  | INT (ten) unsigned | NO  | PRI | NULL  | auto_increment |
| | k   | int (a) unsigned | NO  | MUL | 0 | | |
C   | char (=)    | NO | | | | |     NO  | | |        |
+-------+------------------+------+-----+---------+----------------+
4 rows in Set (0.00 sec)

The test command above represents the OLTP benchmark for MySQL, the number of tables is 10, the number of rows per table is about 50w (how much of the delete will be inserts), and the read-only test for non-transactions, continuous 60s, the number of concurrent threads 12.

Options to note:

mysql-db=dbtest1a: Test the target database used, this library name to be created in advance

--oltp-tables-count=10: Number of tables produced

--oltp-table-size=500000: Number of rows of records produced per table

--oltp-dist-type=uniform: Specifies the random sampling type, the optional values are uniform (evenly distributed), Gaussian (Gaussian distribution), special (spatial distribution). Default is Special

--oltp-read-only=off: Indicates that more than just read-only SQL is generated, that is, read-write blending mode is used when using Oltp.lua. The default off, if set to on, does not produce Update,delete,insert SQL.

--oltp-test-mode=nontrx: Execution mode, this is a non transactional type. Optional values are Simple,complex,nontrx. Default is complex

simple: Easy Query,SELECT c FROM sbtest WHERE id=N


Complex (Advanced transactional) : Transaction mode with begin and commit before starting and ending transactions, a transaction can have multiple statements, such as point query, range query, sort query, update, delete, INSERT, etc. And in order not to break the data in the test table, the next record in this mode will add an identical record in the same transaction after it is deleted.

Nontrx (non-transactional) : Similar to simple, but can perform update/insert and so on, so if you do a continuous contrast test, you may need to cleanup,prepare again.

--oltp-skip-trx=[on|off] : Omit the begin/commit statement. Default is Off

--rand-init=on: Whether to initialize data randomly, if not randomized, then the initial good data each row content in addition to the primary key different from the other exactly the same

--num-threads=12: Number of concurrent threads, which can be understood to simulate the number of client concurrent connections

--report-interval=10: Indicates a test progress report per 10s output

--max-requests=0: The total number of requests generated by the stress test, which is set to 0 if the following max-time is used

--max-time=120: The duration of the stress test, here is 2 minutes.

Note that the values for different options will have different child options. For example, oltp-dist-type=special, for example, Oltp-dist-pct=1, oltp-dist-res=50 Two sub options, representing 50% of the query fell on 1% rows (that is, hot data), the other 50% evenly (sample uniformly) falls on another 99% record line.

For example, you oltp-test-mode=nontrx can have, optionally, oltp-nontrx-mode a select (default), Update_key, Update_nokey, insert, Delete, which represents the test SQL type used in a non-transactional mode.

The above represents a read-only example, you can either num-threads increment (16,36,72,128,256,512), or adjust the my.cnf parameters, compare the effect. Also note that most MySQL middleware handles transactions by default by sending SQL to the main library, so read-only tests need to be added oltp-skip-trx=on to skip the explicit transactions in the test.

PS1: Read-only tests can also be performed using Share/tests/db/select.lua, but are simply point select.

PS2: I use sysbench pressure, in the MySQL back-end session sometimes see a lot of query cache lock, if the use of uniform sampling, it is best to turn off the query cache. Of course, if it is to do two sets of performance contrast pressure test, because all affected by this factor, care is not big.

2.2 Mixed Read and write

Read and write test or use Oltp.lua, just put the --oltp-read-only equals off.

./bin/sysbench--test=./share/tests/db/oltp.lua--mysql-host=10.0.201.36--mysql-port=8066--mysql-user=ecuser- Mysql-password=ecuser--mysql-db=dbtest1a--oltp-tables-count=10--oltp-table-size=500000--report-interval=10-- Rand-init=on--max-requests=0--oltp-test-mode=nontrx--oltp-nontrx-mode=select--oltp-read-only=off--max-time=120 --num-threads=128 prepare./bin/sysbench--test=./share/tests/db/oltp.lua--mysql-host=10.0.201.36--mysql-port= 8066--mysql-user=ecuser--mysql-password=ecuser--mysql-db=dbtest1a--oltp-tables-count=10--oltp-table-size= 500000--report-interval=10--rand-init=on--max-requests=0--oltp-test-mode=nontrx--oltp-nontrx-mode=select-- Oltp-read-only=off--max-time=120--num-threads=128 run./bin/sysbench--test=./share/tests/db/oltp.lua--mysql-host =10.0.201.36--mysql-port=8066--mysql-user=ecuser--mysql-password=ecuser--mysql-db=dbtest1a--oltp-tables-count= --oltp-table-size=500000--report-interval=10--rand-init=on--max-requests=0--oltp-test-mode=nontrx--oltp-nontrx-mode=select--oltp-read-only=off--max-time=120--num-threads=128 cleanup 

However oltp-test-mode=nontrx , I have not followed my expectations, and in the MySQL general log see the same SQL record as the complex mode. So the example above --oltp-test-mode=nontrx --oltp-nontrx-mode=select can be erased.

Update

The original version of Sysbench 0.5 removed this option, because the author is preparing the 1.0 version, so there is no update of the 0.5 version of Doc. Online blog flying all over the sky, there is no one proposed, is no one.

By analyzing the Oltp.lua script content, you can clearly see the default proportions for individual transactions: select:update_key:update_non_key:delete:insert = 14:1:1:1:1 You can adjust the read and write weights by,,,,, and oltp-point-selects oltp-simple-ranges oltp-sum-ranges oltp-order-ranges oltp-distinct-ranges oltp-index-updates oltp-non-index-updates the options.

As with a read-only test, all queries are sent to the main library if they are not added in a middleware test such as Atlas,mycat, oltp-skip-trx=on but the problem occurs if you use --oltp-skip-trx=on skip begin and commit if there are writes:

Alert:failed to execute the MySQL Query:insert into Sbtest4 (ID, k, c, pad) VALUES (48228, 47329, ' 82773802508-44916890724-8 5859319254-67627358653-96425730419-64102446666-75789993135-91202056934-68463872307-28147315305 ', ' 13146850449-23153169696-47584324044-14749610547-34267941374 '):
alert:error 1062 Duplicate entry ' 48228 ' for key ' PRIMARY '
fatal:failed to execute function ' event ': (NULL)

The reason is also very easy to understand, each thread will select a random table, without transactions in the case of high Concurrent update (insert) The probability of duplicate key, but we do not care about the data, so we need to skip this error --mysql-ignore-errors=1062 , the problem of foreigners have to play a patch of the program allows --mysql-ignore-duplicates=on , But the author's newly added ignore error code has replaced it with this feature. The mysql-ignore-errors option is included in version 0.5, but is not currently documented.

Here we have to admire the efficiency and responsibility of foreigners, a doubt can immediately get back to the domestic, for example, in the Atlas,mycat project mentioned problems to now have no one to talk ...

2.3 Update only

If you only want to compare the update (or insert) efficiency of two items when benchmarking, instead of using OLTP scripts, you can use the following directly update_index.lua :

./bin/sysbench--test=./share/tests/db/update_index.lua \
--mysql-host=10.0.201.36--mysql-port=8066- Mysql-user=ecuser--mysql-password=ecuser \
--mysql-db=dbtest1a--oltp-tables-count=10--oltp-table-size= 500000 \
--report-interval=10--rand-init=on--max-requests=0 \
--oltp-read-only=off--max-time=120-- num-threads=128 \
[Prepare | run | cleanup]

At this point oltp-read-only=off , like many parameters are invalidated. What needs to be explained here is the (non) index update, not the where condition to find the update based on the index, but the value on the indexed column.

Three. Interpretation of the results

Sysbench 0.5:multi-threaded System Evaluation benchmark Running the test with following options:number of threads:128
The intermediate results every second (s) initializing random number generator from timer.
Random number generator seed is 0 and would be ignored initializing worker threads ...
Threads started! [20s] threads:128, tps:2354.54, reads:33035.89, writes:9423.39, Response time:66.80ms (+), errors:0.00, reconnect s:0.00 [40s] threads:128, tps:2377.75, reads:33274.26, writes:9507.55, Response time:66.88ms (+), errors:0.00, R  econnects:0.00 [60s] threads:128, tps:2401.35, reads:33615.30, writes:9607.40, Response time:66.40ms (+), errors:  0.00, reconnects:0.00 [80s] threads:128, tps:2381.20, reads:33331.50, writes:9522.55, Response time:67.30ms (95%), errors:0.00, reconnects:0.00 [100s] threads:128, tps:2388.85, reads:33446.10, writes:9556.35, Response time:67.00 MS (+), errors:0.00, reconnects:0.00 [120s] threads:128, tps:2386.40, reads:33421.35, writes:9545.35, Response time:66.94ms (+), errors:0.00, reconnects:0.00 OLTP test statistics : Queries performed:read:4003048//Total Select quantity write:1143728//Total update, INSERT, delete            Number of sentences other:571864//commit, unlock tables, and the number of other mutexes total:5718640 transactions:
  285932 (2382.10 per Sec.)/////(TPS) Read/write requests:5146776 (The 42877.85 per Sec.)
  Other operations:571864 (4764.21 per sec.)
Ignored errors:0 (0.00 per Sec.)//Ignore Error number reconnects:0 (0.00/sec.) General Statistics:total time:120.0334s//That is the total number of events for the max-time specified to be measured by the actual sum of events:285932//
     , generally the same as transactions total time taken by event execution:15362.6623s response time:min:17.60ms The average response time of a AVG:53.73MS//95% statement max:252.90ms approx percentile:66.88Ms Threads Fairness:events (avg/stddev): 2233.8438/9.04 Execution Time (Avg/stddev): 120.0208/0.01 

Our general attention to the indicators used for drawing mainly include:

response time avg: Average response time. (The size of the rear 95% can be changed by--percentile=98)

transactions: It's precisely the TPS that follows this one. But if used -oltp-skip-trx=on , this transaction number constant is 0, need to be removed with total time to get TPS (in fact, can also be divided into read TPs and write TPs)

read/write requests: Use it to divide total time to get throughput QPS

There are, of course, some CPU,IO,MEM related indicators at the system level

Sysbench can also be used for file system IO testing, CPU performance testing, and memory allocation and transmission speed testing, which is not covered here.

Summarize

The disadvantage of sysbench is that the simulated table structure is too simple to be a complete transaction system like Tpcc-mysql. However, it is still useful for the performance-pressure comparison, because the Sysbench uses the same environmental parameter limits. The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring certain help, if you have questions you can message exchange.