How to Stop a Running Table Analysis Job

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

Original topic: 如何关闭running状态的表分析job

| username: 扬仔_tidb

【TiDB Usage Environment】Production Environment
【TiDB Version】v6.5.5
【Reproduction Path】What operations were performed that caused the issue

The cluster manually configured table analysis on a schedule. Due to excessive sampling, it is now stuck in the running state. How can I disable this scheduled analysis?

I found that using kill process_id doesn’t work.
select id, process_id, update_time, start_time, end_time, state, table_schema from mysql.analyze_jobs where table_name=‘xxx’ order by update_time desc;
【Attachments: Screenshots/Logs/Monitoring】

| username: dba远航 | Original post link

Look at the picture

| username: 扬仔_tidb | Original post link

My current cluster doesn’t have the time set, and the threshold is also the default 0.5. These jobs piling up in the cluster are scheduled tasks I configured in November. They are all in the running state now. How can I stop these running table analysis jobs?

| username: h5n1 | Original post link

The image is not visible. Please provide the text you need translated.

| username: 扬仔_tidb | Original post link

show analyze status where state=‘running’;
Listing the process_id as above, why can’t it be killed?

| username: forever | Original post link

On the tidb-server instance where the collection script is executed?

| username: 扬仔_tidb | Original post link

At that time, the connection was made using the load IP. Now, manually killing the process_id or killing the tidb process_id on both servers doesn’t work.

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

Check your TiDB version.

| username: 扬仔_tidb | Original post link

±-------------------+
| version() |
±-------------------+
| 5.7.25-TiDB-v6.5.5 |
±-------------------+

| username: 扬仔_tidb | Original post link

Is it possible that this is a bug?
During table analysis, the server restarts due to excessive sampling, but the mysql.analyze_jobs table always shows a running status.
However, on both servers, the show processlist does not display the process_id for table analysis.

| username: xingzhenxiang | Original post link

Turn off automatic analyze

show variables like 'tidb_enable_auto_analyze';
set GLOBAL tidb_enable_auto_analyze=off;
| username: 小龙虾爱大龙虾 | Original post link

Try restarting.

| username: xingzhenxiang | Original post link

Automatically close the ongoing analyze
set global tidb_max_auto_analyze_time = 18600; Takes effect immediately, and those started before the setting that exceed this time will also be killed;
18600 is set to a small value

| username: 扬仔_tidb | Original post link

Restarting a server node can clear the tasks in the show analyze status where state='running'; of that node. However, after version 6, tasks within the last 7 days seem to be recorded in mysql.analyze_jobs.

| username: TiDBer_小阿飞 | Original post link

You’re not stopping the scheduled analysis; you’re deleting the currently running analysis task, right?

| username: 路在何chu | Original post link

I also tried but couldn’t kill it.

| username: dba远航 | Original post link

Adjust the configuration parameters for statistical analysis.