0

As innodb_file_per_table option separates table files instead of putting all data and indexes of DBs into one ibdata file, is using this option improve speed of alter table?

I have a table of 40M rows and when I alter a specific table it takes about 5 to 6 hours.

Does this solution help? Is there other ways around to improve alter table speed on heavy tables?

1

innodb_file_per_table is convenient option to manage disk space used by innodb, but it won't improve ALTER TABLE time.

As of 5.6 InnoDB supports online DDL for some kind of ALTERs. Check Online DDL page to see what's currently supported.

If not, you may consider using pt-online-schema-change or similar tool(https://github.com/github/gh-ost, https://github.com/facebookincubator/OnlineSchemaChange). It will run long time, but won't block the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.