Translated from 日本語 • View original Hello. This is Komiya on the OPS side.
One morning, I suddenly received a consultation from the developer, so I will record it for later.
Consultation details:
I did a production deployment on jenkins, but the process stopped midway.
(Deployed with Cake's DB migration script)
I tried to put the INDEX on the KEY column, but the DB did not respond and I could not connect.
As a result, the table was corrupted, so I used the RDS function to specify the time and roll back.
(I don't know if ALTER was over because I rolled it back)
After doing the same operation in a test environment with the same number of records, it ended smoothly without any abnormalities.
I want to do the same thing again in real life, but what should I do?
The MySQL version is 5.5.27.
My personal perception:
Normally, when you turn ALTER, a lock is applied,
Estimate the time it will take in advance in a test environment with the same configuration and number of cases.
You should stop the service and put in maintenance for that time.
(*Up to 5.5.) It seems that from 5.6 some ALTER has become fine online. ）
Switch to the sorry display and try it in an environment where there are no updates.
(Remove all the web under the existing ELB, cut the vhost to the web of another subdomain, put the sorry content in the ELB, etc.)
If you force quit in the middle, it seems that the table will break easily with MyISAM.
show full processlist; You can see the current query and the time it takes.
But I think killing is the last resort.
You can find out a lot by googling alter table mysql or plus 5.6.
If you look at sh2's blog, it reads as follows.
Check the progress of ALTER TABLE statements in MySQL - SH2's Diary
----------------
In MySQL,
Create a working table based on the modified definition,
Copy data from the table before the change to the working table,
Finally, the two tables are swapped.
Adding indexes to tables is also currently supported by most
The ALTER TABLE statement is being executed internally in the case.
How to check how far you are sleeping:
SHOW GLOBAL STATUS LIKE 'Handler_write';
If you keep the Handler_write value and the number of records in the target table before starting the work,
You can see how far the process has progressed.
If you use innotop in InnoDB, there is Ins/Sec in the view of row operations, so
In the same way, you can make a prediction of the completion of ALTER TABLE.
You don't have to calculate the difference yourself, and it's a standard tool, so it's convenient
----------------
It was introduced as follows on Allied's blog.
Check out the new features of MySQL 5.6! | Allied Architects Engineer Blog
----------------
Improved some ALTER TABLE operations in InnoDB to prevent locks
Until now, when you tried to index, the INSERT and UPDATE in the middle of it would be locked.
During the service, we couldn't even tune the performance, so we had to maintain it.
From 5.6, you can create an ALTER TABLE without blocking DML.
This is quite helpful.
However, it seems to be limited to the following, not all ALTER TABLE operations.
- Adding, deleting, and renaming columns
・CREATE and DROP for secondary indexes - Change the value of auto increment
- Adding and removing foreign key constraints
… etc
However, it is enough.
It will be much easier to operate.
----------------
・What happens if you kill ALTER in the middle?
MySQL :: MySQL 5.1 Reference Manual :: 12.5.5.3 KILL Syntax
During the ALTER TABLE, the kill flag is checked before each block of the row is read from the original table.
If the kill flag is set, the statement will terminate abnormally and the temporary table will be deleted.
Warning: Terminating the REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM table will corrupt the table and render it unavailable.
Until you optimize or repair it (without interrupts), writes and reads to such tables will fail.
・Effects such as OPTIMIZE TABLE↓
ANALYZE TABLE Used to update index statistics
OPTIMYZE TABLE MyISAM's variable-length format (with variable-length columns in the table definition) and InnoDB's
Run to eliminate fragmentation in the data part
In the case of MyISAM, the index statistics are updated, defragmented, and the index pages are reordered.
In the case of InnoDB, ALTER TABLE is performed internally and the table is recreated.
Both the index information and the data are recreated to eliminate defragmentation.
CHECK TABLE Used to check tables that may be corrupted
REPAIR TABLE If the corruption is confirmed, it will attempt to restore it
When the ALTER TABLE table is recreated, both the index information and the data are recreated, eliminating defragmentation
・About pt-online-schema-change (reference)
Alter Table | If you have C.A.Mobile Engineers' Blog
PerconaToolkit somewhere, I think you should try it. It is a very useful tool. You can also see the duplication of KEY.
12/13 postscript:
Please also take a look below
Han's computer path: Let's master the ALTER TABLE.
Talking about locking when adding and deleting columns and indexes in MySQL - (゚∀゚)オ彡 sasata299's blog
That's all for now.
Thank you for watching.