Best Practices for TiDB Readpool Configuration

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: Kongdom | Original post link

Like this?

| username: residentevil | Original post link

Similar to this kind

| username: heiwandou | Original post link

Great post, waiting for the update.

| username: Fly-bird | Original post link

Introduction to Key Parameters for Hybrid Deployment

| username: residentevil | Original post link

This is way too simple.

| username: residentevil | Original post link

The official documentation does not provide a detailed explanation of the readpool.coprocessor parameter.

| username: Kongdom | Original post link

:yum: Both of the links I posted contain information about adjustments to the readpool, you can refer to them.

| username: residentevil | Original post link

After multiple adjustments, I found that the performance of the readpool has always been a bottleneck. Let me analyze it from the SQL perspective.

| username: Kongdom | Original post link

Sure, you can start a new thread with the specific SQL and execution plan, and we can analyze it together. :handshake:

| username: swino | Original post link

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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.