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

  • 2
    Please post the SHOW CREATE TABLE yourtable\G and the index creation SQL/DDL. – RolandoMySQLDBA Jun 23 '16 at 17:57
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.

|improve this answer|||||
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.

|improve this answer|||||
  • COMPRESSED involves I/O, but it is not the complete answer. – Rick James Jul 2 '16 at 2:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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