GreenPlum 执行缓慢排查过程

一、排查过程

1、查看服务器负载

free -m

top

2、查看连接数

SELECT COUNT(*) FROM pg_stat_activity WHERE STATE NOT LIKE '%idle';

3、慢 SQL

SELECT 
  datname, 
  usename, 
  client_addr, 
  application_name, 
  state, 
  backend_start, 
  xact_start, 
  xact_stay, 
  query_start, 
  query_stay, 
  REPLACE(
    QUERY, 
    chr(10), 
    ' '
  ) AS QUERY 
FROM 
  (
    SELECT 
      pgsa.datname AS datname, 
      pgsa.usename AS usename, 
      pgsa.client_addr client_addr, 
      pgsa.application_name AS application_name, 
      pgsa.state AS state, 
      pgsa.backend_start AS backend_start, 
      pgsa.xact_start AS xact_start, 
      EXTRACT(
        epoch 
        FROM 
          (NOW() - pgsa.xact_start)
      ) AS xact_stay, 
      pgsa.query_start AS query_start, 
      EXTRACT(
        epoch 
        FROM 
          (NOW() - pgsa.query_start)
      ) AS query_stay, 
      pgsa.query AS QUERY 
    FROM 
      pg_stat_activity AS pgsa 
    WHERE 
      pgsa.state != 'idle' 
      AND pgsa.state != 'idle in transaction' 
      AND pgsa.state != 'idle in transaction (aborted)'
  ) idleconnections 
ORDER BY 
  query_stay DESC 
LIMIT 
  5;

二、解决办法

1、Kill 慢SQL

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE  QUERY LIKE '%<query text>%' AND pid != pg_backend_pid();
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE  QUERY LIKE '%<query text>%' AND pid != pg_backend_pid();

2、更新表的统计信息，使查询计划更准确

ANALYZE <table> 或    VACUUM ANZLYZE <table>

3、查看执行计划

explain [sql statement]

4、SQL调优

重新编写SQL，去除掉不必要的子查询、改写UNION ALL、使用JOIN CLAUSE固定连接顺序等。