TiDB in Index Recommendation and Database SQL Request Throttling

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TIDB在索引推荐、数据库SQL请求限流

| username: Mark

Demand Feedback
Please clearly and accurately describe the problem scenario, required behavior, and background information to facilitate timely follow-up by the product team.
[Problem Scenario Involved in the Demand]

  1. In actual production use, maintenance & development personnel often add indexes to business tables indiscriminately, resulting in counterproductive performance. We hope TiDB can provide recommended indexes through internal mechanisms to improve efficiency for low-profile SQL.

  2. Under normal circumstances, sudden business traffic cannot be flow-controlled at the database layer, leading to a large number of business requests hitting the underlying database, including situations like cache or message accumulation.

  3. TiDB server for load balancing & high availability.

[Expected Required Behavior]
1. Index recommendation feature.
2. SQL flow control feature.
3. TiDB server for load balancing & high availability.

[Alternative Solutions]
1. None at the moment.
2. Currently doing flow control at the business layer.
3. Replace with open-source components.

2
| username: 数据小黑 | Original post link

Let me share my views:

  1. Index recommendation is indeed a very good feature, but it requires a lot of groundwork. If I recall correctly, it was mentioned in this year’s official presentations, so we can look forward to it.
  2. The database actually cannot identify what is the correct traffic and what traffic should be blocked. TiDB itself has some flow control mechanisms, but when flow control is triggered, it is always a scenario that needs attention and does not serve to flatten peaks. The caching and message accumulation mentioned in the post are both good means of flattening peaks, provided that the cache penetration issue is handled well. Additionally, message accumulation is not a “problem” that needs to be “fixed”; message queues are designed for this purpose.
  3. There are now many load balancing solutions. MySQL’s jar can achieve this through configuration, as can Nginx and HAProxy, so it seems unnecessary to reinvent the wheel.
3
| username: 边城元元 | Original post link

Some views on the SQL flow control function: This needs to be determined based on the business scenario. You can look forward to implementing flow control for a specific tidb-server, which can throttle or cancel operations to prevent OOM or timeouts.

4
| username: Mark | Original post link

The DB layer mainly handles SQL traffic, especially high-concurrency queries. When the business peaks or there are issues with the front-end cache or messages, the backend DB cannot impose any restrictions, which may result in OOM (Out of Memory) or the risk of load spikes approaching the limit.

5
| username: Mark | Original post link

The starting point is to hope that TiDB’s official team can eliminate the need for users to consider such architectural issues themselves. One-click delivery and use would reduce maintenance and management costs, thereby shifting the focus of TiDB product usage directly to the development side for users.