Title: Very long-running mysql process
Tags: mysql;database-administration
Question: I have a long-running mysql process to update a very large table where the auto-incrementing ID field needs to be changed from INT32 to INT64 (BIGINT). The data size is about 150GB and there are ~2.4B rows.
This is straightforward enough, and the alter table has been taking about 3 days now and is still running:
Is there any way to either (1) track progress here (or maybe even guesstimate how long it would take)? or (2) any other things I can do to expedite this other than cross my fingers?
Comment: Cross your fingers ... and don't forget to rebuild your indexes when you're done!


Here is another answer: Have you seen this: https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html
It might be too late to get the progress for the alter that is currently running. You have to enable a performance_schema instrument, and I don't know offhand if it would apply retroactively if you enable it now.
Another suggestion I have, also for future alterations, not for the one currently running on your server, is to use pt-online-schema-change. At my last job, we used this for dozens of alterations on large tables like yours every week. It has an option to report progress, and even better, it does not block access to the table while it's running.
Comment for this answer: You're correct - it's doubtful the OP can do anything to track progress for the job he already initiated. It might have already completed anyway.  KEY POINTS: Q: will the OP ever need to do this again?  Hopefully not.  But if so, Q: What (if anything) can he do to mitigate the problem for future runs?  Finally, Q: Is the OP rebuilding his indexes ... and will *THAT* take an excessive amount of time, too?
Comment for this answer: @paulsm4 In InnoDB, any schema change that changes the size of a row (changing from INT to BIGINT would do this) will rebuild the table and all indexes. It's not necessary to rebuild indexes again after an ALTER TABLE.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2022:00:04/01/22', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql><database-administration>', 'Title': 'Very long-running mysql process', 'ViewCount': 70, 'id': 71713290, 'user_AboutMe': '', 'user_AccountId': 328297.0, 'user_DisplayName': 'David542', 'user_Reputation': 103183.0, 'user_Views': 8666.0, 'user_id': 651174.0}}