CODEWITHSUNDEEP
mysqlperformancememoryinnodb
user1209304
user1209304

Reputation: 428

Why mysql alter table always doing copy to temptable?

I have innodb table in mysql with less then 2 million records and data + index size around 1.5 GB I need to do alter table to add a row to the table. Whenever I'm doing alter table mysql always doing copy to temptable. Database machine has 100 GB of RAM so I expect that alter table should be performed in memory. I have set:

tmp_table_size = 10G
max_heap_table_size = 10G
sort_buffer_size = 10G
innodb_buffer_pool_size=50G

But this does not helped. How can I configure mysql to avoid copying to temptable?

Upvotes: 0

Views: 1674

Answers (1)

jeremycole
jeremycole

Reputation: 2761

MySQL does not perform ALTER TABLE in memory in any case. Adding a new column to a table requires creating a new table (with the new schema), copying all rows from the old table to the new table, and atomically swapping the old and new table. Depending on MySQL version, some types of ALTER TABLE, such as adding new indexes, may be possible to be done "online" where other operations are not blocked during the operation. However this is still done "on disk" with regard to your question.

Upvotes: 2

Related Questions