Bug #49232 --storage-engine should be decoupled from default temptable type
Submitted: 30 Nov 2009 20:06 Modified: 26 Jul 2011 18:41
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:any OS:Any
Assigned to: Magne Mæhre CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[30 Nov 2009 20:06] Domas Mituzas
Description:
Currently --storage-engine also affects temporary tables, though usually the idea of default type is somewhat different (as in, used in cases where table type specifications are not given to hands of table creators), whereas temporary tables are supposed to be temporary, and again, their type should not be a matter of end-user.

The very usual setup nowadays is:

storage_engine=InnoDB
 
and it makes temptables transactional, with double-write buffers, checksums, etc, as well as occupies undo slots (and crashes the server too).

Therefore I propose temporary_storage_engine setting, which would allow to decouple 'storage_engine', which is for long-term schema definition practice, from temporary tables, which is purely DBA decision of the moment :)

How to repeat:
SET GLOBAL Storage_engine=...;
CREATE TEMPORARY TABLE ...

Suggested fix:
.
[30 Nov 2009 20:16] Mark Callaghan
I switched default storage engine to innodb on a few busy slaves once. Result was awful because apps used big transactions on temp tables as part of analysis and this caused several innodb crashes. Big transactions are cheap on myisam.
[9 Dec 2009 21:46] Harrison Fisk
Early patch to enable this functionality

Attachment: temp-storage-engine-v1.patch (text/x-patch), 13.08 KiB.

[9 Dec 2009 21:52] Harrison Fisk
I attached a work-in-progress patch that allows you to set the storage engine for temporary tables independently of normal tables.

It currently sets 'temp-storage-engine' based on the storage-engine startup value, unless temp-storage-engine is actually specified.

It breaks backwards compatibility if you do a SET SESSION storage_engine = ...; and then create a temporary table.

The patch does seem to work, there are just a few remaining mtr tests that need to fixed up due to breaking backwards compatibility.
[25 Mar 2010 16:10] Kevin Benton
All,

I believe it's important to note that we really should decouple the variable, not just the command-line switch.  I propose that we utilize the existing STORAGE_ENGINE variable and a new variable TEMP_TBL_STORAGE_ENGINE (or TEMP_TABLE_STORAGE_ENGINE) for many reasons, not the least of which is that there are temporary tables that are created without explicit DDL to create them.  We've observed that with STORAGE_ENGINE set to InnoDB, this causes MySQL to create several types of temporary tables also as InnoDB.  There are valid use cases for asking MySQL to use the MEMORY storage engine as well as MyISAM for temporary tables.  As far as I am concerned, this should be settable both globally and at the session level.
[26 Jul 2011 18:41] Paul Dubois
Noted in 5.6.3 changelogs.

Previously, TEMPORARY tables created with CREATE TEMPORARY TABLES had
the default storage engine unless the definition included an explicit
ENGINE option. (The default engine is the value of the
default_storage_engine system variable.) Since MySQL 5.5.5, when the
default storage engine was changed from the nontransactional MyISAM
engine to the transactional InnoDB engine, TEMPORARY tables have
incurred the overhead of transactional processing.

To permit the default storage engine for TEMPORARY tables to be set
independently of the default engine for permanent tables, the server
now supports a default_tmp_storage_engine system variable. For
example, to create TEMPORARY tables as nontransactional tables by
default, start the server with --default_tmp_storage_engine=MyISAM.
The storage engine for TEMPORARY tables can still be specified on an
individual basis by including an ENGINE option in table definitions.