What is a benchmark

When we optimize the database, we only know if the optimization is effective by measuring the performance of the system. This measurement is benchmarking.Benchmark tests are defined as follows:

We can think of it as follows:

But test-based testing is not equivalent to stress testing:

• Test-based: Direct, simple, easy to compare, used to assess the processing power of a server.Benchmarks may not care about business logic, and the queries used and the authenticity of the business may have nothing to do with business processes
• Stress testing: Testing real business data to get the pressure that a real system can withstand.Stress testing requires different topics, and the data and queries used are also true

The purpose of benchmarking MySQL is:

• 1. Establish performance baseline of MySQL server to determine current MySQL server operation
• 2. Simulate a higher load than the current system to find out the expansion bottleneck of the system.Increase database concurrency and observe changes in QPS and TPS to determine the relationship between concurrency quantity and optimal performance
• 3. Testing different hardware, software and operating system configurations
• 4. Verify that the new hardware device is properly configured

How to Benchmark

In general, there are two ways to benchmark:

• 1. Benchmark the whole system: test from the entrance of the system (Web front-end of the website, APP front-end of the mobile phone)
  • Advantages: can test the performance of the entire system, including web server caches, databases, etc. can reflect the performance problems between the various component interfaces in the system, reflect the real performance status
  • Disadvantages: Complex and time consuming test case design
• 2. Benchmark MySQL separately: only test MySQL services in the system
  • Advantages: Simple test case design and less time-consuming
  • Disadvantages: Unable to fully understand the performance baseline for the entire system

Common metrics for MySQL benchmarks:

• Number of transactions processed per unit time (TPS)
• Number of queries processed per unit time (QPS)
• Concurrent Number: Number of query requests processed simultaneously
• Response time of a single test task
  • Contains: average response time, minimum response time, maximum response time, and percentage of time

Easy to ignore in benchmarking:

• When testing with production environment data, only part of the data is used, which may result in inaccurate test results
  • Recommendation: Use the full backup data of the database for testing
• In a multi-user scenario, only single-user tests were performed
  • Recommended: Use multithreaded concurrency testing
• Testing distributed applications on a single server
  • Recommendation: The same architecture should be used for testing
• Repeatedly executing the same query, the problem is that the same SQL statement is easy to cache hits and does not reflect the true query performance.Queries may not be the same in real-world environments

Steps for benchmarking:

• 1. Choosing whether to test the entire system or a component requires determining what data to use for testing.
• 2. Preparing a collection script for test data usually requires collecting as much information as possible about the current system, such as CPU usage, IO, network traffic, status and counter information, etc.
• 3. Write a script to analyze the benchmark information gathered in the second step and get the test results

Collect scripts and analyze script examples

Collection script for benchmark data:

#!/bin/bash
# Run interval of script
INTERVAL=5
# Create a storage directory for data files
STORE_DIR=/home/mysql/benchmarks
mkdir -p $STORE_DIR
# Which directory will the collected data be stored in and the file prefix
PREFIX=$STORE_DIR/$INTERVAL-sec-status
# Set the running identity of the script
RUNFILE=$STORE_DIR/running
touch $RUNFILE && echo "1" > $RUNFILE
# mysql user name and password
USER=root
PWD=123456
# The path to the mysql command
MYSQL="/usr/local/mysql/bin/mysql -u$USER -p$PWD"
# Record global variables for the current mysql
$MYSQL -e "show global variables" >> mysql-variables

# The running identity file loops when it exists
while test -e $RUNFILE; do
    # Define the current time at which the script is running
    file=$(date +%F_%I)
    # Run the cycle at intervals
    sleep=$(date +%s.%N | awk '{print 5 - ($1 % 5)}')
    sleep $sleep

    ts="$(date +"TS %s.%N %F %T")"
    # Get system load information
    loadavg="$(uptime)"
    # Logging system load information
    echo "$ts $loadavg" >> $PREFIX-${file}-status
    # Record global variable information for the current mysql
    $MYSQL -e "show global status" >> $PREFIX-${file}-status &
    echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
    # Record current innodb status information
    $MYSQL -e "show engine innodb status" >> $PREFIX-${file}-innodbstatus &
    echo "$ts $loadavg" >> $PREFIX-${file}-processlist
    # Record the list of all connection information for the current mysql
    $MYSQL -e "show full processlist\G" >> $PREFIX-${file}-processlist &
    echo $ts
done

echo Exiting because $RUNFILE does not exists

Test data analysis script:

#!/bin/bash
awk '
   BEGIN {
     printf "#ts date time load QPS";
     fmt=" %.2f";
   }
   /^TS/ {
   ts = substr($2,1,index($2,".")-1);
   load = NF -2;
   diff = ts - prev_ts;
   printf "\n%s %s %s %s",ts,$3,$4,substr($load,1,length($load)-1);
   prev_ts=ts;
   }
   /Queries/{
   printf fmt,($2-Queries)/diff;
   Queries=$2
   }
   ' "$@"

### Use examples ###
# Sh. /analyze.sh ${test data file path}
# For example: sh. /analyze.sh /home/mysql/benchmarks/5-sec-status-2020-01-11_04-status

mysqlslap for Mysql Benchmarking Tool

In general, we don't script benchmarks on our own, because there are tools out there.For example, mysqlslap is a benchmarking tool that comes with versions above mysql 5.1.Since it is built-in and does not require a separate installation, the tool is located in the bin directory of mysql.

Functional introduction:

• Simulate server load and output statistics
• You can specify a query statement for a test, or you can use test statements that are automatically generated by the tool

Description of common parameters:

parameter	Explain
--concurrency	The number of concurrent clients, that is, the number of simulated clients, can be specified multiple, separated by commas
--iterations	Specify the number of runs of the test
--auto-generate-sql	Use system-generated SQL scripts for testing
--auto-generate-sql-add-autoincrement	Add self-increasing ID s to automatically generated tables
--auto-generate-sql-load-type	Specify the type of query used in the test (read, write, update, mixed), default mixed
--auto-generate-sql-write-number	Specifies the amount of data generated when initializing the data
--engine	To test the storage engine for tables, allow multiple designations, separated by commas
--no-drop	Specify that test data is not cleaned up
--number-of-queries	Specify the number of queries executed per connection
--debug-info	Specify Output Extra Memory and CPU Statistics
--number-char-cols	Specify the number of int types contained in the test table
--number-int-cols	Specify the number of varchar types contained in the test table
--create-schema	Specify the name of the database used to execute the test
--query	SQL script for specifying custom
--only-print	When this parameter is specified, the test script is not run, but the generated script is printed out

• More parameters are detailed in Official Documents

Example use:

[root@localhost ~]# mysqlslap -uroot -p123456 --concurrency=1,50,100,200 --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 intercepted are as follows:

Benchmark
    # Run tests for the myisam engine
    Running for engine myisam
    # Average 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 number of seconds to run all queries
    Maximum number of seconds to run all queries: 0.022 seconds
    # Number of clients running queries
    Number of clients running queries: 1
    # Average number of queries per client 
    Average number of queries per client: 10

Benchmark
    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: 10

sysbench of Mysql benchmarking tool

In addition to mysqlslap, a native benchmarking tool for mysql, sysbench is a common tool.Sysbench has more features than mysqlslap and is more comprehensive to test.In addition to testing MySQL, sysbench can also test CPU, IO, memory and other aspects of the system.

Install sysbench

The GitHub repository address and source installation documentation for sysbench are as follows:

• https://github.com/akopytov/sysbench
• Building and Installing From Source

In general, the sysbench tool will not come with the system, we need to install it by ourselves. I will install it here by compiling and installing the source code.First, copy the download link of the source package to the following address:

• https://github.com/akopytov/sysbench/releases

Then download it on Linux using the wget command:

[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 on which sysbench is compiled:

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

The downloaded source package is then decompressed and entered into 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, complete the compilation and installation with the following steps:

[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 installation to complete, test whether the installation was successful.I have reported here an error that the library file could not be found:

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

The solution is to set up a soft connection to the file in / 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]# sysbench --version
sysbench 1.0.19

Use of sysbench

The command format for sysbench is as follows:

sysbench [options]... [testname] [command] 

1. options are the specified test parameters. The following are commonly used parameters when testing MySQL with sysbench:

• --mysql-db: The name of the database used to specify the benchmark, which must already exist
• --mysql_storage_engine: MySQL storage engine for the specified test
• --tables: Number of tables that execute tests
• --table_size: Specifies the amount of data in each table
• --threads: Specifies the number of concurrent threads for the test
• --max-time: Specify the maximum test time in seconds
• --report-interval: Output statistics once in seconds at a specified interval
• --mysql-socket: Specifies the path to the.sock file of Mysql to connect to MySQL
• --mysql-user: Specify the user to connect to MySQL for testing
• --mysql-password: Specify the password for the MySQL user
• For more information on other parameters Official Documents

2. Tesname is the specified built-in test type or test script.The built-in test types have the following values:

• fileio: Test file system I/O performance
• cpu: test cpu performance
• Memory: test memory performance
• threads: testing thread performance
• Mutex: test mutex performance
• Executing sysbench [testname] help allows you to view the parameters supported by each test type

Test scripts need to be specified for tests other than built-in test types.The test script for sysbench needs to be written in lua language. 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 script that comes with sysbench is as follows:

[root@txy-server ~]# ls /usr/local/share/sysbench
bulk_insert.lua  # Used to test a large number of insert operation performance
oltp_common.lua  # Public files, referenced by other scripts, not available directly
oltp_delete.lua  # Used to test delete operation performance under oltp
oltp_insert.lua  # Used to test insert operation performance under oltp
oltp_point_select.lua   # Used to test the performance of fixed-point queries under oltp, such as queries based on primary keys
oltp_read_only.lua   # Used to test read-only performance under oltp
oltp_read_write.lua  # Used to test read and write performance under oltp
oltp_update_index.lua  # Used to test the performance of updating index fields
oltp_update_non_index.lua  # Used to test the performance of updating indexed field operations
oltp_write_only.lua  # Used to test write performance under oltp
select_random_points.lua  # Used to test the performance of random fixed-point queries
select_random_ranges.lua  # Used to test random range read performance
...

• Tips: Different versions of sysbench may have different paths to store test scripts and need to confirm themselves

3. command specifies some test actions, such as running tests, clearing data or preparing data, etc.

• Prepare: used to prepare test data before the test starts
• run: execute the test when the test data is ready
• Cleaup: Used to clean up test data after test execution is complete

sysbench benchmark example

1. Testing CPU performance

The following commands let the cpu find prime numbers within 10,000 and count the time required to calculate them to see how well the cpu performs.It is important to note that this approach tests single-core performance rather than multicore performance:

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

The test results are as follows:

# CPU speed
CPU speed:
    # Number of events per second
    events per second:  1039.79

General statistics:
    # Calculate the total time spent on all prime numbers
    total time:                          10.0005s  
    # Total number of events
    total number of events:              10400

Latency (ms):
         # Calculate the minimum time spent on a prime number
         min:                                    0.94
         # Average time to compute a prime number
         avg:                                    0.96
         # Calculate the maximum time spent on a prime number
         max:                                    9.43
         # 95% of cases are 1.01 milliseconds calculating a prime number
         95th percentile:                        1.01
         # Total time consumed
         sum:                                 9994.05

# Thread fairness
Threads fairness:
    events (avg/stddev):           10400.0000/0.00
    execution time (avg/stddev):   9.9941/0.00

2. Test system I/O performance

To test I/O performance, test data needs to be prepared, and the size of test data needs to be larger than physical memory.The commands are as follows:

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

When the data is ready, execute 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: Specifies that the file test mode is random read-write, i.e. tests the random read-write performance of disk I/O
• --report-interval=1: Specifies 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.64

Throughput:
    # Read per second
    read, MiB/s:                  32.65
    # 
    written, MiB/s:               21.76

General statistics:
    total time:                          10.0778s
    total number of events:              79973

Latency (ms):
         min:                                    0.00
         avg:                                    1.00
         max:                                   58.42
         95th percentile:                        3.13
         sum:                                79895.62

Threads fairness:
    events (avg/stddev):           9996.6250/43.23
    execution time (avg/stddev):   9.9870/0.01

Finally, clean up the test data:

[root@txy-server ~]# sysbench --file-total-size=4G fileio cleanup

3. Testing MySQL performance

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

First and foremost, you need to prepare the data with the following commands:

[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 prepare

• Tips: This script is used to test the read and write performance under OLTP.In addition, the author here uses the root account directly for convenience. In the actual test, it is important to create an account for the test.

Perform tests:

[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, such as commit, etc.
        other:                           1492
        # Total number of operations performed
        total:                           14920
    # The number of transactions executed per second, TPS, here 74.48 per second
    transactions:                        746    (74.48 per sec.)
    # The number of queries per second, QPS, here 1489.53 per second
    queries:                             14920  (1489.53 per sec.)
    # Number of errors ignored
    ignored errors:                      0      (0.00 per sec.)
    # Number of reconnections
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0150s
    total number of events:              746

Latency (ms):
         min:                                    8.86
         avg:                                   13.42
         max:                                   99.97
         95th percentile:                       20.00
         sum:                                10009.79

Threads fairness:
    events (avg/stddev):           746.0000/0.00
    execution time (avg/stddev):   10.0098/0.00

Again, the final step is 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 specifically supports, you can view them the same way as the built-in test type, just by replacing the name of the test type with the script:

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