Database optimization-Benchmark test (i)

Purpose of Benchmark testing

Benchmark tests 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 of the system test plan

• 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 are the benchmark tests?

• Full test
  
  • Test all the infrastructure, Webserver, application, cache layer, database, network, external resources, etc.
  • Such a method provides a complete picture, but is very difficult to achieve;
  • The results are complex and difficult to understand. may be misleading.

• Individual components
  
  • Only a single component of the benchmark test infrastructure;
  • Easy implementation. But the real bottleneck may be overlooked.
  • Results easy to understand.

The baseline test for a single component requires consideration of OS configuration, hardware, DB system, parameters, Schema, queries, and workloads.

Benchmark Test Plan guide

1. The target before the baseline is defined.
2. Understand the workload that can be generated repeatedly;
3. Record all the information, even if it may not be practical. Contains hardware information, configuration, version number, table size, etc.;
4. Create a baseline. Each benchmark. Are the baselines of the future.
5. Define the target after the benchmark test;
6. Change only one configuration at a time and return the baseline results.

Type of benchmark test

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

The purpose of the performance test

• Must be planned before the benchmark test;
• Verify that the system meets the prescribed standards;
• Compare two systems;
• Isolate the component 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 measured value that is widely used, which refers to the number of successful transactions in units of time (seconds or minutes);
• Usually focus on averages only;
• Widely used in the market comparison;
• It is very important to test the pressure.

Delay

• The total time required to complete a firm;
• Min/avg/MAX response time.
• The load test is very important.

Stability in the face of scaling

• The performance of the measurement system when it is scaled.
• Identify weaknesses before they become serious problems;
• Very practical for capacity planning;
• Often used for durability testing.

Stability in the face of concurrency

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

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

Database optimization-Benchmark test (i)