VACUUM y ANALYZE son las dos operaciones más importantes para el mantenimiento de la base de datos PostgreSQL.
Vacuum se emplea para recuperar el espacio ocupado por las “tuplas muertas” de una tabla. Una tupla muerta se genera al borrar o actualizar (una eliminación seguida de una inserción) un registro. PostgreSQL no elimina físicamente la fila obsoleta de la tabla, sino que aplica un “marcador” para que sea excluida en las consultas. Cuando se realiza un proceso de vacuum, el espacio ocupado por estas tuplas muertas se marca como reutilizable por otras tuplas.
La operación ANALYZE hace lo que su nombre indica: analiza el contenido de las tablas de una base de datos y recopila estadísticas sobre la distribución de los valores en cada columna de cada tabla. El motor de consultas de PostgreSQL utiliza estas estadísticas para determinar el mejor plan de consulta. A medida que se insertan, borran y actualizan las filas de una base de datos, cambian también las estadísticas de las columnas. ANALYZE — ejecutado manualmente por el DBA o de forma automática por PostgreSQL durante un proceso de autovacuum — garantiza que las estadísticas estén actualizadas.
Aunque parezcan relativamente sencillos, en realidad, VACUUM y ANALYZE son dos procesos complejos. Y, si bien es cierto que afortunadamente los DBAs no tienen que preocuparse mucho por sus características internas, a menudo suelen confundirse al ejecutar estos procesos manualmente o al establecer los valores óptimos para los parámetros de configuración.
En este artículo, presentaremos algunas de las mejores prácticas para VACUUM y ANALYZE.
Consejo 1: No ejecute manualmente VACUUM o ANALYZE sin una razón específica
La ejecución de VACUUM en PostgreSQL (ya sea en modalidad manual o automática) minimiza el sobredimensionamiento en la tabla y previene el reciclaje del ID de transacción. Autovacuum no recupera el espacio del disco ocupado por las tuplas muertas. Esto ocurrirá solamente al ejecutar el comando VACUUM FULL. Sin embargo, VACUUM FULL tiene sus repercusiones en el rendimiento. La tabla de destino permanecerá rigurosamente bloqueada durante el proceso, impidiendo incluso las operaciones de lectura en la misma. El proceso realiza también una copia completa de la tabla, lo cual requiere espacio extra en el disco durante su ejecución. Recomendamos no ejecutar VACUUM FULL a menos que exista un porcentaje muy alto de sobredimensionamiento, y resulten afectadas las consultas. Asimismo, es recomendable realizar el proceso durante los períodos de menor actividad de la base de datos.
Otra práctica recomendada es evitar ejecutar manualmente vacuum con demasiada frecuencia. Esto debido a que, la base de datos pudiera ya haber sido sometida de manera óptima a un proceso de autovacuum. Como resultado, la ejecución manual de vacuum pudiera no eliminar las tuplas muertas, sino provocar cargas innecesarias de I/O o picos en la CPU. Por esta razón, debería ser utilizada únicamente tabla por tabla y cuando sea estrictamente necesario, como por ejemplo, en el caso de que exista una baja proporción entre filas vigentes y muertas, o en caso de intervalos de tiempo considerables entre las operaciones de autovacuum. Adicionalmente, la ejecución manual de vacuum debería realizarse cuando la actividad del usuario sea mínima.
Además, aunque autovacuum mantiene actualizadas las estadísticas de distribución de datos de una tabla, no las reconstruye. Al contrario, la ejecución manual del comando ANALYZE reconstruye estas estadísticas en lugar de actualizarlas. Una vez más, reconstruir las estadísticas cuando ya han sido actualizadas de forma óptima por una normal operación de autovacuum podría provocar una carga innecesaria en los recursos del sistema.
El momento adecuado para ejecutar ANALYZE de forma manual es inmediatamente después de la carga masiva de datos en la tabla de destino. Un gran número (incluso unos pocos centenares) de nuevas filas en una tabla existente alterará significativamente la distribución de los datos en las columnas. Las nuevas filas causarán la desactualización de las estadísticas de las columnas existentes. Cuando el optimizador de consultas usará esas estadísticas, el rendimiento de las consultas podría resultar muy lento. En casos como estos, ejecutar el comando ANALYZE inmediatamente después de una carga de datos — con el fin de reconstruir completamente las estadísticas — es una mejor opción que esperar que autovacuum haga efecto.
Consejo 2: Configurar correctamente el umbral de autovacuum
Es fundamental revisar o ajustar los parámetros de autovacuum y analizar su configuración en el archivo postgresql.conf o en las propiedades individuales de las tablas. Esto permite lograr el equilibrio entre el proceso de autovacuum y el aumento del rendimiento.
PostgreSQL usa dos parámetros de configuración para decidir cuándo realizar un autovacuum:
- autovacuum_vacuum_threshold: tiene un valor predefinido de 50
- autovacuum_vacuum_scale_factor: tiene un valor predefinido de 0.2
Juntos, estos parámetros indican a PostgreSQL que inicie un proceso de autovacuum en el caso de que el número de filas muertas en una tabla exceda el número total de filas en la misma multiplicado por el factor de escala, más el valor del umbral de vacuum. En otras palabras, PostgreSQL iniciará un proceso de autovacuum en una tabla cuando:
pg_stat_user_tables.n_dead_tup > (pg_class.reltuples x autovacuum_vacuum_scale_factor) + autovacuum_vacuum_threshold
Para tablas de tamaño pequeño o mediano, esto puede ser suficiente. Por ejemplo, en una tabla que contiene 10.000 filas, el número de las filas muertas tiene que ser superior a 2.050 ((10.000 x 0,2) + 50) antes de que se produzca un autovacuum.
No todos las tablas en una base de datos registran el mismo índice de modificación de los datos. Normalmente, unas pocas tablas de gran tamaño experimentarán frecuentes modificaciones en los datos y, como resultado, presentarán un mayor número de filas no vigentes. Es posible que los valores predefinidos no se apliquen a esas tablas. Por ejemplo, según los valores predefinidos, una tabla que contenga 1 millón de filas deberá presentar más de 200.050 filas muertas antes de que se inicie un proceso de autovacuum ((1000.000 x 0,2) + 50). Esto puede significar intervalos más largos entre los procesos de autovacuum, tiempos de ejecución de autovacuum cada vez mayores y, lo que es peor, que el proceso de autovacuum no pueda ser realizado en absoluto si las transacciones activas en la tabla lo están bloqueando.
Por lo tanto, el objetivo debería ser configurar estos umbrales en valores óptimos para que el proceso de autovacuum se produzca a intervalos regulares, no se prolongue mucho tiempo (afectando a las sesiones de los usuarios) y se mantenga un número relativamente bajo de filas muertas.
Un enfoque adecuado es utilizar uno de los dos parámetros. De modo que, si configuramos autovacuum_vacuum_scale_factor a 0 y autovacuum_vacuum_threshold a, digamos, 5.000, se producirá el proceso de autovacuum en una tabla cuando su número de filas muertas exceda las 5.000.
Consejo 3: Configurar correctamente el umbral de Autoanalyze
Al igual que autovacuum, autoanalyze también utiliza dos parámetros que determinan cuándo autovacuum desencadenará el proceso de autoanalyze:
- autovacuum_analyze_threshold: tiene un valor predefinido de 50
- autovacuum_analyze_scale_factor: su valor predefinido es 0.1
Tal como en el caso de autovacuum, el parámetro autovacuum_analyze_threshold puede ser configurado con un valor que determine el número de tuplas en una tabla que hayan sido introducidas, eliminadas o actualizadas antes de iniciar el proceso de autoanalyze. Recomendamos definir este parámetro por separado para tablas de gran tamaño y de alta transacción. La configuración de la tabla anulará los valores de postgresql.conf.
El siguiente fragmento de código muestra la sintaxis SQL utilizada para modificar la configuración de autovacuum_analyze_threshold en una tabla.
ALTER TABLE <table_name> SET (autovacuum_analyze_threshold = <threshold rows>)
Consejo 4: Configurar correctamente los workers de autovacuum
Otro parámetro frecuentemente ignorado por los DBAs es autovacuum_max_workers, que presenta un valor predefinido de 3. Autovacuum no consiste en un único proceso, sino en una serie de subprocesos vacuum independientes que se ejecutan en paralelo. El motivo por el cual se especifican múltiples workers es el de asegurarse de que, al realizar el proceso de vacuum en tablas de gran tamaño, no se retenga el mismo proceso en las tablas de menor tamaño y en las sesiones de los usuarios. El parámetro autovacuum_max_workers indica a PostgreSQL que aumente el número de los subprocesos worker de autovacuum para realizar la limpieza.
Una práctica común adoptada por los DBAs de PostgreSQL es aumentar el número máximo de subprocesos worker con el objetivo de acelerar autovacuum. Pero esto en realidad no funciona, ya que todos los subprocesos comparten el mismo autovacuum_vacuum_cost_limit, que tiene un valor predefinido de 200. A cada subproceso de autovacuum se le asigna un límite de costo usando la siguiente fórmula:
individual thread’s cost_limit = autovacuum_vacuum_cost_limit / autovacuum_max_workers
El costo del trabajo realizado por cada subproceso de autovacuum se calcula usando tres parámetros:
- vacuum_cost_page_hit: tiene un valor predefinido de 1
- vacuum_cost_page_miss: tiene un valor predefinido de 10
- vacuum_cost_page_dirty: tiene un valor predefinido de 20
El significado de estos parámetros es el siguiente:
- Cuando un subproceso de vacuum encuentra en el buffer compartido la página de datos que debería limpiar, el costo es de 1.
- Si la página de datos no se encuentra en el buffer compartido, sino en la caché del sistema operativo, el costo será de 10.
- Si la página tiene que ser marcada como “sucia” porque el subproceso de vacuum tuvo que eliminar filas muertas, el costo será de 20.
Un número mayor de subprocesos worker reducirá el límite de costo de cada subproceso. A medida que a cada subproceso se le asigne un límite de costo inferior, éste se inactivará con mayor frecuencia, ya que el umbral de costo será alcanzado con más facilidad, causando en última instancia la ralentización de todo el proceso de vacuum. Recomendamos aumentar el autovacuum_vacuum_cost_limit a un valor superior (como 2000) y luego ajustar el número máximo de subprocesos worker.
Un sistema más eficaz consiste en ajustar estos parámetros para tablas individuales sólo cuando sea necesario. Por ejemplo, si el proceso de autovacuum en una tabla transaccional de gran tamaño tarda demasiado, la tabla puede ser configurada temporalmente para utilizar sus propios límites de costo y de retardo en los costos en vacuum. El límite de costo y el retardo anularán los valores de sistema definidos en postgresql.conf.
El siguiente fragmento de código muestra cómo configurar las tablas individuales.
ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_limit = <large_value>) ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_delay = <lower_cost_delay>)
Usar el primer parámetro asegurará que el subproceso de autovacuum asignado a la tabla realice más trabajo antes de quedarse inactivo. Reducir el valor de autovacuum_vacuum_cost_delay también supondrá que el subproceso quede inactivo durante menos tiempo.
Consideraciones finales
Como pueden notar, modificar los parámetros de configuración y de análisis de vacuum es sencillo, pero exige una minuciosa observación previa. Cada base de datos es diferente en cuanto a tamaño, patrones de tráfico y velocidad de las transacciones. Recomendamos que los DBAs empiecen reuniendo suficiente información sobre su base de datos antes de modificar los parámetros o implementar un sistema manual de vacuum/analyze. La información requerida podría ser:
- El número de filas en cada tabla
- El número de tuplas muertas en cada tabla
- La duración del último proceso de vacuum para cada tabla
- La duración del último proceso de analyze para cada tabla
- El índice de inserción/actualización/eliminación de datos en cada tabla
- La duración de autovacuum en cada tabla
- Las alertas relativas a tablas en las que vacuum no haya sido ejecutado
- El rendimiento actual de las consultas más críticas y de las tablas a las que acceden
- El rendimiento de las mismas consultas tras la ejecución de un proceso manual de vacuum/analyze
Desde ahora en adelante, los DBAs pueden seleccionar unas cuantas tablas “piloto” para empezar a optimizar. Pueden empezar a cambiar las propiedades de vacuum/analyze de las tablas y comprobar su rendimiento. PostgreSQL es un motor de base de datos inteligente – los DBAs a menudo descubrirán que probablemente es mejor dejar que PostgreSQL realice los procesos vacuum y analyze en lugar de realizarlos manualmente.