Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: TiDB readpool相关配置的最佳实践
|
username: residentevil
Is there a best practice for readpool-related configuration in TiDB?
|
username: residentevil
|
username: 
|
username: 
|
username: The official documentation does not provide a detailed explanation of the readpool.coprocessor parameter.
Both of the links I posted contain information about adjustments to the readpool, you can refer to them.
After multiple adjustments, I found that the performance of the readpool has always been a bottleneck. Let me analyze it from the SQL perspective.
Sure, you can start a new thread with the specific SQL and execution plan, and we can analyze it together. 
|
username: - Setting the size of the readpool
The principle of setting the readpool size is to determine the number and size of TiDB nodes based on the server’s CPU, disk, and storage engine capabilities. Generally, one TiDB node can handle read requests from about 10 TiKV instances. Therefore, when increasing TiKV instances, the number and size of TiDB nodes should be increased accordingly.
- Dynamic adjustment of readpool size
TiDB supports dynamic adjustment of the readpool size, so this feature should be utilized as much as possible to modify according to changes in traffic or concurrency performance requirements. For example, if the readpool size is found to be too small, the system’s throughput and performance can be increased by dynamically adjusting the readpool size.
- Separating read requests into different readpools
Standard illustration
TiDB recommends separating read requests into multiple readpools and assigning them to different readpools based on operation type or user account, etc. For example, hotspot queries can be assigned to a dedicated readpool, while other types of queries can be assigned to another general readpool. This can avoid hotspot queries affecting the execution efficiency of other types of queries in some cases.
- Limiting scan-type operations
TiDB recommends setting appropriate limits for scan-type operations to avoid negative impacts on system performance and throughput. For example, results can be limited within a certain range through limit or topn. Additionally, if there are large-scale sorting or memory-intensive operations, options should be reasonably set to control their execution speed and consumption.
- Using TiDB Dashboard to monitor system resources
TiDB provides a graphical Dashboard application to help administrators monitor system performance and resource usage in a visual interface. Using TiDB Dashboard, you can intuitively understand the usage of system resources, including the usage and utilization of various readpools. By monitoring these metrics, problems can be quickly located and corresponding adjustments can be made.