Optimizando consultas a base de datos (mysql / mariadb)

Escribo esta crónica de forma post-mortem, luego de pasar 8 horas intentando solventar mi dilema. Tenía calendarizadas unas pruebas para hacer una demostración de una versión nueva del sistema de casa y todo funcionaba como se esperaba. Las pruebas que habíamos hecho al interior del equipo de desarrollo fueron satisfactorias, cada caso estaba validado:

• Consultas a registros usando distintos filtros
• Generación de registros
• Flujo de proceso

Aparentemente el caso estaba en orden, excepto por un detalle. Se cuenta con un proceso automático que corre en segundo plano, y a causa de la volumetría de datos que se tenía, a pesar de ser un ambiente de pruebas controlado y réplica del ambiente productivo, su ejecución la encontramos lenta: 4 minutos y medio para concluir un proceso sencillo que además, no era nuevo, era el mismo proceso que teníamos desde hacía casi dos años. Cuando se tenía un histórico de datos de 6 meses, el desempeño era aceptable, pero ahora con más de un año de datos, nos encontramos con este obstáculo.

Si este es tu caso…

Antes de intentar optimizar el desempeño de un sistema, programa, módulo, componente… o una simple consulta a una base de datos, debes tener en cuenta que, al igual que en otras disciplinas, quien se aventure a mejorar algo existente debe saber (o tener el apoyo de alguien que lo conozca lo suficientemente bien) al menos:

1. Cómo se usan las herramientas que pretende utilizar.
2. Cómo funciona lo que pretende ajustar (mejorar, arreglar).

Si no consideras estos dos detalles, que se antojan ínfimos sin serlo, no sólo estarás perdiendo valioso tiempo, puedes terminar estropeando elementos que ya funcionan correctamente y/o empeorándolo todo. Es muy frecuente que el punto 2, conocer cómo funciona (la regla de negocio), pase desapercibido y no se le dé la importancia que en realidad tiene. Conocer la regla de negocio en conjunto con el flujo de datos te permitirá localizar el punto de falla.

El título de la crónica habla específicamente de optimizar consultas a base de datos, puesto que al final, eso fue lo que terminé haciendo para solucionar mi dilema. Sin embargo, quiero dejar esta memoria de cómo determiné que el problema para resolver el procesamiento lento se debía a una consulta en una base de datos y cómo se realizó la optimización.

“El sistema está lento”. Sí, pero, ¿qué parte del sistema en sí?

Luego de hacer varios ejercicios con el equipo de trabajo, llegamos a la determinación que la lentitud estaba relacionada con alguno de los componentes del sistema y no con el hardware. ¿Por qué concluimos esto? Porque hicimos pruebas en distintos equipos clientes, en diferentes servidores, tanto aplicativos como de base de datos, y la medición del tiempo de ejecución se mantuvo muy cercana entre ellos; 4 minutos y medio.

Habiendo determinado que el sistema (el software) era el origen del problema, como en cualquier otro conglomerado de partes, no podía (esa era nuestra esperanza) resultar que TODO el sistema estuviera mal, alguna parte (o varias) deberían ser las que estaban causando esta latencia.

Como preámbulo al análisis que siguió, déjenme explicar cómo funciona el proceso en segundo plano. El sistema confía la ejecución de sus procesos a un programa controlador que ejecuta subprogramas en segundo plano y que están encadenados como etapas secuenciales. Conforme un programa termina, se ejecuta el siguiente que está relacionado con la etapa en turno, y así sucesivamente hasta concluir el proceso. Después de 4 ejercicios, determinamos que 4 de las 4 etapas que conforman el proceso demoraban un tiempo similar al del ambiente productivo (ligeramente mayor que en productivo por diferencias en hardware), es decir, la medición del tiempo que cada etapa proporcionaba de inicio a fin, era más o menos la misma que la observada en el ambiente productivo. ¿Dónde encontramos el tiempo excedente entonces? En el programa controlador.

El programa controlador y cada uno de los programas que ejecuta en segundo plano tienen logs de ejecución individuales. Si en el diseño de tu sistema te puedes permitir esta facilidad, hazlo así, es más fácil buscar en un log específico, que en un log que engloba la salida de varias entidades. Durante dos pruebas de ciclo completo, observamos que cada programa registraba el inicio de su ejecución y su término en su log respectivo, y el tiempo de ejecución de estos, como mencioné anteriormente, se encontraba dentro de los parámetros esperados.

Lo que detectamos fue que, a diferencia de otros ambientes de pruebas, el programa controlador tardaba un minuto en escribir en su log cuando pretendía ejecutar un nuevo programa. Este es el punto del análisis donde es importante conocer la regla de negocio, el funcionamiento y el flujo de datos del sistema. Porque conocemos estos detalles es que pudimos validar qué funcionaba bien y qué no.

En ese momento había dos posibilidades de solución “económica”: estábamos usando una versión vieja del controlador o el controlador presentaba deficiencias. Lo más rápido y sencillo era sin dudas, actualizar el programa controlador, así que esa fue la estrategia que se siguió… sin embargo, el resultado no varió, los tiempos permanecieron iguales. Por ende, entonces sí había algo mal en el programa o alguno de sus componentes.

Activamos el modo exhaustivo en los logs del programa controlador, que va listando casi paso a paso lo que está haciendo. Mediante un análisis minucioso, finalmente encontramos el problema en sí: el programa controlador, para determinar qué programa debe ejecutar en consecuencia, ejecuta una consulta en base de datos. Esa consulta tardaba, por cada ejecución, 50 segundos. De tal suerte que en efecto, si hacíamos la aritmética, el proceso que nos encontrábamos revisando ejecutaba 4 programas, lo que requería hacer esa consulta 4 veces, más el tiempo que cada programa tomaba, así nos daban los 4 minutos y 30 segundos promedio por ejecución.

Optimizando la consulta

Esta es la consulta que estaba causando la ejecución lenta en el proceso:

SELECT 
       rcel.id_ctrl_eta_lanzador,
       cep.id_control_etapa_proceso,
       cep.secuencia,
       rcel.id_lote_proceso,
       rcel.id_estatus_lanzador,
       cel.estatus_lanzador,
       rcel.id_proceso,
       cp.nombre nombre_proceso,
       rcel.id_ejecucion,
       rcel.id_programa,
       cpr.nombre nombre_programa,
       cpr.programa,
       cpr.id_tipo_programa,
       ctp.tipo_programa,
       ctp.comando,
       cpr.ruta_programa
  FROM  c_procesos cp 
       INNER JOIN c_etapas_procesos ep on cp.id_proceso = ep.id_proceso
            AND ep.id_tipo_ejecucion = 2
       inner join c_programas cpr on ep.id_programa = cpr.id_programa
       INNER JOIN c_tipos_programas ctp ON cpr.id_tipo_programa = ctp.id_tipo_programa
       INNER JOIN r_ctrl_eta_lanzador rcel on rcel.id_proceso = cp.id_proceso
            AND rcel.id_programa = ep.id_programa
       INNER JOIN control_etapas_procesos cep ON rcel.id_control_etapa_proceso = cep.id_control_etapa_proceso
       INNER JOIN c_estatus_lanzador cel ON cel.id_estatus_lanzador = rcel.id_estatus_lanzador
 WHERE cel.id_estatus_lanzador = idEstatusLanzador;

A simple vista es una consulta bastante sencilla. Cabe mencionar que al final de la cláusula WHERE se encuentra la palabra idEstatusLanzador, que en este caso está fungiendo como un parámetro de la consulta, es decir, lo podemos sustituir por cualquier valor válido como Estatus de Lanzador.

1. Obtener sólo los datos necesarios

La primera aproximación es obtener únicamente los datos que requerimos, esto es, no utilizar el comodín * (Select * FROM).

En el caso de la consulta que nos atañe, ya partimos de que este principio fue respetado y vemos que cada tabla y campo son referidos individualmente.

¿Por qué esto es importante?

Porque si se obtienen todas las columnas aunque no se utilicen, el motor tiene que cargar esos datos en memoria. Entre menos datos, mejor.

2. Reducir el número de tablas

Cuando la consulta requiere datos de distintas tablas, el motor de base de datos requerirá abrir los repositorios de cada una, cargará los datos a memoria y ejecutará las operaciones de discriminación de datos que se haya marcado en las cláusulas de la instrucción WHERE del SELECT. Entre más tablas haya, más datos tendrá que obtener el motor antes de poder emitir un resultado.

Para realizar este análisis se requiere el apoyo de alguien que conozca la estructura de la base de datos, que nos indique si hay datos repetidos en tablas que ya se están usando, o si se pueden obtener de tablas que tengan una menor cantidad de datos. En el ejercicio que nos tocó, no logramos identificar tablas que pudieran sustituirse.

3. Verificar la indexación de las tablas

Cuando se tiene una sola tabla y el filtrado de la cláusula WHERE se hace sobre campos de ésta, debemos asegurarnos de que esos campos estén contenidos en un índice. No hagas índices individuales por cada campo, identifica los campos que más frecuentemente usas para filtrar y genera un índice con la combinación de éstos.

Cuando se cruzan tablas mediante la cláusula WHERE o JOIN, los campos con los que se realiza el cruce deben estar indexados también. En la consulta de esta crónica, había una relación entre la tabla emi_trx33_r mediante el campo id_emisor con la tabla c_entidades refiriendo al campo id_entidad. Id_entidad en c_entidades es llave primaria, lo que ya le proporciona una indexación por naturaleza. Id_emisor sin embargo, es llave foránea hacia c_entidades, y este campo no estaba indexado.

Aplicamos el indice sobre el campo id_emisor de la tabla emi_trx33_r. Ejecutamos el query nuevamente: 2mins 22 segundos. Hemos conseguido una mejora en el desempeño.

4. Actualizar bibliotecas y frameworks

Es claro que la programación hoy en día es la combinación de esfuerzos de múltiples equipos, herramientas y tecnologías. En el proyecto que atañe esta crónica se estaba utilizando Java y MySQL como motor de base de datos.

Así como las plataformas de desarrollo y almacenamiento de datos avanzan, también quienes generan los controladores y APIs para comunicación con la base de datos tienen que mantenerse al día.

Verifica que tus drivers, frameworks y bibliotecas estén usando la versión más reciente y estable compatible con las tecnologías que estás usando.

Nuestro sistema estaba usando la versión más reciente, así que este paso estaba cubierto.

5. Latencia en la red

Cuando la base de datos se encuentra en un equipo diferente, cualquier latencia en la red podrá ser causa de lentitud, en especial si se están haciendo consultas anidadas.

Ahora, no caigas en la zona de comfort y aludas de inmediato a argumentar que puede ser un problema en la red. Si no es un tema de conexión, es poco probable que el problema radique en comunicaciones. No obstante, si ya agotaste el resto de opciones, quizás va por aquí. Una seña clara sería que TODAS las consultas son lentas.

6. Mejorar el hardware

A veces la solución más sencilla es mejorar el hardware, físico o virtual. Si la ejecución está lenta, se agregan más procesadores o más memoria RAM. Pero esto debe hacerse/sugerirse cuando se está seguro que el software funciona de manera óptima y el cambio de hardware es necesario, si no, sólo estamos desperdiciando recursos y mostrando que no somos serios en nuestro trabajo.

Los cambios en hardware que podrán mejorar el desempeño son comunmente:

• Agregar más memoria RAM
• Cambiar discos magnéticos por estado sólido
• Agregar más procesadores
• Cambio de BUS

En conclusión

Cuando te dispongas a mejorar una consulta en base de datos:

1. Asegúrate de conocer las herramientas con las que trabajas a un nivel adecuado (que no tengas que revisar manuales o documentación constantemente).
2. Debes conocer el flujo y las reglas de negocio alrededor de la misma.
3. Debes conocer la estructura de datos.
4. En el query, favorece el obtener sólo los datos necesarios de cada tabla.
5. Verifica que las columnas que se usan en los cruces de tablas (JOINS) tengan un índice y que compartan el mismo tipo de dato (VARCHAR vs INT no te servirá, aunque esén indexados).
6. Verifica que las columnas que usas para filtrar (cláusua WHERE) tengan índices. Recuerda que es más fácil buscar en campos numéricos que en campos alfanuméricos o de fecha.
7. Verifica que estés utilizando la última versión estable de los componentes de software compatibles con tu plataforma.
8. Valida si la lentitud puede deberse a latencia en las comunicaciones (poco probable, pero puede suceder).
9. Y en remoto caso, considera mejorar el hardware del equipo donde se ejecutan los componentes.

Espero que esta crónica te sea de utilidad.