Del por qué un desarrollador NO ES un Database Administrator?

O del por qué podría ser, pero debería primero cambiarse la camisa …

Este POST no busca explicar postgreSQL, ni siquiera es un artículo acerca de trucos o buenas prácticas, es simplemente una reflexión acerca de cómo pequeñas cosas que muchos pasan desapercibidas causan impacto profundo en el diseño de una aplicación.

Preámbulo

Tomé un servidor físico GNU/Linux que únicamente ejecutaba una base de datos y lo mudé a una máquina virtual restringida (¡conchale!, hay que ahorrar recursos!, pensé), pensando que todo quedaría bien.

Sin embargo, los usuarios del sistema (en producción) comenzaron a quejarse de lentitud, además, los administradores de sistemas comenzaron a notar excesivos picos de uso de CPU (¿en un equipo que sólo tiene postgreSQL?) e incluso en varias oportunidades se iba a SWAP.

Antes de devolver la base de datos al equipo físico, decidí hacer una revisión (les dije, «no solo monto sistemas, también sé de programación y mucho de base de datos, lo olvidan?») y estas son las impresiones de la muy breve (pero exitosa) revisión.

El análisis

Lo primero que siempre hago cuando voy a analizar un sistema conectado a postgreSQL, es activar el slow log, esto es, comenzar a medir aquellos queries que consumen más del tiempo que uno supone debería asumir cierto tipo de consulta; paralelamente es daba una clase rápida de postgreSQL, de la importancia de entender el ciclo de parsing/análisis de una consulta SQL y más aún, de la importancia de entender SQL; por ejemplo, dos casos reales extraídos de este caso:

«fecha_inscrito» es un campo VARCHAR, donde almacenan algo como «12/04/2012»; si desean sacar el año, hacen un SPLIT de la cadena con un SUBSTR y sacan el tercer valor luego del «/» … ¿les parece eso correcto?, pues veamos el resultado:

postgres=# SELECT substr(’12/04/2012′, 6);
substr
——–
/2012
(1 fila)

Duración: 61,300 ms

Versus:

postgres=# SELECT EXTRACT(year from ‘2012-04-12’::date);
date_part
———–
2012
(1 fila)

Duración: 0,468 ms

Y comenzaron las alarmas!, 60 milisegundos menos!, pero chamo, a mi no me gusta trabajar con formato ANSI!, y luego les recuerdo que existe la variable de conexión DATESTYLE:

postgres=# SET datestyle to sql;
SET
Duración: 0,124 ms

postgres=# SELECT ‘2012-04-12’::date;
date
————
12/04/2012
(1 fila)

Duración: 0,154 ms

INSOLITO! ¿cómo es posible que una simple función SET cause que todas las fechas salgan con el formato que yo desee?, esto pasa cuando se desconocen los detalles de la base de datos con la que se trabaja.

Igual sucede cuando la gente comprende la diferencia entre usar ON y USING cuando se construyen JOINS (diferencia claro está, a nivel de planificador).

El tercer caso de no-entendimiento de postgreSQL ocurría con un «pseudo-ORM hecho a mano» por los mismos programadores, que no solamente escapaba mal las cadenas:

Esto:

(‘Empresa’, ‘   MATURIN’, ’15/04/2014′, …

No es igual a esto:

(‘Empresa’, ‘MATURIN’, ’15/04/2014′, …

Ya que la cadena »    MATURIN» no es igual a «MATURIN», a menos que se haga un TRIM adicional, afectando el performance de la consulta, sino que además, hacen cosas como esta:

’21’, ’32’, ’16’, ‘8’, …

¿Esos son cadenas o números?, luego de ejecutar un DESCRIBE a la tabla, encontramos con que son campos numéricos, y aunque postgreSQL hace la conversión, lanza un WARNING indicando que esos valores son «not integers», lo cual obviamente no es para nada óptimo.

Esto es lo que pasa cuando los programadores, pensando como programadores, intentan diseñar bases de datos.

El extraño caso del CPU consumido

Me he encontrado con una consulta muy repetida, con esta forma:

SELECT * FROM sol_solvencia WHERE cod_aport = $1 and estatus = $2;

La consulta se repetía muchísimo (sobre todo en temporada de mucho uso de la aplicación web hecha en PHP) y los logs del postgreSQL reportaban que su duración, promedio, era de 550 milisegundos … grité (como gritó Emmet Brown cuando le dijeron que debía alimentar el D’lorean con 1.1Gigawatts) ¡550 milisegundos!, yo en 550 milisegundos corría una nómina y ellos sólo ejecutan un query!.

Luego de usar EXPLAIN ANALYZE nos encontramos con la sorpresa de que la tabla no posee índices de ningún tipo (alegan que es un sistema legado y viene así de informix) y en la actualidad posee unos 98 mil registros; por ende, postgreSQL no le queda de otra para hacer el WHERE que ejecutar un SEQUENTIAL SCAN; o lo que es lo mismo, postgreSQL iteró de manera secuencial por los 98 mil registros para sacar ámbos criterios, ¿en conclusión?, allí se van los 550 milisegundos.

Si el query se ejecuta a un ritmo de hasta 50 veces en un minuto, se imaginan el uso de CPU de postgreSQL para poder mantener ese ritmo.

Indizar, y sino, indizar también …

Luego de construir un par de índices sencillos:

CREATE INDEX idx_sol_solvencia_cod_aport ON sol_solvencia USING btree (cod_aport NULLS FIRST);

Y

CREATE INDEX idx_sol_solvencia_estatus ON sol_solvencia USING btree (cod_estatus);

El EXPLAIN cambia considerablemente:

Aggregate  (cost=32.19..32.20 rows=1 width=4)
  ->  Index Scan using idx_solvencia_fecha on sol_solvencia  (cost=0.00..31.96 rows=94 width=4)
        Index Cond: (fecha_now = ’11-04-2014′::date)
        Filter: ((estado = 1) OR (estado = 15))

Al realizar un escaneo sobre índices y luego una función de agregado sobre ámbos índices, la consulta se ejecuta en 1.7 milisegundos …

¡toda una mejora!, ¿no les parece?

Prepáralo, ponlo para llevar

Luego, les expliqué el concepto del planificador, del GeQo de postgreSQL, y de cómo se analizaban las consultas, luego de ello, les dije «¿y se imaginan que ese tiempo pudiera bajarse más?» … te miran con cara de gallina comiendo sal y tú les muestras PREPARE.

PREPARE genera sentencias preparadas, toma una consulta común, la pasa por el planificador, construye el plan en base a criterios pre-definidos y compila este plan, presto a simplemente recibir los parámetros, ¿y qué ganamos con esto?, ¡ahorrarnos milisegundos valiosos de planificación!.

PREPARE pln_obtenersolvencia (int, varchar) AS
SELECT * FROM sol_solvencia WHERE cod_aport = $1 and estatus = $2;

Como ven, la sentencia preparada es simplemente un QUERY tradicional, al cual hemos pasado sus condiciones (que deben ser criterios, no metadatos) como parámetros, así, postgreSQL puede analizar, planificar y compilar la sentencia y esperar únicamente a que pasemos los parámetros:

EXECUTE pln_obtenersolvencia(682185, 1);

Total runtime: 0.473 ms
(1 filas)

De 1.7 a 0.4 ha sido una mejora interesante, ¿no?.

Conclusiones

No busco «echarme de enemigos» a los desarrolladores, yo también soy uno (de diseñado APIs, frameworks, y módulos en python y PHP), pero, cuando se trata de bases de datos, los desarrolladores deben quitarse los audífonos, la sudadera geek y ponerse en la camisa y anteojos nerd de los DBA; porque al final del día, la aplicación no se está diseñando para yo sentirme cómodo para trabajar con fechas o agregar y agregar campos sin mantener un diccionario de datos simplemente porque me pidieron en un lugar meter el RIF como J310210423 en otro como J-31021042-3 y en otro el tipo de contribuyente por un campo y el código del mismo por otro; no se trata de nuestra comodidad (o de nuestro limitado conocimiento como desarrolladores) sino de la eficiencia y la funcionalidad que deben garantizar que sea el USUARIO FINAL (y sólo él, porque al final, es el que usará todos los días la aplicación) quien disfrute el uso de nuestra aplicación.

Por cierto, como colofón, habrán notado que la tabla se llama «solvencia», si, es un sistema que genera solvencias al público en general, ahora las consultas duran menos de un milisegundo (y no casi un segundo en cargar) por lo que la mejora impacta a miles de personas que solicitan la solvencia en ese sistema …

… Y ya el CPU no se agota en la máquina virtual! …