作者
digoal
日期
2021-12-24
标签
PostgreSQL , 热门问题
- 问题说明(现象、环境)
- 分析原因
- 结论和解决办法
21、为什么要用绑定变量?
https://www.bilibili.com/video/BV1qL4y1b7xp/
1、SQL的执行过程:
https://www.postgresql.org/developer/backend/
- parser
- rewrite
- generate paths
- generate plan
- execute plan
2、如果使用绑定变量, 那么可以跳过parser, rewrite, generate paths, generate plan. 执行过程变成bind parameter, execute.
- 有兴趣的同学可以通过perf去观察pgbench压测时采用simple模式和prepared模式的区别.
3、prepare例子
服务端prepare例子:
https://www.postgresql.org/docs/14/sql-prepare.html
PREPARE fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
PREPARE usrrptplan (int) AS
SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
AND l.date = $2;
EXECUTE usrrptplan(1, current_date);
客户端prepare请参考对应驱动, 例如jdbc:
https://jdbc.postgresql.org/documentation/head/index.html
https://jdbc.postgresql.org/documentation/publicapi/index.html
4、prepare可以减轻数据库SQL解析、rewrite、生成执行计划的开销, 对于高并发、短平快的OLTP类业务, 建议使用.
性能例子
IT-C02YW2EFLVDL:~ digoal$ pgbench -M prepared -n -r -P 1 -c 8 -j 8 -T 120 -S
pgbench (14.1)
progress: 1.0 s, 62676.4 tps, lat 0.126 ms stddev 0.096
progress: 2.0 s, 65493.0 tps, lat 0.122 ms stddev 0.088
progress: 3.0 s, 62853.8 tps, lat 0.127 ms stddev 0.202
progress: 4.0 s, 65102.8 tps, lat 0.122 ms stddev 0.093
progress: 5.0 s, 67121.9 tps, lat 0.119 ms stddev 0.114
progress: 6.0 s, 72911.0 tps, lat 0.109 ms stddev 0.078
progress: 7.0 s, 69617.0 tps, lat 0.114 ms stddev 0.083
progress: 8.0 s, 66310.4 tps, lat 0.120 ms stddev 0.178
progress: 9.0 s, 67849.8 tps, lat 0.117 ms stddev 0.092
progress: 10.0 s, 77373.0 tps, lat 0.103 ms stddev 0.085
progress: 11.0 s, 70912.5 tps, lat 0.112 ms stddev 0.421
progress: 12.0 s, 74559.4 tps, lat 0.107 ms stddev 0.410
IT-C02YW2EFLVDL:~ digoal$ pgbench -M simple -n -r -P 1 -c 8 -j 8 -T 120 -S
pgbench (14.1)
progress: 1.0 s, 49513.3 tps, lat 0.160 ms stddev 0.110
progress: 2.0 s, 49571.4 tps, lat 0.161 ms stddev 0.101
progress: 3.0 s, 47109.3 tps, lat 0.169 ms stddev 0.359
progress: 4.0 s, 45408.0 tps, lat 0.176 ms stddev 0.252
progress: 5.0 s, 47226.3 tps, lat 0.169 ms stddev 0.375
progress: 6.0 s, 48296.1 tps, lat 0.165 ms stddev 0.847
progress: 7.0 s, 44865.8 tps, lat 0.178 ms stddev 0.504
progress: 8.0 s, 40258.4 tps, lat 0.198 ms stddev 0.797
progress: 9.0 s, 42694.7 tps, lat 0.187 ms stddev 0.542
progress: 10.0 s, 44505.2 tps, lat 0.179 ms stddev 0.789
progress: 11.0 s, 47628.3 tps, lat 0.168 ms stddev 0.798
5、同时prepare还有一个好处: 可以规避SQL注入风险.
《PostgreSQL 转义、UNICODE、与SQL注入》
select * from a where id = ? ;
注入:
select * from a where id = 1 or 1=1 ;
使用prepare则不会出现此类风险, 因为?这儿会当成整个value放进去, 而不是拆成另一个or条件. 所以上面这个例子会因为传入值不是int类型而直接报错.
最典型的疑问:
- SQL相关的表的记录变了(数据发生了新增、更新、删除操作)plan会不会动态变化?
- 首先要保证统计信息更新及时, 由autovacuum触发autoanalyze来实现. 例如数据内容的变化超过一个比例(autovacuum_analyze_scale_factor)时, 自动触发analyze.
- SQL的输入条件变了plan会不会变化?
- 这个会由算法保证, 如果有必要变更执行计划, 则会走到custom plan的流程中. 算法如下:
prepare的执行计划选择算法详解:
- 《执行计划选择算法 与 绑定变量 - PostgreSQL prepared statement: SPI_prepare, prepare|execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》
- 《PostgreSQL plan cache 源码浅析 - 如何确保不会计划倾斜》
在生成generic plan(缓存的执行计划)之前, 会使用5次custom plan, 这5次的custom plan每次都会经过generate plan的过程, 并且保留2个值:
- 1、custom plan avg cost
- 2、custom plan 次数
- 第5次custom plan之后, 在每次调用prepare时, 在execute前, 在bind parameter后, 需要先使用传入参数, 通过generic plan计算cost.
- 如果计算得到的 “cost > custom plan avg cost” , 那么就会重新触发custom plan.
- 同时更新"custom plan avg cost" 以及 "custom plan 次数"
olap场景, 因为olap业务SQL执行时长本身就很长, 执行计划的耗时占比非常低, 使用generic plan极端情况下还是会出现数据倾斜导致错误的执行计划.
在olap系统中, 经常使用plpgsql这种存储过程处理逻辑, plpgsql里面就会自动使用generic plan, 想用custom plan怎么办?
1、execute 语法, 每次都会使用custom plan
2、设置plan_cache_mode参数
- plan_cache_mode = force_custom_plan # auto, force_generic_plan or force_custom_plan
《PostgreSQL 11 preview - 增加强制custom plan GUC开关(plancache_mode)，对付倾斜》
期望 PostgreSQL 增加什么功能?
PolarDB for PostgreSQL云原生分布式开源数据库
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
