work_mem es un parámetro de configuración de PostgreSQL que especifica la cantidad de memoria que utilizarán las operaciones de ordenación internas y las tablas hash antes de escribir en archivos de disco temporales (en adelante temp files).
Las operaciones de ordenación y tablas hash se utilizan cuando nuestras consultas tienen ORDER BY, DISTINCT, subconsultas IN y JOINS.
El valor predeterminado es de 4 MB lo que significa que cada operación de estos tipos que se esté ejecutando utilizará 4 MB de memoria antes de que comience a escribir datos en temp files.
Hay que tener en cuenta que para una consulta completa, varias operaciones de ordenación o hash pueden ejecutarse en paralelo, por lo tanto, a cada operación se le permitirá usar tanta memoria como se especifique en este parámetro antes de empezar a escribir en temp files por lo que el uso de memoria se multiplica.
Como en un mismo servidor pueden haber varias sesiones en ejecución realizando operaciones al mismo tiempo, la memoria total utilizada podría ser muchas veces el valor de work_mem.
Descubrir que nuestro servidor está creando demasiados temp files es una señal de que work_mem necesita ajustarse ¡pero cuidado! no podemos asignar un valor demasiado elevado a work_mem porque podría desbordarse la memoria, se cancelaría la ejecución de la consulta y empezaríamos a tener mensajes de error.
Los ficheros temporales solo están activos durante la ejecución de la consulta así que, cuando no se necesiten, se eliminarán (por eso se llaman temporales).
¿Cuántos temp files se están creando en nuestro sistema?
pg_stat_database
La primera pregunta que tendríamos que hacernos para saber si está bien ajustado el parámetro work_mem es averiguar cuántos temp files se están creando en nuestro sistema. Para esto podemos consultar los campos temp_files y temp_bytes de la vista pg_stat_database para saber el el numero total de temp files y el tamaño total de temp files respectivamente.
explain analyze
Una buena costumbre es analizar las queries, sobre todo si tienen una envergadura imoprtante, con explain analyze. En este caso, si obtienes como resultado algo parecido a:
Sort Method: external merge Disk: 7904kB
Esto significa que para hacer la ordenación ha necesitado tomar 8 MB de disco por lo que podríamos incrementar el valor actual de work_mem para aumentar el rendimiento.
pgBadger
Otra forma de saber la creación de temp files que está teniendo lugar en nuestro sistema, es la que yo utilizo, consultar el apartado TEMP FILES del reporte que genera la herramienta pgBadger con la que obtengo, de un solo vistazo todo esta información:
- Gráfica que representa el tamaño de ficheros temporales en cada intervalo de tiempo.
- Gráfica que representa el número de ficheros temporales en cada intervalo de tiempo.
- Número de ficheros temporales y tamaño de ficheros temporales por hora.
- Consultas que han generado el mayor número de archivos temporales.
- Consultas que han generado los ficheros temporales más grandes.
- Gráfica que representa el tamaño de ficheros temporales en cada intervalo de tiempo.
Para que pgBadger pueda generar este informe hay que tener habilitado el parámetro log_temp_files en el fichero de configuración postgresql.conf.
Nota: Si quieres saber cómo instalar pgBadger, cómo configurar el servidor para sacarle el máximo partido a este analizador de log y cómo generar estupendos informes sobre la actividad y la salud de servidores PostgreSQL, no te pierdas el curso Administración PostgreSQL: pgBadger, en analizador del log.
¿Por qué debemos reducir el uso de temp files?
Sencillamente porque se están usando ficheros de disco para hacer operaciones que tendrían que hacer la memoria porque escasea y el disco es más lento que la memoria. Así que si consideramos que se está generando demasiados temp files, lo primero que vamos a hacer es analizar nuestras queries que más influyen en la generación de temp files para intentar optimizarlas.
Si ya están optimizadas y seguimos necesitando más memoria para ejecutarlas vamos entonces a aumentar el parámetro work_mem.
Ajustar el tamaño del parámetro work_mem
En un servidor dedicado podemos usar un 2-4% del total de nuestra memoria si tenemos solamente unas pocas sesiones (clientes) grandes. Como valor inicial podemos usar 8 Mbytes e ir aumentando progresivamente hasta tener un buen equilibrio entre uso de memoria y generación de temp files.
| 1 | 1 | 1 | 1 | 1 |
|---|---|---|---|---|
| 2 | 2 | 2 | 2 | 2 |
| 2 | 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 | 3 |
| 4 | 4 | 4 | 4 | 4 |