-
Run an ALTER TABLE for a huge table in Aurora
Recently, we received an alert for one of our Managed Services customers indicating that the auto_increment value for the table was 80% of its maximum capacity. The column was INT UNSIGNED, which has a limit of 4,294,967,295.
At 80%, we have enough time to change it to BIGINT.…. Right? Let’s see.
So we used pt-online-schema-change to perform the alter.
It started running at a good pace but slowed over time.
Why?
Well, let’s look at the definition of the table:mysql> show create table myschema.mytableG
*************************** 1. row ***************************
Table: mytable
Create Table: CREATE TABLE `mytable` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`long_column` varchar(1000) NOT NULL,
`state` tinyint unsigned NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`short_column` varchar(30) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_long_column` (`long_column`,`state`),
KEY `idx_short_column` (`short_column`,`state`),
KEY `idx_short_col2` (`short_column`)
) ENGINE=InnoDB AUTO_INCREMENT=4009973818 DEFAULT CHARSET=utf8mb3NOTE1: The index on long_column is for a varchar column with a length of 1000; it may not be required, and an index prefix may be more helpful here.
NOTE2: The index idx_short_col2 is duplicated, as it is covered by the index idx_short_column.
Those changes require testing and are out of scope for this emergency, but they are worth mentioning.
Table size:
+---------------+------------+------------+---------+----------+---------+----------+--------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | DATA_GB | INDEX_GB | FREE_GB | TOTAL_GB | ENGINE |
+---------------+------------+------------+---------+----------+---------+----------+--------+
| myschema | mytable | 3906921584 | 1118 | 1790 | 0 | 2907 | InnoDB |
+---------------+------------+------------+---------+----------+---------+----------+--------+Look at the indexes being way bigger than the data.mysql> SELECT database_name, table_name, index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_in_mb FROM mysql.innodb_index_stats WHERE stat_name = 'size' AND index_name != 'PRIMARY' and database_name='myschema' and table_name='mytable' ORDER BY size_in_mb DESC;
+---------------+------------+-------------------+------------+
| database_name | table_name | index_name | size_in_mb |
+---------------+------------+-------------------+------------+
| myschema | mytable | idx_long_column | 1583538.95 |
| myschema | mytable | idx_short_column | 126432.98 |
| myschema | mytable | idx_short_col2 | 122699.95 |
+---------------+------------+-------------------+------------+
3 rows in set (0.01 sec)While the pt-online-schema-change runs, it copies the data to a new table. As the data is being copied, the secondary indexes must be maintained.
NOTE the huge index for a varchar(1000) that is ~1.5T in size. Maintaining such an index becomes increasingly expensive as the data size increases.
The pt-online-schema-change had been running for ~8 days, and its latest estimate was 53 more days, which we can’t afford, since the maximum value would be exceeded in ~15 days. Copying `myschema`.`mytable`: 12% 53+16:48:01 remain
Copying `myschema`.`mytable`: 12% 53+16:48:30 remain
Copying `myschema`.`mytable`: 12% 53+16:48:59 remain
Copying `myschema`.`mytable`: 12% 53+16:49:26 remain
Copying `myschema`.`mytable`: 12% 53+16:49:53 remain
Copying `myschema`.`mytable`: 12% 53+16:50:19 remain
Copying `myschema`.`mytable`: 12% 53+16:50:49 remain
Copying `myschema`.`mytable`: 12% 53+16:51:17 remain
Copying `myschema`.`mytable`: 12% 53+16:51:45 remain
So what do we do now?
We suggested canceling the pt-online-schema-change and creating an Aurora blue-green deployment.
Then perform the direct ALTER on the green cluster. And finally, when ready, do the failover.
Sounds good, doesn’t it?
First, we need to ensure that the new cluster (green) has the replica_type_conversions parameter in its cluster parameter group to “ALL_NON_LOSSY, ALL_UNSIGNED” in order to be able to replicate from an int unsigned column to a bigint unsigned column.
So we tried that, it started too fast ~0.036% per minute, that’s 2 days. That’s great!
We left the process running over the weekend, but we noticed it started to slow down again… By Monday, it was advancing at ~0.01% every 5 mins, which gives an ETA of 34 days.
Why?
Again, using the direct ALTER MySQL copies the data to a temp table, and the bigger the data, the harder it is to maintain the indexes.
Again, unacceptable.
Note that with the above 2 approaches, we lost ~12 days of precious time, and the deadline for auto_increment exhaustion was approaching.
Then we thought: What if we drop the secondary indexes, do the alter, and then add the indexes back?
In theory, it should be faster, as:
Dropping the indexes is a metadata-only operation with ONLINE DDL.
Altering the column datatype from INT to BIGINT is not an ONLINE operation, but the fact that it doesn’t have to update secondary indexes during row copying to a new temporary table prevents the slowdown.
Adding back the secondary indexes is an ONLINE DDL operation:
“Online DDL support for adding secondary indexes means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded.”
https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html
So let’s do this:
The deletion of the indexes was really quick, as expected (metadata-only operation):mysql> ALTER TABLE myschema.mytable DROP INDEX idx_long_column, DROP INDEX idx_short_column, DROP INDEX idx_short_col2;
Query OK, 0 rows affected (49.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
Then the change of the datatype:mysql> ALTER TABLE myschema.mytable CHANGE COLUMN id id bigint unsigned NOT NULL AUTO_INCREMENT;
Query OK, 4058047205 rows affected (13 hours 9 min 10.62 sec)
Records: 4058047205 Duplicates: 0 Warnings: 0
Looks very promising!!!
The final step, add back the indexes:mysql> ALTER TABLE myschema.mytable ADD INDEX `idx_long_column` (`long_column`,`state`), ADD INDEX `idx_short_column` (`short_column`,`state`), ADD INDEX `short_col2` (`short_column`);
ERROR 1878 (HY000): Temporary file write failure.
Why?
Well, the INPLACE operation uses the tmp dir to write sort files. In Aurora, there are certain limits for the temporary space based on the instance type.
In a regular MySQL instance, we can modify the innodb_tmpdir to another location with enough disk space; however, in Aurora, the parameter is not modifiable, which could have made the whole process easier.
Even with a larger instance type, it’s hard to create the 1.5T index without breaking open the piggy bank.
Last resort, add the indexes back with the COPY algorithm:mysql> ALTER TABLE myschema.mytable ALGORITHM=COPY, ADD INDEX `idx_long_column` (`long_column`,`state`), ADD INDEX `idx_short_column` (`short_column`,`state`), ADD INDEX `idx_short_col2` (`short_column`);
Query OK, 4147498819 rows affected (6 days 1 hour 55 min 57.00 sec)
Records: 4147498819 Duplicates: 0 Warnings: 0
Why does it work? Because ALTER TABLE using the COPY algorithm uses the datadir as the destination for the temporary table, the rows are copied there. It doesn’t have the limitation of the temporary directory mentioned above.
We were able to make it on time about 4 days before the auto_increment exhaustion, preventing downtime.
In retrospective we could have used the following approach to avoid the use of the blue/green deployment:
Perform a pt-online-schema-change on the main table, dropping the indexes, and changing the column type to bigint. ( with –no-swap-tables –no-drop-old-table –no-drop-new-table –no-drop-triggers).
Add the secondary indexes using the direct alter with the COPY algorithm in the _new table.
Once the alter finishes, swap the tables and drop the triggers.
Conclusion:
What initially looked like an easy task with pt-online-schema-change, ended up being more complex.
You need to check the data definition, the index sizes, the Aurora limits, and how the different algorithms work to make a decision on the best way to proceed with those tasks, specially on situations like these where you have the pressure of the auto_increment being exhausted and there’s risk of downtime if it is not done on time.
And of course, monitor auto_increment exhaustion for your tables, and use a reasonable threshold that gives you enough time to plan and change the table definition. You can use Percona Monitoring and Management for this, specifically on the MySQL > MySQL Table Details dashboard.
The post Run an ALTER TABLE for a huge table in Aurora appeared first on Percona.
-
MySQL Tuning on OCI HeatWave: What Still Matters, What OCI Manages, and What You Should Actually Tune
Once you move from self-managed MySQL to a MySQL DB System with HeatWave on OCI, the tuning story changes in an important way. On a self-managed server, you worry about two layers: MySQL and the operating system. On OCI MySQL DB Systems with HeatWave, Oracle runs your MySQL instance as a fully-managed service and explicitly […]
-
No More Silent Foreign Key Cascades: MySQL 9.7 Lets Child Triggers Speak Up
MySQL 9.7 introduces a long-requested improvement: Child table triggers are executed during SQL-layer foreign key cascades. Historically, cascades executed inside InnoDB did not invoke child table triggers, which created gaps in auditing, derived data maintenance, and observability. When a parent row change triggered cascading changes in child tables, those child table triggers were not executed. This […]
-
Announcing Vitess 24
Announcing Vitess 24 # The Vitess maintainers are happy to announce the release of version 24.0.0, along with version 2.17.0 of the Vitess Kubernetes Operator.
Version 24.0.0 expands query serving capabilities for sharded keyspaces, modernizes Vitess's observability stack, and introduces faster replica provisioning through native MySQL CLONE support. The companion v2.17.0 operator release brings significant improvements to scheduled backups, with new cluster- and keyspace-level schedules that make production backup management much easier to configure at scale.
-
AI Is Raising the Bar for MySQL Database Security
Best practices for MySQL customers and users in an AI-accelerated security landscape: A practical guide to hardening MySQL and the environment around it Oracle recently described how AI is transforming vulnerability detection and response. The latest generation of AI is increasing the speed and scale at which vulnerabilities can be identified and remediated. Oracle is […]
|
