2

I have a huge table in my mysql 5.6.19 database -around 5.7 gb of data- and there is a need to alter a description column which is currently set to text, to be able to contain around 500000 chars. Now my question is what is the right way to preform such a proccess. What will be the effect of the disk space when doing it? Can indexes be possibly harmed after this operation? Any help would be highly appriciated here. Thank you very much

2 Answers 2

3

You need to ALTER a column from TEXT to MEDIUMTEXT?

Such an ALTER will copy the table over, making the change as it goes. At the end, rebuild the indexes and will RENAME the table back into place. The table will be blocked from (at least) writes for the duration, which could be hours.

For a less invasive approach, see pt-online-schema-change. It will add a Trigger to the table so that writes are tracked while it copies the table over. It ends with a brief finishing lock to flip the table into place.

Either approach will take about 5.7GB of spare disk space. Caveat: If you have innodb_file_per_table = OFF, the spare disk space will not be freed when finished. I recommend ON when creating 'big' InnoDB tables.

2

There are tools that allow MySQL DBAs to perform online schema changes. Both of them come from heavy hitters in the world of MySQL.

The first tool is Percona's pt-online-schema-change.

Documentation is comprehensive and Percona need no introduction in the MySQL world.

The other tool is Shlomi Noach's (called online-schema-change again!) and is available here.

Percona's toolset is written in Perl and Noach's is written in Python - so it might be down to a preference for one or the other.

In any case, both sets of tools are worth checking out for any MySQL DBA.

2
  • Thank you very much about those tool. Do i need to take anything under consideration as im running a slave replication in production? Will this tool work fine with it? The percona one
    – user69153
    Jun 29, 2015 at 10:53
  • Search for "replication" on this page. Interestingly, one of the participants in the discussion at the end is Shlomi Noach who wrote one of the tools, but he comments on replication using pt-online-schema-change - the other (i.e. Percona's) tool. Also, see the Percona documentation page above (again, search for replication).
    – Vérace
    Jun 29, 2015 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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