Database optimization-Benchmark test (i)

Purpose of Benchmark testing

Benchmarks are designed to identify bottlenecks in the system, including:

• Hardware
  disk, memory, network, and so on.
• Operating system
  File system, memory management, driver, scheduling, etc.
• Rdbms
  SQL layer, storage engine layer.
• Schema design
  Index, table structure, data type.
• Query
  Query write bad, logical error.
• Application issues
• Interaction between parts of the system
  Disk IO, RAM, RDBMS, and so on.
• Measured value
  How to measure? Where do you spend the most time? Which component is busiest?
• Find the cause of the bottleneck

Requirements for system test plans

• Choose the right hardware and software;
• Understand the behavior of the system;
• Understand the performance of the system;
• Make the correct tuning and configuration for the system;
• Identify the advantages and disadvantages of the system;
• The weak point of the positioning system;
• Plan for future growth;
• Plan the special events that may occur;
• Understand the scalability of the system;

What benchmarks should be tested?

• Complete test
  
  • Test all infrastructure, Web server, application, cache layer, database, network, external resources, etc.
  • This approach provides a complete picture, but is very difficult to achieve;
  • The results are complex and difficult to understand and may be misleading.
• Individual components
  
  • Only a single component of the benchmark infrastructure;
  • Easy to implement, but may ignore the real bottleneck;
  • The results are easy to understand.

The comparison of benchmarks for individual components requires consideration of OS configuration, hardware, DB systems, parameters, schemas, queries, and workloads.

Benchmark Test Plan guide

1. Define the target before the baseline;
2. Understand the workload that can be generated repeatedly;
3. Record all information, even may not seem to be used, including hardware information, configuration, version, table size, etc.;
4. Create baselines, each of which is a baseline for the future;
5. Define the target after the benchmark test;
6. Modifies only one configuration at a time and returns the baseline results.

Types of benchmark tests

• Performance test Type
  
  • Pressure test
  • Load test
  • Endurance test
  • Peak Test
• Pressure test
  Test the limits of the system, including
  
  • Using Ultimate Load
  • Define Upper Limit
  • Define Capacity
• Load test
  Test the load that the system can withstand, including:
  
  • Number of users
  • Number of transactions
  • Database size
• Endurance test
  Similar to load testing, simulations can be sustained over a long period of time to test the stability of the system.
• Peak Test
  
  • A sudden change in the test workload;
  • Defines the behavior of the system in the current environment. For example:
    
    • Processing spikes without affecting the system;
    • performance will be reduced;
    • The system will stop/fail/crash.

Purpose of performance testing

• Must be planned before the benchmark test;
• Verify that the system meets the prescribed standards;
• Compare two systems;
• Isolating the components of the system's neutral performance;
• High throughput;
• Low latency;
• Stability in the face of stretching;
• Stability in the face of concurrency.

Throughput

• is a widely used measurement that refers to the number of successful transactions in units of time (seconds or minutes);
• Usually just focus on the average;
• Widely used in the market comparison;
• is important for stress testing.

Delay

• The total time required to complete a transaction;
• Minimum/average/maximum response time;
• is important for load testing.

Stability in the face of scaling

• Measure the performance of the system when scaling;
• Identify weaknesses before they become serious problems;
• Useful for capacity planning;
• Often used for durability testing.

Stability in the face of concurrency

• Measure the performance of the system when the number of threads or the number of connections changes;
• Used to find the upper limit of the number of threads, and introduce the queue;
• Often used for peak testing.

Reference: "Understanding performance through measurement, benchmarking and Profiling"

Database optimization-Benchmark test (i)