EXPLAIN minor wishlist

EXPLAIN output: filesortWhile we always want better performance and more and larger features for MySQL, those cannot just “magically appear” from one version to another, requiring deep architecture changes and lots of lines of code. However, there are sometimes smaller features and fixes that could be implemented by an intern or an external contributor, mainly at SQL layer, and that could make the MySQL ecosystem friendlier to newbies and non-experts. Making a piece of software easier to use is sometimes overlooked, but it is incredibly important -not everybody using MySQL is a DBA, and the more people adopting it, more people will be able to live from it, both upstream and as third party providers.

Here it is my own personal list of fixes for EXPLAIN messages. If you are an experienced MySQL user you are probably aware of their meaning, but that doesn’t solve the problem for beginners. The reason why I am writing a blog post is to gather opinions on whether they seem important to you or not, and if my way of solving them seems reasonable so that we can submit them as feature requests.

EXPLAIN messages

As a MySQL instructor, the following case happens a lot with new students. You start with a command like this:

mysql> EXPLAIN SELECT b, c FROM test\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index
possible_keys: NULL
          key: b_c
      key_len: 10
          ref: NULL
         rows: 4
        Extra: Using index
1 row in set (0.00 sec)

So, “Using index” means that an index is being used, right? No, in this case, the type: index is telling us that it is using an index for scanning or accessing the rows (because it is not a type: ALL– although we could get a full row scan and using the index for ordering or grouping them). The Extra: Using index indicates that the index is also used for retrieving the data, without actually needing to read the whole row. This is, as far as I know, commonly referred as Covering index. And that is exactly what I would like to see:

mysql> EXPLAIN SELECT b, c FROM test\G -- edited output
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index
possible_keys: NULL
          key: b_c
      key_len: 10
          ref: NULL
         rows: 4
        Extra: Using covering index
1 row in set (0.00 sec)

or maybe:

        Extra: Covering index

Another common misunderstanding: Using filesort:

mysql> EXPLAIN SELECT * FROM test ORDER BY length(b)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using filesort
1 row in set (0.00 sec)

At this level, I do not care if I am using filesort as an algorithm, and -if I am correct- since 5.6 can also use a priority queue for the sorting algorithm if the number of items is small. Additionally, the “file” in the filesort word can lead to confusion that this requires a temporary table on disk. I do not have a perfect alternative (please provide feedback), but maybe something like the following would be clearer:

mysql> EXPLAIN SELECT * FROM test ORDER BY length(b)\G  -- edited output
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Additional sort phase
1 row in set (0.00 sec)

or maybe:

        Extra: Not using index for order-by

Another example would be:

mysql> EXPLAIN SELECT a FROM test WHERE b > 3 and c = 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: b_c
          key: b_c
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

I understand that the developers didn’t want to confuse us with NDB’s pushed condition, but this output is quite misleading, too. It literally means that “the index condition is being used”, instead of “ICP is being used”. What about:

mysql> EXPLAIN SELECT a FROM test WHERE b > 3 and c = 3\G -- edited output
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: b_c
          key: b_c
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using index condition pushdown
1 row in set (0.00 sec)

There are many other expressions, but those are the most annoying to me in terms of students’ confusion.

Would you agree with me? Would these changes break applications that may parse EXPLAIN output? What other small things would you change in MySQL output or error messages? I would specially would like to hear from MySQL beginners and people coming from other databases, as the more we have used to it, the more we get accustomed to MySQLisms.

Acerca del formato de las tablas temporales en MySQL 5.6

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

Sin embargo, he visto mucha gente que asumía que porque el valor por defecto de default_tmp_storage_engine es “InnoDB”, todas las tablas temporales se crean en formato InnoDB. Esto no es cierto: primero, porque las tablas temporales implícitas se continúan creando en memoria usando el motor MEMORY (a veces denominado HEAP), mientras que MyISAM se usa para tablas en disco. Si no te fías para esto del manual de renferencia, aquí tienes una prueba rápida para comprobarlo:

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)

La única cosa que he hecho aquí arriba es forzar la creación de la tabla temporal en disco añadiendo un campo de tipo TEXT (incompatible con el motor MEMORY, por lo que se fuerza su creación en disco) y usar sleep para tener el suficiente tiempo como para comprobar el sistema de archivos. En la salida de ls podéis observar los .MYD y .MYI particulares del motor MyISAM. El último paso sería innecesario si simplemente utilizaramos PERFORMANCE_SCHEMA para comprobar los waits/io.

Una segunda, y más obvia razón por la que pensar que todas las tablas temporales se crean en formato InnoDB es incorrecto, es porque las tablas temporales explícitas pueden seguir creándose en un motor distinto con la palabra clave ENGINE:

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

¿Cambiará esto en el futuro? 5.7.5 continua teniendo el mismo comportamiento que 5.6. Sin embargo, tal y como indicó Stewart hace un tiempo, las optimizaciones de rendimiento de 5.7 hacen que ciertos usos de MEMORY y MyISAM sean ahora obsoletos, por lo que no me sorprendería que esta dependencia -junto con la de las tablas de permisos MyISAM- desapareciera en un futuro.

Actualización: Morgan me ha contado por email que en la versión 5.7.6 (que todavía no se ha publicado en el momento de escribir estas líneas) cambiará finalmente el comportamiento por defecto para pasar a utilizar sólo InnoDB para las tables temporales implícitas tal y como se puede leer en las notas de actualizació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.

que se puede traducir más o menos como:

InnoDB: el valor por defecto de la opción internal_tmp_disk_storage_engine option, que define el motor por defecto que el servidor usa para las tablas temporales internas en disco (véase Cómo 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.

internal_tmp_disk_storage_engine se introdujo en 5.7.5, pero su valor por defecto era MYISAM.

Esto permitirá una ventaja con el rendimiento de InnoDB en memoria para campos de tamaño variable, por lo que estoy 100% a favor. ¡Gracias Morgan por la información extra!

Regarding MySQL 5.6 temporary tables format

A temporary tabledefault_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 in 5.6. 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, 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 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.

Update: I’ve been told by email by Morgan 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:

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.

internal_tmp_disk_storage_engine was introduced in 5.7.5, but its default value then was MYISAM.

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!

Felices fiestas y próspero 2015

Nos gustaría desearte unas felices fiestas en nombre del equipo de DBAHire.com. ¡Que todos vuestros sueños se hagan realidad en el 2015!

Happy holidays from Valencia!
Fotografía tomada en el mercado de navidad de Valencia (España) en diciembre de 2014.

¡Que vuestras innodb_buffer_pool_reads sean bajas y vuestro Uptime alto en 2015!

Mi Charla en la PyConES 2014 de Zaragoza sobre MySQL Fabric

pyconesEl próximo sábado 8 de noviembre a las 19:30 estaré hablando sobre MySQL Fabric en la PyConES 2014 (la versión española de la PyCon), la reunión anual de programadores y entusiastas de Python en España.
Aunque no me auto-denomino como programador, una gran parte de mi tiempo cono consultor de MySQL require implementar procedimientos automáticos (backups, comprobaciones de salud, gestión de AWS, …) y para ello utilizo principalmente una combinación de Python y Bash.

En mi charla, que he titulado “MySQL Fabric, a High Availability solution for Connector/Python” explicaré cómo montar y configurar un conjunto de servidores MySQL y aplicaciones clientes Python usando Connector/Python con el objetivo de proporcionar estabilidad en el servicio y rendimiento extra tanto para lecturas como para escrituras (gracias a sus capacidades de proporcionar sharding de manera semi-automática) para tu aplicación. El framework en sí mismo (Fabric, parte de las MySQL Utilities) es software libre y está siendo muy activamente desarrollado (también programado en Python, por supuesto!). Si en cualquier punto de tu carrera has sufrido de bajo rendimiento de tu base de datos, o pérdida de servicio, tienes que pasarte por mi charla! También lo compararé con otros productos relativamente similares, indicando los pros y contras de cada uno. La sesión se impartirá en inglés.

Como ponente, tengo un descuento para compartir con vosotros “DescuentoPonentes“, ya que todavía estáis a tiempo de registraros para el evento.

Os veo en Zaragoza!

Actualización: aquí tenéis las transparencias:

My Talk at PyConES 2014 Zaragoza about MySQL Fabric

pyconesNext Saturday, 8 November 2014, at 19:30 I will be speaking about MySQL Fabric for PyConES 2014 (the Spanish version of the PyCon), the annual meeting point for all developers and enthusiasts of Python in Spain.
While I say myself that I am not a developer, a lot of my time as a MySQL consultant requires implementing automatic procedures (backups, health checks, AWS management, …) and for that I mainly use a combination of Python and Bash.

In my talk, which I have titled “MySQL Fabric, a High Availability solution for Connector/Python” I will explain how to setup and configure a set of MySQL servers and Python application clients using Connector/Python in order to provide service resiliency and extra performance for both reads and writes (thanks to its semi-automatic sharding capabilities) on your application. The framework itself (Fabric, part of the MySQL Utilities) is open source and under heavy development (also programmed in Python, of course!). If at any point in your career you suffered from bad database performance or application downtime, you must come to my talk! I will also compare it to other relatively similar solutions, providing its pros and cons. The session will be delivered in English.

As a speaker, I have a discount code to share with you “DescuentoPonentes“, as there is still time to register for the event.

See you in Zaragoza!

Update: Here you have the slides:

Mi tutorial sobre optimización de consultas para la Percona Live London 2014 (e información importante si deseáis asistir)

Percona Live London es la próxima semana!El lunes de la próxima semana, 3 de noviembre, impartiré un tutorial de la mayor conferencia europea de MySQL, la Percona Live London 2014. El tema es una continuación natural de la que impartí el año pasado en el mismo lugar, “Optimización de consultas con MySQL 5.6: trucos nuevos y viejos“. Este año me centraré en los últimos cambios en el optimizador que podemos encontrar no sólo en los ya publicados 5.6 y MariaDB 10, pero también alguna de las características de los todavía en desarrollo MySQL 5.7 y MariaDB 10.1. Algunos de los temas de este taller, que he titulado “Optimización de consultas con MySQL 5.7 y MariaDB 10: más trucos que nunca“, incluyen: el nuevo optimizador de 5.7 basado en costes, columnas virtuales, api para plugins de reescritura de consultas, nuevos métodos de JOIN, optimización de subconsultas, cambios en los modos sql, búsqueda de cadenas full text search y mejoras en GIS. Todo ello con ejemplos fáciles de seguir y prácticas en ordenador.

Aquí tenéis la agenda completamente detallada (se impartirá en inglés):

  1. Introduction
  2. Break (VM installation)
  3. General Optimizer Improvements
  4. Computed/Virtual Columns
  5. Query Rewrite Plugins
  6. SQL Mode Changes
  7. Join Optimization
  8. Subquery Optimization
  9. Fulltext search
  10. GIS Improvements
  11. Break
  12. Query Profiling
  13. Results and Conclusions
  14. Q&A

Actualización: Aquí tenéis las transparencias completas:

Todos los materiales necesarios están en: dbahire.com/pluk14

INFORMACIÓN IMPORTANTE: si estás pensando en asistir a este tutorial, para evitar problemas de conectividad de último minuto, por favor, descarga con antelación todos los archivos necesarios tal y como se indica en la guía del tutorial. Estos incluyen la base de datos de ejemplo, así como la instalación del servidor 5.7/MariaDB.

Aunque proporcionaré llaves USB con esos mismos archivos, y podrás seguir la explicación tan sólo viendo mi pantalla ya que mostraré todo por mí mismo, sin embargo podrás sacar mucho más partido al tutorial si te tomas 5 minutos para preparar tu sistema por adelantado.

Regalaré varios pendrives entre aquellos que se tomen el tiempo para preparar sus sistemas con anterioridad y asistan a mi tutorial, como un gesto de agradecimiento para hacer la sesión más ágil. Mencióname en Twitter diciendo algo como “Ya he preparado todo para el tutorial de @dbahire_es http://dbahire.com/pluk14 #perconalive”, de manera que pueda reservarte uno!

Os veo el próximo martes a las 9:00 hora de Londres en el “Orchard 2”.

My Tutorial on Query Optimization for Percona Live London 2014 (and Important Information If You Wish to Attend)

Percona Live London is next week!Monday next week, on November 3rd, I will be delivering a tutorial on the greatest MySQL European Conference, the Percona Live London 2014. The topic is a natural continuation of the one I delivered last year on the same venue, “Query Optimization with MySQL 5.6: Old and New Tricks“. This year I will be focusing on the newest optimizer changes that we can find not only in the already published 5.6 and MariaDB 10, but also some of the latest features in the still in development MySQL 5.7 and MariaDB 10.1. Topics on this workshop, which I have titled “Query Optimization with MySQL 5.7 and MariaDB 10: Even Newer Tricks“, will include: new 5.7 cost-based optimizer, virtual columns, query rewriter plugin api, new join methods, subquery optimization, sql mode changes, full text search and GIS improvements. All of it with easy-to follow examples and hands-on exercises.

Here you have the full detailed agenda:

  1. Introduction
  2. Break (VM installation)
  3. General Optimizer Improvements
  4. Computed/Virtual Columns
  5. Query Rewrite Plugins
  6. SQL Mode Changes
  7. Join Optimization
  8. Subquery Optimization
  9. Fulltext search
  10. GIS Improvements
  11. Break
  12. Query Profiling
  13. Results and Conclusions
  14. Q&A

Update: Here you have the full deck of slides:

All necessary materials are on: dbahire.com/pluk14

IMPORTANT INFORMATION: if you plan to attend this tutorial, in order to avoid last-minute connectivity problems, please pre-download all necessary files as indicated in the tutorial guide. These include the example database and the 5.7/MariaDB server installation.

While I will be handling removable media with those same files, and you will be able to follow the explanation fully just by watching my screen, as I will show everything myself, but you will get much more out of the tutorial if you took 5 minutes to prepare your system in advance.

I will be gifting several usb drives among those that take the time to setup their systems beforehand and attend my tutorial as a thank you for helping make the session smoother. Mention me on twitter saying something like “I already have everything prepared for the @dbahire_en tutorial http://dbahire.com/pluk14 #perconalive”, so I can reserve yours!

See you next Monday at 9:00 London time at Orchard 2.

Probando la manera más rápida de importar una tabla en MySQL (y unos resultados interesantes del rendimiento de 5.7)

Como mencioné en mi anterior entrada, donde comparaba las opciones de configuración por defecto de 5.6 y 5.7, he estado haciendo algunos tests para un tipo particular de carga en varias versiones de MySQL. Lo que he estado comprobando es las diferentes maneras de cargar un archivo CSV (el mismo fichero que usé para comprobar las herramientas de compresión) en MySQL. Aquellos administradores de bases de datos y programadores MySQL con experiencia probablemente ya conozcáis la respuesta, así que podéis saltar a los resultados de 5.7 respecto de 5.6. Sin embargo, la primera parte de este post está dedicado a los desarrolladores/aquellos que dan sus primeros pasos con MySQL que desean saber la respesta a la pregunta del título, paso a paso. Debo decir que yo también he aprendido algo, ya que sobrestimé e infravaloré algunos efectos de ciertas opciones de configuración para este tipo de carga.

Aviso: no pretendo realizar benchmarks formales, la mayoría de los resultados obtenidos aquí se han creado ejecutándose un par de veces, y muchos de ellos con la configuración por defecto. Esto ha sido por diseño, ya que intento mostrar alguna de las “malas prácticas” para la gente que acaba de empezar a trabajar con MySQL, y lo que deberían evitar hacer. Tan sólo la comparativa entre 5.6 y 5.7 me ha llamado la antención. Aviso adicional: No me llamo a mí mismo programador, y mucho menos un programador de Python, así que me disculpo por adelantado por mi código- después de todo, esto va de MySQL. El enlace de descarga de los scripts está al final del post.

Las reglas

Empiezo con un archivo CSV (recordad que realmente es un archivo de valores separado por comas, TSV) que tiene 3.700.635.579 bytes de tamaño, y 46.741.126 filas y tiene la siguiente pinta:

171773  38.6048402      -0.0489871      4       2012-08-25 00:37:46     12850816        472193  rubensd
171774  38.6061981      -0.0496867      2       2008-01-19 10:23:21     666916  9250    j3m
171775  38.6067166      -0.0498342      2       2008-01-19 10:23:21     666916  9250    j3m
171776  38.6028122      -0.0497136      5       2012-08-25 00:26:40     12850816        472193  rubensd
171933  40.4200658      -3.7016652      6       2011-11-29 05:37:42     9984625 33103   sergionaranja 

Quiero cargarlo en la siguiente tabla, con la estructura que muestro a continuación:

CREATE TABLE `nodes` (
  `id` bigint PRIMARY KEY,
  `lat` decimal(9,7),
  `lon` decimal(10,7),
  `version` int,
  `timestamp` timestamp,
  `changeset` bigint,
  `uid` bigint,
  `user` varchar(255)
);

El tiempo de importación termina en el momento que la tabla es crash safe (resistente a fallos) (aunque quede E/S pendiente). Eso quiere decir que para InnoDB, el último COMMIT realizado tiene que haberse hecho de manera satisfactoria y flush_log_at_trx_commit debe valer 1, de manera que aunque queden operaciones de escritura por hacerse, es completamente consistente con disco. Para MyISAM, eso quiere decir que fuerzo la ejecución de un FLUSH TABLES antes de terminar el test. Estos, por supuesto, no son equivalentes, pero al menos es una manera de asegurarse de que todo está más o menos sincronizado con disco. Ésta es la parte final de todos mis scripts:

# finishing
if (options.engine == 'MyISAM'):
    cursor.execute('FLUSH TABLES')
else:
    db.commit()

cursor.close()
db.close()

Para el hardware y sistema operativo, comprobad los detalles de máquina de este post antiguo– usé el mismo entorno que el mencionado aquí, con la excepción de usar CentOS7 en vez de 6.5.

Empezando con un método muy ingenuo

Digamos que soy un desarrollador al que le han asignado cargar de manera regular un archivo en MySQL, ¿cómo lo haría? Probablemente estaría tentado de usar una biblioteca de parseo de CSV, el conector de MySQL, y los uniría a ambos juntos en un bucle. ¿Eso funcionaría, no? La parte principal del código sería algo como esto (load_data_01.py):

# import
insert_node = "INSERT INTO nodes (id, lat, lon, version, timestamp, changeset, uid, user) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"

with open(CSV_FILE, 'rb') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter='\t')
    for node in csv_reader:
        cursor.execute(insert_node, node)

Como estoy interpretando el papel de un desarrollador sin experiencia, también usaría la configuración por defecto. Veamos qué resultados obtenemos (de nuevo, esta es la razón por la cual llamo a esto “pruebas”, y no benchmarks). Menos es mejor:
load_data_01

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
Load time (seconds) 4708.594 6274.304 6499.033 6939.722

Guau, ¿5.1 es un 50% más rápido que el resto de versiones? Incorrecto, recordad que 5.5 fue la primera versión en introducir InnoDB como el motor por defecto, e InnoDB tiene una sobrecarga debido a las transacciones, y normalmente no tiene una muy buena configuración por defecto (al contrario que MyISAM, que estan simple que las opciones por defecto pueden funcionar en muchos casos). Normalicemos los resultados por motor de almacenamiento:
load_data_01_by_engine

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
MyISAM 4708.594 5010.655 5149.791 5365.005
InnoDB 6238.503 6274.304 6499.033 6939.722

Esto ya parece más razonable, ¿no? Sin embargo, en este caso, parece haber una ligero empeoramiento en el rendimiento de un sólo hilo a la vez que las versiones van progresando, especialemnte en MySQL 5.7. Por supuesto, es pronto para sacar conclusiones, porque este método de carga de un archivo CSV, fila a fila, es uno de los más lentos, y además estamos usando unas opciones de configuración muy pobres (las que vienen por defecto) -las cuales varían de versión a versión- y que no deberían usarse para llegar a ninguna conclusión.

Lo que podemos decir es que MyISAM parece funcionar mejor para este escenario muy específico por las razones que comentaba antes, pero aún así tardaríamos de 1 a 2 horas en cargar un archivo tan sencillo.

Otro método incluso más ingenuo

La siguiente pregunta no es: ¿podemos hacerlo mejor? sino ¿podemos hacerlo incluso más lento? Un texto en particular me llamó la atención mientras miraba la documentación del conector de MySQL:

Since by default Connector/Python does not autocommit, it is important to call this method after every transaction that modifies data for tables that use transactional storage engines.

que es algo así como:

Debido a que por defecto Connector/Python no realiza autocommit, es importante llamar a este método después de cualquier transacción que modifique datos en tablas de motores transaccionales.

-de la documentación de connector/python

Pensé- ¡ah!, entonces podemos reducir el tiempo de importación realizando commit para cada fila de la base de datos, una a una, ¿no? Después de todo, estamos insertando en la tabla en una única transacción enorme. ¡Seguro que crear un enorme número de transacciones será mejor! 🙂 Este es el código ligeramente modificado (load_data_02.py):

insert_node = "INSERT INTO nodes (id, lat, lon, version, timestamp, changeset, uid, user) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"

with open('/tmp/nodes.csv', 'rb') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter='\t')
    for node in csv_reader:
        cursor.execute(insert_node, node)
        db.commit()

Y en este caso no tengo ni siquiera un bonito gráfico que enseñaros, porque después de 2 horas, 19 minutos y 39.405 segundos, cancelé la importación porque sólo se habían insertado 111533 nodos en MySQL 5.1.72 con InnoDB con la configuración por defecto (innodb_flush_log_at_trx_commit = 1). Obviamente, millones de fsyincs no harán que la carga sea más rápida, considerad esto como una lección aprendida.

Haciendo progresos: multi-inserts

El siguiente paso que quería probar es cómo de efectivo es agrupar consultas en inserciones múltiples. Este método lo usa mysqldump, y supuesta mente minimiza la carga extra de SQL por tener que gestionar cada consulta por separado (parseo, comprobación de permisos, plan de la consulta, etc.). Éste es el código principal (load_data_03.py):

concurrent_insertions = 100
[...]
with open(CSV_FILE, 'rb') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter='\t')
    i = 0
    node_list = []
    for node in csv_reader:
        i += 1
	node_list += node
        if (i == concurrent_insertions):
            cursor.execute(insert_node, node_list)
            i = 0
            node_list = []
    csv_file.close()

# insert the reminder nodes
if i > 0:
    insert_node = "INSERT INTO nodes (id, lat, lon, version, timestamp, changeset, uid, user) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
    for j in xrange(i - 1):
        insert_node += ', (%s, %s, %s, %s, %s, %s, %s, %s)'
    cursor.execute(insert_node, node_list)

Lo probamos con, por ejemplo, 100 filas insertadas en cada consulta. ¿Cuáles son los resultados? Menos es mejor:
load_data_03.py results

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
MyISAM 1794.693 1822.081 1861.341 1888.283
InnoDB 3645.454 3455.800 2849.299 3032.496

Con este método observamos una mejora en el tiempo de importación de 262-284% respecto del tiempo original para MyISAM y de 171-229% respecto del tiempo original de InnoDB. Recordad que este método no escalará indefinidamente, ya que encontraremos un límite en el tamaño de paquete si intentamos insertar demasiadas filas a la vez. Sin embargo, se trata de una clara ventaja sobre el insertado fila a fila.

Los tiempos para MyISAM son esencialmente los mismos entre versiones, mientras que InnoDB muestra una mejora a lo largo del tiempo (que podrían deberse a los cambios en código y la optimización, pero también a la configuración por defecto del tamaño del log de transacciones), excepto de nuevo entre 5.6 y 5.7.

El método correcto para importar datos: Load Data

Si se tiene un mínimo de experiencia con MySQL, conoceréis la existencia de una palabra clave especializada para importaciones, y ésta es LOAD DATA. Veamos como quedaría el código usando esta opción (load_data_04.py):

# data import
load_data = "LOAD DATA INFILE '" + CSV_FILE + "' INTO TABLE nodes"

cursor.execute(load_data)

¿Simple, verdad? Con esto estamos minimizando la sobrecarga de SQL, y ejecutando el bucle en código C compilado de MySQL. Echemos un vistazo a los resultados (menos es mejor):
load_data_04.py results

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
MyISAM 141.414 149.612 155.181 166.836
InnoDB 2091.617 1890.972 920.615 1041.702

En este caso, MyISAM tiene una mejora dramática –LOAD DATA convierte la importación en 12 veces más rápida. InnoDB, de nuevo todavía con los parámetros por defecto mejora en 3 veces, y más significativamente en las versiones más recientes (5.6, 5.7) que en las anteriores (5.1, 5.5). Mi predicción es que esto tiene mucho más que ver con la diferente configuración de los archivos de log que con cambios en el código.

Intentado mejorar Load Data para MyISAM

¿Podemos mejorar los tiempos de carga para MyISAM? Hay dos cosas que podemos hacer -aumentar el tamaño de key_cache_size y deshabilitar Performance Schema para 5.6 y 5.7. Establecí el tamaño de la caché de índices (key_cache_size) en 600M (intentado que cupiera la clave primaria en memoria) y cambié performance_schema = 0, probando las 3 combinaciones restantes. Menos es mejor:
load_data_04.py results for myisam

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
default 141.414 149.612 155.181 166.836
key_buffer_size=600M 136.649 170.622 182.698 191.228
key_buffer_size=600M, P_S = OFF 133.967 170.677 177.724 186.171
P_S = OFF 142.592 145.679 150.684 159.702

Hay varias cosas que destacar aquí:

  • P_S=ON and P_S=OFF no debería tener un efecto en MySQL 5.1 y 5.5, pero se muestran resultados diferentes debido a errores de medida. De ahí podemos entender que sólo 2 cifras son realmente significativas.
  • key_buffer_cache, en general, no mejora el rendimiento, de hecho se podría decir que estadísticamente la empeora. Esto es razonable porque, después de todo, estoy escribiendo a la caché de archivos, y una caché de claves mayor puede requerir una reserva o copia de memoria más costosa. Esto debería investigarse más a fondo para llegar a una conclusión.
  • Performance_schema podría estar empeorando el rendimiento de esta carga, pero no estoy seguro estadísticamente hablando.
  • MyISAM (o quizás el servidor MySQL) parece haber empeorado su rendimiento para este tipo de carga (carga por lotes con un sólo hilo).

Hay más cosas que me gustaría probar con MyISAM, como ver el impacto de los diferentes formatos de fila (fixed) pero me gustaría continuar con los otros motores.

Intentado mejorar Load Data en InnoDB

InnoDB es un motor mucho más interesante, ya que es ACIDo por defecto, y más complejo. ¿Podemos hacerlo tan rápido como MyISAM para una importación?

La primera cosa que quiero cambiar son los valores por defecto de innodb_log_file_size y innodb_buffer_pool_size. El log es diferente antes y después de 5.6, y no es adecuado para una gran carga de escritura. Lo cambié para un primer test a 2G, que es el mayor tamaño que 5.1 y 5.5 pueden usar (lo puse a 2.147.483.136 ya que tiene que se menor de 2G), lo cual quiere decir que tenemos un tamaño total de los logs de 4G. También cambié el buffer pool a una tamaño más conveniente, 8GB, suficiente para albergar el tamaño completo del conjunto de datos. Recordad que una de las razones por las cuales InnoDB es tan lento a la hora de realizar importaciones es porque escribe las nuevas páginas (al menos) dos veces a disco -en el log, y en el espacio de tablas. Sin embargo, con estos parámetros, la segunda escritura debería realizarse en su mayoría a través del buffer de memoria. Estos son los nuevos resultados (menos es mejor):
load_data_04.py innodb results

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
default 1923.751 1797.220 850.636 1008.349
log_file_size=2G, buffer_pool=8G 1044.923 1012.488 743.818 850.868

Ahora esto es un test que parece más razonable. Podemos comentar que:

  • La mayoría de las mejoras que teníamos antes en 5.6 y 5.7 respecto a 5.1 y 5.5 se debían al tamaño de log 10 veces más grande.
  • Aún así, 5.6 y 5.7 son más rápidos que 5.1 y 5.5 (algo razonable, ya que 5.6 ha visto unos cambios en InnoDB impresionantes, tanto en código como en configuración)
  • InnoDB continúa siendo al menos 5 veces más lento que MyISAM
  • ¡Y 5.7 continúa siendo más lento que 5.6! Estamos teniendo una regresión consistente de un 13-18% en 5.7 (y me estoy empezando, ahora sí, a preocupar)

Comenté antes que el mayor overhead de InnoDB es escribir los datos 2 veces (al log y a las tablas). Esto es en realidad incorrecto, ya que podría tener que escribirlos 3 veces (al área de doble escritura) e incluso 4, en el log binario. El log binario no está activo por defecto, pero el double write sí, ya que permite protegernos de la corrupción. Aunque nunca recomendamos deshabilitar esta estructura en producción, la verdad es que en una importación nos da igual si los datos terminan corruptos (ya que podríamos borrarlos y volver a reimportarlos). También hay opciones en algunos sistemas de archivos para evitar tener que activarlo.

Otras características que están en InnoDB por seguridad, no por rendimiento, son los checksums de InnoDB- éstos incluso se convierten en el cuello de botella en sistemas de almacenamiento utrarrápidos como tarjetas flash PCI. En estos casos, ¡la CPU es demasiado lenta para calcularlos! Sospecho que esto no será un problema en este caso porque las versiones más modernas de MySQL (5.6 y 5.7) tienen la opción de cambiarlo por la función acelerada por hardare CRC32 y, mayormente, porque estoy usando discos magnéticos, que son el verdadero cuello de botella aquí. Pero no nos fiemos de lo que hemos aprendido y pongámoslo a prueba.

La otra cosa que podemos comprobar es la sobrecarga de performance_schema. He encontrado casos en los que por el tipo de operaciones produce tiene un efecto negativo bastante notable, mientras que casi ninguno en otros. Probemos también a activarlo y deshabilitarlo.

Estos son los resultados (menos es mejor):
load_data_04.py results for innodb optimized

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
default security and monitoring enabled 1044.923 1012.488 743.818 850.868
doublewrite=off 896.423 848.110 483.542 468.943
doublewrite=off,checksums=none 889.827 846.552 488.311 476.916
doublewrite=off,checksums=none,P_S=off 488.273 467.716

Hay varias cosas que comentar aquí, alguna de las cuales ni siquiera soy capaz de proporcionar una explicación:

  • El doublewrite no reduce el rendimiento a la mitad, pero impacta de manera significativa en el rendimiento (entre un 15% y un 30%)
  • Sin la escritura doble, la mayoría de la regresión de 5.7 desaparece (¿por qué?)
  • El doublewrite es más significativo en 5.6 y 5.7 que en versiones previas de MySQL. Me atrevería a decir que la mayoría de otros cuellos de botella han sido eliminados (¿o quizá sea algo como las particiones del buffer pool estando activas por defecto?)
  • Los checksums de innodb no producen absolutamente ninguna diferencia para este tipo de carga y hardware en particular.
  • De nuevo, no puedo dar significado estadístico a una pérdida de rendimiento del performance schema. Sin embargo, he obtenido resultados muy variables en los tests, con resultados como un 10% de latencia extra en los valores centrales respecto aquellas con él deshabilitado, pero no estoy 100% seguro de esto.

En resument, con unos pocos cambios aquí y allí, podemos obtener resultados que tan sólo son el doble de lentos que MyISAM, en vez de 5 veces o 12 veces.

Importar en MyISAM, convertir a InnoDB

He visto a veces a algunas personas en los foros recomendar la importación de una tabla en formato MyISAM, y después convertirla a InnoDB. Veamos si podemos pillar o confirmar este mito con el siguiente código (load_data_06.py):

load_data = "LOAD DATA INFILE '/tmp/nodes.csv' INTO TABLE nodes"
alter_table = "ALTER TABLE nodes ENGINE=InnoDB"

cursor.execute(load_data)
cursor.execute(alter_table)

Esta es la comparación (menos es mejor):
load_data_06.py results

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
LOAD DATA InnoDB 1923.751 1797.220 850.636 1008.349
LOAD DATA MyISAM; ALTER TABLE ENGINE=InnoDB 2075.445 2041.893 1537.775 1600.467

Puedo ver cómo esto podría ser casi cierto en 5.1, pero es claramente falso en todas las versiones soportadas de MySQL. Sin embargo, tengo que remarcar que es más rápido que importar la tabla dos veces, una para MyISAM y otra para InnoDB.

Dejaré como deberes para el lector comprobarlo para otros motores, como MEMORY o CSV [Pista: Quizá podemos importar a éste último motor de una manera diferente].

Carga de manera paralela

MyISAM escribe a las tablas usando un bloqueo a nivel de tabla (aunque en algunos casos es posible realizar inserts concurrentes), pero InnoDB sólo requiere un bloqueo a nivel de fila en muchos casos. ¿Podríamos acelerar el proceso realizando una importación en paralelo? Esto es lo que quise probar con mi último test. No confío en mis habilidades de programación (o más bien no tengo mucho tiempo) para realizar el movimiento de cursor de archivo y el particionamiento de manera eficiente, así que comenzaré con un .csv ya dividido en 8 trozos. No debería llevar mucho tiempo, pero las herramientas limitadas de la librería de threads por defecto, así como mi falta de tiempo me hicieron optar por este plan. Tan sólo debemos ser conscientes de que no comenzamos con el mismo escenario exacto en este caso. Este es el código (load_data_08.py):

NUM_THREADS = 4

def load_data(port, csv_file):
    db = mysql.connector.connect(host="localhost", port=port, user="msandbox", passwd="msandbox", database="test")
    cursor = db.cursor()
    load_data_sql = "LOAD DATA INFILE '" + csv_file + "' INTO TABLE nodes"
    cursor.execute(load_data_sql)   
    db.commit()

thread_list = []

for i in range(1, NUM_THREADS + 1):
   t = threading.Thread(target=load_data, args=(port, '/tmp/nodes.csv' + `i`))
   thread_list.append(t)

for thread in thread_list:
   thread.start()

for thread in thread_list:
   thread.join()

Y estos son los resultados, con diferentes parámetros (menos es mejor):
load_data_08.py results

MySQL Version 5.1.72 5.5.39 5.6.20 5.7.4
1 thread, log_file_size=2G, buffer_pool=8G 894.367 859.965 488.273 467.716
8 threads, log_file_size=2G, buffer_pool=8G 752.233 704.444 370.598 290.343
8 threads, log_file_size=5G, buffer_pool=20G 301.693 243.544
4 threads, log_file_size=5G, buffer_pool=20G 295.884 245.569

En este método podemos ver que:

  • Hay pocos cambios de rendimiento entre realizar la carga paralela con 4 u 8 threads. Esta es una máquina con 4 cores (8 HT)
  • La paralelización ayuda, aunque no escala (4-8 hilos proporcionan alrededor de un 33% de velocidad)
  • Éste es el caso en le que 5.6 y especialmente 5.7 destacan
  • Un log de transacciones y un buffer pool mayor (de más de 4G, sólo disponible en 5.6+) siguen siendo útiles para reducir la carga
  • La carga paralela con 5.7 es la manera más rápida en la que puedo cargar este archivo usando InnoDB: 243 segundos. Es 1.8x veces la manera más rápida en la que puedo cargar una tabla en MyISAM (5.1, monohilo): 134 seconds. ¡Esto son casi 200K filas/s!

Resumen y preguntas abiertas

  • La manera más rápida en la que puedes importar una tabla en MySQL sin usar archivos binarios es usando la sintaxis LOAD DATA. Usa la paralelización en InnoDB para mejorar los resultados, y recuerda ajustar parámetros básicos como el tamaño de archivo de transacciones y el buffer pool. Una programación y imporación hecha con el debido cuidado pueden convertir un problema de más de 2 horas en un proceso de apenas 2 minutos. Es posible deshabilitar temporalmente algunas características de seguridad para obtener rendimiento extra.
  • Parece haber una importante regresión en 5.7 para esta carga en particular de una importación monohlo tanto para MyISAM como para InnoDB, con unos resultados hasta un 15% peores en rendimiento que 5.6. Todavía no sé porqué.
  • En el aspecto positivo, parece haber un aumento (de hasta el 20%) en relación con 5.6 en una carga paralela de escrituras.
  • Es posible que haya un impacto en el rendimiento de Performance schema para este tipo de operación, pero soy incapaz de medirlo de manera fiable (es más cercando a 0 que mi error de medida). Esto es algo positivo.

Estaría agradecido si me pudierais indicar cualquier error en alguna de mis suposiciones.

Aquí podéis descargar los diferentes scripts de Python usados para probar la carga de datos en MySQL.

Recordad que esto no son benchmarks “formales”, y que ya no dispongo de acceso a la máquina donde los generé. Todavía tengo pendiente el analizar si existe el mismo problema en 5.7.5. Hay otras personas apuntando a regresiones bajo limitada concurrencia, como Mark Callaghan, quizá estén relacionados? Como de costumbre, podéis dejarme un comentario aquí o mandarme una respuesta mediante Twitter.