Basic MySQL performance test
Test reason
Why is performance testing required?
- Simulate a higher load than the current system to identify performance bottlenecks
- Reproduce online exceptions
- Test different hardware and software configurations
- Plan future business growth
Test Category Classification of performance tests
- Device layer test
- Test the service layer
- Test the database layer
Device layer test
- Metrics
-
- Server, disk Performance
- Disk Bad block rate
- Server Life Cycle
Business Layer Test
Test the database layer
- When to test Mysql?
-
- Test different versions of Mysql
- Test different mysql versions
- Test different mysql Parameter Combinations
Mysql Test Category
- CPU Bound-full-memory test. The test data is much smaller than the configured memory. This way, the test results are not affected because of the disk IO performance.
- IO Bound-the test data volume is much larger than the memory, which means a large amount of data is read and written from the disk IO;
4 sub-categories:
- Write Test
- Update Test
- Purity Test
- Hybrid mode (based on different services)
Common Test Tools
- Open-source mysql Performance Testing Tool
-
- Sysbench
- Tpcc-mysql
- Mysqlslap
- Write Performance testing tools for businesses
-
- Blogstores-a test tool based on the specific business of Netease blog
Performance testing metrics:
- Service Throughput
-
- TPS-Total number of transactions executed per second
- QPS-Total number of requests executed per second
- Service Response Time
- Service concurrency-the number of concurrent operations in the job, or the number of threads or connections in the job at the same time. For example, a web site "With 50000 users at the same time" may only have 10-15 concurrent requests to the mysql database. At this time, the number of concurrent requests is only 10-15.
- Scalability ------------- simply put, you can double the throughput by doubling the system's resources (for example, double the number of CPUs. Of course, the performance (Response Time) must also be within the acceptable range. Most systems cannot achieve such ideal linear expansion.
Test Method Common design Benchmark Testing errors:
- Use a subset of real data instead of a complete set.
- Does not match real user behavior.
- No check error. ----------- Check the error log when unexpected results are encountered during the test. The basic requirements are as follows.
- Ignore the system warm-up process-after the system is restarted, there is no data in the cache. In this case, the test is not in line with the actual situation. In fact, it is likely that there is already a lot of data in the cache.
- The test time is too short.
1 test plan:
- Record Test Data
- System configuration steps
- Steps for testing
- Analysis result
- Preheating Scheme
Parameters and results should be documented, and each round of testing must be documented in detail. 2. How long should the benchmark test run a simple test rule, that is, wait until the system looks stable at least equal to the system warm-up time. The benchmark test should be run long enough. If accurate and complete benchmarking is not completed, it will be a waste of time. Sometimes it is better to trust others' test results than to make a half-pull test to get a wrong conclusion. 3. data to be recorded for system performance and status retrieval includes system status and performance indicators:
- Cpu usage
- Disk I/O
- Network Traffic Statistics
- Show global status counters, etc.
Use scripts to collect the data. The default configuration based on mysql is meaningless, because the default configuration is based on extremely small applications that consume a small amount of memory. 4. It is a good solution to run the benchmark test and analyze the results of the automated benchmark test. It can be a makefile or a set of scripts. Use as much as possible to automate all testing processes, including data loading, system push, test execution, and result recording. . The importance of drawing 5 is measured multiple times. problems can be detected immediately through graphs, which are hard to be noticed in raw data. Collect as much detailed data as possible during the benchmark test, and then draw the data into a graph to help you quickly identify problems. Gnuplot or R plotting; Test Tool Sysbench
- Industry-renowned performance testing tools
- Allows you to test disks, CPUs, and databases.
- Supports multiple databases: Oracle, DB2, and MYSQL
- Download, compile, and install the SDK by yourself.
- Recommended version: sysbench0.5
Sysbench is used not only to test the database performance, but also to test the performance of the server running the database. We strongly recommend that you familiarize yourself with sysbench testing. This should be one of the most useful tools in the mysql user's toolkit.
- Sysbench cpu Benchmark Test
- Sysbench file I/O Benchmark Test
- Sysbench OLTP Benchmark
Other benchmark tests of sysbench are not directly related to database performance.
- Memory ----- test the continuous read/write performance of the memory
- Thread ----- test the performance of the thread scheduler.
- Mutex lock-test the performance of the mutex lock.
- Sequential write-test the performance of sequential write.
Tpcc-mysql
- TPC-C is a specification specifically designed for online transaction processing systems (OLTP systems)
- Tpcc-mysql is implemented by percona according to specifications
The TPCC process can better simulate online businesses using this test tool: You need to create data and table structures, load data, and perform the test in three steps. Benchmark () of mysql: You can test the execution speed of certain operations.
mysql> set @input := 'hello world';Query OK, 0 rows affected (0.00 sec) mysql> select benchmark(1000000,MD5(@input));+--------------------------------+| benchmark(1000000,MD5(@input)) |+--------------------------------+| 0 |+--------------------------------+1 row in set (1.45 sec) mysql> select benchmark(1000000,SHA1(@input));+---------------------------------+| benchmark(1000000,SHA1(@input)) |+---------------------------------+| 0 |+---------------------------------+1 row in set (1.40 sec)
Although the benchmark () function is easy to use, it is not suitable for real benchmarking, because the function simply returns the time when the server executes the expression and does not involve analysis and overhead, and other factors. In addition, the expression must include User-Defined variables (input) like in this example. Otherwise, the same expression will be executed multiple times and the result will be affected due to system cache hits. For more information about the test practices, see sysbench practices and tpcc-mysql practices.
- Four sub-categories: Write test, update test, purity test, hybrid mode
- Performance testing metrics:
-
- Service Throughput
-
- TPS-Total number of transactions executed per second
- QPS-Total number of requests executed per second
- Service Response Time
- Service concurrency
- Common design test errors:
-
- Use a data subset instead of a complete set,
- Does not match real user behavior,
- No check error,
- The system Preheating Process is ignored, and the test time is too short;
- Test Method
-
- Test Plan:
-
- Record test data,
- System configuration steps,
- Test procedure,
- Analysis results,
- Preheating scheme;
- Test Time: The test should be run long enough, at least equal to the system push time.
- Obtain the system performance and status: cpu, IO, network traffic, and mysql status counters;
- Run the test: the automated test includes: data loading, system push, execution test, and result recording.
- Drawing analysis: intuitive problem discovery;
- Test Tools: sysbench, tpcc-mysql, benchmark ()
- Test summary:
- IO Bound test data volume is much larger than memory, cpu Bound test data volume is smaller than memory
- It is recommended that the test time be greater than 60 minutes to reduce the error; there is a system push time;
- Sysbench is more inclined to test Mysql performance, and Tpcc is closer to business
- To run the test program, you must monitor the server load at the same time, and monitor various Mysql metrics.
This article permanently updates the link address:
