Diagnóstico de sobrecarga de tablas e índices

Puede utilizar el control de simultaneidad multiversión (MVCC) de PostgreSQL para ayudar a preservar la integridad de los datos. El MVCC de PostgreSQL funciona guardando una copia interna de las filas actualizadas o eliminadas (también denominadas tuplas) hasta que se confirme o anule una transacción. Esta copia interna guardada es invisible para los usuarios. Sin embargo, la tabla se puede sobrecargar si las utilidades VACUUM o AUTOVACUUM no limpian esas copias invisibles con regularidad. Si no se controla, la sobrecarga de las tablas puede generar mayores costes de almacenamiento y ralentizar la velocidad de procesamiento.

En muchos casos, la configuración predeterminada de VACUUM o AUTOVACUUM en Aurora es suficiente para gestionar la sobrecarga no deseada de las tablas. Sin embargo, es posible que desee comprobar si existe sobrecarga si su aplicación presenta las siguientes condiciones:

• Procesa una gran cantidad de transacciones en un tiempo relativamente corto entre los procesos de VACUUM.

• Funciona mal y se queda sin espacio de almacenamiento.

Para empezar, recopile la información más precisa sobre cuánto espacio ocupan las tuplas inactivas y cuánto espacio puede recuperar si elimina la sobrecarga de tablas e índices. Para ello, utilice la extensión pgstattuple para recopilar estadísticas de su clúster de Aurora. Para obtener más información, consulte pgstattuple. Los privilegios para usar la extensión pgstattuple están limitados al rol pg_stat_scan_tables y a los superusuarios de la base de datos.

Para crear la extensión pgstattuple en Aurora, conecte una sesión de cliente al clúster, por ejemplo, psql o pgAdmin, y utilice el siguiente comando:

CREATE EXTENSION pgstattuple;

Cree la extensión en cada base de datos que desee perfilar. Tras crear la extensión, utilice la interfaz de línea de comandos (CLI) para medir cuánto espacio inutilizable puede recuperar. Antes de recopilar estadísticas, modifique el grupo de parámetros del clúster configurando AUTOVACUUM en 0. Un ajuste de 0 impide que Aurora limpie automáticamente las tuplas inactivas que haya dejado la aplicación, lo que puede afectar a la precisión de los resultados. Introduzca el siguiente comando para crear una tabla sencilla:

postgres=> CREATE TABLE lab AS SELECT generate_series (0,100000);
SELECT 100001

En el siguiente ejemplo, ejecutamos la consulta con AUTOVACUUM activado para el clúster de base de datos. dead_tuple_count es 0, lo que indica que AUTOVACUUM ha eliminado los datos o tuplas obsoletos de la base de datos PostgreSQL.

Para usar pgstattuple para recopilar información sobre la tabla, especifique el nombre de la tabla o un identificador de objeto (OID) en la consulta:

postgres=> SELECT * FROM pgstattuple('lab');

    
    
table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056    | 100001      | 2800028   | 77.16         | 0                | 0              | 0                  | 16616      | 0.46
(1 row)
        
    

En la siguiente consulta, desactivamos AUTOVACUUM e introducimos un comando que elimina 25 000 filas de la tabla. Como resultado, dead_tuple_count aumenta a 25 000.

postgres=> DELETE FROM lab WHERE generate_series < 25000;    

DELETE 25000  
   

SELECT * FROM pgstattuple('lab');
    

        
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056 | 75001 | 2100028 | 57.87 | 25000 | 700000 | 19.29 | 16616 | 0.46
(1 row)        
    
    

Para recuperar esas tuplas inactivas, inicie un proceso VACUUM.

Observación de la sobrecarga sin interrumpir la aplicación

La configuración de un clúster de Aurora está optimizada para proporcionar las prácticas recomendadas para la mayoría de las cargas de trabajo. Sin embargo, es posible que desee optimizar un clúster para que se adapte mejor a sus aplicaciones y patrones de uso. En este caso, puede utilizar la extensión pgstattuple sin interrumpir una aplicación ocupada. Para ello, realice estos pasos:

1. Clone su instancia de Aurora.

2. Modifique el archivo de parámetros para desactivar AUTOVACUUM en el clon.

3. Realice una consulta pgstattuple mientras prueba el clon con una carga de trabajo de ejemplo o con pgbench, que es un programa para ejecutar pruebas de referencia en PostgreSQL. Para obtener más información, consulte pgbench.

Tras ejecutar las aplicaciones y ver el resultado, utilice pg_repack o VACUUM FULL en la copia restaurada y compare las diferencias. Si observa una reducción significativa en dead_tuple_count, dead_tuple_len o dead_tuple_percent, ajuste la programación de vacuum en su clúster de producción para minimizar la sobrecarga.

Evitar la sobrecarga en las tablas temporales

Si la aplicación crea tablas temporales, asegúrese de que las elimina cuando ya no sean necesarias. Los procesos Autovacuum no localizan tablas temporales. Si no se seleccionan, las tablas temporales pueden sobrecargar rápidamente la base de datos. Además, la sobrecarga puede extenderse a las tablas del sistema, que son las tablas internas que realizan un seguimiento de los objetos y atributos de PostgreSQL, como pg_attribute y pg_depend.

Cuando ya no necesite una tabla temporal, puede utilizar la instrucción TRUNCATE para vaciar la tabla y liberar espacio. A continuación, vacíe manualmente las tablas pg_attribute y pg_depend. Al vaciar estas tablas, se garantiza que al crear y truncar/eliminar tablas temporales de forma continua no se añaden tuplas ni se contribuye a la sobrecarga del sistema.

Para evitar este problema al crear una tabla temporal, incluya la siguiente sintaxis, que elimina las filas nuevas cuando se confirma el contenido:

CREATE TEMP TABLE IF NOT EXISTS table_name(table_description) ON COMMIT DELETE ROWS;

La cláusula ON COMMIT DELETE ROWS trunca la tabla temporal cuando se confirma la transacción.

Evitar la sobrecarga en los índices

Al cambiar un campo indexado de una tabla, la actualización del índice da como resultado una o más tuplas inactivas en ese índice. El proceso autovacuum elimina la sobrecarga de los índices de forma predeterminada, pero esa limpieza consume una cantidad importante de tiempo y recursos. Para especificar las preferencias de limpieza del índice al crear una tabla, incluya la cláusula vacuum_index_cleanup. De forma predeterminada, en el momento de creación de la tabla, la cláusula se establece en AUTO, lo que significa que el servidor decide si es necesario limpiar el índice cuando vacía la tabla. Puede establecer la cláusula en ON para activar la limpieza de índices de una tabla específica, o en OFF para desactivarla. Recuerde que, aunque es posible que ahorre tiempo si desactiva la limpieza de índices, podría dar lugar a un índice sobrecargado.

Puede controlar manualmente la limpieza de índices si utiliza VACUUM en una tabla en la línea de comandos. Para vaciar una tabla y eliminar las tuplas inactivas de los índices, incluya la cláusula INDEX_CLEANUP con el valor ON y el nombre de la tabla:

acctg=> VACUUM (INDEX_CLEANUP ON) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM

Para vaciar una tabla sin limpiar los índices, especifique el valor OFF:

acctg=> VACUUM (INDEX_CLEANUP OFF) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM