Analysis of Sysbench's benchmarking process for MySQL

Preface

1. Benchmarking is a type of performance testing that emphasizes quantitative, reproducible, and comparable testing of certain performance indicators of a class of test objects.

To understand further, benchmarking is to establish a known performance level (called a baseline) through benchmarking at a certain time. When the system's hardware and software environment changes, benchmarking is performed again to determine the impact of those changes on performance. This is also the most common use of benchmarking. Other uses include determining performance limits at certain load levels, managing system or environmental changes, and identifying conditions that could lead to performance problems.

2. The role of benchmarking:

For most Web applications, the system bottleneck often occurs easily on the database side. The reason is simple: other factors in Web applications, such as network bandwidth, load balancing nodes, application servers (including CPU, memory, hard disk light, number of connections, etc.), and cache, can easily achieve performance improvements through horizontal expansion (commonly known as adding machines). For databases such as MySQL, due to the requirement for data consistency, it is impossible to disperse the pressure of writing data to the database by adding machines; although the pressure can be reduced through front-end caching (Redis, etc.), read-write separation, and sharding of libraries and tables, it is subject to too many restrictions compared with the horizontal expansion of other components of the system.
The purpose of database benchmarking is to analyze the performance of the database under the current configuration (including hardware configuration, OS, database settings, etc.), so as to find the performance threshold of the database and adjust the configuration according to the actual system requirements. In addition, benchmarking of database servers is also often used to observe how performance is affected before and after database structure modifications.

3. The difference between benchmark test and stress test:

Many times, benchmark testing and stress testing are easily confused in actual use. Benchmark testing can be understood as a stress test for the system. However, benchmark testing does not care about business logic and is simpler, more direct, and easier to test. Data can be generated by tools and is not required to be real. Stress testing generally considers business logic (such as shopping cart business) and requires real data.

4. Benchmarking Tools:

SysBench is a modular, cross-platform, multi-threaded benchmark tool that is mainly used to evaluate database load under various system parameters. It mainly includes the following types of tests:

1. CPU performance

2. Disk IO performance

3. Scheduler Performance

4. Memory allocation and transmission speed

5. POSIX thread performance

6. Database performance (OLTP benchmark)

Currently, sysbench mainly supports three databases: MySQL, pgsql, and oracle.

Install

yum -y install sysbench

sysbench --help ##Check whether the installation is successful

Friendly reminder: If there is a missing dependency package during installation, please see here

Data preparation

Prepare

create database sysbench_test;

show databases; #Check the database

quit #Exit

2. Get Started

find / -name oltp*.lua #Find the path of the sysbench built-in data writing script, which will be used to execute commands later

sysbench /usr/share/sysbench/oltp_read_write.lua --tables=5 --table_size=100 --mysql-user=root --mysql-password=xxx --mysql-host=192.168.0.103 --mysql-port=3306 --mysql-db=sysbench_test prepare
#/usr/share/sysbench/oltp_read_write.lua: the path of the sysbench built-in read and write scripts found above
#--tables: specifies the number of generated tables. Five tables are set here, indicating that five test tables are generated. Readers can adjust this value according to actual needs.
#--table_size: specifies the amount of data generated in the generated table. The above example shows that 100 test data are generated for each table. The actual value can be adjusted as needed. For example, it can be adjusted to: 1000000, which means that one million test data are generated.
#--mysql-db: The name of the test database to connect to. The database created above is used for testing here.
#--mysql-user: the username of the connected database
#--mysql-password: the password of the connected database
#--mysql-port: the port of the connected database development

Execution prompt error

reason:

The imported data exceeds the default value of the database

solve:

You need to modify the data configuration on the server where MySQL is installed

vim /etc/my.cnf

Modify the value of max_allowed_packet. If it does not exist, add a line at the end.

Execute successfully again, check the data on the mysql client

Execute the test

sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=xxx --mysql-host=192.168.0.103 --mysql-port=3306 --mysql-db=sysbench_test --tables=5 --table_size=100 --threads=10 --time=30 --report-interval=3 run
#--threads: indicates the number of threads
#--time: indicates execution time
#--report-interval: indicates the interval in seconds to output test information
# run: indicates running. Other parameter information is the same as above and will not be explained here.

The above command indicates that 10 concurrent threads are used, the execution time is 30 seconds, and the test information is output every 3 seconds.

Among them, the more important information for us includes:

• queries: total number of queries and qps
• transactions: total number of transactions and tps
• Latency-95th percentile: The response time of the first 95% of requests.

Cleaning the data

Don't forget the final finishing touches after the test is completed. A large amount of test data stored in the database still has an impact.

sysbench /usr/share/sysbench/oltp_read_write.lua --tables=5 --table_size=100 --mysql-user=root --mysql-password=xxx --mysql-host=192.168.0.103 --mysql-port=3306 --mysql-db=sysbench_test cleanup
#The parameters here are filled in according to the parameters of the previously inserted data, making sure all are cleared

Check the data

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.