PostgreSQL DBA 日常管理 SQL

  1. 云栖社区>
  2. 阿里云数据库ApsaraDB>
  3. 博客>
  4. 正文

PostgreSQL DBA 日常管理 SQL

德哥 2019-04-27 18:34:33 浏览769
展开阅读全文

标签

PostgreSQL , DBA , 日常


背景

1、AWR 数据库健康报告,

《PostgreSQL AWR报告(for 阿里云ApsaraDB PgSQL)》

《如何生成和阅读EnterpriseDB (PPAS(Oracle 兼容版)) AWR诊断报告》

2、查看TOP SQL,以及SQL优化方法

《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL优化内容) - 珍藏级》

3、监控指标

《PostgreSQL 实时健康监控 大屏 - 低频指标 - 珍藏级》

《PostgreSQL 实时健康监控 大屏 - 高频指标(服务器) - 珍藏级》

《PostgreSQL 实时健康监控 大屏 - 高频指标 - 珍藏级》

4、排查FREEZE引入的IO和CPU飙升,

《PostgreSQL Freeze 风暴预测续 - 珍藏级SQL》

《PostgreSQL freeze 风暴导致的IOPS飙升 - 事后追溯》

5、查看当前慢SQL,长事务,长2PC事务,

例如执行时间超过5秒的QUERY

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 ]     

9、索引自动推荐

《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) 索引推荐功能使用》

《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索引查询效率变差

《PostgreSQL pageinspect 诊断与优化GIN (倒排) 索引合并延迟导致的查询性能下降问题》

通常可能是大量并发写入数据,AUTOVACUUM WORKER来不及合并GIN PENDING LIST造成。

14、曾经的慢SQL为什么慢

历史慢SQL,通过auto_explain可以记录慢SQL的整个执行计划,包括执行计划里面每一个步骤花费的时间,消耗的IO时间,IO命中率等。

《PostgreSQL 函数调试、诊断、优化 & auto_explain & plprofiler》

网友评论

登录后评论
0/500
评论
德哥
+ 关注
所属云栖号: 阿里云数据库ApsaraDB