I have a very large table (> 6 bn rows & > 3tb of data) that I'm trying to alter.
Example schema:
CREATE TABLE `huge_table` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`col1` int(11) unsigned DEFAULT NULL,
`col2` int(11) unsigned DEFAULT NULL,
`col3` int(11) unsigned NOT NULL,
`col4` int(11) unsigned DEFAULT NULL,
`col5` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`col6` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`col7` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`col8` datetime GENERATED ALWAYS AS (cast(`created` as date)) VIRTUAL,
`dt1` timestamp(3) NULL DEFAULT NULL,
`dt2` timestamp(3) NULL DEFAULT NULL,
`dt3` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `idx_col5` (`col5`),
KEY `col4` (`col4`),
KEY `col1` (`col1`),
KEY `col3` (`col3`),
KEY `col6` (`col6`,`col3`),
KEY `col2` (`col2`),
KEY `col8` (`col8`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Most of these indexes are unnecessary, so I'm trying to clean them up, and add a more precise index. Note that all of the ALTER TABLE statements below take well over 24 hours to run.
First, I tried modifying all the indexes using Online DDL:
ALTER TABLE huge_table
DROP INDEX col4_idx,
DROP INDEX col1_idx,
DROP INDEX col3_idx,
DROP INDEX col6_idx,
DROP INDEX col2_idx,
DROP INDEX col8_idx,
ADD INDEX new_idx(col3, col6, col1),
ALGORITHM=INPLACE, LOCK=NONE;
When this finally finished (no errors), I checked the schema, only to find nothing had changed in the schema. Therefore, I decided to try again without forcing it to run "INPLACE":
ALTER TABLE huge_table
DROP INDEX col4_idx,
DROP INDEX col1_idx,
DROP INDEX col3_idx,
DROP INDEX col6_idx,
DROP INDEX col2_idx,
DROP INDEX col8_idx,
ADD INDEX new_idx(col3, col6, col1);
This didn't work, either (it still seemed to be running in online mode). I decided to break up the process into two: drop the unnecessary columns, first, then add the new column:
ALTER TABLE huge_table
DROP INDEX col4_idx,
DROP INDEX col1_idx,
DROP INDEX col3_idx,
DROP INDEX col6_idx,
DROP INDEX col2_idx,
DROP INDEX col8_idx;
ALTER TABLE huge_table
ADD INDEX new_idx(col3, col6, col1);
The dropping of the columns only took a few seconds, and the new index completed without errors (again, in online mode). Unfortunately, the new index didn't take.
I presume the entire DDL statement was ultimately rolled back after failing to create the new index, but don't see any record of that; it seems to fail silently. I'm wondering if it's because there's a VIRTUAL column, but I would expect the engine to return some sort of error message. Has anyone else seen this type of issue?
So, right before submitting this question, I found the error:
Error Code: 1799. Creating index 'new_idx' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.
The issue is that the table is heavily used, and the innodb_online_alter_log_max_size setting is a hard limit to the amount of DML statements that are stored during the online DDL process. As a result, it seems I cannot modify this table in online mode, unless I bump up this value.