Shulou(Shulou.com)06/01 Report--

What is a benchmark?

When we optimize the database, only by measuring the performance of the system can we know whether the optimization is effective or not. The way of measurement is benchmarking. The benchmark is defined as follows:

Benchmarking is an activity that measures and evaluates software performance metrics to establish a performance benchmark at some point so that when a software / hardware change occurs in the system, the benchmark is retested to assess the impact of the change on performance.

We can think of it this way:

Benchmark is a kind of stress test for system settings, which can be used to observe the behavior of the system under different pressures. Evaluate the capacity of the system and observe how the system handles different data.

But based on testing is not the same as stress testing:

Test-based: direct, simple, easy to compare, used to evaluate the processing power of the server. The benchmark may not care about business logic, and the authenticity of the query and business used can have nothing to do with the business link: test the real business data to get the pressure that the real system can withstand. Stress tests need to be targeted at different topics, and the data and queries used are really practical.

The purpose of benchmarking MySQL is to:

1. Establish the performance baseline of MySQL server, determine the current operation of MySQL server 2, simulate the higher load than the current system, in order to find out the expansion bottleneck of the system. Increase the concurrency of the database and observe the changes of QPS and TPS to determine the relationship between concurrency and optimal performance. 3. Test different hardware, software and operating system configurations. 4. Prove whether the new hardware is configured correctly and how to benchmark.

In general, there are two ways to benchmark:

1. Benchmark the whole system: test from the entrance of the system (website Web front end, mobile phone APP front end) advantages: it can test the performance of the whole system, including web server cache, database, etc. It can reflect the performance problems between the interfaces of various components in the system, and reflect the shortcomings of the real performance situation: the design of the test case is complex and time-consuming. 2. Benchmarking the MySQL separately: testing only the advantages of MySQL services in the system: the test case design is simple and takes less time. Disadvantages: unable to fully understand the performance baseline of the whole system.

Common metrics of the MySQL benchmark:

Transactions per unit time (TPS) number of queries processed per unit time (QPS) concurrent number of query requests processed at the same time response time of a single test task includes: average response time, minimum response time, maximum response time and percentage of each time

Issues that are easy to overlook in benchmarking:

When testing with production environment data, only part of the data is used, which may lead to inaccurate recommendation of test results: testing with full backup data of the database in a multi-user scenario, only a single-user test recommendation is made: testing distributed applications on a single server using multithreaded concurrent testing recommendation: testing the same query repeatedly using the same architecture The problem is that the same SQL statement is easy to cache hits and does not reflect the real query performance. However, in the actual environment, the query methods may be different.

Steps for benchmarking:

1. Choose whether to test the whole system or a certain component, and determine what kind of data to use for testing. 2. Prepare the test data collection script, usually need to collect as much information about the current system as possible, such as CPU utilization, IO, network traffic, status and counter information, etc. 3. Write scripts to analyze the benchmark information collected in the second step. Finally, some examples of test results collection script and analysis script are obtained.

Collection script for benchmark data:

#! / bin/bash# script run interval INTERVAL=5# create data file storage directory STORE_DIR=/home/mysql/benchmarksmkdir-p $STORE_DIR# to which directory will the collected data be stored And the user name and password of the file prefix PREFIX=$STORE_DIR/$INTERVAL-sec-status# setting script RUNFILE=$STORE_DIR/runningtouch $RUNFILE & & echo "1" > $RUNFILE# mysql the path where the USER=rootPWD=123456# mysql command is located MYSQL= "/ usr/local/mysql/bin/mysql-u$USER-p$PWD" # record the global variable of the current mysql $MYSQL-e "show global variables" > > mysql-variables# run identity file will loop while test-e $RUNFILE all the time Do # defines the current time when the script is running file=$ (date +% funding% I) # implementation runs the loop sleep=$ (date +% s% N | awk'{print 5-($1% 5)}') sleep $sleep ts= "$(date +" TS% s% N% F% T ")" # get system load information loadavg= "$(uptime)" # Note Record system load information echo "$ts $loadavg" > > $PREFIX-$ {file}-status # record current mysql global variable information $MYSQL-e "show global status" > > $PREFIX-$ {file}-status & echo "$ts $loadavg" > > $PREFIX-$ {file}-innodbstatus # record current innodb status information $MYSQL-e "show engine innodbstatus" > > $PREFIX-$ {file}-innodbstatus & echo "$ts $loadavg" > > $PREFIX- ${file}-processlist # record the current mysql connection information list $MYSQL-e "show full processlist\ G" > > $PREFIX-$ {file}-processlist & echo $tsdoneecho Exiting because $RUNFILE does not exists

Test data analysis script:

#! / bin/bashawk 'BEGIN {printf "# ts date time load QPS"; fmt= "% .2f";} / ^ TS/ {ts= substr ($2, ".")-1); load = NF-2; diff = ts-prev_ts; printf "\ n% s% s% s", ts,$3,$4,substr ($load,1,length ($load)-1); prev_ts=ts } / Queries/ {printf fmt, ($2-Queries) / diff; Queries=$2}'"$@" # use example # sh. / analyze.sh ${Test data file path} # e.g. sh. / analyze.sh / home/mysql/benchmarks/5-sec-status-2020-01-mysqlslap of the 11_04-statusMysql benchmark tool

Generally speaking, we don't write benchmark scripts ourselves, because we all have ready-made tools. For example, mysqlslap is a benchmark tool that comes with mysql 5.1 and above. Because it comes with it and does not need to be installed separately, the tool is located in the bin directory of mysql.

Function introduction:

Simulate the server load and output relevant statistics, you can specify the query statements for the test, or you can use the test statements automatically generated by the tool

Description of common parameters:

Parameter description-the number of concurrency concurrency, that is, the number of clients simulated, can be specified, separated by commas-- iterations specifies the number of times the test is run-- auto-generate-sql uses system-generated SQL scripts for testing-- auto-generate-sql-add-autoincrement adds self-incrementing ID--auto-generate-sql-load-type to the automatically generated table to specify the type of query used in the test (values: read, write, update, mixed) The default mixed--auto-generate-sql-write-number specifies the amount of data generated when the data is initialized-engine tests the storage engine of the table, allowing you to specify multiple Use comma separation-- no-drop specifies not to clean up test data-- number-of-queries specifies the number of queries executed per connection-- debug-info specifies the output of additional memory and CPU statistics-- number-char-cols specifies the number of int types contained in the test table-- number-int-cols specifies the number of varchar types contained in the test table-- create-schema specifies the number of tests to be executed. According to the name of the library-- query is used to specify a custom SQL script-- when only-print specifies this parameter Instead of running the test script, the generated script is printed out for more parameters as detailed in the official documentation

Examples of use:

[root@localhost] # mysqlslap-uroot-p123456-- concurrency=1,50100200-- iterations=3-- number-int-cols=5-- number-char-cols=5-- auto-generate-sql--auto-generate-sql-add-autoincrement-- engine=myisam,innodb-- number-of-queries=10-- create-schema=test

After running the test, some of the output snippets are as follows:

Benchmark # runs a test of the myisam engine Running for engine myisam # average number of seconds to run all queries Average number of seconds to run all queries: 0.020 seconds # minimum seconds to run all queries Minimum number of seconds to run all queries: 0.018 seconds # maximum seconds to run all queries Maximum number of seconds to run all queries: 0.022 seconds # to run queries Number of clients Number of clients running queries: 1 # average number of queries per client Average number of queries per client: 10Benchmark Running for engine innodb Average number of seconds to run all queries: 0.049 seconds Minimum number of seconds to run all queries: 0.042 seconds Maximum number of seconds to run all queries: 0.059 seconds Number of clients running queries: 1 Average number of queries per client: sysbench of the 10Mysql benchmark tool

In addition to mysqlslap, the benchmark tool that comes with mysql, another commonly used tool is sysbench. Sysbench has more functions and can be tested more comprehensively than mysqlslap. Sysbench can not only test MySQL, but also test CPU, IO, memory and other aspects of the system.

Install sysbench

The GitHub repository address and source code installation document of sysbench are as follows:

Https://github.com/akopytov/sysbenchBuilding and Installing From Source

The general system will not come with sysbench tools, we need to install their own, I use the source code to compile and install here. First, go to the following address to copy the download link for the source package:

Https://github.com/akopytov/sysbench/releases

Then go to Linux and use the wget command to download:

[root@txy-server ~] # cd / usr/local/src [root@txy-server / usr/local/src] # wget https://github.com/akopytov/sysbench/archive/1.0.19.tar.gz

Install some of the libraries that you rely on when compiling sysbench:

[root@txy-server / usr/local/src] # yum-y install make automake libtool pkgconfig libaio-devel mariadb-devel openssl-devel

Then decompress the downloaded source package and go to the decompressed directory:

[root@txy-server / usr/local/src] # tar-zxvf 1.0.19.tar.gz [root@txy-server / usr/local/src] # cd sysbench-1.0.19/

Finally, refer to the following steps to complete the compilation and installation:

[root@txy-server / usr/local/src/sysbench-1.0.19] #. / autogen.sh [root@txy-server / usr/local/src/sysbench-1.0.19] #. / configure-- with-mysql-includes=/usr/local/mysql/include-- with-mysql-libs=/usr/local/mysql/lib [root@txy-server / usr/local/src/sysbench-1.0.19] # make & & make install

After waiting for the compilation and installation to be completed, test whether the installation is successful. I reported an error here that the library file could not be found:

[root@txy-server / usr/local/src/sysbench-1.0.19] # sysbench--versionsysbench: error while loading shared libraries: libmysqlclient.so.21: cannot open shared object file: No such file or directory

The solution is to establish a soft connection to the file to the / usr/lib64/ directory:

[root@txy-server / usr/local/src/sysbench-1.0.19] # ln-s / usr/local/mysql/lib/libmysqlclient.so.21 / usr/lib64/

Test again and execute successfully:

[root@txy-server / usr/local/src/sysbench-1.0.19] # use of sysbench--versionsysbench 1.0.19sysbench

The command format for sysbench is as follows:

Sysbench [options]... [testname] [command]

1. Options is the specified test parameter. The following parameters are commonly used when testing MySQL using sysbench:

-- mysql-db: the name of the database used to specify the benchmark, which must be existing-- mysql_storage_engine: specify the MySQL storage engine for the test-- tables: number of tables that execute the test-- table_size: specify the amount of data in each table-- threads: specify the number of concurrent threads for the test-- max-time: specify the maximum test time. Unit: seconds-- report-interval: specify how often the statistics are output (in seconds-- mysql-socket: specify the .sock file path of mysql, which is used to connect to mysql--mysql-user: specify the user used to connect to mysql during testing-- mysql-password: specify the password of the mysql user for other parameters, see the official documentation.

2. Testname, which specifies the built-in test type or test script. The built-in test types have the following values:

Fileio: test file system cpu performance cpu: test cpu performance memory: test memory performance threads: test thread performance mutex: test mutex performance execute sysbench [testname] help, you can view the parameters supported by each test type

For tests other than the built-in test type, you need to specify a test script. The test script of sysbench needs to be written in Lua. You can write your own test script according to your actual needs, or you can use the test script that comes with sysbench. The lua scripts that come with sysbench are as follows:

[root@txy-server ~] # ls / usr/local/share/sysbenchbulk_insert.lua # is used to test a large number of insert operation performance oltp_common.lua # public files, which are referenced by other scripts, can not be directly used to test delete operation performance under oltp oltp_insert.lua # for testing insert operation performance under oltp oltp_point_select.lua # for testing fixed-point query performance under oltp For example, query oltp_read_only.lua # according to the primary key to test the read-only performance under oltp oltp_read_write.lua # to test the read-write performance under oltp oltp_update_index.lua # to test the performance of updating index fields oltp_update_non_index.lua # to test the performance of updating unindexed field operations oltp_write_only.lua # to test write operations under oltp Performance select_random_points.lua # for testing the performance of random fixed-point queries select_random_ranges.lua # for testing the performance of random range reads. Tips: different versions of sysbench may have different paths for test scripts Self-confirmation is required.

3. Command specifies some test operations, such as running tests, clearing data, or preparing data, etc.

Prepare: used to prepare test data before test starts run: for test data preparation after completion, execute test cleanup: for cleaning test data after test execution is completed sysbench benchmark test example 1, test CPU performance

The following command allows cpu to find primes less than 10000 and calculate the time required to know the performance of cpu. It is important to note that this approach tests single-core performance, not multicore performance:

[root@txy-server] # sysbench-- cpu-max-prime=10000 cpu run

The test results are as follows:

# CPU Speed CPU speed: # events per second events per second: 1039.79General statistics: # calculate the total time consuming of all primes total time: 10.0005s # Total number of events total number of events: 10400Latency (ms): # calculate the minimum time consuming min of a prime: 0.94 # the average time-consuming avg for calculating a prime number: 0.96 # the longest time-consuming max for calculating a prime number is 9.43 # 95% . 01 millisecond calculates a prime number 95th percentile: 1.01 # Total time sum: 9994.0 thread fairness Threads fairness: events (avg/stddev): 10400.0000max 0.00 execution time (avg/stddev): 9.9941hip 0.002, Test the performance of the system Icano

To test the performance of Icano, you need to prepare test data first, and the size of test data needs to be larger than physical memory. The command is as follows:

# this command generates 127test files with a total size of 4G [root@txy-server ~] # sysbench-- file-total-size=4G fileio prepare...Creating file test_file.125Creating file test_file.126Creating file test_file.1274294967296 bytes written in 39.00 seconds (105.04 MiB/sec). [root@txy-server ~] #

When the data is ready, perform the test:

[root@txy-server] # sysbench-threads=8-file-total-size=4G-file-test-mode=rndrw-report-interval=1 fileio run

Parameter description:

-- threads=8: set the number of concurrent threads to 8--file-test-mode=rndrw: specify that the file test mode is random read and write, that is, test the random read and write performance of disk IWeiO-- report-interval=1: specify that statistics are output once per second

The test results are as follows:

File operations: reads/s: 2089.75 writes/s: 1392.67 fsyncs/s: 4553.64Throughput: # read read per second, MiB/s: 32.65 # written MiB/s: 21.76General statistics: total time: 10.0778s total number of events: 79973Latency (ms): min: 0.00 avg: 1.00 max: 58.42 95th percentile: 3.13 sum: 79895.62Threads fairness: events (avg/stddev): 9996.6250 execution time (avg/stddev): 9.9870

Finally, perform the cleanup operation to clear the test data:

[root@txy-server ~] # sysbench-- file-total-size=4G fileio cleanup3, test MySQL performance

Because the built-in test type of sysbench does not have MySQL, testname needs to be specified as the path to the test script when testing MySQL with sysbench. Sysbench comes with some MySQL test scripts, so we don't have to write our own test scripts without special needs. Next, give a simple demonstration of how to use test scripts to test the performance of MySQL.

First of all, you need to prepare the data. The command is as follows:

[root@txy-server ~] # sysbench-- mysql_storage_engine=innodb-- table_size=10000-- mysql-db=test-- tables=10-- mysql-socket=/tmp/mysql.sock-- mysql-user=root-- mysql-password=123456 / usr/local/share/sysbench/oltp_read_write.lua prepareTips: this script is used to test the read and write performance under OLTP. In addition, the author directly uses the root account here for convenience. In the actual test, it is necessary to create an account for testing.

Perform the test:

[root@txy-server] # sysbench-mysql_storage_engine=innodb-- table_size=10000-- mysql-db=test-- tables=10-- mysql-socket=/tmp/mysql.sock-- mysql-user=root-- mysql-password=123456 / usr/local/share/sysbench/oltp_read_write.lua run

The test results are as follows:

SQL statistics: queries performed: # Total number of read operations read: 10444 # Total number of write operations write: 2984 # Total number of other operations For example, other such as commit: 1492 # Total number of operations performed total: 14920 # transactions per second, or TPS, here is the number of transactions: 1492 (74.48 per sec.) # queries per second, that is, QPS Here is the number of queries: 14920 (1489.53 per sec.) # ignored errors per second ignored errors: 0 (1489.53 per sec.) # number of reconnections reconnects: 0 (1489.53 per sec.) General statistics: total time : 10.0150s total number of events: 746Latency (ms): min: 8.86 avg: 13.42 max: 99.97 95th percentile : 20.00 sum: 10009.79Threads fairness: events (avg/stddev): 746.0000max 0.00 execution time (avg/stddev): 10.0098max 0.00

Again, finally, you need to clean up the test data:

[root@txy-server] # sysbench-mysql_storage_engine=innodb-- table_size=10000-- mysql-db=test-- tables=10-- mysql-socket=/tmp/mysql.sock-- mysql-user=root-- mysql-password=123456 / usr/local/share/sysbench/oltp_read_write.lua cleanup

If you want to know which parameters the test script supports, it is viewed in the same way as the built-in test type, except that the name of the test type is changed to a script:

[root@txy-server ~] # sysbench/ usr/local/share/sysbench/oltp_read_write.lua help

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.