3

I have a table with 25 million records and 10 columns on which I am trying to create indexes. According to the following documentation no table copy should occur but when I monitor disk usage it is showing that MySQL has high disk usage and data directory is increasing in size. Can anyone explain why this is happening.

http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid

asked Jun 21, 2016 at 3:51
1
  • 2
    Please post the SHOW CREATE TABLE yourtable\G and the index creation SQL/DDL. Commented Jun 23, 2016 at 17:57

2 Answers 2

2

This is working as intended. The indexes are objects written to the disk. The index creation is also written to the online logs. Creating indexes is expected to take up disk space and cause I/O operations.

answered Jun 27, 2016 at 9:23
1

That happens when InnoDB compressed tables with large amount of rows are being updated (no matter if the algorithm is in place). Large operations for ALTER tables, will use the "online log", which is expected to consume space. You can check this using SHOW ENGINE INNODB STATUS in the History Length line.

This happens always that you have large operations that need consistency across transaction history.

You can check this in the section "InnoDB create index limitations" . Some recommendations can be found here.

If you share more details about the operation it can be useful to improve this answer.

answered Jun 27, 2016 at 3:31
1
  • COMPRESSED involves I/O, but it is not the complete answer. Commented Jul 2, 2016 at 2:08

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.