{"id":656,"date":"2015-02-24T15:14:57","date_gmt":"2015-02-24T14:14:57","guid":{"rendered":"http:\/\/dbahire.com\/?p=656"},"modified":"2022-03-10T12:46:00","modified_gmt":"2022-03-10T11:46:00","slug":"acerca-del-formato-de-las-tablas-temporales-en-mysql-5-6","status":"publish","type":"post","link":"https:\/\/jynus.com\/dbahire\/acerca-del-formato-de-las-tablas-temporales-en-mysql-5-6\/","title":{"rendered":"Acerca del formato de las tablas temporales en MySQL 5.6"},"content":{"rendered":"

\"UnaLa variable default_tmp_storage_engine<\/code> se introdujo en 5.6.3, permitiendo la configuraci\u00f3n del motor por defecto para las tablas temporales. Esto parece ir en la direcci\u00f3n, como he comentado con anterioridad, de convertir MyISAM en un motor opcional<\/a>. En 5.7, se crea un espacio de tablas separado para guardar estas tablas con el objetivo de reducir su impacto en el rendimiento<\/a> (esas tablas no tienen se rehacerse si el servidor falla de manera inesperada, por lo que se evitan escrituras extra).<\/p>\n

Sin embargo, he visto mucha gente que asum\u00eda que porque el valor por defecto de default_tmp_storage_engine<\/code> es “InnoDB”, todas las tablas temporales se crean en formato InnoDB. Esto no es cierto: primero, porque las tablas temporales impl\u00edcitas se contin\u00faan creando en memoria usando el motor MEMORY<\/code> (a veces denominado HEAP<\/code>), mientras que MyISAM se usa para tablas en disco<\/strong>. Si no te f\u00edas para esto del manual de renferencia, aqu\u00ed tienes una prueba r\u00e1pida para comprobarlo:<\/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

La \u00fanica cosa que he hecho aqu\u00ed arriba es forzar la creaci\u00f3n de la tabla temporal en disco a\u00f1adiendo un campo de tipo TEXT (incompatible con el motor MEMORY, por lo que se fuerza su creaci\u00f3n en disco) y usar sleep para tener el suficiente tiempo como para comprobar el sistema de archivos. En la salida de ls<\/code> pod\u00e9is observar los .MYD y .MYI particulares del motor MyISAM. El \u00faltimo paso ser\u00eda innecesario si simplemente utilizaramos PERFORMANCE_SCHEMA<\/code> para comprobar los waits\/io<\/code>.<\/p>\n

Una segunda, y m\u00e1s obvia raz\u00f3n por la que pensar que todas las tablas temporales se crean en formato InnoDB es incorrecto, es porque las tablas temporales expl\u00edcitas pueden seguir cre\u00e1ndose en un motor distinto<\/a> con la palabra clave ENGINE<\/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

\u00bfCambiar\u00e1 esto en el futuro? 5.7.5 continua teniendo el mismo comportamiento que 5.6. Sin embargo, tal y como indic\u00f3 Stewart hace un tiempo<\/a>, las optimizaciones de rendimiento de 5.7 hacen que ciertos usos de MEMORY y MyISAM sean ahora obsoletos, por lo que no me sorprender\u00eda que esta dependencia -junto con la de las tablas de permisos MyISAM- desapareciera en un futuro.<\/p>\n

Actualizaci\u00f3n:<\/strong> Morgan<\/a> me ha contado por email que en la versi\u00f3n 5.7.6 (que todav\u00eda no se ha publicado en el momento de escribir estas l\u00edneas) cambiar\u00e1 finalmente el comportamiento por defecto para pasar a utilizar s\u00f3lo InnoDB para las tables temporales impl\u00edcitas tal y como se puede leer en las notas de actualizaci\u00f3n<\/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

que se puede traducir m\u00e1s o menos como:<\/p>\n

InnoDB: el valor por defecto de la opci\u00f3n internal_tmp_disk_storage_engine option, que define el motor por defecto que el servidor usa para las tablas temporales internas en disco (v\u00e9ase C\u00f3mo utiliza MySQL tablas temporales), tiene ahora el valor INNODB. Con este cambio, el optimizador usa ahora el motor de almacenamiento InnoDB en vez de MyISAM para las tablas temporales internas.<\/p><\/blockquote>\n

internal_tmp_disk_storage_engine<\/code> se introdujo en 5.7.5, pero su valor por defecto era MYISAM.<\/p>\n

Esto permitir\u00e1 una ventaja con el rendimiento de InnoDB en memoria para campos de tama\u00f1o variable, por lo que estoy 100% a favor. \u00a1Gracias Morgan por la informaci\u00f3n extra!<\/p>\n","protected":false},"excerpt":{"rendered":"

La variable default_tmp_storage_engine se introdujo en 5.6.3, permitiendo la configuraci\u00f3n del motor por defecto para las tablas temporales. Esto parece ir en la direcci\u00f3n, como he comentado con anterioridad, de convertir MyISAM en un motor opcional. En 5.7, se crea<\/p>\n","protected":false},"author":1,"featured_media":645,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[85],"tags":[86,163,293,291,295,183,297,184,457,287,289,299],"class_list":["post-656","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql-es","tag-5-6-es","tag-5-7-es","tag-default_tmp_storage_engine","tag-formato","tag-heap-es","tag-innodb-es","tag-memory-es","tag-myisam-es","tag-mysql-es","tag-tabla","tag-temporal","tag-temporary-es"],"_links":{"self":[{"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/posts\/656","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=656"}],"version-history":[{"count":7,"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/posts\/656\/revisions"}],"predecessor-version":[{"id":1045,"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/posts\/656\/revisions\/1045"}],"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=656"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/categories?post=656"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jynus.com\/dbahire\/wp-json\/wp\/v2\/tags?post=656"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}