0

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.

Daniel K
  • 649
  • 1
  • 4
  • 16
jmazaredo
  • 202
  • 5
  • 13

1 Answers1

1

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.

...

HBruijn
  • 77,029
  • 24
  • 135
  • 201
  • HBruijn if the database is version is 5.5 which I think has no instant algorithm (or it has) it will use the copy algorithm and size will increase because of copy? – jmazaredo Oct 06 '19 at 10:52