Shulou(Shulou.com)05/31 Report--

This article is about how MySQL uses sysbench to benchmark OLTP. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. installation

Download source code package for ①: https://dev.mysql.com/downloads/benchmarks.html

② installation depends on yum-y install automake autoconf libtool

③ tar xzvf sysbench-0.4.12.10.tar.gz; cd sysbench-0.4.12.10/

④. / autogen.sh;. / configure

⑤ make & & make install

Then we can see that the file sysbench can be executed in the sysbench directory

II. Orders

First of all, take a look at the basic usage of the command.

Root@10.30.5.2:sysbench#. / sysbench-- help

Usage:

Sysbench [general-options]... Test= [test-options]... Command

General options:

-- num-threads=N number of threads to use [1]

-- max-requests=N limit for total number of requests [10000]

-- max-time=N limit for total execution time in seconds [0]

-- forced-shutdown=STRING amount of time to wait after-- max-time before forcing shutdown [off]

-- thread-stack-size=SIZE size of stack per thread [32K]

-- init-rng= [on | off] initialize random number generator [off]

-- seed-rng=N seed for random number generator, ignored when 0 [0]

-- tx-rate=N target transaction rate (tps) [0]

-- tx-jitter=N target transaction variation, in microseconds [0]

-- report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]

Report-checkpoints= [LIST,...] dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint (s) must be performed. Report checkpoints are off by default. []

-- test=STRING test to run

-- debug= [on | off] print more debugging info [off]

-- validate= [on | off] perform validation checks where possible [off]

-- help= [on | off] print help and exit

-- version= [on | off] print version and exit

Log options:

-- verbosity=N verbosity level {5-debug, 0-only critical messages} [4]

-- percentile=N percentile rank of query response times to count [95]

Compiled-in tests:

Fileio-File I do O test

Cpu-CPU performance test

Memory-Memory functions speed test

Threads-Threads subsystem performance test

Mutex-Mutex performance test

Oltp-OLTP test

Commands: prepare run cleanup help version

See 'sysbench-- test= help' for a list of options for each test.

As you can see above, sysbench can test CPU, disk IO, memory, threads, MUTEX and OLTP

Common parameters:

-- number of num-threads=N concurrent threads

-- max-requests=N limits the total number of stress test requests

-- max-time=N limit pressure test time

Here's a look at the OLTP test method.

. / sysbench-- test=oltp help

Sysbench 0.4.12.10: multi-threaded system evaluation benchmark

Oltp options:

-- oltp-test-mode=STRING test type to use {simple,complex,nontrx,sp} [complex]

-- oltp-reconnect-mode=STRING reconnect mode {session,transaction,query,random} [session]

-- oltp-sp-name=STRING name of store procedure to call in SP test mode []

-- oltp-read-only= [on | off] generate only 'read' queries (do not modify database) [off]

-- oltp-avoid-deadlocks= [on | off] generate update keys in increasing order to avoid deadlocks [off]

-- oltp-skip-trx= [on | off] skip BEGIN/COMMIT statements [off]

Oltp-range-size=N range size for range queries [100]

-- oltp-point-selects=N number of point selects [10]

-- oltp-use-in-statement=N Use IN-statement with 10 competing for lookups per query [0]

-- oltp-simple-ranges=N number of simple ranges [1]

-- oltp-sum-ranges=N number of sum ranges [1]

-- oltp-order-ranges=N number of ordered ranges [1]

-- oltp-distinct-ranges=N number of distinct ranges [1]

-- oltp-index-updates=N number of index update [1]

-- oltp-non-index-updates=N number of non-index updates [1]

-- oltp-nontrx-mode=STRING mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]

-- oltp-auto-inc= [on | off] whether AUTO_INCREMENT (or equivalent) should be used on id column [on]

-- oltp-connect-delay=N time in microseconds to sleep after connection to database [10000]

-- oltp-user-delay-min=N minimum time in microseconds to sleep after each request [0]

-- oltp-user-delay-max=N maximum time in microseconds to sleep after each request [0]

-- oltp-table-name=STRING name of test table [sbtest]

-- oltp-table-size=N number of records in test table [10000]

-- oltp-dist-type=STRING random numbers distribution {uniform,gaussian,special} [special]

-- oltp-dist-iter=N number of iterations used for numbers generation [12]

-- oltp-dist-pct=N percentage of values to be treated as' special' (for special distribution) [1]

-- oltp-dist-res=N percentage of 'special' values to use (for special distribution) [75]

-- oltp-point-select-mysql-handler= [on | off] Use MySQL HANDLER for point select [off]

-- oltp-point-select-all-cols= [on | off] select all columns for the point-select query [off]

-- oltp-secondary= [on | off] Use a secondary index in place of the PRIMARY index [off]

-- oltp-num-partitions=N Number of partitions used for test table [0]

-- oltp-num-tables=N Number of test tables [1]

General database options:

-- db-driver=STRING specifies database driver to use ('help' to get list of available drivers)

-- db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]

Compiled-in database drivers:

Mysql-MySQL driver

Mysql options:

-- mysql-host= [LIST,...] MySQL server host [localhost]

-- mysql-port=N MySQL server port [3306]

-- mysql-socket=STRING MySQL socket

-- mysql-user=STRING MySQL user [sbtest]

-- mysql-password=STRING MySQL password []

-- mysql-db=STRING MySQL database name [sbtest]

-- mysql-table-engine=STRING storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]

-- mysql-engine-trx=STRING whether storage engine used is transactional or not {yes,no,auto} [auto]

-- mysql-ssl= [on | off] use SSL connections, if available in the client library [off]

-- myisam-max-rows=N max-rows parameter for MyISAM tables [1000000]

-- mysql-create-options=STRING additional options passed to CREATE TABLE []

Description:

Common parameters

Basic parameters of ①

-- db-driver=mysql conducts OLTP benchmark test on mysql

-- mysql-host,-- mysql-port,-- mysql-socket,-- mysql-user,-- mysql-password these are basic parameters, so I'm not going to explain them.

-- database of mysql-db=xxx pressure test. You have to specify it here.

Common parameters of ② oltp

-- oltp-test-mode=complex/simple/nontrx test mode

-- maximum number of tables tested by oltp-num-tables=10 oltp version 0.4.10 16

-- number of records in the oltp-table-size=xxx test table

III. Testing

① test preparation:

Root@10.30.5.2:sysbench#./sysbench-num-threads=64-max-requests=200000-test=oltp-db-driver=mysql-mysql-user=root-mysql-host=10.30.22.2-mysql-password=x-oltp-test-mode=complex-mysql-db=tab-oltp-table-size=5000000-oltp-num-tables=16 prepare

Sysbench 0.4.12.10: multi-threaded system evaluation benchmark

Creating table 'sbtest14'...

Creating table 'sbtest7'...

Creating table 'sbtest'...

Creating table 'sbtest11'...

Creating table 'sbtest8'...

Creating table 'sbtest6'...

Creating table 'sbtest9'...

Creating table 'sbtest12'...

Creating table 'sbtest3'...

Creating table 'sbtest15'...

Creating table 'sbtest2'...

Creating table 'sbtest4'...

Creating table 'sbtest1'...

Creating table 'sbtest5'...

Creating table 'sbtest13'...

Creating table 'sbtest10'...

Creating 5000000 records in table 'sbtest11'...

Creating 5000000 records in table 'sbtest1'...

Creating 5000000 records in table 'sbtest14'...

Creating 5000000 records in table 'sbtest'...

Creating 5000000 records in table 'sbtest6'...

Creating 5000000 records in table 'sbtest2'...

Creating 5000000 records in table 'sbtest13'...

Creating 5000000 records in table 'sbtest15'...

Creating 5000000 records in table 'sbtest12'...

Creating 5000000 records in table 'sbtest4'...

Creating 5000000 records in table 'sbtest3'...

Creating 5000000 records in table 'sbtest9'...

Creating 5000000 records in table 'sbtest8'...

Creating 5000000 records in table 'sbtest10'...

Creating 5000000 records in table 'sbtest5'...

Creating 5000000 records in table 'sbtest7'...

② test results

Click (here) to collapse or open

Root@10.30.5.2:sysbench#. / sysbench-num-threads=64-- max-requests=200000-- test=oltp-- db-driver=mysql-- mysql-user=root-- mysql-host=10.30.22.xxx-- mysql-password=xxx-- oltp-test-mode=complex-- mysql-db=tab-- oltp-table-size=5000000-- oltp-num-tables=16 run

Sysbench 0.4.12.10: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 64

Random number generator seed is 0 and will be ignored

Doing OLTP test.

Running mixed OLTP test

Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)

Using "BEGIN" for starting transactions

Using auto_inc on the id column

Maximum number of requests for OLTP test is limited to 200000

Using 16 test tables

Threads started!

Done.

OLTP test statistics:

Queries performed:

Read: 2800224

Write: 1000080

Other: 400032

Total: 4200336

Transactions: 200016 (2000.64 per sec.)

Deadlocks: 0 (0.00 per sec.)

Read/write requests: 3800304 (38012.16 per sec.)

Other operations: 400032 (4001.28 per sec.)

General statistics:

Total time: 99.9760s

Total number of events: 200016

Total time taken by event execution: 6394.8091

Response time:

Min: 11.00ms

Avg: 31.97ms

Max: 293.00ms

Approx. 95 percentile: 52.10ms

Threads fairness:

Events (avg/stddev): 3125.2500Universe 50.55

Execution time (avg/stddev): 99.9189 Universe 0.01

③ test cleanup

Click (here) to collapse or open

Root@10.30.5.2:sysbench#. / sysbench-num-threads=64-- max-requests=200000-- test=oltp-- db-driver=mysql-- mysql-user=root-- mysql-host=10.30.22.xxx-- mysql-password=xxx-- oltp-test-mode=complex-- mysql-db=tab-- oltp-table-size=5000000-- oltp-num-tables=16 cleanup

Sysbench 0.4.12.10: multi-threaded system evaluation benchmark

Dropping table 'sbtest'...

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

Dropping table 'sbtest4'...

Dropping table 'sbtest5'...

Dropping table 'sbtest6'...

Dropping table 'sbtest7'...

Dropping table 'sbtest8'...

Dropping table 'sbtest9'...

Dropping table 'sbtest10'...

Dropping table 'sbtest11'...

Dropping table 'sbtest12'...

Dropping table 'sbtest13'...

Dropping table 'sbtest14'...

Dropping table 'sbtest15'...

Done.

Summary:

1 > PREPARE phase

In the PREPARE phase, we need to figure out the configuration of mysql, such as the size of innodb_flush_log_at_trx_commit, sync_binlog and BP.

Then combined with the size of the BP, we need to create the number of records of the table, the number of tables, concurrent threads and so on.

① if the amount of data

< BP 所有数据都会缓存到内存，此时增加 并发线程数 来测整个此时的CPU核数是否能抗住测试压力 ② 若数据量 >

> BP mainly tests the stability of the entire system. We can obtain the weak points of the entire database system by monitoring the cache hit rate (orzdba) and the corresponding disk IO (iostat / orzdba).

2 > RUN phase

During the RUN phase, we can view the current database status through tools such as orzdba/iostat.

We can see the test results above ①.

Transactions: 200016 (2000.64 per sec.) TPS is about 2000.

Read/write requests: 3800304 (38012.16 per sec.) QPS reaches 38000

Approx. 95 percentile: 95% of 52.10ms 's requests are in 52.10ms.

It can be said that the performance is quite good (here I am testing Tencent Cloud CDB and BP configured as 1000MB)

② checks database status through orzdba combined with running process

Click (here) to collapse or open

Root@10.30.5.2:orzdba_home#. / orzdba-mysql-innodb-rt

. =.

| | Welcome to use the orzdba tool! |

| | Yep...Chinese English~ |

'= Date: 2017-04-18 ='

HOST: 10.30.22.2 IP: 10.30.5.2

DB: performance_schema | tab

Var: binlog_ format[MIXED] max_binlog_cache_ size[17179869184G] max_binlog_ size[1G]

Max_connect_errors [999999999] max_connections [800] max_user_connections [0]

Open_files_limit [102400] sync_binlog [0] table_definition_cache [768]

Table_open_cache [512] thread_cache_size [512]

Innodb_adaptive_ flushing[ON] innodb_adaptive_hash_ index[ON] innodb_buffer_pool_ size[893M]

Innodb_file_per_ table[ON] innodb_flush_log_at_trx_commit [2] innodb_flush_ method[O _ DIRECT]

Innodb_io_capacity [20000] innodb_lock_wait_timeout [7200] innodb_log_buffer_ size[64M]

Innodb_log_file_ size[500M] innodb_log_files_in_group [2] innodb_max_dirty_pages_pct [75]

Innodb_open_files [1024] innodb_read_io_threads [4] innodb_thread_concurrency [0]

Innodb_write_io_threads [4]

-QPS--TPS--Hit%--innodb bp pages status--- innodb data status---- innodb log-- his-- log (byte)-- read-query----threads--bytes-----tcprstat (us)-

Time | ins upd del sel iud | lor hit | data free dirty flush | reads writes read written | fsyncs written | list uflush uckpt view inside que | run con cre cac | recv send | count avg 95-avg 99-avg |

17:24:53 | 0 100.00 | 0 00 | 00 | 00 | 00 | 00 | 00 | 00 | 00 |

17:24:54 | 2153 6454 2153 30115 10760 | 478891 97.72 | 55869 0 19432 2372 | 11355 6686 177.4m 79.2m | 25.1m | 1371.7m 180.6m 5700 | 206801 | 1.1m 10.7m | 23711267 183236 |

17:24:55 | 1960 5891 1962 27470 9813 | 437599 97.71 | 55872 19793 2132 | 104166063 162.8m 71.4m | 14.8m | 142.4m 183.5m 6200 | 146801 | 1005k 9.7m | 23004356215283 |

17:24:56 | 2027 6091 2033 28422 10151 | 451846 97.74 | 55870 20024 2249 | 10609 6320 165.8m 75.2m | 34.9m | 133927k 186.5m 5900 | 8 68 01 | 1.0 m 10.2m | 22684,299 186244 |

17:24:57 | 2291 6865 2286 32067 11442 | 511514 97.69 | 55870 0 20248 2611 | 123147194 192.4m 87.0m | 25.4m | 12819k 189.6m 5400 | 46801 | 1.1m 11.5m | 25197267188 234 |

17:24:58 | 2210 6632 2207 30947 11049 | 493747 97.77 | 55865 0 20361 2371 | 11478 6810 179.3m 79.5m | 25.4m | 130267k 192.9m 64 00 | 486801 | 1.1m 11.3m | 24586,270182 |

17:24:59 | 2225 6680 2226 31102 11131 | 496716 97.70 | 55866 0 20305 2602 | 11891 7059 185.8m 86.6m | 1.93m | 149473k 196.0m 5300 | 116801 | 1.1m 11.3m | 20655,371,219304 |

17:25:00 | 2126 6377 2130 29819 10633 | 472984 97.70 | 55868 20195 2489 | 11332 6749 177.1m 82.8m | 25.0m | 125,370k 199.0m 6200 | 136801 | 1.1m 10.8m | 8707,958664856 |

17:25:01 | 2169 6507 2165 30307 10841 | 484346 97.71 | 55766 99 20214 2485 | 115506849 180.5m 82.7m | 1.151m | 133,808k 202.0m 5700 | 156801 | 1.1m 11.2m | 8578996636844 |

You can find that when 32 thread perform complex operations concurrently, the amount of insert per second, the amount of update, the amount of delete, the amount of select, as well as the amount of fsync of innodb_log, and the response time of the database.

Thank you for reading! This is the end of this article on "how MySQL uses sysbench to do OLTP benchmark testing". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.