Regarding MySQL 5.6 temporary tables format

http://ift.tt/1Lz7AXd

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 created to hold those tables in order to reduce its performance penalty (those tables do not need to be redone if the server crashes, so extra writes are avoided). However, I have seen many people assuming that because default_tmp_storage_engine has the value “InnoDB”, all temporary tables are created in InnoDB format. This is not true: first, because implicit temporary tables are still being created in memory using the MEMORY engine (sometimes called the HEAP engine), while MyISAM is being used for on-disk tables. If you do not trust the reference manual on this, here it is a quick test to check it: mysql> SELECT version(); +------------+ | version() | +------------+ | 5.6.23-log | +------------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES like 'default%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | default_week_format | 0 | +----------------------------+--------+ 3 rows in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES like 'tmpdir'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | tmpdir | /tmp | +---------------+-------+ 1 row in set (0.00 sec) mysql> CREATE TABLE test (id serial, a text); Query OK, 0 rows affected (0.10 sec) mysql> insert into test (a) values ('a'); Query OK, 1 row affected (0.06 sec) mysql> insert into test (a) values ('aa'); Query OK, 1 row affected (0.00 sec) mysql> insert into test (a) values ('aaa'); Query OK, 1 row affected (0.00 sec) mysql> SELECT *, sleep(10) FROM test ORDER BY rand(); ... [ec2-user@jynus_com tmp]$ ls -la total 24 drwxrwxrwt 5 root root 4096 Feb 24 11:55 . dr-xr-xr-x 23 root root 4096 Jan 28 14:09 .. drwxrwxrwt 2 root root 4096 Jan 28 14:09 .ICE-unix -rw-rw---- 1 mysql mysql 0 Feb 24 11:55 #sql_7bbd_0.MYD -rw-rw---- 1 mysql mysql 1024 Feb 24 11:55 #sql_7bbd_0.MYI drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:41 ssh-5ZGoXWFwtQ drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:43 ssh-w9IkW0SvYo ... +----+------+-----------+ | id | a | sleep(10) | +----+------+-----------+ | 1 | a | 0 | | 2 | aa | 0 | | 3 | aaa | 0 | +----+------+-----------+ 3 rows in set (30.00 sec) 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, do 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 the .MYD and .MYI particular to the MyISAM engine. That last step would be unnecessary if we just used PERFORMANCE_SCHEMA to check the waits/io. 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 with the ENGINE keyword: mysql> CREATE TEMPORARY TABLE test (i serial) ENGINE=MyISAM; Query OK, 0 rows affected (0.00 sec) [ec2-user@jynus_com tmp]$ ls -la total 36 drwxrwxrwt 5 root root 4096 Feb 24 12:16 . dr-xr-xr-x 23 root root 4096 Jan 28 14:09 .. drwxrwxrwt 2 root root 4096 Jan 28 14:09 .ICE-unix -rw-rw---- 1 mysql mysql 8554 Feb 24 12:12 #sql7bbd_36a3_0.frm -rw-rw---- 1 mysql mysql 0 Feb 24 12:12 #sql7bbd_36a3_0.MYD -rw-rw---- 1 mysql mysql 1024 Feb 24 12:12 #sql7bbd_36a3_0.MYI drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:41 ssh-5ZGoXWFwtQ drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:43 ssh-w9IkW0SvYo mysql> DROP TEMPORARY TABLE test; Query OK, 0 rows affected (0.00 sec) [ec2-user@jynus_com tmp]$ ls -la total 20 drwxrwxrwt 5 root root 4096 Feb 24 12:17 . dr-xr-xr-x 23 root root 4096 Jan 28 14:09 .. drwxrwxrwt 2 root root 4096 Jan 28 14:09 .ICE-unix drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:41 ssh-5ZGoXWFwtQ drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:43 ssh-w9IkW0SvYo 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, 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.

from Planet MySQL http://ift.tt/1du18ol