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 ...]
它在13分钟后崩溃：
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
[...]
我如何添加该列... :(
与现有的16G相比，分配给innodb_buffer_pool_size的内存过多，但是也使用了剩余的2G。
Connections use a certain amount of memory. An
ALTER TABLElocks 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.