Performance Tuning Objectives
The ultimate goal of database system performance tuning is to fully utilize server hardware and software resources, enabling database software to deliver efficient data services. Specific metrics include:
QPS/TPS (Queries Per Second/Transactions Per Second): measure the system's request processing capability.
RT (Response Time): refers to the time required for a request to be processed from initiation to completion.
Improving QPS/TPS can fully utilize single-server resources, enhance cost-effectiveness, and reduce the Total Cost of Ownership (TCO). Reducing RT enhances user experience while further boosting the system's processing capability.
Performance Tuning Steps
Performance tuning is typically divided into three key steps:
1. Determine optimization direction: Analyze the current system's load characteristics and business requirements to clarify whether the primary focus is on increasing throughput or reducing latency.
2. Identify bottlenecks: Through monitoring tools and execution plan analysis, identify performance bottlenecks in the system, such as high CPU utilization, memory shortage, network latency, or hotspot row contention.
3. Develop optimization solutions: Take corresponding measures based on the causes of bottlenecks, such as adjusting parameter configurations, optimizing SQL execution paths, or modifying data distribution policies.
System-Level Performance Tuning
System tuning focuses on the operational efficiency of the entire system rather than the performance of individual SQL. The primary approaches include:
Comprehensively analyzing the execution plans of multiple SQL statements and system load characteristics;
Focus on global issues, such as:
hotspot row contention
Buffer Cache hit ratio
Reasonableness of partition table design
Optimize by adjusting access paths, execution order, logical restructuring, and other methods;
When the number of partitions is large, consider increasing query parallelism for higher performance, but requires careful consideration of resource consumption.