Home / server / Questions / 986660
Accepted
jmazaredo
Asked: 2019-10-04 06:53:07 +0800 CST

How large should the mysql temp folder be when altering table information like auto increment?

  • 772

If I have 500GB table size, how big should the temp folder be in order to alter table information? I will be configuring temp in my.cnf and, in this example, I have 64 GB physical ram.

mysql

1 Answers

  1. Best Answer

    The size of the temp folder seems irrelevant according to the "Performance and Space Requirements" section in the manual for ALTER as the space is taken from the same location where the original table is located: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

    The temporary copy of the table is created in the database directory of the original table

    and

    For InnoDB tables, an ALTER TABLE operation that uses the COPY algorithm on a table that resides in a shared tablespace can increase the amount of space used by the tablespace. Such operations require as much additional space as the data in the table plus indexes.

    ...

    ALTER TABLE with DISCARD ... PARTITION ... TABLESPACE or IMPORT ... PARTITION ... TABLESPACE does not create any temporary tables or temporary partition files.

    ...

    • 1