Announcement Module
No announcement yet.

pt-online-schema-change and disk space

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • pt-online-schema-change and disk space


    if I want to add index to table with pt-online-schema-change, before running pt-online-schema-change I should check that there is at least

     SELECT table_name AS "Table",  round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"  FROM information_schema.TABLES  WHERE table_schema = "$DB_NAME"  AND table_name = "$TABLE_NAME";
    space in my hard drisk (where $TABLE_NAME is name of the table I want to add index), right? Because AFAIK percona copies all data to temp table, do changes, and then rename temp table to old table. And probably I should account for space additional index will create, right?

  • #2

    Yes, you are right that in normal situation, pt-table-schema-change will create temp tables with required changes, load data and then rename table. But If you want to add secondary index and you are using MySQL/PS 5.5 and above version OR MySQL 5.1 with the InnoDB Plugin then you can use a good feature innodb_fast_index_creation.

    It will not create temporary data file and index data will be directly added into original table data file. Read more about that in my blog post.

    I would suggest to test it locally first and then try to run on prod server.