Shulou(Shulou.com)06/01 Report--

This article mainly introduces how MySQL is tested. The introduction in this article is very detailed and has certain reference value. Interested friends must read it!

Foreword:

Benchmark: Basic skill, a stress test for system design, the only convenient and effective way to learn what happens next under a given workload. It can observe the behavior of the system under different stresses, evaluate the capacity of the system, grasp what is important, or observe how the system handles different data. It can create virtual scenarios outside the actual load of the system for testing (grasp the behavior of the system).

Text:

Benchmarking is, like, important! What can be done: In general: test hardware, estimate hardware, verify system, measure pressure, adjust configuration

1. Verify assumptions based on the system to confirm whether the assumptions conform to 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 with increased pressure;5. Plan future business growth, hardware, network capacity, and related resources;6. Test the ability of the application to adapt to variable environments; 7. Test different hardware, software and operating system configurations to prove whether the equipment is configured correctly;

The purpose 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 the configuration according to the actual system requirements. [Source]

Different from real stress: real complexity and variability; benchmarks require execution as fast as possible, simple and straightforward, easy to compare results, and low cost

2.2 strategy

For the whole system: integrated full-stack

MySQL tested separately: single-component

Recommended overall test: to set it correctly

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

Recommended separate tests: data required

1. Compare the performance of different schemas or queries;2. Test for a specific problem;3. Avoid long, short-term, fast cycle cycles

2.2.1 indicators

Objective: to refine into a series of problems, specific analysis of specific problems

Throughput:

Transactions per unit time, TPC-C, multi-user interactive applications, transactions per second, transactions per minute

response time or delay

Overall time required for testing tasks, average loudness, minimum loudness, maximum loudness and percentage; with the help of graphs

Concurrency:

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

Web server concurrency!= Database, only table session storage mechanism data processing capacity; measure web concurrency at any time how much concurrency;

scalability of

Scalability: doubling the work of the system, two parts of the result can be obtained under rational circumstances; doubling the resources of the system or twice the throughput

System business pressures may change: Scalability measurement is essential; this metric is useful for capacity specification: provides information to discover application bottlenecks

Collect test requirements as much as possible, design tests based on requirements, and avoid paying attention to only some indicators while ignoring others.

2.3 method

To be as close as possible to the real application:

Use full set, data distribution characteristics, true distribution parameters, multi-user, matching user behavior, multi-type, check error log, system warm-up: how long it takes to reach normal performance capacity after restart, lasting for a certain time;

2.3.1 Design and specification

Ask questions, clear goals

Standard Benchmarking: The Right Solution TPC-H OLTP

Dedicated testing: complex, iterative, easy-to-restore snapshots of production datasets

Planning: documentation of parameters, results, detailed documentation of tests

2.3.2 time

Benchmarks should be run long enough to be able to run for unconfirmed times and continue to observe until the confirmation system is stable

A simple test rule: the time it takes for the system to appear stable is at least equal to the time it takes for the system to warm up.

2.3.3 Get system performance and status

Collect as much information as possible about the system under test

Best creates directories, creating separate subdirectories for each test run, where results, configuration files, test metrics, scripts, and other related instructions are stored

Data to be recorded:

System status, performance metrics: CPU utilization, disk I/O, network traffic statistics, SHOWGLOBAL STATUS counters

Reasonable intervals, record start time, use time stamps, just collect

2.3.4 Get accurate results

Answer some questions:

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

Are the wrong test criteria used: IO intensive references CPU-intensive test criteria used to evaluate performance?

Are test results repeatable? Ensure consistent system status before retest; symptomatic test

Influencing factors:

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

Note:

The resources required in the process are dedicated to testing; the parameters are modified as little as possible during the test, and the parameters of the benchmark test are gradually modified through iteration; the anomalies in the process are carefully studied and the causes are found

2.3.5 Run Test Analysis Results

Automation: Reduce human error, Makefiles, scripts

The test results meet the current requirements. Simply run a few rounds of tests and see if the results are OK. If the results vary greatly, you can run several times or longer.

Results:

Analysis, turning numbers into knowledge, with the ultimate goal of answering design questions.

How meaningful results are abstracted depends on how data is collected, scripted, analyzed, human error reduced, workload repeatable, documented

2.3.6 importance of drawing

A picture is worth a thousand words. Originally, there were some knowledge points. The baby wanted to draw a guide picture, but there was no drawing.

There is such a statement in the book, share it: SHOW FULL PROCESSLIST SHOW PROCESSLIST shows which threads are running, you can also use mysqladmin processlist statement to get this information, if you have SUPER permission, you can see all threads, otherwise, you can only see your own threads, do not use FULL keyword, only show the first 100 characters of each query [Source]

2.4 Benchmarking Tools Integrated Testing Tools: Entire Application

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

http_load: ab is similar to more flexible, designed to test web servers, providing multiple URLs through an input file, randomly selected for testing, and can also be customized to test them at a time rate.

3, JMeter, java program, can load other applications and test their performance, this sounds good, the above two have not been contacted, do not comment

One-component approach: testing MySQL, MySQL based system performance

1. mysqlslap: comes with mysql5.1, simulates the load of the server, outputs timing information, can execute concurrent connections, specify sql statements, otherwise automatically generate select statements [Reference]

2、MySQL Benchmark Suite (sql-bench): comes with, 5.7 removed, benchmark test suite, used for comparison tests on different database servers, single thread serial execution, test query execution speed; contains a large number of predefined tests, easy to use, easy to compare performance tests of different engines or configurations, CPU intensive, the results will show which types of operations execute faster on the server, disadvantages: the test dataset is small and cannot be used with specified data, perl BDB support is required;[Reference]

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

Database Test Suite: A toolset similar to some industry standard tests, free TPC-C OLTP testing tools

5. sysbench: multithreaded system pressure test, evaluating system performance according to factors affecting database server performance, all-round test tool, hardware test supporting MySQL, operating system and hardware [Reference][2]

MySQL's BENCHMARK() function: tests the execution speed of a specific operation, the argument can be the number of times to execute or an expression (any scalar expression)

2.5 case

The word n is omitted here.

2.6 summary

Finally wait until you ~ music; suggest at least to be familiar with sysbench, how to use oltp (compare different system performance) and fileio test; often perform benchmark test, it is necessary to formulate some principles, choose appropriate test tools, establish script library, collect information analysis results, proficient in a drawing tool;

That's all for MySQL How to Test. Thanks for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.