Sysbench detailed tutorial on mysql stress testing, sysbench stress testing

Sysbench detailed tutorial on mysql stress testing, sysbench stress testing

Preface

When you benchmark the overall performance of a website, you can use a variety of tools, such as the well-known AB (Apache runtime) and http_load tools. Here we do not care about their use. If you want to know, you can find the answer on the Internet.

There are also many tools available for us to choose from, such as mysqlslap, sysbench, and Super Smack. Here, mysqlslap uses the MySQL official website to provide an introduction, super Smack is a powerful tool for server stress testing. sysbench is a great tool for MySQL Benchmark Testing.

Sysbench

Sysbench is an open-source multi-threaded performance testing tool that can perform performance tests on CPU, memory, thread, IO, and database. The database currently supports MySQL, Oracle, and PostgreSQL;

It mainly includes the following testing methods:

1. cpu Performance

2. Disk I/O performance

3. scheduler Performance

4. Memory Allocation and transmission speed

5. POSIX thread Performance

6. database performance (OLTP benchmark test)

The database OLTP test of sysbench supports MySQL, PostgreSQL, and Oracle. Currently, it is mainly used in Linux operating systems. the open-source community has transplanted sysbench to Windows and supports Benchmark Testing of SQL Server.

Not much nonsense. Start.

1. Install sysbench

Mysql version: mysql-community-server-5.6.29

OS: CentOS 6.7 X86_64

Compared with version 0.5, sysbench 0.4 has some changes, including oltp testing combined with lua scripts and some hidden options. This article will cover some of them.

// First install and compile the dependent environment $ sudo yum install gcc-c ++ automake make libtool mysql-community-devel $ cd/tmp & git clone unzip cd/tmp/sysbench & &. /autogen. sh $. /configure -- prefix =/usr/local/sysbench-0.5 $. /make & sudo make install // version 0.5 requires oltp. lua test script // if it is installed using the rpm package, under/usr/share/doc/sysbench/tests/db/, you can find $ cd/usr/local/sysbench & sudo mkdir-p share/tests/db $ cp/tmp /sysbench/tests/db /*. lua share/tests/db/$. /bin/sysbench -- versionsysbench 0.5

To test PostgreSQL and Oracle, add –with-oracle Or–with-pgsql Parameters

Ii. Use sysbench for mysql stress testing

2.1 Read-Only Example

./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 in the last row, you must use prepare to prepare tables and data before a test starts. run runs the real stress test and cleanup is used to clear data and tables. Actual prepare table structure:

mysql> desc dbtest1a.sbtest1;+-------+------------------+------+-----+---------+----------------+| Field | Type       | Null | Key | Default | Extra     |+-------+------------------+------+-----+---------+----------------+| id  | int(10) unsigned | NO  | PRI | NULL  | auto_increment || k   | int(10) unsigned | NO  | MUL | 0    |        || c   | char(120)    | NO  |   |     |        || pad  | char(60)     | NO  |   |     |        |+-------+------------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

The test command above indicates that the oltp benchmark test is performed on mysql. The number of tables is 10, and the number of rows in each table is about 50 w (the number of rows will be inserted when there is almost delete ), in addition, the read-only test for non-Transactions lasts 60 s and the number of concurrent threads is 12.

Options to be noted:

mysql-db=dbtest1a: Target database used for testing. The database name must be created in advance.

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

--oltp-table-size=500000: Number of records generated for each table

--oltp-dist-type=uniform: Specifies the random sampling type. Optional values include uniform (even distribution), Gaussian (Gaussian distribution), and special (spatial distribution ). The default value is special.

--oltp-read-only=off: Not only read-only SQL statements are generated, that is, the hybrid read/write mode is used when oltp. lua is used. The default value is off. If it is set to on, the SQL statements of update, delete, and insert are not generated.

--oltp-test-mode=nontrx: Execution mode, which is non-transactional. Optional values include simple, complex, and nontrx. The default value is complex.

Simple: Simple query,SELECT c FROM sbtest WHERE id=N


Complex (advanced transactional): Add begin and commit before the transaction starts and ends. A transaction can contain multiple statements, such as vertex query, range query, sort query, update, delete, and insert, in order not to damage the data in the test table, this mode adds the same record to the same transaction after the next record is deleted.

Nontrx (non-transactional): Similar to simple, but can perform update/insert operations. Therefore, if you perform continuous comparative stress testing, you may need to re-cleanup and prepare.

--oltp-skip-trx=[on|off] : The begin/commit statement is omitted. The default value is off.

--rand-init=on: Whether to initialize data randomly. If the data is not randomized, the content of each row of the initial data is identical except that the primary key is different.

--num-threads=12: Number of concurrent threads, which can be understood as the number of concurrent connections on the simulated Client

--report-interval=10: Indicates that a Test Progress Report is output every 10 seconds.

--max-requests=0: Total number of requests generated by stress testing. If the following maxtime is used, this value is set to 0.

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

Note:Different sub-options are available for different options. For example, if oltp-dist-type = special, there are two sub-options: oltp-dist-pct = 1 and oltp-dist-res = 50, 50% of queries fall on 1% of rows (that is, hotspot data), and 50% of the average (sample uniformly) falls on the other 99% of record rows.

For exampleoltp-test-mode=nontrxYou can haveoltp-nontrx-modeOptional values include select (default), update_key, update_nokey, insert, and delete, which represent the test SQL type used in non-transactional mode.

The preceding figure shows a read-only example.num-threadsIncrease sequentially (72,128,256,512,), or adjust the my. cnf parameter to compare the effect. In addition, most mysql middleware processes transactions and sends SQL statements to the master database by default for execution. Therefore, read-only tests must be added.oltp-skip-trx=onTo skip the explicit transactions in the test.

Ps1: Read-only tests can also be performed using share/tests/db/select. lua, but it is only a simple point select.

Ps2: When I press sysbench, I sometimes see a large number of query cache locks in mysql backend sessions. If uniform sampling is used, it is best to disable the query cache. Of course, if you are doing two groups of Performance Comparison stress tests, because these are all affected by this factor, you are not very concerned about it.

2.2 hybrid read/write

The read/write test still uses oltp. lua.--oltp-read-onlyEqual to 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=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 cleanup

Howeveroltp-test-mode=nontrxI have never followed my expectation. The SQL record in mysql general log is the same as that in complex mode. So in the above example--oltp-test-mode=nontrx --oltp-nontrx-mode=selectIt can be deleted.

Update:

This option was removed from the original sysbench 0.5 version because the author is preparing version 1.0, so there is no updated version 0.5 doc. There is no such blog on the Internet.

Analyze the content of the oltp. lua script to understand the default proportion of each transaction operation:select:update_key:update_non_key:delete:insert = 14:1:1:1:1, You can useoltp-point-selects,oltp-simple-ranges,oltp-sum-ranges,oltp-order-ranges,oltp-distinct-ranges,oltp-index-updates,oltp-non-index-updatesThese options adjust the read/write weight.

Similar to read-only tests, in middleware tests such as atlas and mycatoltp-skip-trx=on, All queries will be sent to the master database, but if you use--oltp-skip-trx=onSkipping BEGIN and COMMIT will cause problems:

ALERT: failed to execute MySQL query: INSERT INTO sbtest4 (id, k, c, pad) VALUES (48228, 47329, '82773802508-44916890724-85859319254-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 easy to understand. Each thread will select a random table, which has a high probability of duplicate keys during high-concurrency Update (insertion) without transactions, however, we do not care about this data during stress testing, so we need to skip this error.--mysql-ignore-errors=1062This problem has been approved by foreigners through patching.--mysql-ignore-duplicates=onHowever, the new ignore error code function has replaced it.mysql-ignore-errorsThis option is added to version 0.5, but is not specified in the document currently.

Here, I have to admire the efficiency and sense of responsibility of foreigners. I can immediately get a response to my doubts. In contrast to my country, for example, no one has taken care of the problems mentioned in the atlas and mycat projects...

2.3 update only

If you only want to compare the update (or insert) Efficiency of two projects during the benchmark test, you can directly use the oltp script insteadupdate_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 ]

In this caseoltp-read-only=offMany parameters are invalid. It should be noted that here (not) The index is updated, rather than the where condition searches for updates based on the index, but the value of the index column is updated.

Iii. Interpretation of results

Sysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options: Number of threads: 128 Report intermediate results every 20 second (s) Initializing random number generator from timer. random number generator seed is 0 and will be ignoredInitializing worker threads... threads started! [20 s] threads: 128, tps: 2354.54, reads: 33035.89, writes: 9423.39, response time: 66.80 ms (95%), errors: 0.00, reconnects: 0.00 [40 s] threads: 128, tps: 2377.75, reads: 33274.26, writes: 9507.55, response time: 66.88 ms (95%), errors: 0.00, reconnects: 0.00 [60 s] threads: 128, tps: 2401.35, reads: 33615.30, writes: 9607.40, response time: 66.40 ms (95%), errors: 0.00, reconnects: 0.00 [80 s] threads: 128, tps: 2381.20, reads: 33331.50, writes: 9522.55, response time: 67.30 ms (95%), errors: 0.00, reconnects: 0.00 [100 s] threads: 128, tps: 2388.85, reads: 33446.10, writes: 9556.35, response time: 67.00 ms (95%), errors: 0.00, reconnects: 0.00 [120 s] threads: 128, tps: 2386.40, reads: 33421.35, writes: 9545.35, response time: 66.94 ms (95%), errors: 0.00, reconnects: 0.00 OLTP test statistics: queries saved med: read: 4003048 // total select count write: 1143728 // total update, insert, delete statement count other: 571864 // Number of commit, unlock tables, and other mutex total: 5718640 transactions: 285932 (2382.10 per sec .) // number (TPS) read/write requests: 5146776 (42877.85 per sec .) other operations: 571864 (4764.21 per sec .) ignored errors: 0 (0.00 per sec .) // Number of ignored errors reconnects: 0 (0.00 per sec .) general statistics: total time: 120.0334 s // that is, the actual total number of events: 285932 // total number of events specified by max-time, generally, it is the same as transactions in total time taken by event execution: 15362.6623 s response time: min: 17.60 ms avg: 53.73 ms // The average response time of a 95% ms statement max: 252.90 ms approx. 95 percentile: 66.88 msThreads fairness: events (avg/stddev): 2233.8438/9.04 execution time (avg/stddev): 120.0208/0.01

We generally focus on the following metrics for plotting:

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

transactions: Specifically, the TPS after this item. However, if-oltp-skip-trx=on, The number of this transaction is always 0. You need to use the total number of events to divide the total time and get the tps (in fact, it can also be divided into read tps and write tps)

read/write requests: Divide it by the total time to get the throughput QPS.

Of course, there are also some system-level cpu, io, mem-related indicators

Sysbench can also perform file system I/O testing, CPU performance testing, and memory allocation and transmission speed testing. We will not introduce it here.

Summary

The disadvantage of sysbench is that the simulated table structure is too simple, unlike the complete transaction system like tpcc-mysql. However, it is useful for performance stress testing and comparison, because the environmental parameter limits used by sysbench are the same. The above is all about this article. I hope this article will help you in your study or work. If you have any questions, please leave a message.