MySQL benchmark tool

1, Benchmarking

Benchmark is a kind of stress test for system design.

Benchmarking is simplified stress testing.

1.1 common indicators

  • TPS
  • QPS
  • response time
  • Concurrency

1.2 script for data collection and analysis

The shell script gather.sh for collecting data

#!/bin/sh

INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/home/benchmarks/running
mysql -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
    file=$(date +%F_%I)
    sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
    sleep $sleep
    ts="$(date +"TS %s.%N %F %T")"
    loadavg="$(uptime)"
    echo "$ts $loadavg" >> $PREFIX-${file}-status
    mysql -e 'show global status;' >> $PREFIX-${file}-status &
    echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
    mysql -e 'show engine innodb status;' >> $PREFIX-${file}-innodbstatus &
    echo "$ts $loadavg" >> $PREFIX-${file}-processlist
    mysql -e 'show full processlist;' >> $PREFIX-${file}-processlist &
    echo $ts 
done
echo Exiting because $RUNFILE does not exist.

analyze.sh

#!/bin/sh
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;
        prev_ts = ts;
        printf "\n%s %s %s %s", ts, $3, $4, substr($load, 1, length($load) - 1);
    }
    /Queries/ {
        printf fmt, ($2-Queries)/diff;
        Queries=$2;
    }
' "$@"

2, Benchmarking tools

2.1 mysqlslap

Common parameters

# mysqlslap --help
--auto-generate-sql Automatically generated by the system SQL Script to test
--auto-generate-sql-add-autoincrement Add autoincrement to the generated table ID
--auto-generate-sql-load-type=name Specify the type of query used in the test
--auto-generate-sql-execute-number= Specifies the amount of data generated when data is initialized
--concurrency= Specify the number of concurrent threads
--engine Specifies the storage engine for the table to be tested, multiple storage engines can be separated by commas
--no-drop Specify not to clean up test data
--iterations Specify the number of test runs
--number-of-queries Specify the number of queries executed by each thread
--debug-info Specify output extra memory and CPU statistical information
--number-int-cols Specifies the INT Number of type columns
--number-char-cols Specifies the varchar Number of types
--create-schema Specifies the name of the database used to perform the test
--query Used to specify customization SQL Script
--only-print Instead of running the test script, print out the generated script

Example

mysqlslap \
--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 \
> 1.txt

test result

[root@mysql211 ~]# cat 1.txt
Benchmark
	Running for engine myisam
	Average number of seconds to run all queries: 0.001 seconds
	Minimum number of seconds to run all queries: 0.001 seconds
	Maximum number of seconds to run all queries: 0.001 seconds
	Number of clients running queries: 1
	Average number of queries per client: 10

Benchmark
	Running for engine myisam
	Average number of seconds to run all queries: 0.043 seconds
	Minimum number of seconds to run all queries: 0.037 seconds
	Maximum number of seconds to run all queries: 0.052 seconds
	Number of clients running queries: 50
	Average number of queries per client: 0

Benchmark
	Running for engine myisam
	Average number of seconds to run all queries: 0.082 seconds
	Minimum number of seconds to run all queries: 0.082 seconds
	Maximum number of seconds to run all queries: 0.084 seconds
	Number of clients running queries: 100
	Average number of queries per client: 0

Benchmark
	Running for engine myisam
	Average number of seconds to run all queries: 0.245 seconds
	Minimum number of seconds to run all queries: 0.245 seconds
	Maximum number of seconds to run all queries: 0.246 seconds
	Number of clients running queries: 200
	Average number of queries per client: 0

Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 0.007 seconds
	Minimum number of seconds to run all queries: 0.002 seconds
	Maximum number of seconds to run all queries: 0.010 seconds
	Number of clients running queries: 1
	Average number of queries per client: 10

Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 0.229 seconds
	Minimum number of seconds to run all queries: 0.212 seconds
	Maximum number of seconds to run all queries: 0.238 seconds
	Number of clients running queries: 50
	Average number of queries per client: 0

Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 0.519 seconds
	Minimum number of seconds to run all queries: 0.471 seconds
	Maximum number of seconds to run all queries: 0.555 seconds
	Number of clients running queries: 100
	Average number of queries per client: 0

Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 0.849 seconds
	Minimum number of seconds to run all queries: 0.767 seconds
	Maximum number of seconds to run all queries: 0.909 seconds
	Number of clients running queries: 200
	Average number of queries per client: 0

[root@mysql211 ~]# 

2.2 sysbench 0.5

download

# Version found on MySQL is too low
# https://dev.mysql.com/downloads/benchmarks.html
wget https://downloads.mysql.com/source/sysbench-0.4.12.14.tar.gz

# https://github.com/akopytov/sysbench/releases
wget https://github.com/akopytov/sysbench/archive/1.0.13.tar.gz
wget https://github.com/akopytov/sysbench/archive/0.5.zip

For installation, refer to the extracted README.md

# After decompression, the directory is ~ / sysbench-0.5
unzip sysbench-0.5.zip
cd sysbench-0.5
# You can continue without error after running
./autogen.sh
# To configure
./configure \
--with-mysql-includes=/usr/local/mysql/include \
--with-mysql-libs=/usr/local/mysql/lib
# Compilation and installation
make && make install

Common parameters

sysbench --help
--test Used to specify the type of test to be performed, the following parameters are supported
  fileio - File I/O test file system I/O performance testing
  cpu - CPU performance test CPU performance testing
  memory - Memory functions speed test Memory performance test
  threads - Threads subsystem performance test
  mutex - Mutex performance test
Oltp Specific tests should be specified lua Script
Lua Script is located sysbench-0.5/sysbench/tests/db
--mysql-db Specifies the database name to perform the benchmark
--mysql-table-engine Used to specify the storage engine used
--oltp-tables-count Number of tables to test
--oltp-table-size Specify the number of data rows in each table
--num-threads Specify the number of concurrent threads to test
--max-time Specify the maximum test time
--report-interval Specifies how often statistics are output at intervals
--mysql-user Specifies the MySQL user
--mysql-password Specifies the MySQL User's password

prepare Used to prepare test data
run For actual testing
cleanup Used to clean up test data

Test CPU

sysbench --test=cpu --cpu-max-prime=1000 run 

Test disk I/O

# free -m view memory
sysbench --test=fileio --file-total-size=1G prepare
# View related parameters
sysbench --test=fileio --help
# test
sysbench --test=fileio \
--num-threads=8 \
--init-rng=on \
--file-total-size=1G \
--file-test-mode=rndrw \
--report-interval=1 \
run

2.3 sysbench test database

Create libraries and tables

create database dbtest;
grant all privileges on *.* to dbtest@'localhost' identified by '123456';

Prepare to generate tables and data

# Enter script directory
cd ~/root/sysbench-0.5/sysbench/tests/db
# View script
ls -l *.lua
# Get ready
sysbench --test=./oltp.lua \
--mysql-table-engine=innodb \
--oltp-table-size=10000 \
--mysql-db=dbtest \
--mysql-user=dbtest \
--mysql-password=123456 \
--oltp-tables-count=10 \
--mysql-socket=/tmp/mysql.sock \
prepare

Run the collect test data script gather.sh

# Create the directory required for the script
mkdir /home/benchmarks && cd /home/benchmarks
# Place the gather.sh script on the above target and create running
touch running
# Background operation
sh gather.sh &

Conduct benchmarking

# Function
sysbench --test=./oltp.lua \
--mysql-table-engine=innodb \
--oltp-table-size=10000 \
--mysql-db=dbtest \
--mysql-user=dbtest \
--mysql-password=123456 \
--oltp-tables-count=10 \
--mysql-socket=/tmp/mysql.sock \
run

After the test, run the script analyze.sh to analyze the data

# View acquired data
[root@mysql211 benchmarks]# ll -h 5*
-rw-r--r--. 1 root root 124K Mar 12 16:41 5-sec-status-2018-03-12_04-innodbstatus
-rw-r--r--. 1 root root  11K Mar 12 16:41 5-sec-status-2018-03-12_04-processlist
-rw-r--r--. 1 root root 236K Mar 12 16:41 5-sec-status-2018-03-12_04-status

# Analyze the data. Sh filename
sh analyze.sh /home/benchmarks/5-sec-status-2018-03-12_04-innodbstatus

Three, reference

  • Chapter 2 of high performance mysql (version 3)

Tags: MySQL SQL Database github

Posted on Sun, 05 Apr 2020 22:26:11 -0700 by Cheeseweasel