When auto_analyze is executed, it may impact business operations. Are there any best practice configurations for production environments?

Translated
1

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

Original topic: auto_analyze执行的时候，会对业务造成影响，生产环境上，有没有比较好的实践配置

| username: 朱振文啊

[TiDB Usage Environment] Production Environment
[TiDB Version] v5.4
[Reproduction Path] Trigger auto analyze
[Encountered Problem: Phenomenon and Impact]
DML blocking, slow query and insertion, causing the business system’s database connection pool to be full and slow response.
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

image
image1380×386 47 KB

image
image1080×592 43.6 KB

2
| username: WalterWj | Original post link
  1. Adjust the phone’s statistics time to perform auto analyze during non-business hours.
  2. Alternatively, disable auto analyze and write a script to collect data manually.
3
| username: 朱振文啊 | Original post link

After this issue occurs, the execution plan of the SQL shows stats:pseudo. Is it necessary to manually collect statistics for all tables to resolve it?

4
| username: WalterWj | Original post link

This means that the statistical information is inaccurate.

Sure, you can analyze it during non-peak business hours.

5
| username: tidb狂热爱好者 | Original post link

There is a parameter that can be turned off.

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

I think we can enable fast statistics collection with tidb_enable_fast_analyze and increase the data modification threshold tidb_auto_analyze_ratio before collecting statistics. Additionally, for those large tables, we can deploy manual tasks (during non-business hours, with small sample collection).

7
| username: gcworkerishungry | Original post link

I encountered a similar issue with version V5.*. Has version V6 alleviated this problem? For example, is the analyze process faster and less costly?

8
| username: WalterWj | Original post link

Then just use fast analyze, but this thing isn’t that accurate.

9
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.