Announcement

Announcement Module
Collapse
No announcement yet.

pt-online-schema-change and disk space

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

  • pt-online-schema-change and disk space

    Hello,

    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

    Code:
     
     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
    Hi,

    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.
    https://dev.mysql.com/doc/refman/5.5...ate-index.html

    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.
    http://www.mysqlperformanceblog.com/...ile_per_table/

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

    Comment

    Working...
    X