开发者学堂课程【PostgreSQL云上开发实践:阿里云PostgreSQL_开发实践】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/528/detail/7105
阿里云PostgreSQL_开发实践
目录：
一．如何解决
二．TOP SQL
三．最耗 IO SQL
四.当前慢 SQL
五．plpgsql 函数性能诊断
六．plpgsql 判断有无满足条件记录
七．事务可靠性、可用性级别设置
八.防雪崩
九.DDL 操作建议
十. 限制慢 SQL 并发度
十一. 杀会话、杀 QUERY
比如说我们的查询的数据使用的过程当中发生了大量数据的变更导致数据比如说出现了一些倾斜，执行计划又没有及时的收集，导致执行计划的不准确，是一种，执行统计信息导致。
那么另外还有一种说使用绑定变量，使用了绑定变量之后，通用的指定计划和我们通常输入的一些变量，它的通用执行计划和custom的执行计划并不匹配，导致执行计划不准确，也是一种。
一．如何解决？
1. 更新统计信息
2. SQL Hint
l 强制扫描
l 索引扫描
SQL Hint 用法：
1. 创建 extension
2. 加载 SO 文件
3. 把 hint 写上
二．TOP SQL：
TOP SQL 是数据库使用过程当中，特别是优化的过程当中用的最多的。
pg_state_statements 这个插件可以实现很多功能。
动态视图：
三．最耗IO SQL
单次调用最耗 IQ SQL TOP 5
Select userid::regrole, dbid, query from pg_stat_statements order by(blk_read_time+blk_write_time)/call desc limit 5;
总最耗 IO SQL TOP 5
Select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;
最耗时 SQL
单次调用最耗时 SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;
总最耗时 SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;
响应时间抖动最严重 SQL
select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;
最耗共享内存 SQL
select userid:regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;
最耗临时空间 SQL
select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;
四.当前慢 SQL：
运行中慢 SQL
-select * from pg_stat_activity where now()-query_start > interval '?s’;·长运行中事务
-select * from pg_stat_activity where state='active' now()-xact_start >interval "?s";
长空闲事务
-select * from pg_stat_activity where state=‘idle in transaction'now()-xact_start > interval '?s';
长2PC事务
-select * from pg_prepared_xacts where now()-prepared > interval '?s';
慢 SQL 执行计划详情：
auto_explain
慢 sQL 执行计划详情
-plan
- node time
-buffers, hints
-filter
五．plpgsql 函数性能诊断
l auto_explain
l plpgsql 函数中每一个调用的详细执行计划
plpgsql 函数debug
l pldebugger extension + pgadmin
l raise notice
l print stack
-GET STACKED DIAGNOSTICs variable {= | := } item[ , ...];
- GET [ cURRENT ]DIAGNOSTICs variable {= [ := }item[ , ... ];
六．plpgsql判断有无满足条件记录
l perform 1 from tbl where .... limit 1;
l if FOUND then
l else
l end if;
l DON'T use
- select count(* ) into var from tbl where ....;- if var >= 1 then
- else
- end if;
七．事务可靠性、可用性级别设置
-synchronous_standby_names
l on, remote_apply,remoie_write, local
-synchronous_standby_names
l [FIRST] num_sync ( standby_name L ...])
l ANY num_sync ( standby_name [. ...])
l standby_name [, ...]
通常来说，在业务系统里面有一些事物，比如说涉及到钱，涉及到资金对可靠性的要求特别高，另外一些只是一些用户的操作日志一些用户的行为跟踪日志，可靠性要求比较低。
八.防雪崩
statement_timeout
- 语句超时，防止雪崩
lock_timeout
- 锁超时
deadlock_timeout
- 死锁超时
idle_in_transaction_session_timeout,
- 空闲中事务超时
通常来说，业务系统会怎样，业务系统通常会发起更多的连接过来，因为一个连接一旦处理不过来的时候，一定会有新的请求过来，然后导致连接越来越多，把连接打满，另外一个是即使连接打满了那边的请求，还有堆积，有应用端还有堆积，因为你的数据库已经处理响应不过来了。
如何防止？
设 statement 这样一个 time out 的参数。
九. DDL 操作建议
锁等待机制介绍
https://github.com/digoal/blog/blob/master/201705/20170521_01.md
DDL、大锁建议
- begin;
- set lock_timeout='ns';
- DDL
- end;
十. 限制慢 SQL 并发度
杀掉最近发起的慢 SQL，老的慢 SQL 继续,保证 N 个慢 SQL 并发
- select pg_terminate_backend(pid) frompg_stat_activity where now()-query_start >
interval '? second' order by query_start offset $N;
- 或pg_cancel_backend(pid)
十一. 杀会话、杀 QUERY
杀会话
- select pg_terminate_backend(pid);
杀某个会话
- select pg_terminate_backend(pid) from pg_stat_activity where
pg_backend_pid()<>pid;
杀所有会话
杀某个用户的所有会话
- select pg_terminate_backend(pid) from pg_stat_activity where usename=?and pid>pg_backend_pid();
杀 QUERY
- select pg_cancel_backend($pid);
.防 DDoS
auth_delay
- auth_delay.milliseconds = '500'
是否被 DDoS
V1、当前总连接数:
- select count(*) from pg_stat_activity;
V2、最大允许连接数:
- show max_connections;.
V3、当前已占用 slot 数:
- netstat -anplgrep -c $xxxx
DDoS 判断标准:
- (V2= v3)>V1
