Optimización de Bases de Datos: Estadísticas y Limpieza de Tablas

Clase 28 de 33 • Curso de AWS Redshift para Manejo de Big Data

Resumen

¿Cómo mantener actualizadas las estadísticas de tu base de datos?

Tener estadísticas precisas y adecuadas en tu base de datos es fundamental para optimizar su rendimiento y asegurar que las consultas se ejecuten de manera eficiente. Este proceso se logra con el uso de ciertos comandos que ayudan a mantener la limpieza de las tablas y actualizar la información estadística.

¿Qué es el comando ANALYZE y cómo se utiliza?

El comando ANALYZE se usa para recopilar estadísticas sobre el contenido de una base de datos, facilitando así el trabajo de los optimizadores de consultas. Puedes utilizarlo de diversas maneras:

Por columnas: Si has actualizado ciertas columnas y necesitas que las estadísticas reflejen estos cambios, puedes especificar qué columnas deben ser analizadas. Por ejemplo:
```
ANALYZE sales(pricipet, sales_ID);
```
Esto actualizará las estadísticas de las columnas pricipet y sales_ID de la tabla sales.
Por columnas predicadas: Esta opción se enfoca en las columnas que suelen usarse en filtros o condiciones, como las usualmente identificadas por Revshit. Al mantener estas estadísticas actualizadas, puedes mejorar la resolución de consultas con condiciones.ANALYZE sales.
De toda la tabla: Se puede ejecutar sin especificar columnas lo que actualizará las estadísticas de toda la tabla:
```
ANALYZE sales;
```

¿Cómo saber qué estadísticas están actualizadas?

La tabla stl_analyze es una herramienta útil para ver registros de cuándo se ejecutó el ANALYZE y si se hizo de forma manual o automática. Los análisis automáticos realizados por Repstheet se indican con is_background = true.

¿Qué papel juega el comando VACUUM?

El comando VACUUM es esencial para mantener el espacio limpio en las tablas eliminando filas obsoletas y asegurando la eficiencia del acceso a los datos. Los modos más comunes de VACUUM son:

VACUUM básico: Limpia registros eliminados y mejora el orden de los restantes.
```
VACUUM sales;
```
VACUUM SORT ONLY: Se utiliza cuando una tabla está parcialmente desordenada. Aplica solo si más del 75% de una tabla está ordenada.
```
VACUUM SORT ONLY sales;
```
VACUUM DELETE ONLY: Ideal si has eliminado muchos registros recientemente.
```
VACUUM DELETE ONLY sales;
```
VACUUM REINDEX: Específico para tablas con un ordenamiento intercalado. Es una operación intensa y depende del volumen de datos existentes.
```
VACUUM REINDEX sales;
```

Consejos para optimizar el uso de ANALYZE y VACUUM

Automatización: Aunque puedes ejecutar estos comandos manualmente, a menudo es más eficiente programarlos para que se procesen automáticamente en períodos de baja carga de transacciones.
Monitoreo constante: Asegúrate de revisar periódicamente las tablas pg_statistics_statistic_indicator y sbb_tableinfo para evaluar el estado de las estadísticas y el orden de las tablas.
Consultas EXPLAIN PLAN: Utilizar estadísticas precisas te ayudará a optimizar consultas complejas en función de la particularidad de tus datos.

Estas prácticas no solo sirven para mantener la base en un buen estado de rendimiento, sino para prever posibles ralentizaciones en las consultas. Mantenerte al tanto de cuándo y cómo ejecutar estos comandos puede hacer una diferencia significativa en el rendimiento de tus bases de datos. ¡Recuerda, siempre puedes dejar tus preguntas y comentarios para continuar mejorando!

Mayra Carolina Morataya Guerra

student•

Les comparto los querys:

SELECT *
FROM pg_statistic_indicator
WHERE stairelid = (SELECT DISTINCT id
FROM stv_tbl_perm
WHERE name = 'sales');

SELECT *
FROM pg_table_def
WHERE tablename = 'sales';

analyze sales(salesid, pricepaid);
analyze sales predicate columns;
analyze sales;

SELECT *
FROM stl_analyze;

--Vaccum
SELECT "table", unsorted, vacuum_sort_benefit
FROM svv_table_info;

vacuum sales;
vacuum sort only sales to 75 percent;
vacuum delete only sales to 75 percent;
vacuum reindex sales;

JAVIER SANTIAGO SALGADO

student•

---- Analyze

SELECT * FROM pg_catalog.pg_statistic_indicator;

SELECT * FROM stv_tbl_perm
WHERE name = 'sales'; --101656

SELECT * FROM pg_catalog.pg_statistic_indicator
WHERE stairelid = 101656;

SELECT * FROM pg_catalog.pg_table_def 
WHERE tablename = 'sales';

ANALYZE sales(salesid ,pricepaid);

ANALYZE sales predicate columns;

ANALYZE sales;

SELECT * FROM pg_catalog.stl_analyze;

-----Vacuum

SELECT * FROM svv_table_info;

SELECT "table", unsorted, vacuum_sort_benefit FROM svv_table_info;

VACUUM sales;
VACUUM sort ONLY sales TO 75 PERCENT;
VACUUM DELETE ONLY sales TO 75 PERCENT;
VACUUM reindex sales; -- solo intercalado

Mario Alexander Vargas Celis

student•

Para realizar estadísticas y limpieza en tablas de bases de datos como Redshift, es importante seguir algunos pasos clave. Aquí algunos consejos:

1. Actualización de estadísticas:

Refrescar estadísticas: En Redshift, las estadísticas deben mantenerse actualizadas para que las consultas sean eficientes. Puedes actualizar las estadísticas manualmente o configurar un trabajo automatizado para hacerlo regularmente.ANALYZE TABLE nombre_tabla;
Parámetro relevante:
- autovacuum y vacuum: Mantener una buena gestión de estos parámetros ayuda a mantener las estadísticas y la limpieza de datos.

2. Limpieza de datos:

Eliminar datos antiguos o redundantes:DELETE FROM nombre_tabla WHERE fecha_columna < 'fecha_limite';
Optimización de almacenamiento:VACUUM nombre_tabla;
- Evaluar el uso de compresión y distribución para reducir el tamaño de las tablas.
- Usar vacuum para liberar espacio ocupado por datos eliminados.

3. Optimización de rendimiento:

Revisar y ajustar las distribuciones de las tablas según su uso.
Verificar el uso de índices y optimizar las claves.

Si necesitas más detalles sobre cómo implementar estos procesos, ¡hazmelo saber!

Alfonso Rodrigo Feria Cruz

student•

Hola, tengo una duda, cuando hacemos alter de la llave de distribución y llave de ordenamiento, es recomendable aplicar este comando de limpieza ?

Alarcon7a

student•

Si, al realizar este cambio, la distribucion de nuestros datos no es inmediata, es mejor hacer la limpieza para que se organice como debe ser, usualmente este proceso es automatico en redshift en los momentos de menos uso en el cluster.

Orlando Murcia Perdomo

student•

Hola profe tengo una duda estaba estudiando el tema de las tablas 'PG' pero increíblemente ahora no puedo consultar gran parte de ellas porque al parecer la tabla 'pg_class_extended' se dañó algúna idea del por qué y cómo repararla ?

Alarcon7a

student•

Qué error te sale ???

Carlos Javier Guevara Contreras

student•

Muy Util

Optimización de Bases de Datos: Estadísticas y Limpieza de Tablas

Primeros pasos en la arquitectura no transaccional

Manejo de Big Data con Reptiit en Amazon AWS

Data Warehouse y Modelo Dimensional en Amazon Repsheet

Bases de Datos Columnares: Eficiencia en Consultas Analíticas

Procesamiento de Datos con Repsheet y Clústeres SQL

Configura tu entorno de trabajo para Redshift

Configuración de IAM y S3 en AWS para Repsheet

Configuración de Clúster en Amazon Repsheet para Big Data

Conexión y Configuración de Repsheet con Clientes Externos

Carga de Datos a Redshift desde Amazon S3: Paso a Paso

Cómo diseñar tu base de datos para mejorar su desempeño

Compresión de Datos en Repsheet: Algoritmos y Aplicaciones

Algoritmos de Compresión de Datos: Musley y Otros Métodos Eficientes

Compresión de Datos en SQL: Evaluación y Comparación de Algoritmos

Compresión de Datos en Repsheet: Optimización y Análisis

Algoritmos de Distribución de Datos en Repsheet

Distribución de Datos en Tablas SQL con Repsheet

Llaves de Ordenamiento en Bases de Datos: Compuesta vs. Intercalada

Pruebas de Algoritmos de Ordenamiento en SQL con AWS S3 y Redshift

Consultas SQL y Algoritmos de Ordenamiento Avanzados

Optimización de Datos en Data Warehouses con Repsheet

Manejo de Tipos de Datos en Amazon Redshift

Optimización de Bases de Datos en Modelos Dimensionales

Manipular enormes cantidades de datos

Carga Masiva de Datos en Repshit con el Comando COPY

Cargar datos JSON a Redshift usando el comando Copy

Parámetros Comunes del Comando COPY en Amazon Redshift

Carga Masiva de Datos sin Delimitador en RedSheet

Inserción de Datos en Repsheet sin Archivos Planos

Actualización Eficiente de Datos en Repsheet con Tablas Auxiliares

Optimización de Bases de Datos con Analyze y Vacuum en Repsheet