Performance Tuning Overview

This document introduces the performance tuning ideas in YMatrix, which can cover server system resource tuning, database resource tuning, and SQL tuning.

1 Why do you need to perform performance tuning?

Performance tuning is a very comprehensive and cannot be ignored.

There are generally two reasons for performance tuning:

The performance is much lower than normal, and performance bottlenecks need to be solved;
**A SQL statement has normal performance but fails to meet the needs of the target business scenario, so further optimization and improvement are needed.

One premise:

Without affecting the correct operation of the database management system.

2 How to effectively optimize performance?

![](https://img.ymatrix.cn/ymatrix_home/performance tuning process (screenshot)_1693817912.png)

2.1 Describe the problem

Describe current performance bottlenecks or performance requirements based on performance monitoring data and other requirements information.

A good problem description should cover the context and clearly describe the problem phenomenon, and should not be too long, usually including but not limited to the following information:

Database Version
The component that has a problem (or global database problem)
Quantitative problem phenomenon

A better description, for example: using the writing tool that comes with the graphical interface of YMatrix 5.0, the number of 280W is entered. After 30 tests, the delay increased from 30 seconds to 1 minute, and the performance dropped by nearly 1 times.

A poor description for example: using a graphical interface to generate test data is getting slower and slower.

2.2 Defining the target

Different business scenarios have different optimization goals. For example, for a timing scenario of the Internet of Vehicles, there may be the following optimization goals: improve the performance of aggregation queries and associated queries involved in detailed queries. For a financial core OLTP system, the optimization goal may be: reduce the long-tail delay of transactions, etc.

A good definition of optimization goals should be quantitative, for example:

From 9am to 10am during peak business hours, the p50 delay for transfer transactions requires less than 200ms.
The real-time response of user board 1 needs to be controlled within 10 seconds.

Instead:

The system is too slow and has no response and needs optimization.
Real-time response of user kanbans is needed.

2.3 Collect information

YMatrix recommends collecting but not limited to the following information for analysis:

Server resource information:
- CPU Usage
- Memory usage rate
- Disk I/O
- Network speed
Software environment information:
- Operating system version
- YMatrix version
Cluster information:
- Cluster deployment topology
Database information (no full collection is required, please decide based on actual conditions):
- Table structure
- Related log
- Slow query
- Query plan
- Save the on-site environment

2.4 Analyze the cause/positioning performance bottlenecks

Based on the collected information, you can just locate or infer the performance bottleneck, and it may take multiple times to collect relevant information to finally locate the problem. For detailed ideas, see Performance Tuning.

Notes!
Communication and collaboration are one of the tricks to quickly analyze the causes.

2.5 Propose a solution

After determining the performance bottleneck through analysis, an optimization plan with low cost, low risk and maximum benefits is proposed based on actual conditions.

It should be noted that even if a solution has the greatest potential benefits to optimize the maximum bottleneck point, the risks and costs of the solution need to be evaluated simultaneously. Typically, the tuning sequence recommended by YMatrix is as follows: Cost/risk from low to high:

Hardware The effect of hardware updates can be quickly determined through [Configuration Test] (https://baike.baidu.com/item/Configuration Test/9906249?fr=ge_ala) and [Benchmark Test] (https://www.ibm.com/docs/zh/db2/11.1?topic=methodology-benchmark-testing).
Query SQL statements Use the ANALYZE statement to re-collect statistics in this table; or modify the SQL statement through analysis of the query plan to reduce logical complexity and cost overhead.
System configuration parameters Performance improvements are achieved by adjusting the system configuration parameters related to this query. It should be noted that if the configuration parameter to be modified is System, you must carefully analyze whether there will be adverse global impact after the modification.
Data Model When the above methods cannot complete performance tuning, you need to consider making certain adjustments to the table building DDL statement. Since the impact and risks of adjusting the table structure are large, please do not take this method unless you have to.

Notes!
In order to avoid irreparable performance bottlenecks during business operation, please pay more attention to the initial design and scientifically plan, analyze and test the data model of this scenario to ensure its correctness.

2.6 Implementation of tuning

The process of implementing tuning usually requires but is not limited to performing the following work:

Conduct multiple proposal discussions and record each discussion in detail
After many iterations and modifications, a detailed and complete tuning plan is finally completed
Try to restore the test environment to verify and return the changed content in a complete way to avoid risks in formal changes
Implement changes to the production system and record every detail of the change in detail

2.7 Evaluation results

Generally, performance tuning is not a process that can be completed in one go. For the same performance problem, it may take multiple cycles of steps 2.3 - 2.7 to finally get satisfactory results.

Therefore, it is necessary to evaluate the tuning results intuitively and take the following actions:

Achieve the tuning goals, deliver the project, and arrange a tuning review meeting. In order to cope with business growth, you may need to further improve the capacity planning of the system.
The tuning target is not achieved, repeat steps 2.3 - 2.7 until the target is achieved.