The Execution Logic of `ANALYZE TABLE`

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

Original topic: 获取analyze table 的执行逻辑

| username: TiDBer_明明

[Test Environment] TiDB
[TiDB Version] TiDB 4.0.9
[Reproduction Path] The table has a total of 338 rows, analyze table sbtest_338;
[Encountered Problem: Phenomenon and Impact]


It is mentioned on the official website: 统计信息简介 | PingCAP 归档文档站 but I cannot understand its specific execution logic.

Executing the ANALYZE TABLE statement in TiDB takes longer than in MySQL or InnoDB. InnoDB only samples a small number of pages, but TiDB completely reconstructs a series of statistics. Scripts suitable for MySQL mistakenly assume that executing ANALYZE TABLE takes a short time.

I want to know the approximate execution logic & process of analyze table in TiDB, but I couldn’t find relevant information.
[Resource Configuration]
This is unrelated to resources
[Attachments: Screenshots/Logs/Monitoring]

| username: TiDBer_明明 | Original post link

The issue originated from an online table with 338 rows, where the analyze table operation took 22 minutes. The tidb_enable_fast_analyze option was already enabled, and the table had no significant changes. Therefore, I took this opportunity to understand its execution process.

  1. Multiple analyze table operations were executed simultaneously.
  2. Resource information of the server at that time:
    Image

| username: Kongdom | Original post link

You can check here:

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

This is definitely not normal. Create an identical table, insert 338 rows of data, manually analyze it, and check the time. See if it’s an issue with the table or with the analyze operation.

| username: TiDBer_明明 | Original post link

It is indeed abnormal, but without knowing its execution logic, I don’t know how to troubleshoot it.

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

Create an identical table, insert 338 rows of data, manually analyze it, and check the time to see if it’s an issue with the table or with the analyze process.

| username: TiDBer_明明 | Original post link

Manually, in version 4.0.9, it takes about 2 seconds. Although it’s still relatively long.

| username: TiDBer_明明 | Original post link

This only emphasizes the difference between TiDB and MySQL in executing analyze table, but does not provide the execution logic.

| username: TiDBer_明明 | Original post link

Unable to analyze the reasons for the slowness.

| username: Kongdom | Original post link

What about this column article?

https://asktug.com/t/topic/37691

| username: TiDBer_明明 | Original post link

It tells me:
Oops! The page does not exist or is a private page.

| username: Kongdom | Original post link

What about this link? 专栏 - TiDB统计信息原理简介与实践 | TiDB 社区

Here are two links from the column:

| username: TiDBer_明明 | Original post link

I have also seen it…

| username: Kongdom | Original post link

:thinking: Further progress really requires looking at the source code.

| username: TiDBer_明明 | Original post link

You won’t really give up from the start, right? :upside_down_face:

| username: xingzhenxiang | Original post link

Should we delete the database and run away? :grinning:

| username: system | Original post link

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