PostgreSQL DBA 日常管理 SQL

select pid,state,query_start,xact_start,now()-query_start,wait_event_type,wait_event,query from pg_stat_activity where now()-query_start > '5 s' order by query_start;

select * from pg_prepared_xacts ;

6、根据PID杀会话或QUERY，

查询当前系统在执行的SQL，PID。（普通用户无法查看其它用户执行的QUERY）

select pid,state,query_start,xact_start,now()-query_start,wait_event_type,wait_event,query from pg_stat_activity order by query_start;

KILL QUERY

select pg_cancel_backend(pid);

KILL 会话

select pg_terminate_backend(pid);

7、查看膨胀的表和索引，截取自bucardo开源的check_postgres

《如何检测、清理膨胀、垃圾(含修改分布键) - 阿里云HybridDB for PG最佳实践》

8、不堵塞DML的并行建索引方法，

加索引时，可以使用CONCURRENTLY语法，不堵塞DML操作。

Command: CREATE INDEX Description: define a new index Syntax: CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ] ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ WITH ( storage_parameter = value [, ... ] ) ] [ TABLESPACE tablespace_name ] [ WHERE predicate ]

《PostgreSQL SQL自动优化案例 - 极简，自动推荐索引》

10、系统瓶颈或数据库代码瓶颈

《PostgreSQL 源码性能诊断(perf profiling)指南 - 珍藏级》

《PostgreSQL 代码性能诊断之 - OProfile & Systemtap》

11、锁等待排查

如果你发现数据库CPU,IO都不高，但是性能不行，或者连接打满，或者SQL执行HANG死的情况，通常是锁等待造成。

《PostgreSQL 锁等待监控珍藏级SQL - 谁堵塞了谁》

12、防雪崩方法

设置语句超时，锁等待超级可解，特别是对于DDL语句，一定要设置锁等待超时，否则业务高峰期，如果有高并发的其他QUERY访问DDL的表可能会导致雪崩。

《PostgreSQL 设置单条SQL的执行超时 - 防雪崩》

《如何防止数据库雪崩(泛洪 flood)》

13、GIN索引pending页，直接导致GIN索引查询效率变差