"MySQL Database" chapter II interpretation: MySQL benchmark test

Preface:

Benchmark benchmark: Basic skills, a stress test for system design, is the only convenient and effective way to learn what happens next to a given workload, he can observe the behavior of the system under different pressures, assess the capacity of the system, and master what is the important change, or observing how the system handles different data, you can create a virtual scene outside the actual load of the system to test it (mastering system behavior)

Body:

such as preface, Benchmark test is very, heavy, want! What can be done: Overall: testing hardware, estimating hardware, validating systems, measuring pressure, adjusting configuration

1, verify the assumptions based on the system, confirm whether the hypothesis is in accordance with the actual situation, 2, reproduce some abnormal behavior in the system to solve; 3, the test system current operation, the use of historical results to diagnose unpredictable problems; 4. Simulate a higher load to find out the scalability bottlenecks that the system may encounter as the pressure increases; 5. Planning for future business growth, hardware, network capacity, related resources; 6, the test application adapts to the variable environment ability, 7, tests the different hardware, the software and the operating system configuration, proves the equipment is configured correctly;

The purpose of benchmarking the database is to analyze the performance of the database under the current configuration (including hardware configuration, OS, database settings, etc.), to find out the performance threshold of MySQL and adjust the configuration according to the requirements of the actual system. Source

Different from real pressure: real complex and changeable, benchmark requirements to be completed as soon as possible, simple and direct, easy to compare results, low cost and easy to do

2.2 Strategy

For the whole system: integrated Full-stack

Test MySQL separately: single-component single-component

Recommended overall test: to set correctly

1, the user is concerned about the overall performance; 2, MySQL is not always the bottleneck, 3, more can reveal the real performance of the application

Recommended individual tests: Data required

1, need to compare the performance of different schemas or queries, 2, for a specific problem of testing; 3, avoid long, short-term, fast cycle cycles

2.2.1 Indicators

Objective: To refine into a series of problems, concrete analysis of specific problems

Throughput:

Number of transactions per unit of time, TPC-C, multi-user interactive applications, transactions/sec, transactions per minute

Response time or delay

The overall time required to test the task, the average sound time, the minimum ring time, the maximum, and the percentage; chart

Concurrency:

Test the performance of the application under different concurrency, pay attention to the concurrent operation in the work, and the number of threads in the work simultaneously;

Web server Concurrency! = database, table-only session storage mechanism data processing ability; How much concurrency can be found in web concurrency at any time;

Scalability

Scalability: To increase the system one times the work, the rational situation can get two of the results; Add a resource or twice times more throughput to the system

System business pressure may change: scalability is necessary; This indicator is useful for capacity specification: providing information to identify application bottlenecks

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

2.3 Methods

To be as close as possible to real-world applications:

Use complete set, data distribution characteristics, real distribution parameters, whether multiple users, matching user behavior, multi-type, check error log, System preheating: How long after the restart to achieve normal performance capacity, sustained a certain time;

2.3.1 Design, Specification

Ask questions and clear goals

Standard Benchmark: the right solution for Tpc-h OLTP

Dedicated testing: complex, iterative, easy-to-restore production data set snapshots

schedule: parameters, results documentation, test detail records

2.3.2 Time

The benchmark should run long enough to confirm that the time is running and that continuous observation confirms that the system is stable

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

2.3.3 Getting system performance and status

Collect as much information as possible of the system under test

Best build directory, each test run to create a separate subdirectory, the results, configuration files, test indicators, scripts and other related instructions to save them

Required records are data:

System Status, performance metrics: CPU usage, disk I/O, network traffic statistics, ShowGlobal status counters

Reasonable interval, record start time, use timestamp, just collect just fine

2.3.4 to get accurate results

Answer some questions:

Have you selected the correct benchmark test? Did you collect data for the problem? Is the warm-up time long enough?

Are you using the wrong test criteria: IO-intensive references use CPU-intensive test standards to evaluate performance?

Are test results repeatable? Ensure system status is consistent before re-testing.

Impact factors:

External pressure, performance analysis, monitoring system, verbose logging, periodic operation

Attention:

The required resources in the process are designed to be tested, the parameters of the benchmark are modified as few as possible in the test, and the parameters of the benchmarks are gradually modified by iteration; Study the abnormal situation in the process and find out the reason

2.3.5 Running test Analysis results

Automation: Reduce human error, makefile files, scripts

Test results to meet the current requirements, simple run a few rounds of testing, see the results are OK, if the results vary greatly, can be run several times, or longer

Results:

analysis, turning numbers into knowledge, the ultimate goal is to answer questions at design time

How to abstract meaningful results, rely on how to collect data, write scripts to analyze data, reduce human error, workload, repeatable, documented

2.3.6 Drawing Importance

A picture is worth thousands of words, originally some knowledge point baby is want to draw map, but there is painting

There is a statement in the book to share: 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 privileges, You can see all the threads, otherwise, you can only see your own thread, without using the full keyword, only the first 100 characters of each query "source"

2.4 Benchmark Test tools

Integrated testing tools: the entire application

1, AB is Apache HTTP, up to the maximum number of requests per second "reference" "2"

2, http_load:ab Similar more flexible, is designed to test the Web server, through an input file to provide multiple URLs, randomly selected to test, can also be customized to the time ratio to test "reference"

3, Jmeter,java program, can load other applications and test its performance, this listening good, the above two have not contacted, not comment

Single-component: MySQL-based system performance

1, mysqlslap:mysql5.1, simulate the load of the server, output timing information, the number of concurrent connections can be executed, specify the SQL statement, or automatically generate the SELECT statement "Reference"

2, MySQL Benchmark Suite (sql-bench): Bring, 5.7 take off, benchmark suite, used for comparison test on different database servers , single-threaded serial execution, test the speed of executing queries , including a large number of pre-defined tests, easy to use, Easily compare performance tests for different engines or configurations, CPU-intensive, and results show which types of operations perform faster on the server, with the disadvantage that the test dataset is small and cannot be used with 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 to library, random data Fill test table "Reference"

4. Database Test Suite: A toolset similar to some industry standard tests, free tpc-c OLTP testing tools

5, Sysbench: multi-threaded system pressure measurement , according to the factors affecting database server performance evaluation system performance, all- round testing tools , support MySQL, operating system, hardware testing "reference" "2"

MySQL benchmark () function: Tests the execution speed of a particular operation, the number of times a parameter can be executed, or an expression (any scalar expression)

2.5 Cases

Omit N word here

2.6 Summary

Finally wait until you ~ the music; it is advisable to familiarize yourself with the sysbench, how to use OLTP (compare different system performance) and FileIO testing, often perform benchmark tests, develop some principles, choose the right test tools, build scripts, collect information analysis results, and be proficient in a drawing tool;

That's what the big guy said, so do you know what my next article is?

Related articles:

"MySQL Database" chapter I interpretation: MySQL Architecture and history

"MySQL Database" chapter III Interpretation: Server performance profiling (bottom)