CODEWITHSUNDEEP
mysqlperformanceforeign-keysalter-table
merlin
merlin

Reputation: 2917

Create foreign key on MySQL table takes forever with copy to tmp table

I am trying to set a foreign key constraint on a 5.7 InnoDB table with 30M+ rows.

It now already runs for 45 minutes on a quad core 64GB server. The processlist outputs the state copy to tmp table for the issued alter table command.

InnoDB_buffer_pool_size is set to 32G and has room.

Why does the system create a tmp table and can this somehow be increased in performance?

Upvotes: 3

Views: 2417

Answers (2)

Marco
Marco

Reputation: 2922

Attention! This disables key checking so know what you are doing, in some cases this is not recommended, but can help many people so I think it's worth answering.

I had this problem this week, I have a client that still have mySQL 5.5, so I had to make it work. You just need to disable keys checking, as well as put your application down for maintenance (so you don't have any operations). Before creating your FK or adding a column, use:

ALTER TABLE table_name DISABLE KEYS;

Then run your command, my table with 1M rows took only 57 seconds.

Then you run:

ALTER TABLE table_name ENABLE KEYS;

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562691

It's likely that the time is being taken building an index for that foreign key. If you already had an index where the foreign key column(s) were the leftmost columns of the index, then it would use that index and not build a new one.

45 minutes doesn't sound like an unusual amount of time to build an index on such a large table. You haven't said what the data type of the foreign key column(s) are, so perhaps it's a large varchar or something and it is taking many gigabytes to build that index.

Perhaps your server's disk is too slow. If you're using non-SSD storage, or remote storage (like Amazon EBS), it's slow by modern standards.

The CPU cores isn't going to make any difference, because the work is being done in one thread anyway. A faster CPU speed would help, but not more cores.

At my company, we use pt-online-schema-change to apply all schema changes or index builds. This allows clients to read and write the table concurrently, so it doesn't matter that it takes 45 minutes or 90 minutes or even longer. Eventually it finishes, and swaps the new table for the old table.

Upvotes: 4

Related Questions