Sample Header Ad - 728x90

Getting OOM when trying to change id from int to bigint in MySQL w/ percona

0 votes
0 answers
49 views
We have 2.5tb table with id column as int in Aurora 3 (MySQL 8.0.26). We need to change it to bigint as ids will run out soon. We tested it with pt-online-schema-change and the server crashed because of memory. I was a bit surprise to see it crashing on memory (it's not our first time running percona) because the work is being done in bulks. Moreover it crashed in dev, where the activity on that table is low. This table is insert intensive, with some updates and deletes. Approaches that will not work/not an option: 1. Recreating the table in the side and copy deltas. 2. CTAS 3. Add column, copy ids, downtime, rename columns. Would love to hear another approach or how to investigate/fix the percona OOM issue. Table DDL: CREATE TABLE tab ( id int unsigned NOT NULL AUTO_INCREMENT, a varchar(50) DEFAULT '', b int DEFAULT NULL, c varchar(255) DEFAULT NULL, d varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, e timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, f tinyint NOT NULL DEFAULT 0, g int DEFAULT NULL, h text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, PRIMARY KEY (id), KEY a (a,c), KEY b (b), KEY c_2 (c,a), KEY g (g,c,e), KEY e (e), KEY g_2 (g,d(191)), KEY ix_c_h (c,h(255)), KEY d (d(20),c,a), KEY ix_c_composite (c,f,e,g,a,id), KEY ix_c_e (c,e) ) charset=latin1 Percona code: ./pt-online-schema-change --host $host D=db,t=tab \ --alter "CHANGE COLUMN id id bigint unsigned NOT NULL AUTO_INCREMENT" \ --execute --recursion-method=none
Asked by Nir (529 rep)
Feb 25, 2024, 03:00 PM
Last activity: Feb 26, 2024, 07:26 AM