Changing Field Type is Very Slow

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

Original topic: 变更字段类型非常慢

| username: Kongdom

[TiDB Usage Environment] Production Environment
[TiDB Version] v5.1.0
[Reproduction Path] Modify table column type
[Encountered Problem: Problem Phenomenon and Impact]
Using ALTER TABLE xxx MODIFY COLUMN to change the column type from int to varchar. The table has 170 million rows and it took approximately 2.5 hours. The cluster is set up according to the standard deployment in the official documentation, with 3 TiDB nodes, 3 PD nodes, and 3 TiKV nodes.
Is there any way to speed up the process?

| username: 裤衩儿飞上天 | Original post link

My dear comrade,
You can upgrade to 6.5 and give it a try :face_with_peeking_eye: :face_with_peeking_eye: :face_with_peeking_eye: :face_with_peeking_eye:

| username: Kongdom | Original post link

That 170 million, 2.5 hours was achieved in version 6.5.3, and now we need to work on version 5.1.0. :joy:

| username: 裤衩儿飞上天 | Original post link

Waiting for the results, let’s see how much slower it is compared to 6.5 :thinking:

| username: Kongdom | Original post link

:astonished: 2.5 hours is completely unacceptable~ btw, will the table be locked when changing the field type?

| username: h5n1 | Original post link

DDL does not block DML, but it may disrupt transactions.

| username: Kongdom | Original post link

:rofl: Can’t accept breaking transactions~

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

This modification is similar to a full table update, it will definitely be slow.

| username: Kongdom | Original post link

Then I will consider creating a new table and re-importing the data.

Are there no parameters that can speed up the process?

| username: 裤衩儿飞上天 | Original post link

Your 5.1 might block subsequent DML operations It won’t block DML, but the 5.1 transaction will fail to commit. It seems that starting from 6.5, there is a metadata lock, so it won’t block DML, but it might extend the time for your DDL operations. You should check the official documentation on metadata locks for specifics.

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

These two parameters adjust the concurrency of the re-organize phase and should be able to improve the speed.

| username: Kongdom | Original post link

It seems that none of them can be operated during the day~ so frustrating~

| username: Kongdom | Original post link

I’ll go check it out.

| username: 裤衩儿飞上天 | Original post link

I checked, and without metadata locks, that is in version 5.1, it won’t block subsequent DML, but the transaction will fail to commit :upside_down_face:

| username: Kongdom | Original post link

:joy: Gave up, just rebuild the table and import the data directly.

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

If you don’t want to affect the business, you can only do it in the middle of the night. Time to work overtime again.

| username: Kongdom | Original post link

The main issue is that it can’t be finished in one night, so I want to see if there are any ways to speed it up.

| username: redgame | Original post link

Actually, there’s no way.

| username: longzhuquan | Original post link

There’s no way around it; changing the type requires loading all the data into TiDB and then writing it back, which is definitely slow. You can check the ddl job in real-time by using admin show ddl_jobs to see the current progress.

| username: Kongdom | Original post link

Unexpectedly, after upgrading to v6.5.3, adding an index was very fast. I feel that modifying a column should also be very fast.