Rendimiento lento de la base de datos

Los síntomas típicos de este problema son los siguientes:

• Alto uso de E/S (entrada/salida) del disco o de la memoria
• Consultas lentas

La causa más común es que se estén examinando consultas lentas. Siga los siguientes pasos para determinar si este es el caso:

1. Enumera las consultas de larga duración:

   select age(now(),query_start), pid, client_addr, state, substring(query,0,80) from pg_stat_activity where state != 'idle' order by 1 desc;
   

2. Validar bloqueo de consultas:

   select pg_blocking_pids(pid), pid AS blocked_pid, state, query, age(now(),query_start) AS duration from pg_stat_activity where array_length(pg_blocking_pids(pid),1) > 0 order by 1,2;
   

3. Comprueba el plan de ejecución de la consulta:

   Explain <query>;
   

4. Comprueba si faltan estadísticas de la base de datos. Utilice la siguiente consulta para validar: Acción > Ejecutar análisis Como alternativa, ejecute el siguiente comando :

   Analyze <tablename>  select relname, n_live_tup, n_dead_tup, last_vacuum,last_autovacuum,last_analyze,last_autoanalyze, analyze_count,autoanalyze_count from pg_stat_all_tables where schemaname='public';
   

5. Comprueba si falta el índice en la tabla. PostgreSQL proporciona métodos de índice como B-Tree, hash, GiST,SP-GiST, GIN y BRIN. Utilice uno de estos métodos para crear el índice.

6. Comprueba si la tabla está inflada. Ejecute la siguiente consulta para validar. Acción > Ejecutar vacío

   Como alternativa, utilice la siguiente instrucción:

   SELECT
   relname AS table_name,
   pg_size_pretty(pg_relation_size(c.oid)) AS actual_size,
   pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24))) * 100 / pg_relation_size(c.oid)
   END
   ) AS bloat_percentage, pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24)))
   END
   )AS bloat_size
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE relkind = 'r' AND n.nspname = 'public' -- Adjust schema if needed
   ORDER BY bloat_size DESC NULLS LAST;
   

7. Considere si la base de datos o los objetos se han creado con pg_dump o pg_restore. Utiliza Acción > Actualizar estadísticas para refrescar.

8. Asegúrese de que se work_mem asigna suficiente si la consulta está completando una operación de ordenación.

9. Utilice pg_stat las declaraciones para obtener la siguiente información:

   • consultar el tiempo medio de ejecución y el número de llamadas

   • Porcentaje de uso de la CPU por las consultas

   • Porcentaje de uso de memoria por las consultas

pg_stat_activity, pg_stat_bgwriter, y pg_buffercache son herramientas importantes en PostgreSQL para supervisar y comprender el rendimiento de las bases de datos.

Supervisión del despliegue y supervisión de la carga de la base de datos

Databases for PostgreSQL ofrecen una integración con el servicio IBM Cloud Monitoring para supervisar el uso de recursos en su despliegue. Utilice este servicio para supervisar su implementación (disco y memoria) y la carga de su base de datos.

Utilice Cloud Databases los paneles de control para configurar alertas sobre los umbrales de CPU, memoria y IOPS del disco. Muchas de las métricas disponibles, como el uso del disco y las IOPS, son útiles para ayudarte a configurar el autoescalado en tu despliegue. El autoescalado no está habilitado de forma predeterminada, por lo que debe configurarlo manualmente.

Observar las tendencias en su uso y configurar el autoescalado para responder a estas tendencias puede ayudar a aliviar los problemas de rendimiento antes de que sus bases de datos se vuelvan inestables debido al agotamiento de los recursos. Por ejemplo, cambios en la E/S del disco o fallos de la CPU o la memoria que provocan problemas de rendimiento.

IOPS de disco

El número de operaciones de entrada/salida por segundo (IOPS) está limitado por el tipo y el tamaño del volumen de almacenamiento. Los volúmenes de almacenamiento para Databases for PostgreSQL implementaciones se aprovisionan en volúmenes Block Storage de resistencia en el nivel de 10 IOPS por GB.

Si la carga operativa satura o supera el límite de IOPS, las peticiones y operaciones de la base de datos se retrasan hasta que el subsistema de almacenamiento puede ponerse al día. Los periodos prolongados de gran carga pueden provocar que su implantación no sea capaz de procesar las consultas y deje de estar disponible. Puede aumentar el número de IOPS disponibles para su implantación aumentando el tamaño de los discos. Por ejemplo, para 10 IOPS por nivel de GB, puede aumentar las IOPS aumentando el tamaño del volumen.

Los periodos prolongados de utilización del disco, incluso del 40-50 %, pueden tener un impacto negativo significativo en el rendimiento de la base de datos. Asigne al menos 100 GB de disco (1.000 IOPS) para entornos de producción. IOPS = 10 × GB asignados (por ejemplo, 100 GB = 1000 IOPS)

Uso de memoria

La memoria es la forma más rápida y eficiente de acceder y procesar datos. Por este motivo, una base de datos casi siempre funciona más rápido utilizando la memoria en lugar de leer los datos del disco. Hay otras métricas que hay que tener en cuenta y considerar, como las visitas a la caché, los bloques leídos y los bloques visitados. El simple hecho de ver un uso de memoria del 100 % no es motivo de preocupación en sí mismo. Si se agota la memoria y se utilizan páginas de intercambio, el rendimiento puede verse significativamente reducido.

Puede establecer la cantidad de memoria dedicada al conjunto de búferes compartidos de la base de datos ajustando shared_buffers en la configuración de Databases for PostgreSQL. El valor máximo recomendado es el 25% de la memoria total de la implantación. Asignar demasiada memoria a la reserva de búferes compartidos puede privar al sistema de memoria para otros fines, puede entorpecer el rendimiento o incluso desactivar la base de datos.

Supervisión de la carga de la base de datos

Tienes dos opciones para comprobar la carga de la base de datos:

{

source logs|filter message.attr.durationMillis>=1000

}

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT username, database, queryid, query_preview, calls, total_exec_time, pct_exec_time, ROUND(SUM(pct_exec_time) OVER (ORDER BY total_exec_time DESC)) AS cum_pct_exec_time, avg_exec_time FROM ( SELECT pu.usename AS username, pd.datname AS database, pss.queryid, LEFT(pss.query, 50) AS query_preview, pss.calls, ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time, ROUND((100.0 * pss.total_exec_time / SUM(pss.total_exec_time) OVER ())::numeric, 2) AS pct_exec_time, ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time FROM pg_stat_statements pss JOIN pg_user pu ON pss.userid = pu.usesysid JOIN pg_database pd ON pss.dbid = pd.oid ) AS subquery ORDER BY total_exec_time DESC LIMIT 25;

SELECT
   username,
   database,
   queryid,
   query_preview,
   calls,
   pct_calls,
   ROUND(SUM(pct_calls) OVER (ORDER BY calls DESC)) AS cum_pct_calls,
   total_exec_time,
   avg_exec_time
FROM (
   SELECT
      pu.usename AS username,
      pd.datname AS database,
      pss.queryid,
      LEFT(pss.query, 50) AS query_preview,
      pss.calls,
      ROUND(100.0 * pss.calls / SUM(pss.calls) OVER (), 2) AS pct_calls,
      ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time,
      ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time
   FROM
      pg_stat_statements pss
      JOIN pg_user pu ON pss.userid = pu.usesysid
      JOIN pg_database pd ON pss.dbid = pd.oid
) AS subquery
ORDER BY
   calls DESC
LIMIT 25;

SELECT query, calls, total_exec_time/calls as avg_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Otras medidas que puede tomar para mejorar el rendimiento

También puede considerar las siguientes acciones para solucionar problemas de rendimiento:

• Optimizar las consultas lentas

  • Ejecute EXPLAIN (ANALYZE, BUFFERS) en consultas lentas. Esto muestra cómo la base de datos ejecuta la consulta y dónde es lenta. Por ejemplo:

  EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM student WHERE student_id = 12345;
  

  • Busque:

    • Consultas que utilizan mucha memoria o espacio en disco

    • Añadir IBM Cloud recursos según sea necesario

    • Amplíe el disco/memoria para obtener mayores IOPS y memoria

• Los índices que faltan o son ineficaces son una causa habitual de consultas lentas. Utilice EXPLAIN para identificar los escaneos secuenciales y considere la posibilidad de añadir índices.

• Ejecute VACUUM para ayudar con el análisis del estado de la base de datos.

• Considere la posibilidad de utilizar la agrupación de conexiones para gestionar más conexiones. Databases for PostgreSQL establece en 115 el número máximo de conexiones a su base de datos Databases for PostgreSQL. 15 conexiones están reservadas para el superusuario para mantener el estado y la integridad de tu base de datos, y 100 conexiones están disponibles para ti y tus aplicaciones. Una vez alcanzado el límite de conexiones, cualquier intento de iniciar una nueva conexión produce un error. Para evitar saturar despliegue con conexiones, utilice agrupación de conexiones o reduzca el despliegue y aumente el límite de conexiones. Para obtener más información, consulte Administración del PostgreSQL agrupamiento de conexiones.

• Comprueba si hay alguna receta que esté realizando copias de seguridad y cargando datos por lotes, por ejemplo: Las copias de seguridad automáticas se realizan a diario y se conservan durante un periodo de retención de 30 días. Si una copia de seguridad se ha quedado atascada, puede consultar la sección Copias de seguridad disponibles e identificar la copia de seguridad atascada en la página de la interfaz de usuario de la nube de la instancia de la base de datos.

• Comprueba tus IBM Cloud notificaciones para ver si hay algún mantenimiento. Por ejemplo, la aplicación de parches a bases de datos.

• Si cree que se trata de un problema de la plataforma, como por ejemplo un mantenimiento, póngase en contacto con IBM el servicio de asistencia técnica indicando el número de referencia de la base de datos.