pt-online-schema-change and disk space

Polyglot Projects Percona Toolkit
1

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


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
[url]https://dev.mysql.com/doc/refman/5.5/en/innodb-create-index.html[/url]

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.
[url]http://www.mysqlperformanceblog.com/2014/02/26/monitor-alter-table-progress-innodb_file_per_table/[/url]

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