Consulta y análisis de Top SQL

Descripción

El entorno complejo de O&M de las bases de datos distribuidas de DWS es propenso a emergencias como saltos en el plan de ejecución, interrupciones inesperadas y transacciones de larga duración. Los enfoques tradicionales de O&M no pueden reproducir excepciones, rastrear rutas de ejecución ni retener métricas de tiempo de ejecución. Esto convierte a la localización de fallas en un proceso que consume muchos recursos y que a menudo es cíclico.

Para abordar estos desafíos, DWS introduce el monitoreo de Top SQL. Rastrea tanto las sentencias SQL en tiempo real como las históricas para:

• Localizar rápidamente las sentencias de consulta que afectan al rendimiento de la base de datos y consumen la mayor cantidad de recursos.
• Monitorear los cambios de rendimiento de estas sentencias de consulta con el tiempo.
• Analizar el plan de ejecución de consultas para determinar los métodos de optimización potenciales.

Como una herramienta de soporte principal para O&M de bases de datos, el monitoreo de Top SQL se ha integrado profundamente en varios escenarios del entorno de producción para ayudar a diagnosticar el rendimiento del sistema, rastrear el deterioro de SQL y rastrear la auditoría de seguridad. La matriz de monitoreo cubre múltiples métricas principales, como el uso de memoria, el tiempo de ejecución, el throughput de E/S, la latencia de red y el espacio de almacenamiento.

Principios de Top SQL

Después de que se entrega y ejecuta un trabajo, el kernel de la base de datos registra la información de recursos del trabajo, como memoria, CPU, desbordamiento, E/S e información de red, mediante el uso de stubs. La información se almacena en la cola sin bloqueo y luego se volca al catálogo del sistema dbms_om.gs_wlm_session_info por el subproceso auxiliar de fondo de gestión de recursos. Posteriormente, los datos se envejecen periódicamente mediante el uso de topsql_retention_time.

Figura 1 Principios de Top SQL

Parámetros GUC relacionados

Después de crear un clúster, el monitoreo de Top SQL se habilita por defecto. Esta función está controlada por los parámetros use_workload_manager, enable_resource_track y enable_resource_record (Su valor predeterminado es on). Para obtener más información sobre los parámetros, consulte Tabla 1.

1

SELECT * FROM pgxc_total_memory_detail WHERE nodename LIKE 'cn_%' AND memorytype LIKE '%topsql%' ORDER BY 1,2;

Cómo configurar los parámetros GUC

Configure los parámetros en la consola de DWS.

1. Inicie sesión en la consola de DWS. En el panel de navegación, seleccione Dedicated Clusters > Clusters.
2. En la lista de clústeres, busque el clúster objetivo y haga clic en el nombre del clúster. Se muestra la página Cluster Information.
3. Haga clic en la pestaña Parameters y modifique los valores de los parámetros. A continuación, haga clic en Save.

Sentencias SQL comunes para consultar la configuración de los parámetros GUC

SELECT name,setting FROM pg_settings WHERE name LIKE '%resource%';

Vistas y métodos de análisis

El nivel de monitoreo SQL superior se determina mediante el parámetro resource_track_level. El valor puede ser query (predeterminado), perf, operator_realtime u operator.

• query: La información del plan (similar a la salida de EXPLAIN) de las sentencias SQL se registra en las sentencias de Top SQL.
• perf: La información del plan (similar a la salida de EXPLAIN ANALYZE) que contiene el tiempo de ejecución real y el número de filas ejecutadas se registra en las sentencias de Top SQL.
• operator_realtime: La información sobre los operadores de trabajos en tiempo real se registra en las sentencias de Top SQL, pero no se conserva en las sentencias de Top SQL históricas.
• operator: La información que se registrará incluye el tiempo de ejecución real, el número de filas ejecutadas y la ejecución del operador.

Las sentencias de Top SQL se transportan mediante vistas en Tabla 2. Puede analizar la información de Top SQL en función de los campos de las vistas. Para obtener más información sobre los campos de análisis comunes en las vistas, consulte Tabla 3.

Conclusión:

1. El trabajo está procesando una cantidad de datos mucho mayor que antes. Puede analizar A2, B1, D1 y G1 para verificar si hay un aumento sustancial en el volumen de datos de las tablas consultadas.
2. Actualmente, las vistas SQL históricas principales contienen una gran cantidad de campos. Puede ver la vista PGXC_QUERY_INFO para consultar los campos clásicos, en lugar de filtrar manualmente los campos irrelevantes.
3. La competencia por recursos de otros trabajos es una causa frecuente de ralentizaciones. Para la cola de trabajos, puede analizar A1, B1 y D1 para verificar si se ejecuta una gran cantidad de trabajos simultáneos durante la ejecución del trabajo.
4. Para la contención de CPU, puede analizar A2/D1/E1 para verificar si se está ejecutando una gran cantidad de trabajos simultáneos.
5. Para la contención de E/S, puede analizar A2/F1 para verificar si se está ejecutando una gran cantidad de trabajos simultáneos.
6. Un cierto tipo de sentencias utiliza muchos recursos. Puede analizar H1 para prohibir su ejecución.

La contención de CPU, la contención de E/S y la cola de trabajos pueden ocurrir simultáneamente durante la contención de recursos. Puede analizar y resolver los problemas paso a paso. Por ejemplo:

1. Ajuste la secuencia de ejecución del trabajo, reduzca el número de trabajos simultáneos y reduzca el tiempo de bloqueo.
2. Identifique y cambie los trabajos intensivos de cómputo y almacenamiento a períodos fuera de horas pico para minimizar la contención con trabajos prioritarios.
3. Si no intervienen otros trabajos, realice un análisis más profundo.

Notas y restricciones

• Top SQL no registra las sentencias de la lista blanca ni las internas, pero registra las sentencias entregadas por superusuarios y tareas programadas.
• La diferencia clave entre la consulta y el rendimiento del campo query_plan. query_plan proporciona detalles a nivel de operador en query, pero en perf, el campo complementa esto con estadísticas de tiempo de ejecución, como el uso real de memoria, la información de expansión automática de memoria y los datos de CU/búferes.
• Para los niveles de query y perf, el campo start_time representa el tiempo de entrega del trabajo en top SQL en tiempo real, pero el tiempo que el trabajo comienza a ejecutarse en el historial de Top SQL.
• Al consultar las sentencias históricas de Top SQL de los niveles de query, perf y operator, solo se puede conectar a la base de datos postgres.
• Restricciones en las sentencias de Top SQL en tiempo real:
  1. Las sentencias especiales, como SET, RESET, SHOW, ALTER SESSION SET y SET CONSTRAINTS, no se registran.
  2. Las sentencias DDL, como CREATE, ALTER, DROP, GRANT, REVOKE y VACUUM, se registran.
  3. Se registran las sentencias DML, incluidas:
     1. SELECT, INSERT, UPDATE y DELETE
     2. EXPLAIN ANALYZE y EXPLAIN PERFORMANCE
     3. Vistas en el nivel de query o perf
  4. Se registran las sentencias de entrada para invocar funciones y procedimientos almacenados. Cuando el parámetro GUC enable_track_record_subsql está habilitado, se pueden registrar algunas sentencias internas (excepto la sentencia de definición DECLARE) de un procedimiento almacenado. Solo se registran las sentencias internas entregadas a los DN para su ejecución.
  5. Se registra la sentencia de bloque anónimo. Cuando el parámetro GUC enable_track_record_subsql está habilitado, se pueden registrar algunas sentencias internas de un bloque anónimo. Solo se registran las sentencias internas entregadas a los DN para su ejecución.
  6. Se registran las sentencias de cursor. El sistema solo registra las sentencias de cursor que se ejecutan en los DN. Se mejoran la sentencia y el plan de ejecución. Si los datos de un cursor se sirven desde la caché, la sentencia no se registra. Además, una limitación arquitectónica conocida impide el registro de datos de monitoreo para cursores dentro de funciones o bloques anónimos que leen grandes cantidades de datos de un DN, pero no consumen por completo el conjunto de resultados. La sintaxis del cursor With Hold tiene una lógica de ejecución especial. Ejecuta consultas cuando se confirma una transacción. Si se informa de un error de ejecución de sentencia, el estado aborted del trabajo no se puede registrar en la tabla de historial de Top SQL.
  7. No se recopilan estadísticas para los trabajos en el proceso de redistribución.
  8. Para una sentencia con marcadores de posición ejecutada por JDBC, el contenido del parámetro suele complementarse. Sin embargo, si la longitud total del parámetro y la sentencia original supera los 64 KB, el parámetro no se registra. Las sentencias ligeras se entregan directamente a los DN para su ejecución y sus parámetros no se registran. Si una sentencia común supera los 64 KB, se truncará. Para obtener más información, consulte el campo query.
  9. En clústeres de la versión 8.1.3 o posterior, el monitoreo de SQL principal a nivel de consulta y rendimiento no afecta el rendimiento de las consultas. El valor predeterminado de resource_track_cost es 0. Cuando consulta las vistas de monitoreo en tiempo real, todas las sentencias que se están ejecutando se muestran de forma predeterminada.
  10. En clústeres de la versión 8.1.3 o posterior, si enable_track_record_subsql está habilitado, independientemente de si el monitoreo de sub-sentencias está habilitado en las sentencias de servicio, puede ver la información de ejecución de sub-sentencias en las vistas de monitoreo en tiempo real.
  11. En los clústeres de la versión 8.1.3, las sentencias no se pueden filtrar por tiempo. Una vez habilitado enable_track_record_subsql, es posible que se registren demasiadas sentencias y que la tabla de monitoreo archivada ocupe una gran cantidad de espacio en disco. Se recomienda habilitar solo los parámetros en sesiones específicas cuando se consulte información de monitoreo en tiempo real o se localicen y analicen algunos procedimientos almacenados. En los clústeres de la versión 8.2.1, puede utilizar resource_track_subsql_duration para filtrar las sub-sentencias que se van a archivar en función del tiempo de ejecución. Su valor predeterminado es de 180 segundos y puede cambiar el valor según sea necesario.
  12. Para una sentencia principal que no se ha volcado a los discos, su registro en la tabla de historial de Top SQL solo se muestra cuando se entrega el siguiente trabajo.
  13. En los clústeres de la versión 8.2.1.200 o posterior, puede habilitar el monitoreo de nivel operator_realtime para consultar el plan de ejecución y la información detallada de ejecución de una sentencia. Cuando consulta las vistas de monitoreo operator-realtime de las sentencias de Top SQL, todas las sentencias que se están ejecutando se muestran de forma predeterminada. Sin embargo, en los escenarios de procedimientos almacenados y cursores, no se puede mostrar la información de monitoreo operator-realtime.
  14. El monitoreo operator_realtime no se admite para sentencias de CN livianas y procedimientos almacenados. Los operadores se ejecutan a alta velocidad, por lo que hay un retraso en la visualización de la información del operador.
  15. El campo spill_size a nivel de consulta (monitoreo de trabajos) y a nivel de operador (monitoreo de operadores) varía debido a la dimensión estadística. El campo indica los archivos de sentencias volcados a los discos a nivel de consulta, mientras que a nivel de consulta, indica el volumen de E/S de lectura y escritura de un operador específico en la capa lógica.
  16. Cuando enable_stream_operator se establece en off, la información de ejecución del operador que se muestra puede ser inexacta.
• Para los clústeres de la versión 8.1.3, excepto para el usuario inicial, si enable_gtm_free está habilitado y la cola unida no está controlada, los trabajos de usuario no se gestionan en la gestión de recursos. El sistema no registra los trabajos entregados por el usuario en tiempo real ni en las sentencias SQL históricas principales.

Identificación de sentencias SQL que consumen muchos recursos mediante el monitoreo de Top SQL

• Identifique las sentencias con una gran cantidad de flujos.

  SELECT *,(length(query_plan) - length(replace(query_plan, 'Streaming', ''))) / length('Streaming') AS stream_count FROM pgxc_wlm_session_info ORDER BY stream_count DESC limit 100; 

• Identifique las sentencias con un alto uso de memoria.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' ORDER BY max_peak_memory desc limit 100; 

• Identifique las sentencias que se deben optimizar.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' AND warning is not null ORDER BY duration desc limit 100; 

• Identifique las sentencias que tardan mucho tiempo en ejecutarse.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' ORDER BY duration desc; 

• Identifique las sentencias que no se pueden delegar.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' AND warning like '%can not be shipped%' ORDER BY max_peak_memory desc;

• Identifique las sentencias con un alto uso de CPU.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' ORDER BY max_cpu_time desc; 

• Identifique las sentencias con una gran cantidad de datos que se deben escribir en los discos.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' ORDER BY max_cpu_time desc; 

• Identifique las sentencias que no se analizan.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' AND warning ilike '%statistics%';

Monitoreo de las sentencias SQL principales a nivel de trabajo de PERT

En la red en vivo, una sentencia SQL que inicialmente se ejecuta rápidamente puede experimentar ralentizaciones más tarde. Para diagnosticar esto, utilice query_plan y datos de recursos en las sentencias de Top SQL. Un query_plan detallado actúa como un EXPLAIN PERFORMANCE y simplifica la localización de fallas. La diferencia clave entre las sentencias SQL principales a nivel de consulta y a nivel de rendimiento es el detalle en el query_plan. query_plan a nivel de perf proporciona detalles de ejecución para cada operador, incluidos el tiempo real, el uso de memoria, las filas procesadas y la actividad del búfer, mientras que query_plan a nivel de query no lo hace.

Monitoreo de las sentencias de Top SQL principales a nivel de OPERATOR

Para monitorear el progreso a nivel de operador de sentencias SQL de ejecución prolongada, establezca resource_track_level en OPERATOR. Esto permite a los usuarios identificar operadores lentos por su tiempo de ejecución y filas procesadas, y determinar si se deben eliminar las sentencias SQL.

El monitoreo de operadores visualiza los datos de ejecución de SQL, lo que proporciona una visión general intuitiva del estado y el rendimiento de los operadores. Sus beneficios incluyen:

• Experiencia de usuario mejorada: Obtiene una comprensión inmediata e intuitiva del comportamiento y el estado de los operadores.
• Ajuste de rendimiento simplificado: Puede identificar rápidamente los cuellos de botella y las ineficiencias de rendimiento dentro de los operadores para optimizar la velocidad de ejecución.
• Resolución de problemas acelerada: Puede detectar excepciones y problemas de tiempo de ejecución en tiempo real, lo que permite una remediación más rápida y mejora la capacidad de mantenimiento general de SQL.
• Planificación de escalabilidad informada: Puede descubrir y eliminar cuellos de botella, lo que aumenta la escalabilidad de los operadores para el crecimiento futuro del negocio.

El monitoreo de operadores es similar al monitoreo de sentencias de query/perf. Ambos incluyen información en tiempo real e histórica o información estática y dinámica.

• La información estática de las sentencias es generada por el optimizador antes de que se ejecute una sentencia. La información incluye el nombre del nodo del plan de ejecución, el ID de la consulta y las filas estimadas. Puede utilizar la información para analizar si el plan de ejecución generado es apropiado.
• La información dinámica de las sentencias se refiere a la información de recursos ocupados durante la ejecución de sentencias en el ejecutor. La información incluye el progreso del operador, la memoria máxima, el tamaño de desbordamiento, el tamaño de la red, la E/S de disco (bytes de lectura y bytes de escritura) y el tiempo de CPU en cada DN. Puede utilizar la información para analizar el progreso y el consumo de recursos durante la ejecución de sentencias.

Ejemplo: Consulte el operador view pgxc_wlm_operator_statistics en tiempo real en otra sesión. El resultado del comando es el siguiente.

Interacción entre las vistas de Top SQL y otras vistas

Las diferentes vistas recopilan estadísticas sobre el estado de la base de datos desde diferentes dimensiones. Dado que estas vistas comparten campos comunes, se pueden unir para realizar un análisis más detallado. La siguiente figura enumera otras vistas que se utilizan con frecuencia con las vistas históricas de Top SQL.

Figura 2 Interacción entre las vistas de Top SQL y otras vistas

Ejemplo: La vista histórica pgxc_wlm_session_info muestra que una sentencia se completa rápidamente, pero la vista pgxc_stat_activity muestra que la sentencia se ha ejecutado durante mucho tiempo. En este caso, puede producirse una excepción.

Realice los siguientes pasos para localizar la falla:

1. Consulte la vista pgxc_stat_activity para encontrar los trabajos anormales que se han estado ejecutando durante mucho tiempo.

   SELECT coorname, usename, client_addr, now()-query_start as dur, state, enqueue, waiting, pid, query_id, substr(query,1,150) FROM pgxc_stat_activity WHERE usename not in ('omm','Ruby') AND state = 'active' ORDER BY dur DESC limit 100; 

2. Consulte la vista histórica utilizando la información obtenida en 1 y compare el tiempo de ejecución de la sentencia antes y después de la falla. Un aumento marcado en la duración sugiere una sentencia anormal. La vista está particionada por día. Puede utilizar start_time para consultar la vista histórica.

   SELECT * FROM pgxc_wlm_session_info WHERE start_time > '2024-08-22 00:00' AND start_time < '2024-08-24 00:00' AND query ilike '%XXXXXXXXX%' ORDER BY start_time; 

3. Consulte la vista pgxc_thread_wait_status en función del query_id obtenido en 1, obtenga el número de subproceso del trabajo lwtid e imprima la pila de trabajos para su análisis.

   SELECT * FROM pgxc_thread_wait_status WHERE query_id = xxx; 
   SELECT * FROM gs_stack('nodename',tid); 

Análisis y gestión de casos de Top SQL

Cuando hay una mayor cantidad de información sobre las sentencias SQL principales, puede utilizar start_time para dirigirse a intervalos de tiempo específicos y LIMIT para restringir el conjunto de resultados. Esto evita los análisis completos de tablas y evita problemas del lado del cliente debido a datos excesivos.

• Caso 1: El rendimiento a nivel de sistema de un clúster de un cliente es deficiente, el uso de la CPU sigue aumentando y los servicios se ven afectados.

  La vista histórica de Top SQL muestra que más de 10 sentencias SQL tienen más de 100 flujos. El uso de la CPU es elevado.

  SELECT *,(length(query_plan) - length(replace(query_plan, 'Streaming', ''))) / length('Streaming') as stream_count FROM pgxc_wlm_session_info ORDER BY stream_count DESC limit 100;

  

  Solución: Ponga la sentencia SQL fuera de línea y optimícela.

• Caso 2: Una sentencia se ejecuta rápidamente al principio y luego se ralentiza.

  El monitoreo de Top SQL registra la ejecución de sentencias y el consumo de recursos para ayudarle a diagnosticar problemas de rendimiento. Por ejemplo, si una sentencia SQL periódica se ralentiza, puede comparar los tiempos de ejecución y bloqueo para determinar si estaba en cola o es realmente lenta. Analice el plan de ejecución para verificar si hay estadísticas obsoletas u operaciones ANALYZE faltantes. Verifique si el bajo rendimiento se debe a una gran cantidad de datos desbordados a los discos.

  1. Verifique la información histórica de SQL en la vista pgxc_wlm_session_info. (Nota: La vista histórica se particiona por día. Al consultar la vista, agregue start_time.)

     SELECT * FROM pgxc_wlm_session_info WHERE start_time > '2023-08-22 00:00' AND start_time < '2023-08-24 00:00' AND query ilike '%XXXXXXXXX%' ORDER BY start_time; 

  2. Determine el estado de ejecución histórica de los trabajos basándose en sql_hash y analice la causa del deterioro del rendimiento del trabajo basándose en la información de recursos y el campo query_plan de las sentencias SQL históricas principales.

     SELECT start_time, block_time, duration, sql_hash, warning, max_peak_memory, max_spill_size, query_plan FROM pgxc_wlm_session_info were start_time > 'xxxx-xx-xx xx:xx' and sql_hash = 'xxx' ORDER BY start_time desc limit 10; 

     Compare los planes de ejecución (query_plan) de las sentencias rápidas y lentas. Se detecta que los planes de ejecución cambian mucho.

     

  3. Realice ANALYZE en la tabla correspondiente y elija un plan adecuado. Después de eso, se restaura el rendimiento de la sentencia.

     Las estadísticas obsoletas a menudo causan cambios en el plan de ejecución y consultas lentas. Para evitar este problema, ejecute ANALYZE, que no afecta a las operaciones de lectura y escritura.

     ANALYZE dwrdim_dw1.dwr_dim_region_rc_d;

• Caso 3: Un trabajo se ejecuta durante mucho tiempo y no termina.

  Si un trabajo se ejecuta sin colas ni bloqueos, pero tarda demasiado, puede utilizar el monitoreo operator_realtime para identificar los operadores lentos. En función de la duración de un operador y las filas procesadas, puede decidir si eliminar la sentencia.

  1. Habilite el monitoreo operator_realtime.

     SET resource_track_level = 'operator_realtime';

     

  2. Elimine el trabajo según sea necesario.

     SELECT * FROM pg_terminate_backend (xxx); #The input parameter is pid.

• Caso 4: El uso general de la memoria de un trabajo es alto. Debe analizar el uso de la memoria de los operadores.

  Cuando el uso general de la memoria de un trabajo es alto, el monitoreo a nivel de consulta no puede capturar los detalles de los recursos a nivel de operador. Para resolver esto, establezca el nivel de monitoreo en perf. Este nivel registra datos de ejecución más detallados con una sobrecarga de rendimiento inferior al 5 %. El campo query_plan del monitoreo a nivel perf puede mostrar el tiempo del operador (actual time, memory, rows, y buffer).

  1. Habilite el monitoreo a nivel perf.

     SET resource_track_level = 'perf';

  2. Después de ejecutar un trabajo, consulte la vista pgxc_wlm_session_info para verificar el uso de la memoria de cada operador.