ANALYZE TABLE Impact on Business Queries

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

Original topic: ANALYZE TABLE 影响业务查询

| username: 大飞飞呀

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed to cause the issue
In the production environment, analyze table is performed daily from 16:00 to 4:00 the next day.
[Encountered Issue: Problem Phenomenon and Impact]
During the analyze period, normal queries occasionally become very slow, from milliseconds to 11 seconds.
For example:

SELECT * FROM view_a WHERE (I_MEMBER_ID="28378268392743992") ORDER BY I_ID DESC LIMIT 0,1

Execution Plan:

    id                                  task        estRows operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       actRows execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  memory  disk
Projection_11                       root        1       Column#27, Column#4, Column#26, Column#24, Column#25                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                1       time:11.7s, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            10.5 KB N/A
└─Projection_12                     root        1       0->Column#27, stat.dwd_gs_bill_app_user_detail.i_member_id, Column#24, Column#25, Column#26                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      1       time:11.7s, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            10.1 KB N/A
  └─Limit_15                        root        1       offset:0, count:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   1       time:11.7s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             N/A     N/A
    └─HashAgg_16                    root        1       group by:stat.dwd_gs_bill_app_user_detail.i_member_id, funcs:group_concat(distinct stat.dwd_gs_bill_app_user_detail.ch_pay_channel order by stat.dwd_gs_bill_app_user_detail.ch_pay_channel desc separator ",")->Column#24, funcs:group_concat(distinct stat.dwd_gs_bill_app_user_detail.ch_dealer_name order by stat.dwd_gs_bill_app_user_detail.ch_dealer_name desc separator ",")->Column#25, funcs:group_concat(distinct stat.dwd_gs_bill_app_user_detail.ch_income_mode order by stat.dwd_gs_bill_app_user_detail.ch_income_mode desc separator ",")->Column#26, funcs:firstrow(stat.dwd_gs_bill_app_user_detail.i_member_id)->stat.dwd_gs_bill_app_user_detail.i_member_id    1       time:11.7s, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             20.3 KB 0 Bytes
      └─IndexLookUp_23              root        17.03                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       175     time:11.7s, loops:2, index_task: {total_time: 851.4µs, fetch_handle: 845.3µs, build: 696ns, wait: 5.49µs}, table_task: {total_time: 11.7s, num: 1, concurrency: 8}, next: {wait_index: 942.3µs, wait_table_lookup_build: 820.2µs, wait_table_lookup_resp: 11.7s}                                                                                                                                                                                                                                                                                                                                                                                                            28.4 KB N/A
        ├─IndexRangeScan_21(Build)  cop[tikv]   17.03   table:dwd_gs_bill_app_user_detail, index:IDX_I_MEMBER_ID(I_MEMBER_ID), range:[28378268392743992, 28378268392743992], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              175     time:829.1µs, loops:3, cop_task: {num: 1, max: 766.1µs, proc_keys: 175, tot_proc: 242.1µs, tot_wait: 43.8µs, rpc_num: 1, rpc_time: 747µs, copr_cache_hit_ratio: 0.00, build_task_duration: 21.8µs, max_distsql_concurrency: 1}, tikv_task:{time:0s, loops:3}, scan_detail: {total_process_keys: 175, total_process_keys_size: 8050, total_keys: 176, get_snapshot_time: 16.6µs, rocksdb: {key_skipped_count: 175, block: {cache_hit_count: 13}}}                                                                                                                                                                                                                        N/A     N/A
        └─TableRowIDScan_22(Probe)  cop[tikv]   17.03   table:dwd_gs_bill_app_user_detail, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 175     time:11.7s, loops:2, cop_task: {num: 51, max: 11.7s, min: 0s, avg: 459.7ms, p95: 10ms, max_proc_keys: 14, p95_proc_keys: 10, tot_proc: 115.3ms, tot_wait: 57.4ms, rpc_num: 22, rpc_time: 23.4s, copr_cache_hit_ratio: 0.00, build_task_duration: 702.4µs, max_distsql_concurrency: 1, max_extra_concurrency: 9, store_batch_num: 29}, tikv_task:{proc max:11.7s, min:0s, avg: 461.2ms, p80:5ms, p95:14ms, iters:51, tasks:51}, scan_detail: {total_process_keys: 175, total_process_keys_size: 63376, total_keys: 181, get_snapshot_time: 848.1µs, rocksdb: {key_skipped_count: 18, block: {cache_hit_count: 2280, read_count: 323, read_byte: 2.88 MB, read_time: 69.7ms}}}    N/A     N/A

[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: TiDBer_小阿飞 | Original post link

Why is table analysis at a fixed time?

| username: Billmay表妹 | Original post link

The problem you mentioned is likely due to the fact that the default value of the tidb_distsql_scan_concurrency parameter is 15. When the concurrency is too high, it can lead to excessive memory usage. You can try reducing the value of this parameter to see if it alleviates the issue.

| username: Billmay表妹 | Original post link

You can consider the following points:

  1. Adjust the execution time of analyze table: If possible, try to adjust the execution time of analyze table to off-peak business hours to reduce the impact on normal queries. For example, change the execution time from 4 PM to early morning.
  2. Adjust TiDB and TiKV configuration parameters: Based on the actual situation, appropriately adjust the configuration parameters of TiDB and TiKV to balance resource allocation between analyze table and normal queries. Pay attention to parameters related to concurrent execution and resource scheduling, such as tidb_build_stats_concurrency and tikv_gc_concurrency.
  3. Use sampled statistics: TiDB provides a feature for sampled statistics, which can be used to collect statistics using sampled data to reduce resource consumption. You can refer to the relevant content in the official TiDB documentation [1].
  4. Regularly update statistics: In addition to the daily analyze table operation, you can also consider regularly updating statistics to maintain their accuracy. You can use the ANALYZE TABLE command to manually update statistics or use TiDB’s automatic statistics collection feature.

Finally, if the problem persists, it is recommended to collect performance monitoring data from the TiDB cluster, including monitoring metrics for TiDB, TiKV, and PD, to conduct a deeper analysis and identify performance bottlenecks.

| username: TiDB_C罗 | Original post link

Is it possible that during this period, although the statistical information is very old and has not yet met the requirements for analyze table, the request still selected the index?

| username: 芮芮是产品 | Original post link

From the basic SQL information, it can be seen that the current SQL is using pseudo statistics, and pseudo represents inaccurate statistics, which may lead to TiDB making incorrect execution plan choices based on cost.

Solution 2: Modify the parameter: pseudo-estimate-ratio

Solution 3: Modify the parameter: tidb_enable_pseudo_for_outdated_stats to off

Based on your description, the slow query occurred while the table statistics were being updated. You can check whether the execution plan contains the keyword pseudo in the detailed panel of the current execution plan. If it does, you can use the methods described in this article to modify it, which will solve your slow query problem.

This parameter represents the ratio of modified rows to the total number of rows in the table. When this ratio exceeds a certain value, the system will consider the statistics to be outdated and will use pseudo statistics. The default value of this parameter is 0.8, with a minimum value of 0 and a maximum value of 1. It is the criterion for determining whether the statistics are outdated.

You can adjust this parameter to 1, so that TiKV does not choose pseudo statistics when executing SQL.

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

What evidence do you have to prove that these slow queries are caused by analyze? The automatically executed analyze runs in a single thread and has very little impact on data performance.

| username: heiwandou | Original post link

There is a problem with the ANALYZE TABLE update strategy. If the table updates quickly, the statistics will be inaccurate before the next automatic update.

| username: buddyyuan | Original post link

Check the TiKV monitoring to see if a unified thread pool on a certain node is full.

| username: Hacker007 | Original post link

You can modify the execution time to run it at midnight.

| username: Z六月星星 | Original post link

ANALYZE TABLE should not affect the entire query. Check the slow SQL during this period for analysis.

| username: zhanggame1 | Original post link

Suddenly, the SQL execution became slow. Check if there was an issue with the execution plan at that time or if there were other problems, such as locks.

| username: gcworkerishungry | Original post link

What are the expectations?

| username: Kongdom | Original post link

It is recommended to execute ANALYZE TABLE during off-peak business hours.