DBAkit Expert, I have a question: TiDB often encounters slow SQL with KV at 100%. How do you prevent this when handling big data?

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

Original topic: dbakit 大佬 问个问题 tidb很容易慢sql kv 100% 你做大数据的时候怎么防止的?

| username: tidb狂热爱好者

[TiDB Usage Environment] Production Environment / Testing / POC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots / Logs / Monitoring]
A single SQL query executed 1000 times per second without using an index, causing a severe performance issue.

| username: jaybing926 | Original post link

In our environment, there aren’t that many queries, still 100%.

| username: 大鱼海棠 | Original post link

What is the reason for not using the index, outdated statistics? If it is a known high-frequency SQL, directly bind the execution plan.

| username: xingzhenxiang | Original post link

How about adding TiFlash and giving it a try?

| username: WalterWj | Original post link

If the computational capacity exceeds the cluster itself, then expand the capacity. Alternatively, optimize the SQL.

If you want isolation, then wait for the 7.x multi-tenancy.

| username: dba-kit | Original post link

The fundamental solution is to wait for Resource Control GA to set QoS for each user. For historical versions, all solutions can only alleviate or avoid the issue.

Currently, you can do the following, for example:

  1. Development: Conduct thorough stress testing to catch slow queries during testing.
  2. DBA: In the config, set priorities for different tidb-servers to prevent OLAP queries from affecting high-priority SQL.
| username: tidb菜鸟一只 | Original post link

Monitoring, issue alerts directly, upon discovering an alert, quickly terminate all problematic sessions.

| username: xingzhenxiang | Original post link

Monitor SQL by time and memory dimensions, terminate it if it exceeds the standard, and then optimize it.

| username: TiDBer_pkQ5q1l0 | Original post link

Be more aggressive, set a threshold, and kill any select queries that exceed this threshold.