CODEWITHSUNDEEP
mysqlserver
Leo
Leo

Reputation: 19

Alter a column on 4M+ entries table running on production

I'm trying to add a column on a 4M+ entries table running on production using phpmyadmin

Here is what happens on mysql> SHOW FULL PROCESSLIST; :

Query   |  670 | copy to tmp table               | ALTER TABLE `table_name`  ADD `new_column` VARCHAR(1) NULL DEFAULT NULL  AFTER `other_column`

Sleep   |  671 |                                 | NULL

[... And here all inserts attempts to that table are stacking ...]

It crashes after 13 minutes:

200509  0:44:41  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 71728461312
InnoDB: Doing recovery: scanned up to log sequence number 71733704192
[...]

How can I add that column... :(

Upvotes: 0

Views: 250

Answers (1)

danblack
danblack

Reputation: 14736

Too much memory was allocated to innodb_buffer_pool_size compared to the 16G present, but the remaining 2G was also used.

Connections use a certain amount of memory. An ALTER TABLE locks the table rom inserts so the inserts queue up, using more memory. The alter table use a reasonable amount of memory.

Reducing the buffer pool size down to 10G or so might give enough available ram. Look a mysqltuner results which so RAM calculations. Wait until the service is up for a day or so before looking at other recommendations.

Upvotes: 1

Related Questions