Long Execution Time for Update

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

Original topic: update执行时间长

| username: 像风一样的男子

Updating a table with 1 million records has been running all night and still hasn’t finished. How can I check the progress of the update?

| username: Fly-bird | Original post link

Reference link: https://www.163.com/dy/article/FDFGFVDQ0531GIYX.html

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

This thing really doesn’t look good. Check if there’s a lock first…

| username: zxgaa | Original post link

Take a look at information_schema.CLUSTER_TIDB_TRX and information_schema.TIDB_TRX.

| username: Kongdom | Original post link

:joy: Are you using DBeaver? This tool has a problem where it doesn’t automatically commit transactions in the default configuration. You need to change the tool’s settings.

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

It’s not DBeaver, it’s Navicat, which will automatically commit.

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

The query result is like this.

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

The status is Idle. Is this transaction deadlocked?

| username: TiDBer_小阿飞 | Original post link

  • stats_buckets: Buckets of statistical information
  • stats_histograms: Histograms of statistical information
  • stats_meta: Metadata of the table, such as total row count and modification count
  • stats_feedback: Periodic updates on statistical information
    show analyze status where Table_schema=‘’ can check the time status
| username: 像风一样的男子 | Original post link

This SQL still can’t be killed. Does this mean we can only use the ultimate weapon and restart the TiDB node?

| username: Kongdom | Original post link

:thinking: How about trying a different tool? I always feel like it’s a problem with the tool.

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

The update should have been completed, but the task is still there. How can I remove this record from the processlist?

| username: zhanggame1 | Original post link

You can query using select * from INFORMATION_SCHEMA.CLUSTER_PROCESSLIST, but can’t you delete it with kill tidb XX?

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

Oh wow, this process disappeared on its own.

| username: Kongdom | Original post link

:joy: This is a bit strange~

| username: oceanzhang | Original post link

First, check the execution status of this SQL.

| username: oceanzhang | Original post link

Let’s see which state it is in.

| username: system | Original post link

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