Optimization of the tidb_auto_analyze_ratio Parameter

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

Original topic: 关于tidb_auto_analyze_ratio参数的优化

| username: heiwandou

Regarding the tidb_auto_analyze_ratio parameter setting issue
In the official documentation


In the configuration page, the minimum value can be set to 0

However, in SQL 操作常见问题 | PingCAP 文档中心

In the fault diagnosis, fault scenario section, there is indeed the following configuration suggestion

I hope to optimize it and provide more reasonable parameter configuration suggestions.

| username: changpeng75 | Original post link

This parameter can only be continuously optimized based on the specific system conditions during use, ultimately arriving at a more reasonable configuration.

| username: Billmay表妹 | Original post link

That depends on the scenario. The default configuration is 0.5, which means that when more than 50% of the rows in the table are modified, it triggers an automatic ANALYZE update. As mentioned in the issue, because your analyze is not timely enough, you should lower this threshold a bit to make it trigger more effectively.

| username: Billmay表妹 | Original post link

If there are no issues, there’s no need to change that parameter.

| username: heiwandou | Original post link

I understand what these parameters mean. Let me check if my understanding is correct. In the first image, it means that the range of this parameter value is from 0 to 1. In the second image, the document says that the minimum value of this parameter is 0.3 and cannot be set to a value smaller than 0.3. In the third image, it is set to 0.2. Isn’t it a bit confusing in the same document?

| username: ShawnYan | Original post link

@heiwandou Is Figure 3 from this document?

| username: ShawnYan | Original post link

To be on the safe side, when enabling auto analyze, the minimum value of tidb_auto_analyze_ratio is 0.3.

Is this statement debatable?

The restriction of 0.3 was removed from TiDB v3.0.8 to make auto analyze more timely #14015

const (
	minAutoAnalyzeRatio = 0.3
)

Was the 0.3 restriction removed from TiDB v3.0.8?

| username: zhang_2023 | Original post link

:+1:

| username: zhaokede | Original post link

I haven’t studied it in depth and haven’t managed the value of this parameter. It seems there are no issues when using it in the test environment.

| username: WinterLiu | Original post link

If the table often has unstable execution plans, it might be better to regularly collect statistics using crontab.

| username: system | Original post link

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