Online alter table failure on large partitioned table
Posted by: Gareth Smith
Date: February 18, 2015 04:51AM

I have experienced a problem with online alter table on a large table (~1.85 billion rows, ~54gb data, ~57gb indexes). The table has 50 partitions. I am using MySQL 5.6.20.

This is the alter table script:

alter table my_large_table key_block_size=4, algorithm=inplace, lock=none;

On a small table this ran with no problems.

On the large table it ran for many hours and then appeared to fail. While the online alter table was running I observed that MySQL created lots of files in the data directory with names like "#sql-ib3759.ibd". There seemed to be one file for each partition, and I was able to observe progress by seeing how the files grew - they started at some small fixed size (a few kbs) and each file grew in turn until it was hundreds of megabytes or a few gigabytes in size. During this period of file growth everything seemed to be working OK, and the "alter table" statement appeared in the list of MySQL processes. After MySQL had finished growing the "#sql-ibXXXX.ibd" files then it seemed to just give up. The alter table terminated and the client received this message:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Does anybody know why this might have failed?

Apart from the alter table not working, I now have a problem with the "#sql-ibXXXX.ibd" files: these are taking up ~100gb of space that I would like to reclaim. Would it be safe to delete these files?


Options: ReplyQuote

Written By
Online alter table failure on large partitioned table
February 18, 2015 04:51AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.