[MySQL Database] Interpretation of Chapter 2: MySQL Benchmark Test

Preface:

## He can observe the behavior of the system under different pressures, evaluate the capacity of the system, understand what are the important changes, or observe how the system processes different data, and can create virtual scenarios outside the actual load of the system. Testing (mastering system behavior)

Text:

As mentioned in the preface, benchmark testing is very important! What can be done: In general: test hardware, estimate hardware, verify system, measure pressure, adjust configuration

1. Verify assumptions based on the system and confirm whether the assumptions are consistent with the actual situation; 2. Reproduce Some abnormal behaviors in the system to solve; 3. Test the current operation of the system and use historical results to analyze and diagnose unpredictable problems; 4. Simulate higher loads to find out the scalability bottlenecks that the system may encounter as the pressure increases. ; 5. Plan for future business growth, hardware, network capacity, and related resources; 6. Test the application's ability to adapt to variable environments; 7. Test different hardware, software, and operating system configurations to prove whether the equipment is configured correctly;

The role of database benchmark testing is to analyze the performance of the database under the current configuration (including hardware configuration, OS, database settings, etc.), so as to find out the performance threshold of MySQL and adjust it according to the actual system requirements. configuration. [Source]

Different from real pressure: real complexity and change; benchmark testing requires execution as quickly as possible, simple and direct, easy to compare results, low cost and easy to implement

2.2 Strategy

For the entire system: integrated full-stack

Test MySQL individually: single-component

Recommended overall test: correct settings

1. Users are concerned about the overall performance; 2. MySQL is not always a bottleneck; 3. It can better reveal the true performance of the application

Recommended separate testing: data required

1. Need to compare different schemas or Query performance; 2. Testing for a specific problem; 3. Avoid lengthy, short-term, fast cycle cycles

2.2.1 Indicators

Objective: refine it into a series of questions, Detailed analysis of specific issues

Throughput:

Number of transactions per unit time, TPC-C, multi-user interactive applications, number of transactions per second, number of transactions per minute

Response time or delay

The overall time required for the test task, average response time, minimum response time, maximum response time and percentage; with the help of the chart

Concurrency:

Test the performance of the application under different concurrency, pay attention to the concurrent operations at work, the number of threads and connections working at the same time;

Web server concurrency! = database, only table session storage mechanism data processing capabilities; test web concurrency at any time how much concurrency;

Scalability

Scalability: double the work of the system, rational situation The results of both parts can be obtained at the same time; doubling the resources of the system or doubling the throughput

System business pressure may change: it is necessary to measure scalability; this indicator is useful for capacity specifications: providing information to Discover application bottlenecks

Collect test requirements as much as possible, design tests based on requirements, avoid focusing on only some indicators and ignoring other indicators

2.3 Method

Be as close to the real application as possible Situation:

Use the complete set, data distribution characteristics, real distribution parameters, whether there are multiple users, match user behavior, multiple types, check error logs, system warm-up: how long does it take to reach normal performance capacity after restarting, Last for a certain period of time;

2.3.1 Design and specification

Raise questions and clear goals

Standard benchmark test:Suitable solution TPC-H OLTP

Dedicated testing: Complex, iterative, easy-to-restore snapshots of production data sets

Planning: Documentation of parameters, results, testing Detailed records

2.3.2 Time

The benchmark test should

run for a long enough time. If the time cannot be confirmed, it can be run continuously. Continue to observe until the system is confirmed to be stable

A simple test rule: wait for the system to look stable at least = the time for the system to warm up

2.3.3 Obtain system performance and status

Collect as much of the system under test as possible Information

bestCreate a directory, create a separate subdirectory for each round of testing, and save the results, configuration files, test indicators, scripts and other related instructions

The data that needs to be recorded:

System status, performance indicators: CPU usage, disk I/O, network traffic statistics, SHOWGLOBAL STATUS counter

Reasonable intervals, record start time, utilization timestamp, just collect

2.3.4 Get accurate results

Answer some questions:

Did you choose the right benchmark? Was data collected for the question? Is the warm-up time long enough?

Is the wrong test standard used: IO-intensive references use CPU-intensive test standards to evaluate performance?

Are the test results repeatable? Ensure that the system status is consistent before retesting; symptomatic testing

Influencing factors:

External pressure, performance analysis, monitoring system, detailed logs, periodic operations

Note:

The resources required in the process are specially used for testing; modify the parameters as little as possible during the test, and gradually modify the parameters of the benchmark test through iteration; carefully study the abnormal situations in the process and find the reasons

2.3.5 Run test analysis results

Automation: Reduce human errors. Makefile files and scripts

The test results meet the current needs. Simply run a few rounds of tests and see the results. If the results change greatly, , you can run it several times or for a longer time

Result:

Analysis, turning numbers into knowledge, the ultimate goal is to answer Problems during design

How to abstract meaningful results depends on how to collect data, write scripts to analyze data, reduce human errors, workload, repeatability, and documentation

2.3.6 DrawingImportance

A picture is worth a thousand words. There are some knowledge points that the baby originally wanted to draw a guide map, but there are no drawings

in the book Share this statement: SHOW FULL PROCESSLIST SHOW PROCESSLIST shows which threads are running. You can also use the mysqladmin processlist statement to get this information. If you have SUPER permissions, you can see all threads. Otherwise, you can only see you. In its own thread, if the FULL keyword is not used, only the first 100 characters of each query will be displayed [Source]

2.4 Benchmark Test Tool

Integration Test Tool: Entire Application

1. ab is Apache HTTP, how many requests can be processed per second [Reference] [2]

2. http_load: ab is similar to more flexible and is designed to test web servers and is provided through an input file Multiple URLs can be randomly selected for testing, or can be customized to test according to time ratio [Reference]

3. JMeter, a java program, can load other applications and test their performance. This sounds good. I have not come into contact with the above two, so I will not comment.

Single-component type: testing MySQL, system performance based on MySQL

1. mysqlslap: comes with mysql5.1, simulates the load of the server, and outputs Timing information, you can execute the number of concurrent connections and specify the sql statement, otherwise the select statement will be automatically generated [Reference]

2. MySQL Benchmark Suite (sql-bench): Comes with it, removed from 5.7, benchmark test suite, use Comparison testing on different database servers, single-threaded serial execution, measuring the speed of query execution; Contains a large number of predefined tests, easy to use, easily compare performance tests of different engines or configurations, CPU-intensive , the results will show which types of operations are performed faster on the server. Disadvantages: the test data set is small and cannot use the specified data, and requires perl BDB support; [Reference]

3. Super Smack: MySQL, PostgreSQL provides stress testing and load balancing, complex and powerful tools that can simulate multi-user access, load test data into the database, and fill test tables with random data [Reference]

4. Database Test Suite: A tool set similar to some industrial standard tests, Free TPC-C OLTP test tool

5. sysbench: Multi-threaded system stress test , evaluate the performance of the system based on the factors that affect the performance of the database server, all-in-one testing tool, supports hardware testing of MySQL, operating system, and hardware [Reference] [2]

MySQL BENCHMARK() function: Tests the execution speed of a specific operation. The parameter can be the number of times to be executed or an expression (any scalar expression)

2.5 Case

The n word is omitted here

2.6 Summary

It’s finally waiting for you~ Music starts; it is recommended to at least be familiar with sysbench, how to use oltp (comparing different system performance) and fileio testing; it is necessary to perform benchmark tests frequently and formulate some principles to choose the appropriate one Test tools, build script libraries, collect information analysis results, and be proficient in a drawing tool;

The bosses have said so, so do you know what my next article is?

Related articles:

[MySQL Database] Chapter 1 Interpretation: MySQL Architecture and History

[MySQL Database] Interpretation of Chapter 3: Server Performance Analysis (Part 2)

The above is the detailed content of [MySQL Database] Interpretation of Chapter 2: MySQL Benchmark Test. For more information, please follow other related articles on the PHP Chinese website!