Personal Summary of the Percona Live Amsterdam 2015 Conference

perconalive_europe_amsterdam-logoLast week, 21-23 September, it took place the European MySQL Conference, or “Data performance Conference” as this year’s subtitle was “MySQL. NoSQL. Data in the cloud.”. This year, it changed its location from London to Amsterdam and, as most people I talked to agreed, the change was for good. As every year, Percona was the company organizing it, but it had the participation of all the major players in the open source MySQL/MongoDB/Cloud data world. Special mention goes to Booking.com, which had more talks than usual (despite being one of the largest MySQL users out there), and were the hosts of the community diner (and probably one of the main reasons to moving to Amsterdam, as their main HQ is at that city).

While MySQL was still the king in terms of interest, I saw a growing interest for MongoDB, both from Percona (now owning TokuDB, and releasing its own Percona Server version of Mongo) and from Facebook, promoting its project RocksDB (its much improved fork of LevelDB, with both Mongo and MySQL frontends). Both are interesting offerings for write-heavy workloads with better compression ratios than other engines, but lacking of some features to be ready for everybody’s production usage. However, I have high expectations from both projects.

There were also many talks about MySQL high availability models, with different points of view. While some are pushing for its the upstream GTID-based setup, others opt for alternative GTID implementations; others, for the usage of dedicated binlog servers, while others have a hybrid approach.

Obviously, MySQL folks from Oracle were present and gave many first party sessions about the soon-to-be released 5.7 and its new features regarding query optimization, performance_schema, internals, etc. But the best part of the conference was, without doubt, being able to interact with the developers and suggesting features for 5.8, face-to-face. I think many people loved that and it should be a regular thing from now on.

In a more personal way, for me it was great to devirtualize some people for the first time, plus get to catch up with others I already knew about both professionaly and personally. I had the chance to talk to people from all the previously mentioned companies (Percona, Facebook/RocksDB, Booking, Oracle) and also representatives, developers and DBAs from Google, Vividcortex, Solarwind, MariaDB, VMWare, Galera, Pythian, Github, Freelancers and more.

jaime-presentationI was a speaker in one tutorial and a session: “Query optimization: From 0 to 10 (and up to 5.7)” and “MySQL at Wikimedia: How we do relational data at the Wikimedia Foundation”. I had a full room for the first tutorial, while having very good feedback on the second one, with many people wanting to either contribute or apply for a job with us.

I’ve seen that many of the other speakers have not yet uploaded they deck of slides, and I want to encourage to do it (sadly there were so many at the same time that I could not attend to all of them). You can see and download mine at Slideshare, too: Query Optimization Tutorial / MySQL at Wikipedia. Both are released with Creative Commons Attribution-ShareAlike-4.0 licenses and you even can get the source file of the 238-slide tutorial, developed over almost 2 years while I was a freelance DBA, and improve it, remix it or modify it as you want, if you keep it equally free.

Additionally, a reminder that if someone wants to contribute to our (Wikimedia’s) infrastructure, we are glad to accept patches from the community on our puppet repository and also have several remote positions open, including a Database Administrator profile.

See you next year!

DBAHire.com patrocina el “IX Concurso universitario de software libre”

CUSL (Foto de Familia)
Foto: Ana Rey CC-BY-SA-2.0
El “Concurso Universitario de Software Libre” (CUSL) es una iniciativa similar a la del Google Summer of Code, pero específicamente dirigida la comunidad universitaria y de estudiantes de bachillerato españoles y organizada por una grupo de Oficinas de software libre de universidades.

Como parte del esfuerzo para promover el crecimiento del ecosistema del software libre, así como introducir a talento joven en el desarrollo de aplicaciones y tecnologías libres, DBAHire.com acaba de convertirse en patrocinador plata de la competición, proporcionando recursos para los premios, gastos de desplazamiento y alojamiento de los estudiantes.

La fase final de la competición tendrá lugar el próximo 7-8 de Mayo en Zaragoza, y nuestro consultor MySQL Jaime Crespo impartirá ese viernes una pequeña charla titulada “Software libre ¿es rentable?”.

DBAHire.com sponsors the “IX Concurso Universitario de Software Libre”

CUSL (Foto de Familia)
Photo: Ana Rey CC-BY-SA-2.0
The “Concurso Universitario de Software Libre” (CUSL, Free Software University Contest), is an initiative similar to the Google Summer of Code, but specifically aimed to the Spanish university and high school students and organized by a group of Free Software University Offices.

As part of the effort to encourage the growth of the free software ecosystem, and introduce young talent to the development of free applications and technologies, DBAHire.com has just become a silver sponsor of the competition, providing resources for the prizes, traveling and accommodation to the students..

The final phase of the competition will take place on the 7-8 May in Zaragoza, and our MySQL consultant Jaime Crespo will deliver on that Friday a short speech in Spanish titled “Free Software ¿Is it profitable?”.

Stop using FLUSH PRIVILEGES

Mermaids have the same probability of fixing your permission problems, but people continue believing in the FLUSH PRIVILEGES myth.
Mermaids have the same probability of fixing your permission problems, but people continue believing in the FLUSH PRIVILEGES myth.
I see suggesting the usage of FLUSH PRIVILEGES every time someone writes a tutorial or a solution to a problem regarding creating a new account or providing different privileges. For example, the top post on /r/mysql as of the writing of these lines, “MySQL:The user specified as a definer does not exist (error 1449)-Solutions” has multiple guilty cases of this (Update: the user has corrected those lines after I posted this article).

It is not my intention to bash that post, but I have seen committing that mistake many, many times. Even if you go to the reference manual for the GRANT command, you will see a comment at the bottom -from a third party user- using GRANT and then FLUSH PRIVILEGES.

Why should I bother? Is executing FLUSH PRIVILEGES an issue? Why is everybody doing it? The reason why that command exists is because —in order to improve performance— MySQL maintains an in-memory copy of the GRANT tables, so it does not require to read it from disk on every connection, every default database change and every query sent to the server. The mentioned command forces the reload of this cache by reading it directly from disk (or the filesystem cache) as the MySQL reference manual itself clearly indicates (having even its own section: When Privilege Changes Take Effect). However, its execution is unnecessary in most practical cases because:

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

Then only reason to perform that reload operation manually is when:

you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE

For most operations, like creating a user, changing its privileges, or changing its password, you will want to use the high-level statements. Not only they are easier to use and they are compatible with a larger number of MySQL versions, but they will also prevent you from making mistakes (of course, remember to setup the NO_AUTO_CREATE_USER sql mode). They even usually work nicely in a MyISAM-hostile environment like a Galera cluster. There are certainly reasons to edit the tables manually- as an administrator, you may want to tweak the privileges in a special way or import the mysql.* tables from elsewhere, so in those cases running FLUSH PRIVILEGES is mandatory. Please note that, as the manual page says, in most cases (e.g. global privileges) changing a user’s grants will only affect new connections, and certainly never to ongoing queries, as privileges are checked at the beginning of the query processing- read the manual page for details.

So, again, why my crusade against the overuse of FLUSH PRIVILEGES, after all, worst case scenario, the same privileges will be loaded again! It is not a question of performance issues. Although, in an extreme case it certainly can be an issue. Check for example the following script, that executes 10 000 CREATE USER statements (this can only be done in a single thread as the grant tables are still in MyISAM format, even in 5.7.6):

def execute_test(port, drop, flush, thread):
   db = mysql.connector.connect(host="localhost", port=port, user="msandbox", passwd="msandbox", database="test")
   for i in range(0, TIMES):
      cursor = db.cursor()
      if (drop):
         sql = "DROP USER 'test-" + `thread` + '-' + `i` + "'@'localhost'"
      else:
         sql = "CREATE USER 'test-" + `thread` + '-' + `i` + "'@'localhost' IDENTIFIED BY '" + DEFAULT_PASSWORD + "'"
      cursor.execute(sql)
      cursor.close()
      db.commit()
      if (flush):
         cursor = db.cursor()
         flush_sql = "FLUSH PRIVILEGES"
         cursor.execute(flush_sql)
         cursor.close()
         db.commit()
   db.close()

The timing for both executions are:

$ time ./test_flush.py
Not flushing
Executing the command 10000 times

real    0m15.508s
user    0m0.827s
sys     0m0.323s

$ ./test_flush.py -d
Not flushing
Dropping users
Executing the command 10000 times

$ time ./test_flush.py -f
Flushing after each create
Executing the command 10000 times

real    2m7.041s
user    0m2.482s
sys     0m0.771s

$ ./test_flush.py -d
Not flushing
Dropping users
Executing the command 10000 times

We can see that using FLUSH PRIVILEGES is 8x slower that not using them. Again, I want to stress that performance is not the main issue here, as most people would execute it only once at the end of each command block, so it wouldn’t be a huge overload. Even if there is some extra read IO load, we must assume that every round trip to the database, and every commit takes some server resources -so that can be extrapolated to any command. Additionally, concurrency issues is not a typical problem for MySQL account creation, as the mysql.user table it not usually (or should not be) very dynamic.

The main issue I have against the overuse of FLUSH PRIVILEGES is that people execute it without really understanding why they do it and what that command actually does. Every time a person has a problem with MySQL privilege systems, the first piece of advice that is given is to execute this command “just in case”. Check, for example, answers on dba.stackexchange like this, this and this (which I have selected among many others), and where the original user was not altering manually the mysql.* tables. The issue is that in most cases this command does nothing, and the real problem lays on the poor understanding of MySQL’s permission model. As the saying tells- when you have a hammer, every problem looks like a nail. People read that that is a proper way to solve permission-related problems, and they pass the “knowledge” on, creating basically the MySQL equivalent of an urban myth.

So, the next time you encounter a problem with a user not being able to log it, or apply privileges to a user, there are many other sources of issues such as: using old_passwords, using a different authentication method than the native passwords, not having the actual privileges or the WITH GRANT OPTION properties to apply them, your server not identifying you with the same user or host than the one you are actually in, using skip-name-resolve so dns entries are ignored, waiting for a new connection for the changes to take effect, … and many other issues that come with authorization and authentication. MySQL grant system is not precisely obvious and perfect (Hello, granting permissions from databases that do not exist?), but taking 5 minutes to read the extensive manual on privileges can avoid you many headaches in the future. TL;TR RTFM

For those people that already know when to use or not to use FLUSH PRIVILEGES, please, next time you find someone overusing it, educate the user on best practices so people no longer relay in magic and urban myths to solve problems, go to reddit/stackoverflow/your favorite social network/etc. and upvote good practices/comment on bad practices. Today it could be FLUSH PRIVILEGES, tomorrow it could be “add OPTIMIZE TABLE in a cron job every 5 minutes for your InnoDB tables” (and yes, that last one was actually found in the wild).

Dejad de usar FLUSH PRIVILEGES

Una sirena tiene las mismas probabilidades de arreglar tus problemas de permisos, la diferencia es que la gente continúa creyendo en el mito de FLUSH PRIVILEGES.
Una sirena tiene las mismas probabilidades de arreglar tus problemas de permisos, la diferencia es que la gente continúa creyendo en el mito de FLUSH PRIVILEGES.
Cada vez que alguien escribe un tutorial o solución a un problema relacionado con la creación de una nueva cuentas de usuario o la provisión de diferentes privilegios veo la sugerencia de utilizar FLUSH PRIVILEGES. Por ejemplo, el primer post en /r/mysql en el momento de escribir estas líneas, “MySQL:The user specified as a definer does not exist (error 1449)-Solutions” es culpable múltiples veces de esto mismo (Actualización: el usuario ha tachado estas líneas, tras la publicación de este artículo).

No es mi intención denunciar ese artículo, pero lo cierto es que he visto cometer ese error muchas, muchas veces. Incluso si os dirigís a la página del manual de referencia sobre el comando GRANT veréis al final -de un usuario ajeno a MySQL- usar GRANT seguido de un FLUSH PRIVILEGES.

¿Por qué debería importarme? ¿Es FLUSH PRIVILEGES un problema? ¿Por qué lo hace todo el mundo? La razón de que ese comando exista es porque —para mejorar el rendimiento— MySQL mantiene una copia en memoria de las tablas GRANT (de permisos de usuario). De esta manera, no necesita leerlas de disco en cada conexión, en cada cambio de la base de datos por defecto, y en cada consulta enviada al servidor. El comando mencionado anteriormente fuerza la recarga de esta caché, leyéndola directamente de disco (o de la caché del sistema de archivos), tal y como el manual de referencia de MySQL indica claramente (teniendo incluso su propia sección When Privilege Changes Take Effect “Cuándo tienen efecto los cambios de privilegios”). Sin embargo, su ejecución es totalmente innecesaria en la mayoría de los casos prácticos porque:

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

que significa, más o menos:

Si modificas las tablas de privilegios de manera inderecta, usando sentencias de gestión de cuentas tales como GRANT, REVOKE, SET PASSWORD o RENAME USER el servidor tiene en cuenta estos cambios y carga las tablas en memoria de nuevo de manera inmediata.

La única razón en la cual es necesario realizar la operación de recarga manualmente es cuando:

you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE (se modifican las tablas de privilegios directamente usando sentencias como INSERT, UPDATE o DELETE)

Para la mayoría de operaciones, como crear un usuario, cambiar sus privilegios o contraseña, es preferible usar las operaciones de alto nivel. No sólo son más fáciles de usar, también son en general compatibles con un mayor número de versiones de MySQL y además te evitarán cometer errores (por supuesto, es necesario acordarse de configurar el modo sql NO_AUTO_CREATE_USER). Incluso normalmente funcionarán sin problemas en un entorno hostil para MyISAM como es un clúster de Galera. Ciertamente, hay razones por las que puede ser necesario editar las tablas manualmente- como administradores, es posible que deseemos manipular de manera especial los privilegios o importar las tablas mysql.* de algún otro lugar, por lo que en estos casos ejecutar FLUSH PRIVILEGES es obligatorio. Tened en cuenta que, como dice la página del manual, en la mayoría de los casos (por ejemplo, privilegios globales) cambiar los permisos de un usuario sólo afectará a nuevas conexiones y nunca a consultas que se estén ejecutando ya que los privilegios se comprueban al inicio del procesado de la consulta -léase el manual para los detalles.

Así, pues, ¿por qué mi cruzada contra el abuso de FLUSH PRIVILEGES? Después de todo, en el peor de los casos, ¡se recargarán los mismos privilegios de nuevo! No es una cuestión de rendimiento. Aunque, en casos extremos bien podría ser un problema. Echad un vistazo al siguiente script, que ejecuta 10 000 sentencias CREATE USER (esto sólo se puede hacer en un único hilo ya que las tablas grant todavía están en formato MyISAM, incluso en 5.7.6):

def execute_test(port, drop, flush, thread):
   db = mysql.connector.connect(host="localhost", port=port, user="msandbox", passwd="msandbox", database="test")
   for i in range(0, TIMES):
      cursor = db.cursor()
      if (drop):
         sql = "DROP USER 'test-" + `thread` + '-' + `i` + "'@'localhost'"
      else:
         sql = "CREATE USER 'test-" + `thread` + '-' + `i` + "'@'localhost' IDENTIFIED BY '" + DEFAULT_PASSWORD + "'"
      cursor.execute(sql)
      cursor.close()
      db.commit()
      if (flush):
         cursor = db.cursor()
         flush_sql = "FLUSH PRIVILEGES"
         cursor.execute(flush_sql)
         cursor.close()
         db.commit()
   db.close()

Las mediciones de tiempos de ambas ejecuciones son las siguientes:

$ time ./test_flush.py
Not flushing
Executing the command 10000 times

real    0m15.508s
user    0m0.827s
sys     0m0.323s

$ ./test_flush.py -d
Not flushing
Dropping users
Executing the command 10000 times

$ time ./test_flush.py -f
Flushing after each create
Executing the command 10000 times

real    2m7.041s
user    0m2.482s
sys     0m0.771s

$ ./test_flush.py -d
Not flushing
Dropping users
Executing the command 10000 times

Podemos ver que usar FLUSH PRIVILEGES es 8 veces más lento que no utilizarlo. De nuevo, quiero enfatizar que el rendimiento no es el mayor de los problemas en esta situación, ya que la mayoría de las personas lo ejecutarían tan sólo una vez al final de cada bloque de comandos, por lo que no supondría una gran sobrecarga. Incluso si hay una mayor cantidad de operaciones de lectura de disco, debemos asumir que todo round trip a la base de datos, y todo commit requiere ciertos recursos extra, por lo que esto mismo se puede extrapolar a cualquier comando. Además, la concurrencia no suele ser un problema en la creación de cuentas de usuario, ya que la tabla mysql.user no es normalmente (o no debería) se muy dinámica.

El mayor problema contra el abuso de FLUSH PRIVILEGES es que la gente lo ejecuta sin comprender porqué lo hacen, y lo que realmente hace este comando. Cada vez que una persona tiene un problema con el sistema de privilegios de MySQL, el primer consejo dado es ejecutar dicha sentencia “por si acaso”. Si no os lo creéis, echad un vistazo rápido a respuestas en dba.stackexchange como esta, esta y esta (que he seleccionado entre las muchas existentes), cuyo usuario original no estaba alterando manualmente las tablas mysql.*. La problemática está en que la mayor parte de las veces el comando no hace absolutamente nada, y el problema subyace en la pobre comprensión del sistema de permisos de MySQL. Como dice el dicho en inglés- cuando tienes un martillo, todo problema parece un clavo. Los usuarios leen que esa es la manera adecuada de resolver problemas relacionados con permisos y pasan el “conocimiento” a otros usuarios, creando básicamente el equivalente a una mito urbano en el mundo MySQL.

Así pues, la siguiente vez que encuentres un problema como un usuario que no puede entrar en la base de datos, o aplicarle privilegios, hay muchas otras fuentes de confusión, tales como: el uso de old_passwords, usar un método de autenticación distinto de las contraseñas nativas, no disponer de los permisos adecuados o de la propiedad WITH GRANT OPTION para transmitirlos, el servidor no detectando el usuario con el mismo nombre y host que en el que realmente está, el uso de skip-name-resolve lo qual hace que se ignores las entradas dns, no esperar a una nueva conexión para que los cambios surtan efecto, … y muchas otras dificultades que van unidas a la autenticación y autorización. El sistema de privilegios de MySQL no es precisamente obvio ni perfecto(¡Hola, otorgar permisos para bases de datos que no existen?), pero invertir 5 minutos en leer el detallado manual sobre grants puede ahorrar muchos quebraderos de cabeza en el futuro. TL;TR RTFM

Para aquellos que ya saben cuándo usar o no usar FLUSH PRIVILEGES, por favor, la siguiente vez que veáis a alguien abusarlo, educar al usuario sobre mejores prácticas para que la gente no siga fundamentándose en la magia y mitos urbanos para resolver problemas; id a reddit/stackoverflow/vuestra red social favorita/etc. y votad positivamente las buenas prácticas/comentad sobre las malas. Hoy podría ser FLUSH PRIVILEGES, mañana puede que sea “añadir OPTIMIZE TABLE en un trabajo del cron cada 5 minutos para tus tablas InnoDB” (y sí, esto último lo he encontrado en producción ahí fuera).

Pequeña lista de deseos para EXPLAIN

Salida de EXPLAIN: filesortAunque siempre deseamos un mayor rendimiento y más y mejores características para MySQL, estas no pueden aparecer “sin más” de una versión a otra, requiriendo profundos cambios en la arquitectura y muchas líneas de código. Sin embargo, a veces hay pequeños cambios y arreglos que podrían implementarse por un becario o contribuidor externo, principalmente en la capa SQL, que podrían hacer el ecosistema de MySQL más amigable para novatos y no expertos. Hacer que un programa sea más fácil de usar es muchas veces pasado de largo, pero es increíblemente importante -no todo el mundo usando MySQL es un administrador de bases de datos, y cuanta más gente lo adopte, más gente podrá vivir de él, tanto sus desarrolladores originales como proveedores externos.

Esta es mi propia lista de arreglos para los mensajes de EXPLAIN. Si ya eres un usuario experimentado de MySQL probablemente ya conozcas su sugnificado, pero eso no resuelve el problema para los principiantes. La razón por la que estoy escribiendo este post es para recoger opiniones sobre si os parecen importantes o no, y si mi manera de resolverlos parece razonable para poder enviarlas como “feature requests”.

Mensajes de EXPLAIN

Como instructor MySQL, el caso siguiente me ocurre mucho con nuevos estudiantes. Comienza con un comando como el siguiente:

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)

Entonces, “Using index” significa que se está usando un índice, ¿verdad? No, en este caso type: index nos indica que se está usando un índice para escanear o acceder a las filas (porque no es un type: ALL– aunque podríamos obtener un full row scan e usar el índice para ordenar o agruparlas). El Extra: Using index indica que el índice se usa para devolver los datos, sin necesidad de leer la fila completa. A esto, por lo que yo sé, se le refiere comúnmente como Covering index. Y eso es exactamente lo que me gustaría ver:

mysql> EXPLAIN SELECT b, c FROM test\G -- salida editada
*************************** 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)

o alternativamente:

        Extra: Covering index

Otro malentendido común: 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)

A este nivel no me interesa si estoy usando filesort como algoritmo y -si no me equivoco- desde 5.6 también se puede usar una cola de prioridad como algoritmo de ordenamiento si el numero de elementos es pequeño. Además, el “file” de la palabra filesort puede llevar la confusión de que esto requiera una tabla temporal en disco. No tengo una alternativa perfecta (por favor, dadme ideas), pero algo como lo siguiente sería más claro:

mysql> EXPLAIN SELECT * FROM test ORDER BY length(b)\G  -- salida editada
*************************** 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)

o alternativamente:

        Extra: Not using index for order-by

Otro ejemplo sería:

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)

Entiendo que quizá los desarrolladores no quisieron confundirnos con la técnica pushed condition de NDB, pero esta salida lleva al equívoco también. Literalmente significa que “la condición del índice se está usando”, en vez de que se está usando “ICP. ¿Qué os parece:

mysql> EXPLAIN SELECT a FROM test WHERE b > 3 and c = 3\G -- salida editada
*************************** 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)

Hay muchas otras expresiones, pero esas son las que más me molestas en términos de confusión de estudiantes.

¿Estás de acuerdo conmigo? ¿Romperían estos cambios aplicaciones que parsean la salida de EXPLAIN? ¿Qué otras pequeñas cosas cambiarías en la salida de MySQL or sus mensajes de error? Me gustaría oír especialmente a los que están empezando con MySQL y a las personas que vengan de otras bases de datos, ya que cuanto más lo usamos, más nos acostumbramos a los MySQLismos.

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!