Una historia corta sobre una solicitud "pesada" y una solución elegante a un problema
Recientemente, por la noche, las alertas comenzaron a despertarnos: no había suficiente espacio en disco. Rápidamente descubrimos que el problema está en las tareas de ETL.
La tarea ETL se realizó en una tabla donde se almacenan registros binarios y volcados. Todas las noches, esta tarea consistía en eliminar volcados duplicados y liberar espacio.
Para buscar volcados duplicados, utilizamos esta consulta:
id, MIN(id) OVER (PARTITION BY blob ORDER BY id) FROM dumps
La consulta combina los mismos volcados por el campo BLOB. Usando la función de ventana, obtenemos el identificador de la primera aparición de cada volcado. Luego, con esta solicitud, eliminamos todos los volcados duplicados.
La solicitud se ejecutó durante algún tiempo y, como se puede ver en los registros, se comió mucha memoria. El gráfico muestra cómo obtuvo el espacio libre en el disco todas las noches:
Con el tiempo, la solicitud requirió más memoria, las fallas se profundizaron. Y, mirando el plan de ejecución, vimos de inmediato a dónde va todo:
Buffers: shared hit=3916, temp read=3807 written=3816 -> Sort (cost=69547.50..70790.83 rows=497332 width=36) (actual time=107.607..127.485 rows=39160) Sort Key: blob, id Sort Method: external merge Disk: 30456kB Buffers: shared hit=3916, temp read=3807 written=3816 -> Seq Scan on dumps (cost=0..8889.32 rows=497332 width=36) (actual time=0.022..8.747 rows=39160) Buffers: shared hit=3916 Execution time: 159.960 ms
La clasificación ocupa mucha memoria. En términos de ejecución, la clasificación requiere aproximadamente 30 MB de memoria de un conjunto de datos de prueba.
Por qué
PostgreSQL asigna memoria para el hash y la clasificación. La cantidad de memoria es controlada por el parámetro work_mem . El tamaño predeterminado de work_mem es de 4 MB. Si se necesitan más de 4 MB para el hash o la clasificación, PostgreSQL consume temporalmente espacio en disco.
Nuestra consulta consume claramente más de 4 MB, por lo que la base de datos usa mucha memoria. Decidimos: no nos apuraremos, y no aumentamos el parámetro ni expandimos el almacenamiento. Es mejor buscar otra forma de recortar la memoria para ordenar .
Clasificación económica
"La cantidad de clasificación dependerá del tamaño del conjunto de datos y la clave de clasificación. No se puede reducir el conjunto de datos, pero el tamaño de la clave es posible .
Para el punto de referencia, tomamos el tamaño promedio de la clave de clasificación:
avg ---------- 780
Cada clave pesa 780. Para reducir la clave binaria, se puede cifrar. En PostgreSQL hay md5 para esto (sí, no seguridad, pero para nuestro propósito lo hará). Veamos cuánto pesa el hash BLOB con md5:
avg ----------- 36
El tamaño de la clave hash a través de md5 es de 36 bytes. Una clave hash pesa solo el 4% de la opción original .
A continuación, lanzamos la solicitud original con una clave hash:
id, MIN(id) OVER ( PARTITION BY md5(array_to_string(blob, '') ) ORDER BY id) FROM dumps;
Y el plan de implementación:
Buffers: shared hit=3916 -> Sort (cost=7490.74..7588.64 rows=39160 width=36) (actual time=349.383..353.045 rows=39160) Sort Key: (md5(array_to_string(blob, ''::text))), id Sort Method: quicksort Memory: 4005kB Buffers: shared hit=3916 -> Seq Scan on dumps (cost=0..4503.40 rows=39160 width=36) (actual time=0.055..292.070 rows=39160) Buffers: shared hit=3916 Execution time: 374.125 ms
Con una clave hash, la solicitud consume solo 4 megabytes adicionales, es decir, un poco más del 10% de los 30 MB anteriores. Por lo tanto, el tamaño de la clave de clasificación afecta en gran medida la cantidad de memoria que consume la clasificación .
Más más
En este ejemplo, analizamos el BLOB usando md5 . Los hashes creados con MD5 deben pesar 16 bytes. Y tenemos más:
md5_size ------------- 32
Nuestro hash era exactamente el doble de grande, porque md5 produce un hash en forma de texto hexadecimal.
En PostgreSQL, puede usar MD5 para el hash con la extensión pgcrypto . pgcrypto crea MD5 de tipo bytea (en binario) :
select pg_column_size( digest('foo', 'md5') ) as crypto_md5_size crypto_md5_size --------------- 20
El hash sigue siendo 4 bytes más de lo que debería ser. Es solo que el tipo bytea usa estos 4 bytes para almacenar la longitud del valor, pero no lo dejaremos así.
Resulta que el tipo uuid en PostgreSQL pesa exactamente 16 bytes y admite cualquier valor arbitrario, por lo que eliminamos los cuatro bytes restantes:
uuid_size --------------- 16
Eso es todo 32 bytes con md5 convierten en 16 con uuid .
Verifiqué los efectos del cambio tomando un conjunto de datos más grande. Los datos en sí no se pueden mostrar, pero compartiré los resultados:
Como puede ver en la tabla, la solicitud problemática original pesaba 300 MB (y nos despertó en medio de la noche). Con la clave uuid , la clasificación tomó solo 7 MB.
Consideraciones de seguimiento
Una solicitud con una clave de clasificación de memoria hash consume menos, pero funciona mucho más lento:
El hash utiliza más CPU, por lo que una solicitud con un hash es más lenta. Pero tratamos de resolver el problema con el espacio en disco, además, la tarea se realiza por la noche, por lo que el tiempo no es un problema. Nos comprometimos a ahorrar memoria.
Este es un gran ejemplo del hecho de que no siempre necesita intentar acelerar las consultas de la base de datos . Es mejor usar lo que está equilibrado y exprimir al máximo un mínimo de recursos.