MySQL Bugs: #73250: create dynamic innodb_tmpdir variable to control where InnoDB writes temp files

Bug #73250	create dynamic innodb_tmpdir variable to control where InnoDB writes temp files
Submitted:	10 Jul 2014 0:56	Modified:	30 Oct 2014 17:43
Reporter:	Ben Krug	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.6	OS:	Any
Assigned to:	Sunny Bains	CPU Architecture:	Any

Description:
InnoDB creates temporary tables for online ALTER statements in the tmpdir.  In some cases, the tmpdir is too small, or for other reasons, not the best choice.  It would be good to have a new dynamic variable, innodb_tmpdir, that would determine where the temp tables are created.  This should also replicate, so that if a user sets it dynamically on a master, slaves don't need to be handled separately.

How to repeat:
online ALTER for InnoDB tables will create temporary tables in the tmpdir

Suggested fix:
create dynamic variable innodb_tmpdir to control this behavior

There is a slight confusion in the Description.

ALTER TABLE creates a number of temporary files. When the table is being rebuilt (for example, for ADD/DROP PRIMARY KEY, or adding or dropping columns, or changing ROW_FORMAT), then ALTER TABLE will also create an intermediate copy for the altered table. This copy would then replace the original table.

This "temporary table" must be on the same file system as the original table, so that the switchover by renaming *.ibd files will work. We cannot change this. Currently, both *.ibd files will be in the same directory; the name of the other copy will start with #sql-ib.

What we can change is the location of the temporary files that are created by row0merge.cc and row0log.cc.

Currently the temporary file creation is controlled by the MySQL config parameter 'tmpdir'. So, I guess that this new parameter should default to the value of tmpdir.

This parameter would affect all callers of innobase_mysql_tmpfile() and os_file_create_tmpfile(). There are some minor users of these, apart from InnoDB ALTER TABLE.

Posted by developer:
 
Fixed as of the upcoming 5.6.22, 5.7.6 release, and here's the changelog entry:

A new "InnoDB" configuration option, "innodb_tmpdir", allows you to
configure a separate temporary file directory for online "ALTER TABLE"
operations. This option was introduced to help avoid "tmpdir" overflows
that could occur as a result of large temporary files created during
online "ALTER TABLE" operations. "innodb_tmpdir" is a SESSION variable and
can be configured dynamically using a "SET" statement. 

Added configuration option documentation:
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_tmpdir
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_tmpdir

$ bzr log -r 6223
------------------------------------------------------------
revno: 6223
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: mysql-5.6
timestamp: Tue 2014-10-28 14:11:18 +0530
message:
  Bug #19183565	CREATE DYNAMIC INNODB_TMPDIR VARIABLE TO CONTROL WHERE
  			INNODB WRITES TEMP FILES
  
  Problem:
  
  InnoDB creates temporary files for online ALTER statements in the
  tmpdir. In some cases, the tmpdir is too small, or for other reasons, not the
  best choice. 
  
  Solution:
  	Create a new dynamic session variable "innodb_tmpdir"
  that would determine where the temp files should create during alter
  operation.
  
  Behaviour of innodb_tmpdir :
  ----------------------------
  1) Default value is Empty String.
  2) Valid inputs are String ,NULL and Empty String.
  3) Directory Permission or existence checked while using innodb_tmpdir
  not during initialization of variable.
  ??? (in this case alter table when trying to create temp file).
  4) If value is set to NULL or Empty String, then temporary file is
  creating in the location of mysql server variable(--tmpdir).
  5) ONLINE ALTER TABLE operation will report error when innodb_tmpdir is
  set to invalid location.
  6) Both innodb_tmpdir and --tmpdir values are set by the user. 
  innodb_tmpdir will overrides the value of --tmpdir during ONLINE ALTER
  TABLE only.
  
  	Approved by Marko [rb 6724]

$ bzr log -r 6225
------------------------------------------------------------
revno: 6225
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: mysql-5.6
timestamp: Tue 2014-10-28 17:46:14 +0530
message:
  Bug #19183565   CREATE DYNAMIC INNODB_TMPDIR VARIABLE TO CONTROL WHERE
                          INNODB WRITES TEMP FILES
  	Pushing Post fix failure in pb2.

$ bzr log -r 6226
------------------------------------------------------------
revno: 6226
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: mysql-5.6
timestamp: Wed 2014-10-29 13:41:21 +0530
message:
  Bug #19183565   CREATE DYNAMIC INNODB_TMPDIR VARIABLE TO CONTROL WHERE
                          INNODB WRITES TEMP FILES
  	Pushing Post fix failure in pb2.

$ bzr log -r 6235
------------------------------------------------------------
revno: 6235
tags: mysql-5.6.22
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: mysql-5.6.22-release
timestamp: Fri 2014-11-21 10:50:02 +0530
message:
  Bug #19183565	CREATE DYNAMIC INNODB_TMPDIR VARIABLE TO CONTROL
  		WHERE INNODB WRITES TEMP FILES
  		- Reverting the patch.

innodb_tmpdir variable patch for mysql-5.6

Attachment: bug19183565r6163rb6724r10.patch (text/x-patch), 27.19 KiB.

The attached patch is a trial one but not the final one.

$ git show -s f9725ff
commit f9725ff9c621c1790c3cb9c27356ec14fb82b511
Author: Thirunarayanan B <thirunarayanan.balathandayuth@oracle.com>
Date:   Fri Nov 21 10:50:02 2014 +0530

    Bug #19183565       CREATE DYNAMIC INNODB_TMPDIR VARIABLE TO CONTROL
                WHERE INNODB WRITES TEMP FILES
                - Reverting the patch.

$ git show -s 4b291e9
commit 4b291e9113f1e8f69973861bd16d5f570b5d8e91
Author: Thiru <thirunarayanan.balathandayuth@oracle.com>
Date:   Mon Nov 24 15:54:48 2014 +0530

    Bug #19183565   CREATE DYNAMIC INNODB_TMPDIR VARIABLE TO CONTROL
                WHERE INNODB WRITES TEMP FILES
    
                - Reverting the patch.

Posted by developer:
 
Fixed as of the upcoming 5.6.29, 5.7.11, 5.8.0 release, and here's the changelog entry:

A new InnoDB configuration option, innodb_tmpdir, allows you to configure
a separate temporary file directory for online ALTER TABLE operations.
This option was introduced to help avoid tmpdir overflows that could occur
as a result of large temporary files created during online ALTER TABLE
operations. innodb_tmpdir is a SESSION variable and can be configured
dynamically using a SET statement. 

The innodb_tmpdir option is documented here:
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_tmpdir

Posted by developer:
 
The following documentation was also updated for 5.6, 5.7, and 5.8:
http://dev.mysql.com/doc/refman/5.7/en/temporary-files.html

Posted by developer:
 
The changelog entry for this bug was revised as follows:

"A new InnoDB configuration option, innodb_tmpdir, allows you to configure
a separate directory for temporary files created during online ALTER TABLE
operations. This option was introduced to help avoid tmpdir overflows that
could occur as a result of large temporary files created during online
ALTER TABLE operations. innodb_tmpdir can be configured dynamically using
a SET statement. 

Online ALTER TABLE operations that rebuild
a table also create an intermediate table file in the same directory as
the original table. The innodb_tmpdir option is not applicable to
intermediate table files."

The following sections were updated in the reference manual:

http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_tmpdir
http://dev.mysql.com/doc/refman/5.7/en/temporary-files.html