Introduction to the use of MySQL performance stress benchmark tool sysbench

Time:2021-9-24

1. Introduction to sysbench

Here is a MySQL database stress testing software sysbench, which is used for benchmark testing.

Sysbench is an open source, modular and cross platform multithreading performance testing tool,

It can be used to test the performance of CPU, memory, disk I / O, thread and database.

Currently supported databases are mysql, Oracle and PostgreSQL.

#Project download address:

https://launchpad.net/sysbench
http://github.com/akopytov/sysbench

The idea of the benchmark suite is to quickly get an impression of system performance without setting complex database benchmarks or even installing databases.
The current function allows the following system parameters to be tested:

  • #CPU performance
  • File I / O performance # file IO performance, disk IO performance
  • Scheduler performance # scheduler performance
  • Memory allocation and transfer speed # memory allocation and transfer speed
  • POSIX threads implementation performance #posix threads implementation performance
  • Database server performance (OLTP benchmark) # database server performance (OLTP benchmark)

The OLTP of sysbench is mainly used to evaluate and test the database load under various system parameters.
Compared with version 0.4, OLTP testing in subsequent versions mainly combines Lua scripts without modifying the source code. Different business types can be tested by customizing Lua scripts.  

Disadvantages of sysbench:
The simulated table structure is too simple. It is not a complete transaction system like TPCC mysql, but it is still very useful for MySQL performance test comparison.

2. Sysbench installation process

#Installation related dependencies


mount /dev/cdrom /mnt
yum -y install make automake libtool pkgconfig libaio-devel libtool

#The installation process begins

#Make a link to the client lib package
ln -sf /mysql/app/mysql/lib/libmysqlclient.so.20 /usr/lib/libmysqlclient.so.20
#Decompression installation
cd /softtar zxvf sysbench-1.0.15.tar.gzcd sysbench-1.0.15./autogen.sh
./configure --prefix=/mysql/app/sysbench --with-mysql=/mysql/app/mysql --with-mysql-includes=/mysql/app/mysql/include/
#--with-mysql-libs=/usr/lib/
make && make install
#Configure environment variables
vi ~/.bash_profile
PATH=$PATH:/mysql/app/sysbench/bin:$HOME/bin
source ~/.bash_profile
sysbench --version
#If an error is prompted
sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

#Then add the line / usr / Lib in / etc / ld.so.cnf
[[email protected] local]# cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
/usr/lib/
#Execute ldconfig - V to update the configuration
[[email protected] local]# /sbin/ldconfig -v

#Prompt Description:

If you install MySQL header files and libraries in non-standard locations (no   Mysql_config can be found in path),

You can explicitly specify — with MySQL includes and — with MySQL LIBS option parameters, as shown above.

To compile sysbench without MySQL support, use — without mysql.
If no database driver is available, database related scripts will not work, but other benchmarks will work.

#General command line options

The following table lists the commonly supported options, their descriptions and default values:

option
describe
Default value
–threads
Total number of worker threads to create
1
–events
Limit the total number of requests. 0 (default) means there is no limit
0
–time
Total execution time limit in seconds. 0 means there is no limit
10
–warmup-time
Execute the event with statistics disabled for a few seconds before running the actual benchmark with statistics enabled. This is useful when you want to exclude the initial period of a benchmark run from statistics. In many benchmarks, the initial phase is not representative because CPU / database / pages and other caches require some time to warm up
0
–rate
Average transaction rate. This number specifies the average number of events (transactions) that all threads should execute per second. 0 (default) means unlimited rate, that is, the event executes as fast as possible
0
–thread-init-timeout
Wait time (in seconds) for worker thread initialization
30
–thread-stack-size
Stack size per thread
32K
–report-interval
Intermediate statistics are reported periodically at specified intervals in seconds. Note that this option generates statistics by time interval rather than cumulative. 0 disable intermediate reporting
0
–debug
Print more debugging information
leave
–validate
Verify the test results as much as possible
leave
–help
Print the help according to the general syntax or the specified test, and then exit
leave
–verbosity
Level of detail (0-critical messages only, 5-debug)
4
–percentile
Sysbench measures the execution time of all processed requests to display statistics such as minimum, average, and maximum execution times. For most benchmarks, it is also useful to know the request execution time value that matches a certain percentile (for example, 95% percentile means that we should discard 5% of the longest request and choose the maximum value from the rest of the requests). This option allows you to specify the percentage of query execution time to be calculated
95
–luajit-cmd
Execute the luajit control command. This option is equivalent to luajit – J. For more information, see the luajit documentation.

Note that you can add the corresponding multiplication suffix (k for kilobytes, m for megabytes, G for gigabytes, t for megabytes)
To specify the values of all size options (- thread stack size as shown in this table).

#Sysbench uses [command] commonly:

1) Prepare test preparation and generate data
2) Run execute test
3) Cleanup cleanup data
4) help
5) version

The above is the details of the introduction to the use of MySQL performance stress benchmark tool sysbench. For more information about the use of MySQL sysbench, please pay attention to other related articles of developeppaer!