How to add an index to a large table in TiDB without causing the database to hang when using direct ALTER; are there any experiences or solutions for this issue?

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

Original topic: tidb如何给大表加索引,直接alter会导致数据库卡死,现在这种问题有没有什么经验

| username: Jjjjayson_zeng

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
As the title suggests, we are hesitant to directly operate on large tables. Is there any standard procedure in TiDB now?

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

There is no problem adding an index directly to a table with billions of rows.

| username: Jjjjayson_zeng | Original post link

My table with hundreds of millions of rows just froze.

| username: 小龙虾爱大龙虾 | Original post link

It won’t get stuck. There’s no standard procedure for this; just execute the add index directly. If it takes a long time, you can simply close the window executing the add index with ctrl+close. TiDB will continue to perform the DDL in the background. No other DDL operations can be performed on this table until the index is added. Then, monitor the DDL progress and adjust the DDL concurrency as needed. I managed to add an index to a table with tens of terabytes in just over a week. :joy_cat:

| username: Jjjjayson_zeng | Original post link

It’s a production environment. Forget about a week, even a day of downtime is unacceptable.

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

What are the symptoms of a freeze?

| username: Jjjjayson_zeng | Original post link

It directly OOMed.

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

Are you sure the OOM was caused by adding an index? I have seen that adding an index consumes very little memory.

| username: Jjjjayson_zeng | Original post link

| username: Jjjjayson_zeng | Original post link

Understood, only this one will be executed.

| username: Jjjjayson_zeng | Original post link

I have sent you the screenshot.

| username: Jjjjayson_zeng | Original post link

| username: Kongdom | Original post link

Another solution is to create a new table, add indexes, then import the data, and finally rename to replace the table. This works with flow tables or log tables. However, it feels like this consumes more resources than altering the table.

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

Upgrade version

| username: 有猫万事足 | Original post link

Adding an index should not cause an OOM (Out of Memory) error. However, I see that the query from 10.29.133.205 is a bit risky. It would be best to check how much memory this SQL query is using in the end.

| username: 路在何chu | Original post link

It could be that your resources are insufficient to begin with.

| username: zhanggame1 | Original post link

Is the CPU usage of TiDB usually this high? The cluster doesn’t seem to be normal.

| username: Jjjjayson_zeng | Original post link

That took 11 seconds to execute, brother.

| username: Jjjjayson_zeng | Original post link

As mentioned before, it’s because of the alter that it has become so high; normally, it’s very low.

| username: zhanggame1 | Original post link

It might be that the cluster is already under heavy load. Try setting tidb_ddl_reorg_worker_cnt to the minimum and see how it goes.