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

This article will explain in detail how to install and use sysbench database performance testing tools, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. Introduction and installation

Sysbench is an open source, modular, cross-platform multithreaded performance testing tool, which can be used to test the performance of CPU, memory, disk IBO, threads, and databases. Currently supported databases are MySQL, Oracle and PostgreSQL.

If you are compiling and installing, you need to install mysql's development package first (although the compilation error prompts you that the Mysql library file is missing).

Yum-y install mysql-community-develtar xf 1.0.15.tar.gzcd sysbench-1.0.15./autogen.sh./configuremake-jmake install

After installation, there is only one binary file, sysbench, and many lua scripts are provided.

[root@s1 ~] # rpm-ql sysbench | grep 'bin\ | lua' / usr/bin/sysbench/usr/share/sysbench/bulk_insert.lua/usr/share/sysbench/oltp_common.lua/usr/share/sysbench/oltp_delete.lua/usr/share/sysbench/oltp_insert.lua/usr/share/sysbench/oltp_point_select.lua/usr/share/sysbench/oltp_read_only.lua/usr/share/sysbench/oltp_read_write.lua/usr/share/ Sysbench/oltp_update_index.lua/usr/share/sysbench/oltp_update_non_index.lua/usr/share/sysbench/oltp_write_only.lua/usr/share/sysbench/select_random_points.lua/usr/share/sysbench/select_random_ranges.lua/usr/share/sysbench/tests/include/inspect.lua/usr/share/sysbench/tests/include/oltp_legacy/bulk_insert.lua/usr/share/sysbench/tests/include/oltp_legacy/common.lua/ Usr/share/sysbench/tests/include/oltp_legacy/delete.lua/usr/share/sysbench/tests/include/oltp_legacy/insert.lua/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua/usr/share/sysbench/tests/include/oltp_legacy/oltp_simple.lua/usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua/usr/share/sysbench/tests/include/oltp_legacy/select.lua/usr/share/sysbench/tests/ Include/oltp_legacy/select_random_points.lua/usr/share/sysbench/tests/include/oltp_legacy/select_random_ranges.lua/usr/share/sysbench/tests/include/oltp_legacy/update_index.lua/usr/share/sysbench/tests/include/oltp_legacy/update_non_index.lua

This article introduces the use of new versions of sysbench oltp lua scripts (/ usr/share/sysbench/*.lua), so it doesn't involve traditional lua (tests/include/oltp_legacy/*.lua). If you want to understand the use of these traditional Lua scripts, feel free to find them online.

How to use 2.sysbench

The options commonly used by sysbench for testing mysql are listed below.

[root@xuexi] # sysbench-- helpUsage: sysbench [options]. [test_lua] [lua_options] [command] Commands implemented by most tests: prepare run cleanup help

Common options: the values in parentheses below represent default values

-- threads=N specifies the number of threads [1]

-- events=N limits the maximum number of requests. 0 means no limit [0]

-- time=N limits the maximum execution time. 0 means no limit [10]

-- events or-- time can be chosen.

-- how long do you have to wait for forced-shutdown=STRING to shut down sysbench after reaching the maximum execution time?

Off means to disable this feature [off]

-- thread-stack-size=SIZE stack space used by each thread [64K]

-- the average transaction rate of rate=N. 0 means no limit [0]

-- report-interval=N reports the result every few seconds. 0 means to disable interval reporting [0]

-- config-file=FILENAME reads command line options from files

-- tx-rate=N is obsolete and is an alias for-- rate [0]

-- max-requests=N is obsolete and is an alias for-- events [0]

-- max-time=N is obsolete and is an alias for-- time [0]

-- num-threads=N is obsolete and is an alias for-- threads [1]

Mysql related options:

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

-- mysql-port= [LIST,...] MySQL server port [3306]

-- mysql-socket= [LIST,...] MySQL socket

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

-- mysql-password=STRING MySQL password []

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

-- mysql-ignore-errors= [LIST,...] For the error code to be ignored, the value can be "all" [1213, 1020, 1205]

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

Among them, the command part has four categories: prepare run cleanup and help:

Prepare: the command to prepare the data. For example, before sysbench stress testing, you need to prepare the test library, test table, and data in the test table. See later on the specific usage.

Run: means to conduct a stress test.

Cleanup: clears the data generated during the test.

Help: outputs help for a given lua script.

Test_lua is the lua script you want to use, and if the sysbench is installed by the rpm package, these scripts are all in the / usr/share/sysbench directory. For general database testing, you only need to use oltp-related lua scripts.

Options and lua_options are different, options is the option for sysbench, lua_options is the option for lua scripts, and lua_options should be placed after test_lua (optional, but recommended).

For example, to see the usage of oltp_common.lua, you can:

Sysbench/ usr/share/sysbench/oltp_common.lua help3. Prepare test data

First create the database sbtest required by sysbench (this is the library name used by sysbench by default, and you must create a test library).

Mysqladmin-h227.0.0.1-uroot-pP@ssword1!-P3306 create sbtest

Then, prepare the tables used for the tests, which are placed in the test library sbtest. The lua script used here is / usr/share/sysbench/oltp_common.lua.

Sysbench-- mysql-host=127.0.0.1\-- mysql-port=3306\-- mysql-user=root\-mysql-password=P@ssword1!\ / usr/share/sysbench/oltp_common.lua\-- tables=10\-- table_size=100000\ prepare

Where-- tables=10 means to create 10 test tables,-- table_size=100000 means to insert 10W rows of data into each table, and prepare indicates that this is the process of preparing the number.

Mysql > show tables from sbtest;+-+ | Tables_in_sbtest | +-+ | sbtest1 | | sbtest10 | | sbtest2 | | sbtest3 | | sbtest4 | | sbtest5 | | sbtest6 | | sbtest7 | | sbtest8 | | sbtest9 | +-+ mysql > select count (*) from sbtest.sbtest1 +-+ | count (*) | +-+ | 100000 | +-+

If you want to clear these 10 tables, use the cleanup command.

Sysbench-- mysql-host=127.0.0.1\-- mysql-port=3306\-- mysql-user=root\-- mysql-password=P@ssword1! / usr/share/sysbench/oltp_common.lua\-- tables=10\ cleanup4. Database testing and result analysis

Slightly modify the statement that prepared the data before, and you can use it for testing.

It is important to note that the previously used lua script is oltp_common.lua, which is a generic script that is called by other lua scripts and cannot be tested directly.

So, I use oltp_read_write.lua scripts to do read and write tests here. There are many other types of tests, such as read-only tests, write-only tests, delete tests, mass insert tests, and so on. You can find the corresponding lua script to call.

Sysbench-- threads=4\-- time=20\-- report-interval=5\-- mysql-host=127.0.0.1\-- mysql-port=3306\-- mysql-user=root\-mysql-password=P@ssword1!\ / usr/share/sysbench/oltp_read_write.lua\-- tables=10\-- table_size=100000\ run

The following are the results returned by the test:

Initializing worker threads...

Threads started!

# the following results are returned every 5 seconds. The statistical metrics include:

# Threads, tps (transactions per second), qps (queries per second),

# read / write / other times per second, delay, errors per second, reconnections per second

[5s] thds: 4 tps: 130.16 qps: 2606.30 (r/w/o: 1824.51 reconn/s: 2606.30) lat (ms,95%): 104.84 err/s: 0.00 reconn/s: 0.00

[10s] thds: 4 tps: 126.74 qps: 2539.17 (r/w/o: 1778.17max 507.52) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00

[15s] thds: 4 tps: 136.54 qps: 2736.34 (r/w/o: 1915.25 reconn/s) lat (ms,95%): 102.97 err/s: 0.00 reconn/s: 0.00

[20s] thds: 4 tps: 107.44 qps: 2148.65 (r/w/o: 1505.60 tps 428.17 reconn/s 214.89) lat (ms,95%): 132.49 reconn/s: 0.00

SQL statistics:

Queries performed:

Read: number of read operations performed by 35098 #

Write: number of writes performed by 10028 #

Other: number of other operations performed by 5014 #

Total: 50140

Transactions: 2507 (124.29 per sec.) # average rate of transactions executed

Queries: 50140 (2485.82 per sec.) # average number of queries can be executed per second

Ignored errors: 0 (0.00 per sec.)

Reconnects: 0 (0.00 per sec.)

General statistics:

Total time: 20.1694s # Total elapsed time

Total number of events: 2507 # Total number of requests (read, write, other)

Latency (ms):

Min: 2.32

Avg: 32.13

Max: 575.78

95th percentile: 118.92 # average delay of sampling calculation

Sum: 80554.96

Threads fairness:

Events (avg/stddev): 626.7500

Execution time (avg/stddev): 20.1387 Compact 0.04

5.cpu/io/ memory and other tests

Sysbench has several built-in test metrics.

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

The test method can be output by help directly. For example, the fileio test.

[root@xuexi] # sysbench fileio helpsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2) fileio options:-- file-num=N number of files to create [128]-- file-block-size=N block size to use in all IO operations [16384]-- file-total-size=SIZE total size of files to create [2G]-- file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}-- file-io-mode=STRING file operations mode {sync,async Mmap} [sync]-- file-async-backlog=N number of asynchronous operatons to queue per thread [128]-- file-extra-flags= [LIST,...] List of additional flags to use to open files {sync,dsync,direct} []-- file-fsync-freq=N do fsync () after this number of requests (0-don't use fsync ()) [100]-- file-fsync-all [= on | off] do fsync () after each write operation [off]-- file-fsync-end [= on | off] do fsync () at the end of test [on]-- file-fsync-mode=STRING which method to use for synchronization {fsync Fdatasync} [fsync]-- file-merged-requests=N merge at most this number of IO requests if possible (0-don't merge) [0]-- file-rw-ratio=N reads/writes ratio for combined test [1.5]

For example, create five files for a total of 1G, each about 200m.

Sysbench fileio-file-num=5-file-total-size=1G prepare [root@xuexi ~] # ls-lh test*-rw- 1 root root 205m Jul 8 12:15 test_file.0-rw- 1 root root 205m Jul 8 12:15 test_file.1-rw- 1 root root 205m Jul 8 12:15 test_file.2-rw- 1 root root 205m Jul 8 12:15 test_file.3- Rw- 1 root root 205M Jul 8 12:15 test_file.4

Then, run the test.

Sysbench-- events=5000\-- threads=16\ fileio\-- file-num=5\-- file-total-size=G\-- file-test-mode=rndrw\-- file-fsync-freq=0\-file-block-size=16384\ run

Results:

File operations:

Reads/s: 98.67

Writes/s: 66.85

Fsyncs/s: 6.26

Throughput: # Throughput

Read, MiB/s: 1.54 # indicates the bandwidth of the read

Written, MiB/s: 1.04 # indicates the read bandwidth

General statistics:

Total time: 12.7426s

Total number of events: 2117

Latency (ms):

Min: 0.00

Avg: 86.66

Max: 2919.41

95th percentile: 646.19

Sum: 183460.80

Threads fairness:

Events (avg/stddev): 132.3125 Universe 24.19

Execution time (avg/stddev): 11.4663 Universe 1.09

Rescaling cpu performance test:

[root@xuexi] # sysbench cpu--threads=40-- events=10000-- cpu-max-prime=20000 runsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options:Number of threads: 40Initializing random number generator from current timePrime numbers limit: 20000Initializing worker threads...Threads startedCPU speed: events per second: 2127.81General statistics: total time: 4.986s total number of events: 10000Latency (ms): min: 1.72CPU: 18.16 max: 302.17 5th percentile: 110.66 sum: 181628.49Threads fairness: events (avg/stddev): 250.0000 execution time 30.81 execution time (avg/stddev): 4.5407 Universe 0.10 on how to install and use sysbench database performance testing tools is here. I hope the above content can be of some help to you and 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.