{"id":644,"date":"2015-02-24T13:47:27","date_gmt":"2015-02-24T12:47:27","guid":{"rendered":"http:\/\/dbahire.com\/?p=644"},"modified":"2019-08-27T12:00:17","modified_gmt":"2019-08-27T10:00:17","slug":"regarding-mysql-5-6-temporary-tables-format","status":"publish","type":"post","link":"https:\/\/jynus.com\/dbahire\/regarding-mysql-5-6-temporary-tables-format\/","title":{"rendered":"Regarding MySQL 5.6 temporary tables format"},"content":{"rendered":"

\"Adefault_tmp_storage_engine<\/code> variable was introduced in 5.6.3, allowing the configuration of the default engine for temporary tables. This seems to be in the direction, as I commented before, of making MyISAM an optional engine<\/a>. In 5.7, a separate tablespace is being created to hold those tables in order to reduce its performance penalty<\/a> (those tables do not need to be redone if the server crashes, so extra writes are avoided).<\/p>\n

However, I have seen many people assuming that because default_tmp_storage_engine<\/code> has the value “InnoDB”, all temporary tables are created in InnoDB format in 5.6<\/em>. This is not true: first, because implicit temporary tables are still being created in memory using the MEMORY<\/code> engine (sometimes called the HEAP<\/code> engine), while MyISAM is being used for on-disk tables<\/strong>. If you do not trust the reference manual on this, here it is a quick test to check it:<\/p>\n

mysql> SELECT version();
\n+------------+
\n| version() |
\n+------------+
\n| 5.6.23-log |
\n+------------+
\n1 row in set (0.00 sec)<\/code><\/p>\n

mysql> SHOW GLOBAL VARIABLES like 'default%';
\n+----------------------------+--------+
\n| Variable_name | Value |
\n+----------------------------+--------+
\n| default_storage_engine | InnoDB |
\n| default_tmp_storage_engine | InnoDB |
\n| default_week_format | 0 |
\n+----------------------------+--------+
\n3 rows in set (0.00 sec)<\/code><\/p>\n

mysql> SHOW GLOBAL VARIABLES like 'tmpdir';
\n+---------------+-------+
\n| Variable_name | Value |
\n+---------------+-------+
\n| tmpdir | \/tmp |
\n+---------------+-------+
\n1 row in set (0.00 sec)<\/p>\n

mysql> CREATE TABLE test (id serial, a text);
\nQuery OK, 0 rows affected (0.10 sec)<\/p>\n

mysql> insert into test (a) values ('a');
\nQuery OK, 1 row affected (0.06 sec)<\/p>\n

mysql> insert into test (a) values ('aa');
\nQuery OK, 1 row affected (0.00 sec)<\/p>\n

mysql> insert into test (a) values ('aaa');
\nQuery OK, 1 row affected (0.00 sec)<\/p>\n

mysql> SELECT *, sleep(10) FROM test ORDER BY rand();
\n...<\/p>\n

[ec2-user@jynus_com tmp]$ ls -la
\ntotal 24
\ndrwxrwxrwt 5 root root 4096 Feb 24 11:55 .
\ndr-xr-xr-x 23 root root 4096 Jan 28 14:09 ..
\ndrwxrwxrwt 2 root root 4096 Jan 28 14:09 .ICE-unix
\n-rw-rw---- 1 mysql mysql 0 Feb 24 11:55 #sql_7bbd_0.MYD
\n-rw-rw---- 1 mysql mysql 1024 Feb 24 11:55 #sql_7bbd_0.MYI
\ndrwx------ 2 ec2-user ec2-user 4096 Feb 24 11:41 ssh-5ZGoXWFwtQ
\ndrwx------ 2 ec2-user ec2-user 4096 Feb 24 11:43 ssh-w9IkW0SvYo<\/p>\n

<\/code><\/code>...
\n+----+------+-----------+
\n| id | a | sleep(10) |
\n+----+------+-----------+
\n| 1 | a | 0 |
\n| 2 | aa | 0 |
\n| 3 | aaa | 0 |
\n+----+------+-----------+
\n3 rows in set (30.00 sec)
\n<\/code><\/p>\n

The only thing I have done above is forcing the creation of the temporary table on disk by adding a TEXT field (incompatible with the MEMORY engine, so it has to be created on disk) and using sleep so that we have enough time to check the filesystem. You can see on the output of ls<\/code> the .MYD and .MYI particular to the MyISAM engine. That last step would be unnecessary if we just used PERFORMANCE_SCHEMA<\/code> to check the waits\/io<\/code>.<\/p>\n

A second, and more obvious reason why thinking that all temporary tables are created in InnoDB format, is because explicit temporary tables can still be created in a different engine<\/a> with the ENGINE keyword<\/strong>:<\/p>\n

mysql> CREATE TEMPORARY TABLE test (i serial) ENGINE=MyISAM;
\nQuery OK, 0 rows affected (0.00 sec)<\/code><\/p>\n

[ec2-user@jynus_com tmp]$ ls -la
\ntotal 36
\ndrwxrwxrwt 5 root root 4096 Feb 24 12:16 .
\ndr-xr-xr-x 23 root root 4096 Jan 28 14:09 ..
\ndrwxrwxrwt 2 root root 4096 Jan 28 14:09 .ICE-unix
\n-rw-rw---- 1 mysql mysql 8554 Feb 24 12:12 #sql7bbd_36a3_0.frm
\n-rw-rw---- 1 mysql mysql 0 Feb 24 12:12 #sql7bbd_36a3_0.MYD
\n-rw-rw---- 1 mysql mysql 1024 Feb 24 12:12 #sql7bbd_36a3_0.MYI
\ndrwx------ 2 ec2-user ec2-user 4096 Feb 24 11:41 ssh-5ZGoXWFwtQ
\ndrwx------ 2 ec2-user ec2-user 4096 Feb 24 11:43 ssh-w9IkW0SvYo<\/code><\/p>\n

mysql> DROP TEMPORARY TABLE test;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n

<\/code><\/code>[ec2-user@jynus_com tmp]$ ls -la
\ntotal 20
\ndrwxrwxrwt 5 root root 4096 Feb 24 12:17 .
\ndr-xr-xr-x 23 root root 4096 Jan 28 14:09 ..
\ndrwxrwxrwt 2 root root 4096 Jan 28 14:09 .ICE-unix
\ndrwx------ 2 ec2-user ec2-user 4096 Feb 24 11:41 ssh-5ZGoXWFwtQ
\ndrwx------ 2 ec2-user ec2-user 4096 Feb 24 11:43 ssh-w9IkW0SvYo
\n<\/code><\/p>\n

Will this change in the future? 5.7.5 continues to have the same behavior as 5.6. However, as Stewart pointed some time ago<\/a>, the performance optimizations in 5.7 make some uses of MEMORY and MyISAM obsolete so I will not be surprised if that dependency, together with MyISAM grant tables, will be removed in the future.<\/p>\n

Update:<\/strong> I’ve been told by email by Morgan<\/a> that the yet-to-be-released (at the time of this writing) 5.7.6 will finally change the default behavior to be full InnoDB for implicit temporary tables, too, as seen on the release notes<\/a>:<\/p>\n

InnoDB: The default setting for the internal_tmp_disk_storage_engine option, which defines the storage engine the server uses for on-disk internal temporary tables (see How MySQL Uses Internal Temporary Tables), is now INNODB. With this change, the Optimizer uses the InnoDB storage engine instead of MyISAM for internal temporary tables.<\/p><\/blockquote>\n

internal_tmp_disk_storage_engine<\/code> was introduced in 5.7.5, but its default value then was MYISAM.<\/p>\n

This is in order to get advantage of the in-memory performance of InnoDB for variable-lengh fields, which I am personally 100% for. Thank you Morgan for the extra information!<\/p>\n","protected":false},"excerpt":{"rendered":"

default_tmp_storage_engine variable was introduced in 5.6.3, allowing the configuration of the default engine for temporary tables. This seems to be in the direction, as I commented before, of making MyISAM an optional engine. In 5.7, a separate tablespace is being<\/p>\n","protected":false},"author":1,"featured_media":645,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[33,151,283,125,281,32,279,53,285,277,275,273],"class_list":["post-644","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-5-6","tag-5-7","tag-engine","tag-format","tag-heap","tag-innodb","tag-memory","tag-myisam","tag-primary-en","tag-tables","tag-temporary","tag-tmp"],"_links":{"self":[{"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/posts\/644","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/comments?post=644"}],"version-history":[{"count":12,"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/posts\/644\/revisions"}],"predecessor-version":[{"id":1002,"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/posts\/644\/revisions\/1002"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/media\/645"}],"wp:attachment":[{"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/media?parent=644"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/categories?post=644"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/tags?post=644"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}