每天5分钟,PG聊通透系列1热门问题

作者

digoal

日期

2021-12-09

标签

PostgreSQL , 热门问题

• 问题说明(现象、环境)


• 分析原因


• 结论和解决办法

链接、驱动、SQL

202112/20211224_05.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第24期 - 为什么与检索字段类型不一致的输入条件有时可能不能采用索引?》

202112/20211224_04.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第23期 - 为什么有的函数不能被用来创建表达式索引?》

202112/20211224_03.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第22期 - 为什么创建索引会堵塞DML? 如何在线创建索引?》

202112/20211224_02.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第21期 - 为什么要用绑定变量?》

202112/20211224_01.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第20期 - 为什么分区表的分区过多会导致性能下降?》

202112/20211223_02.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第19期 - 为什么SQL性能会抖动?》

202112/20211222_05.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第18期 - 为什么性能差? 如何找到捣蛋鬼SQL?》

202112/20211222_04.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第17期 - 为什么说有些逻辑应该交给数据库存储过程来做?》

202112/20211222_03.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第16期 - 为什么说有些排序操作建议让业务来做?》

202112/20211222_02.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第15期 - 为什么业务开启多会话并行后反而变慢了?》

202112/20211222_01.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第14期 - 为什么会有死锁?》

202112/20211221_03.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第13期 - 为什么长时间等待业务处理的情况不建议封装在一个长事务中进行处理?》

202112/20211221_02.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第12期 - 为什么SQL会自动启用并行计算?》

202112/20211221_01.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第11期 - 为什么count查询慢?》

202112/20211220_10.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第10期 - 为什么有的索引不支持字符串前置like ~查询?》

202112/20211220_09.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第9期 - 为什么OFFSET(翻页)会越来越慢?》

202112/20211220_08.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第8期 - 为什么order by并没有按中文拼音排序?》

202112/20211220_07.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第7期 - 为什么有的SQL使用pg_cancel_backend, pg_terminate_backend都杀不掉?》

202112/20211220_06.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第6期 - 为什么不需要提供密码就能连接数据库?》

202112/20211220_05.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第5期 - 为什么无法连接数据库?》

202112/20211220_04.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第4期 - 为什么增加连接不能无限提高TPS或QPS? 配置多少个链接合适?》

202112/20211220_03.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第3期 - 为什么会有大量的idle in transaction|idle事务? 有什么危害?》

202112/20211220_02.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第2期 - 为什么会有莫名其妙的连接错误日志?》

202112/20211220_01.md 《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第1期 - 为什么数据库链接长时间空闲时有时侯会自动断开?》

备份、订阅、恢复

1、为什么逻辑复制在主从切换后会丢数据?

2、为什么有备份但是不能恢复到指定时间点? (时区指定有问题、目标时间早于全量备份到逻辑一致位点)

3、为什么逻辑备份可能和业务产生冲突?

4、为什么逻辑备份可能导致实例膨胀?

从库

1、为什么在从库上跑长事务或长SQL可能会报错?

2、为什么从库会出现回放延迟?

3、为什么主备切换后并不总是需要重建HA或重建灾难备份库?

4、为什么有时从库会报上游WAL日志已删除的错误?

性能、管理

1、为什么默认配置性能比较差?

2、为什么要关闭NUMA?

3、为什么高并发的短链接性能会很差?

4、为什么会发生OOM? (adj 防止 https://github.com/digoal/blog/blob/master/201801/20180121_01.md)

5、为什么存在内存浪费严重的现象? (relcache, 分区表, 内存霸占, 未开启huge page页表浪费)

6、为什么在操作系统直接kill 数据库进程会导致数据库重启?

7、为什么会出现数据库块损坏?

8、为什么存在与业务无关的突发IO和CPU飙升?

9、为什么存在与业务无关的持续IO与CPU消耗?

10、为什么表会膨胀? (https://github.com/digoal/blog/blob/master/201801/20180121_01.md)

回收不及时:
产生垃圾速度太快
存储性能太差(RT高) [《一起学PolarDB - 第23期 - 为什么磁盘RT会严重影响vacuum垃圾回收效率?》](https://github.com/digoal/blog202202/20220216_01.md)
内存设置问题, 索引多次扫描
参数设置问题, 回收太懒
non removeable tuple
各自应对策略
SSD性能, 降低RT
其他调参数


11、为什么索引会膨胀?

12、为什么垃圾回收有时不起作用?

为什么有nonremoveable tuple
快照旧
2pc
feedback 从库旧快照
强制延迟回收
slot 影响catalog回收


13、为什么log日志量暴增而且影响性能? (审计, pipeline buffer, 使用采样日志)

14、为什么WAL日志会堆积? (https://github.com/digoal/blog/blob/master/201801/20180121_01.md)

15、为什么WAL会突然暴增?

16、为什么数据文件会突然暴增? (递归死循环、某些大的查询可能导致临时文件的大量产生 https://github.com/digoal/blog/blob/master/201801/20180121_01.md)

17、为什么会出现雪崩?

18、为什么大量delete后表和索引的空间没有变小? (高水位)

19、如何在线降低表、索引水位?

20、为什么push|pull大量数据的读写比较慢? (COPY, pipeline模式)

21、怎么判断数据库有没有瓶颈? (处理能力有没有到顶? 还能支撑多大的业务增长?)

22、如何发现过去、现在、未来的性能问题?

23、为什么SQL执行计划不正确?

24、为什么WAL日志文件不能随便删除?

25、为什么明明索引更快却不用索引或者明明index更快却使用bitmap? random page cost.

26、为什么需要bitmap scan?

27、为什么index scan扫描了很多很多个block?

28、为什么order by 扫描了很多很多个block?

enable_sort off

业务

1、为什么时序类搜索可能有IO放大，除了 cpu,io,net 还有哪些隐藏的瓶颈，为什么要聚集?

2、为什么空间搜索有IO放大，为什么要数据聚集， 为什么要分裂查询?

3、为什么有时索引扫描并不比全表扫描更快?

4、为什么GIN有时不快?

5、为什么BRIN有时不快?

6、为什么输入顺序会影响最终GiST创建出来的索引性能?

7、有几种索引、该如何选择索引?

8、为什么当前还不建议频繁使用临时表?

安全

1、为什么会有SQL注入?

2、为什么赋予了select权限依旧无权查询表? (逻辑结构)

- 怎么赋予默认只读权限

- 怎么赋予默认写权限

- 怎么赋予新增表的默认权限

- 怎么赋予已有表的默认权限

3、为什么事务号会耗尽?

4、为什么慢SQL，空闲事务，长事务，2PC，慢SLOT，standby feedback，强制vacuum age defer都存在风险

期望 PostgreSQL 增加什么功能?

类似Oracle RAC架构的PostgreSQL已开源: 阿里云PolarDB for PostgreSQL云原生分布式开源数据库!

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.