MySQL 5.6 introduced ALTER TABLE…ALGORITHM=INPLACE, which avoids writing undo log for individual records that are copied. No change was made for ALGORITHM=COPY.
There was a previous work-around that would ensure that if the server was killed during ALTER TABLE, rollback would not take too much time:
commit fd069e2bb36a3c1c1f26d65dd298b07e6d83ac8b
|
Author: marko@hundin.mysql.fi <>
|
Date: Wed Nov 3 21:32:48 2004 +0200
|
|
InnoDB: commit after every 10000 rows in ALTER TABLE
|
The proper fix would be to disable the undo logging for the individual rows altogether. This would still leave an orphan #sql table (until crash-safe atomic DDL is implemented), which can be dropped by the user. That fix was implemented in MySQL 8.0, and MariaDB should backport it:
commit b5f211744b49d620d00fdecd13d9af09ef26c15b
|
Author: Thirunarayanan Balathandayuthapani <thirunarayanan.balathandayuth@oracle.com>
|
Date: Wed Dec 2 16:09:15 2015 +0530
|
|
Bug #17479594 AVOID INTERMEDIATE COMMIT WHILE DOING
|
ALTER TABLE ALGORITHM=COPY
|
|
Problem:
|
=======
|
During ALTER TABLE, we commit and restart the transaction
|
for every 10,000 rows, so that the rollback after recovery would not take
|
so long.
|
|
Fix:
|
====
|
Supress the undo logging during copy alter operation. If fts_index is
|
present then insert directly into fts auxiliary table rather
|
than doing at commit time.
|
|
ha_innobase::num_write_row: Remove the variable.
|
ha_innobase::write_row(): Remove the hack for committing every 10000 rows.
|
row_lock_table_for_mysql(): Remove the extra 2 parameters.
|
lock_get_src_table(), lock_is_table_exclusive(): Remove the function.
|
|
Reviewed-by: Marko Mäkelä <marko.makela@oracle.com>
|
Reviewed-by: Shaohua Wang <shaohua.wang@oracle.com>
|
Reviewed-by: Jon Olav Hauglid <jon.hauglid@oracle.com>
|
RB: 10419
|
Bug
Major
MDEV-515 innodb bulk insert
