SQL Statement Optimization

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

Original topic: sql语句优化

| username: yulei7633

May I ask, the time taken to generate the execution plan for this SQL is 1.1s. How should this be optimized?

The above screenshot was generated under high concurrency, but when the concurrency is not high, the statement runs very quickly:
The SQL statement is as follows:

SELECT
  count(1)
FROM
  xxxxxxxxxxxxx
WHERE
  form_id = 1022161
  AND STATUS = 1
  AND MASTER = TRUE
  AND fid = 97253
  AND (
    1 = 1
    AND (
      1 = 1
      AND string_1 = "s"
    )
    AND (
      1 = 1
      AND double_3 = "8"
    )
  );

The execution plan is as follows:

||id                          |task     |estRows|operator info                                                                                                                                                                                                                                                                                                                                               |actRows|execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |memory  |disk|
|---|---|---|---|---|---|---|---|---|
||StreamAgg_14                |root     |1      |funcs:count(1)->Column#2514                                                                                                                                                                                                                                                                                                                                 |1      |time:818.2ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |3.52 KB |N/A|
||└─IndexLookUp_66            |root     |0      |                                                                                                                                                                                                                                                                                                                                                            |2      |time:818.2ms, loops:2, index_task: {total_time: 338.7ms, fetch_handle: 338.7ms, build: 5.15µs, wait: 10.5µs}, table_task: {total_time: 562.7ms, num: 4, concurrency: 24}, next: {wait_index: 245.3ms, wait_table_lookup_build: 12.6ms, wait_table_lookup_resp: 547.9ms}                                                                                                                                                                                                                                                                                                                 |309.4 KB|N/A|
||  ├─IndexRangeScan_63(Build)|cop[tikv]|16.67  |table:xxxxxxxxxxx, partition:p81, index:idx_string_1(string_1), range:[s,s], keep order:false, stats:partial[idx_string_1:missing, idx_double_3:missing]                                                                                                                                                                                   |16375  |time:329.1ms, loops:19, cop_task: {num: 7, max: 65.9ms, min: 36.2ms, avg: 43.4ms, p95: 65.9ms, tot_proc: 4.49µs, tot_wait: 236.8µs, rpc_num: 7, rpc_time: 303.1ms, copr_cache_hit_ratio: 1.00, build_task_duration: 201.2µs, max_distsql_concurrency: 1}, tikv_task:{proc max:3ms, min:1ms, avg: 1.86ms, p80:2ms, p95:3ms, iters:43, tasks:7}, scan_detail: {get_snapshot_time: 56.5µs, rocksdb: {block: {}}}                                                                                                                                                                         |N/A     |N/A|
||  └─Selection_65(Probe)     |cop[tikv]|0      |eq(ffffffffffff.xxxxxxxxxxxx.double_3, 8), eq(ffffffffffffff.xxxxxxxxxxxxx.fid, 97253), eq(ffffffffffffffff.xxxxxxxxxxxxxxxxxxxxx.form_id, 1022161), eq(fffffffff.xxxxxxxxxxxxxx.master, 1), eq(ffffffffffff.xxxxxxxxxxxxxx.status, 1), eq(ffffffff.xxxxxxxxx.string_1, s)|2      |time:546.5ms, loops:6, cop_task: {num: 5, max: 315.4ms, min: 15.6ms, avg: 81.6ms, p95: 315.4ms, max_proc_keys: 7607, p95_proc_keys: 7607, tot_proc: 28.4ms, tot_wait: 1.58ms, rpc_num: 5, rpc_time: 407.4ms, copr_cache_hit_ratio: 0.80, build_task_duration: 307µs, max_distsql_concurrency: 2}, tikv_task:{proc max:420ms, min:10ms, avg: 141.4ms, p80:420ms, p95:420ms, iters:39, tasks:5}, scan_detail: {total_process_keys: 7607, total_process_keys_size: 882412, total_keys: 8364, get_snapshot_time: 56.4µs, rocksdb: {key_skipped_count: 7607, block: {cache_hit_count: 8356}}}|N/A     |N/A|
||    └─TableRowIDScan_64     |cop[tikv]|16.67  |table:xxxxxxxxxxxx, partition:p81, keep order:false, stats:partial[idx_string_1:missing, idx_double_3:missing]                                                                                                                                                                                                                                  |16375  |tikv_task:{proc max:420ms, min:9ms, avg: 140.6ms, p80:420ms, p95:420ms, iters:39, tasks:5}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |N/A     |N/A|
| username: h5n1 | Original post link

Could you provide the table creation statement? What is the CPU utilization of TiDB when concurrency is high?

| username: yulei7633 | Original post link

The create table statement cannot be provided. There are too many fields, approximately 2500 fields? The CPU utilization is shown in the image below:

| username: h5n1 | Original post link

It feels like waiting for the CPU.

| username: yulei7633 | Original post link

Do you feel that the slow response is caused by the CPU?

| username: tidb菜鸟一只 | Original post link

tidb_session_plan_cache_size and [tidb_plan_cache_max_plan_size]

Check the settings for these two parameters. If your SQL is very large, it is recommended to set the latter parameter a bit larger as well. It might be that the execution plan is too large to be cached…

| username: Jasper | Original post link

Generating an execution plan is primarily a CPU operation. The slowness could be due to a long SQL/execution plan or the corresponding TiDB server’s CPU being fully utilized. From the provided information, the SQL seems relatively simple, so it’s most likely due to high TiDB CPU usage. You can check the Grafana monitoring for Executor - parse duration and compile duration to see if they have increased during that time.

| username: zhanggame1 | Original post link

A table with 2500 columns?

| username: yulei7633 | Original post link

I’ll reply tomorrow; the machine is shut down and the CPU is isolated.

| username: yulei7633 | Original post link

I will reply tomorrow. The machine is shut down and the CPU is isolated.

| username: yulei7633 | Original post link

Yes, there is such a requirement in the business aspect.

| username: Fly-bird | Original post link

How is the cluster configuration?

| username: 像风一样的男子 | Original post link

You should monitor if there are many similar slow queries when the CPU is high.

| username: yulei7633 | Original post link

mysql> show variables like ‘%tidb_session_plan_cache_size%’;
±-----------------------------±------+
| Variable_name | Value |
±-----------------------------±------+
| tidb_session_plan_cache_size | 100 |
±-----------------------------±------+

mysql> show variables like ‘%tidb_plan_cache_max_plan_size%’;
±------------------------------±--------+
| Variable_name | Value |
±------------------------------±--------+
| tidb_plan_cache_max_plan_size | 2097152 |
±------------------------------±--------+

| username: yulei7633 | Original post link

Executor- parse duration and compile duration, in which module can these two monitors be viewed? I’ve been looking for a long time and haven’t found them.

| username: yulei7633 | Original post link

Yes. During the stress test, there were quite a few similar slow queries in this time period.

| username: 像风一样的男子 | Original post link

Resources have reached a bottleneck.

| username: yulei7633 | Original post link

The cluster’s configuration performance is quite good, with NVMe drives, 64GB of memory, 24 CPU cores, and the servers are also the latest models from Dell.

| username: h5n1 | Original post link

How was it deployed? Was NUMA configured? Can you check the CPU on the overview page?

| username: yulei7633 | Original post link

TiUP has been deployed.
2 ==> TiUP deployment node
3, 4, 5 ==> PD and TiDB-server shared nodes
12, 13, 14 ==> TiKV nodes
2, 11 ==> TiFlash nodes

These are virtual machines created on physical machines, with all resources in a dedicated state, and not deployed using numactl.