MySQL Bugs: #80932: Better explain space usage for ALTER with ALGORITHM=inplace

Bug #80932	Better explain space usage for ALTER with ALGORITHM=inplace
Submitted:	1 Apr 2016 14:16	Modified:	1 Jun 2017 14:39
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6.27, 5.6.29	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	alter, innodb, online-alter, schema change

Description:
It's not clear how much space is needed for many of the ALTERs with ALGORITHM=inplace

This needed 100GB+:
mysql> ALTER TABLE t1 FORCE, ALGORITHM=inplace;
ERROR 1034 (HY000): Incorrect key file for table 't1'; try to repair it

How to repeat:
See how much space is needed with online alters on large tables.

Suggested fix:
Document expected space usage for ALTERs with inplace option.

I would expect that ALGORITHM=inplace would use less space than ALGORITHM=copy, but that's not true:

mysql> ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, ALGORITHM=inplace;
ERROR 1034 (HY000): Incorrect key file for table 't1'; try to repair it

mysql> ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, ALGORITHM=copy;
Query OK, 631377003 rows affected (10 hours 2 min 29.28 sec)

Hello Daniël,

Thank you for the report.

Thanks,
Umesh

// requested details removed, removing private flag for now

Posted by developer:
 
The following section has been updated to better explain space requirements for online ALTER TABLE operations:

https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-limitations.html 

"An online ALTER TABLE operation that copies the table can cause an error
if the operation uses all of the available disk space on the file system
where the data directory (datadir) resides. To avoid this problem, ensure
that there is enough disk space to accommodate online ALTER TABLE
operations that copy the table. During these operations, MySQL writes
temporary sort files to the MySQL temporary directory ($TMPDIR on Unix,
%TEMP% on Windows, or the directory specified by the --tmpdir
configuration variable). Each temporary file is large enough to hold one
column in the new table or index, and each one is removed as soon as it is
merged into the final table or index. Such operations may require
temporary space equal to the amount of data in the table plus indexes.

Thank you for the bug report.

I don't consider this bug fixed.

"An online ALTER TABLE operation that copies the table can cause an error
if the operation uses all of the available disk space on the file system
where the data directory (datadir) resides."
This is about ALGORITHM=inplace not ALGORITHM=copy. So either this doesn't
apply or is ambiguous.

"To avoid this problem, ensure
that there is enough disk space to accommodate online ALTER TABLE
operations that copy the table. During these operations, MySQL writes
temporary sort files to the MySQL temporary directory ($TMPDIR on Unix,
%TEMP% on Windows, or the directory specified by the --tmpdir
configuration variable). Each temporary file is large enough to hold one
column in the new table or index, and each one is removed as soon as it is
merged into the final table or index. Such operations may require
temporary space equal to the amount of data in the table plus indexes."

So for table with a 150GB ibd  file and 80GB data and 20GB indexes:

With ALGORITHM=COPY this is what happens:
1. a new ibd file is created
2. the 80+20 GB is copied into the new file
3. the old file is removed.

Before starting there should be 100GB of free space.
After the operation the ibd file is 100GB

There might be a little bit of overhead and up to innodb_online_alter_log_max_size
for the alter log.

But I don't know how much space is needed for ALGORITHM=INPLACE.

Posted by developer:
 
The following content was added covering space requirements for online DDL operations:
https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-concurrency.html#innodb-online...

Thank you for the bug report.

Seeing as this was filed on 5.6 and I just spent an hour debugging it...  

5.6 indeed has a problem I couldn't explain by reading docs either.
Fixed in 5.7+ as:

Bug 17657223 - EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE