We’ve a problem with MySql (5.5.x), and I hope you can help us. At some point during the day, I notice a process with state “copy to tmp table”, and this query:
ALTER TABLE `MyTableName` AUTO_INCREMENT=200000001
After this, all other queries get a “Waiting for table metadata lock”, and all the queries become freezed, and nothing process.
I need to Kill that process, and from that point all queries restarted.
Why? How can I fix this problem?
Advertisement
Answer
In MySQL 5.5, an ALTER TABLE such as the one you ran makes a copy of the whole table. The larger the table, the more time this takes. Especially if you have slow storage.
What is the size of your table (you can get this from SHOW TABLE STATUS LIKE 'MyTableName'G and look at the data_length + index_length)?
I just did a test on my laptop. I filled a table in a MySQL 5.5 instance, until the size of the table is about 175MB. Running an alter table to set the auto-increment value takes about 5-6 seconds. Your results may be different, depending on the power of your server and the speed of storage.
While the alter table is running, the thread doing that operation holds a metadata lock on the table, which blocks all other queries, even read-only SELECT statements.
ALTER TABLE was improved in 2013, as a feature of MySQL 5.6. Some types of alters were optimized to be done “in-place” so they don’t have to copy the whole table if it’s not necessary. Changing the AUTO_INCREMENT is one of these operations. No matter how large the table, if you alter table to change the AUTO_INCREMENT, it’s quick because it only changes an attribute of the table, without requiring copying any rows of data.
See https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
In MySQL 5.5, these optimizations were not implemented. So any alter table takes a long time, proportional to the size of the table.
I would recommend the best way to fix this issue in your case is to upgrade to a newer version. MySQL 5.5 is beyond its end-of-life. Even MySQL 5.6 is reaching its end-of-life in February 2021. It’s time to upgrade.
If you can’t upgrade, then you should investigate what client is doing this ALTER TABLE statement. You said you noticed it at some point during the day. Track that down. In the processlist, it will tell you the client host where that SQL statement is being run from. It will also tell you the MySQL user they logged in as. You may also need to do a search on your source code of any apps or scripts that use this database. Or ask your team mates.
Once you have found the client that is doing that ALTER TABLE, try to change the time the client runs this statement to a time of day when the ALTER TABLE won’t block important queries. Or ask the developer responsible if it’s really necessary to do this alter table so often?