檢查數(shù)據(jù)庫日志

• 日志相關(guān)配置官方文檔

• 在日志中查看哪些可能是慢查詢語句。

通過查詢分析器(EXPLAIN)分析語句

看是否有順序掃描(Seq Scan)等低效查詢發(fā)生，如：

EXPLAIN 
SELECT u.username, u.display_name, u.avatar_url, u.lang_tag, u.location, u.timezone, u.metadata, u.wallet,
                u.email, u.facebook_id, u.google_id, u.gamecenter_id, u.steam_id, u.custom_id, u.edge_count,
                u.create_time, u.update_time, u.verify_time, array(select ud.id from user_device ud where u.id = ud.user_id)
FROM users u
WHERE u.id = '4ee52024-e64d-4017-8807-897a1f7249e6';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using users_pkey on users u  (cost=0.42..87.56 rows=1 width=3048)
   Index Cond: (id = '4ee52024-e64d-4017-8807-897a1f7249e6'::uuid)
   SubPlan 1
     ->  Seq Scan on user_device ud  (cost=0.00..79.12 rows=1 width=36)
           Filter: (u.id = user_id)

寫腳本以固定時間間隔輸出對pg_stat_activity的查詢結(jié)果

• 查看耗時較長的查詢

SELECT
    pid,
    NOW(),
    NOW()-query_start AS query_duration,
    query
  FROM pg_stat_activity
  WHERE datname = 'nakama'
    AND pid != pg_backend_pid()
    AND state != 'idle'
ORDER by query_duration DESC;

• 分析腳本定期輸出的結(jié)果，找到慢查詢語句。