MySQL Bugs: #49232: --storage-engine should be decoupled from default temptable type

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

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:
.

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.

Early patch to enable this functionality

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

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.

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.

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.