copy to tmp table which alter table on auto increment

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?

The problem could be due to a server restart, as InnoDb stores the last auto-increment index in memory and recalculates it at server restart (InnoDB AUTO_INCREMENT Counter Initialization):

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by 1. This default can be overridden by the auto_increment_increment configuration setting.

If the table is empty, InnoDB uses the value 1. This default can be overridden by the auto_increment_offset configuration setting.

Look at the mysql logs and try to find out if the server is restarting, causing the ALTER table to reset the autoincrement counter.

Try to restart the mysql server to see if you get this behaviour.

If this is the case, you could try to:

• Prevent restarting the mysql server, maybe there is a cron process that restarts it once a day
• Upgrade your mysql version to 8 (Autoicrement saved to table metadata):

In MySQL 8.0, this behavior is changed. The current maximum auto-increment counter value is written to the redo log each time it changes and is saved to an engine-private system table on each checkpoint. On a server restart following a normal shutdown, InnoDB initializes the in-memory auto-increment counter using the current maximum auto-increment value stored in the data dictionary system table.

• You could try to speed up "copy to tmp table" operations, skip copying to tmp table on disk mysql

References

How to make InnoDB table not reset autoincrement on server restart?

https://serverfault.com/questions/228690/mysql-auto-increment-fields-resets-by-itself