MySQL - alter table column vs mysqldump + load data infile

Question

I have a large table (~250 Gb) and I want to change the column of the primary key from INT to BIGINT. Assuming that the server instance has sufficient resources to configure MySQL in an optimal way for both methods, which method is going to run in the least amount of time? 1. Alter table 2. Creating new table, exporting old with mysqldump and inserting into the new on with LOAD DATA INFILE.

Note: I'm aware of pt-online-schema-change, which is probably the best option to alter a table without impacting the usage of the existing DB, but I would like to know which option requires the least time to run.

Bonus: I suspect the answer is the same for both MySQL and MariaDB?

Answer 1

The best, faster and secure one will be create a new table with changes made and load the data into. Even that you have the resources to run the alter table this will stress the server and can crash in the way.

Answer 2

Each method requires reading and writing the entire table. That puts a lower bound on how much time is involved -- namely the time to do all that I/O.

The dump and reload may require multiple reads and writes, depending on what you do with the dump.

ALTER and dump+reload need for the table to be locked the entire time, or at least you must avoid writing to the table.

PTOSC may be a little slower, but it has a very brief time during which you must not write to it. So, if your real concern is about writes, not elapsed time, this might be the best option.

PTOSC does

1. Create a new table, makes the change(s) (INT to BIGINT).
2. Add a TRIGGER to catch any writes.
3. loop through the table, copying chunks of rows.
4. When almost finished, there is a brief pause (lockout) deals with the info captured by the trigger, swaps tables, and cleans up.

I would expect no differences between MySQL and MariaDB for any method. However, ptosc is probably not available on Windows.

As for resources, each needs room for a second copy of the table, maybe more. Be aware of the impact of innodb_file_per_table. If the table is currently in its own file, and the setting is "1", then the disk handling is clean. Otherwise, ibdata1 or the tablespace or whatever may grow significantly and the old space not be freed up.