核心专业区
数据库深度探秘
PostgreSQL
PG基础知识和优化技巧

PG基础知识和优化技巧

PG基础问题及处理方法

1、PG中如何判断 idle in transaction 为僵尸事务

backend_xid：用来表示会话是否申请了事务号

backend_xmin：事务会话的快照ID

如果backend_xid 和 backend_xmin 为空，可清理掉该进程， 如果不为空，说明事务还没结束，如未提交

2、查看当前事务

select txid_current();

3、PG等待事件根因分析表

https://pgfans.cn/a/1654

4、AUTOVACUUM优化的小技巧

三个基本参数： autovacuum_max_workers：

工作进程数数量默认为3，增加 autovacuum worker的数量意味着更多的进程可用于清理数据，通常应该将worker的数量设置为3-cpu_count之间最先设置为CPU_COUNT的1/2吧，如果在系统高峰期，CPU资源出现了瓶颈，那么适当缩小该值，如果CPU资源还很充足，但是autovacuum的性能不足，那么就增加这个值。

maintenance_work_mem：

值越大，vacuum的效率越高，设置的太大耗尽了内存，会产生严重的性能问题

物理内存充足，建议maintenance_work_mem至少设置为 1 GB

1 GB的维护工作内存足以一次处理大约 1.79 亿个死元组

autovacuum_freeze_max_age：

减少 TXID 回绕的机会，越大，autovacuum运行的频率就越低，延迟太久会消耗txid编号

对表级别进行设置：

这些参数属于表的storage参数。通过ALTER TABLE .. SET STORAGE_PARAMETER可以进行个性化的设置。

autovacuum_vacuum_threshol，autovacuum_analyze_threshol

这两个参数分别确定要为 autovacuum 和 autoanalyzer 表中从上一次vacuum后更新或删除的记录数的最小数量。两者的默认值都是 50

autovacuum_vacuum_scale_factor

autovacuum_analyze_scale_factor

分别确定要为 autovacuum 和 autoanalyzer 安排的表需要更改的表的百分比

autovacuum_vacuum_scale_factor值为 0.2 (20%) 和autovacuum_analyze_scale_factor 0.1 (10%)。

针对大量行超级大表

vacuum threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup

其中n_live_tup是活动元组的数量，这个值可以从pg_stat_all_tables视图获得。一旦死元组的数量达到了这个限额，那么就可以启动vacuum作业。

5、逻辑备份pg_dump

特点：

即使数据库正在被并发使用，它也能创建一致的备份。

不阻塞其他用户访问数据库（读取或写入）

只能备份单个数据库，不会导出角色和表空间相关的信息

生产环境建议方式

\1. -F c 备份为二进制格式, 压缩存储. 并且可被pg_restore用于精细还原，输出输入 IO 比较稳定

二进制文件备份

\1. pg_dump -F c -f /tmp/testdb.dmp -C -EUTF8 -h 127.0.0.1 -U postgres testdb

另外可以

\1. 1）根据二进制备份文件生成toc文件

\2. pg_restore -l -f /tmp/toc1 /tmp/testdb.dmp

\3. 2）修改 toc文件，以首行加分号“；”的方式注释掉不用还原的内容

\4. vi /tmp/toc1

\5. 265; 1259 25280 TABLE public postgres_log postgres

\6. 266; 1259 25293 TABLE public t2 postgres

并行处理

\1. pg_dump -Fd -j4 -f /tmp/db.dir testdb #-F d 以目录的格式创建备份

\2. pg_restore -d testdb3 -j4 /tmp/db.dir

\3. -j 参数指定同时几个进程来同时执行，每个进程同时只处理一个表的数据。

6、优化方法论

了解需求，考虑将需求最小化，具备少做事的意识乃是顶级优化。

7、监控粒度信息

最多的是DataFileWrite，其次是transactionid,再后面是DatafileRead和buffer_mapping。

DatafileWrite是等待想relation文件写入

DatafileRead是从relation文件读入

Transactionid是等待一个事务结束

Buffer_mapping是等待将数据块与缓冲池中的缓冲区。这些都是和Benchmark测试关联比较紧密的

Extend是等待relation文件扩展结束。这个等待居然比tuple还高

tuple是等待获取元组锁。

8、绑定变量的使用问题

优点：让SQL可以共享，可以让一条类似的SQL在多次执行中共享查询执行计划

缺点：绑定变量的差异可能选择不同执行计划才好的问题在很多时候都是存在的

9、开启大页

作用：降低系统内存占用

优势：

1、CPU的TLB可以缓存的物理地址空间更大，从而提升TLB的命中率，降低CPU负载；

2、Huge Page使用的内存是不可交换（swap）的，没有内存空间换入/换出的开销；

3、极大的减少了系统维护PageTable的内存开销。

劣势：

1、Huge Page使用的内存需要预先分配；

2、Huge Page使用固定大小的内存区域，不会被释放；

3、对于写密集型的场景，Huge Page会加大Cache写冲突的发生概率。

**使用场景：**不推荐PG实例开启Huge Page的场景：写业务密集，热点数据集中且内存使用较小。

计算大页脚本

10、快速加载sql入库的方法

查看自动提交是否关闭

\echo :AUTOCOMMIT

关闭

\set AUTOCOMMIT OFF

执行插入数据，在文件后加入commit;参数

\i /var/lib/pgsql/testdb1.sql

以下是测试200w数据

只导出insert形式的数据（用copy导入会报错）

pg_dump -U postgres -d postgres -p 5432 –column-inserts -t “testdb1” > /var/lib/pgsql/testdb1.sql

只导出表结构

pg_dump -U postgres -d postgres -p 5432 -s -t “testdb1” > /var/lib/pgsql/testdb1_struct.sql

方案一：psql -f 导入，7分钟导入数据量

img

方案二：\set AUTOCOMMIT OFF，添加

导入，5分钟之内能弄完

方案三：开启事务

begin:

\i 执行脚本

commit;

大页计算脚本和注意事项

#!/bin/bash PGDATA=’/pg13/pgdata’ pid=head -1 $PGDATA/postmaster.pid echo ‘Pid: $pid’ peak=grep ^VmPeak /proc/$pid/status | awk '{ print $2 }' echo ‘VmPeak: $peak kB’ hps=grep ^Hugepagesize /proc/meminfo | awk '{ print $2 }' echo ‘Hugepagesize: $hps kB’ hp=$((peak/hps)) echo Set Huge Pages: $hp sysctl -w vm.nr_hugepages=$hp 不要忘记将此设置添加到/etc/sysctl.conf，以便在重启后重新应用它。 echo 3170 > /proc/sys/vm/nr_hugepages cat » /etc/sysctl.conf «‘EOF’ vm.nr_hugepages=3170 EOF sysctl -p 通过此脚本计算出大页的大小，然后添加到/etc/sysctl.conf里，sysctl -p生效。 参考连接： https://baijiahao.baidu.com/s?id=1709212963361949625&wfr=spider&for=pc

有用知识点

PG恢复被打了删除标记的列

授权某个模式下所有的表和新增的表

/image-20220329162624551

11、pg10分区表插入更新无法解决方法

方法1：建立函数

create or replace function f_upsert(integer,character,timestamp) returns void as $$  
declare  
  res int;  
begin  
  update users set user_name=$2,logdate=$3 where user_id=$1;  
  if not found then  
    insert into users (user_id,user_name,logdate) values ($1,$2,$3);  
  end if;  
  exception when others then  
    return;  
end;  
$$ language plpgsql strict;

方法2：采用with的方式

with upsert as (update users set user_name=$user_name,logdate=$logdate where user_id=$user_id returning *) insert into users select $user_id,$user_name,$logdate where not exists (select 1 from upsert where user_id=$user_id);    
insert into test (id,info,crt_time) values ($1,$2,$3); 

with upsert as (update users set user_name='DD',logdate='2021-03-21' where user_id=3 returning *) insert into users select 3,$user_name,$logdate where not exists (select 1 from upsert where user_id=$user_id);    


postgres=# select * from users;
 user_id | user_name |        logdate         
---------+-----------+------------------------
       1 | AA        | 2021-01-17 00:00:00-05
       2 | BB        | 2021-02-18 00:00:00-05
       3 | CC        | 2021-03-19 00:00:00-04
(3 rows)

postgres=# select f_upsert(1,'2','2021-01-18');
postgres=# select * from users;
 user_id | user_name |        logdate         
---------+-----------+------------------------
       1 | 2         | 2021-01-18 00:00:00-05
       2 | BB        | 2021-02-18 00:00:00-05
       3 | CC        | 2021-03-19 00:00:00-04
(3 rows)

12、postgresql中mvcc_表膨胀问题

pg中的mvcc 旧版本和新版本在同一个数据库的问题

行发生了修改就有新版本和旧版本，存在同样的数据文件里面，如果垃圾回收不及时，就会发生表膨胀

技术原理

存储旧版本：解决并发事务，方便查询旧的版本数据

影响范围，行业，业务

对于高频率的更新，插入，删除场景就会有问题

传感器，出租车位置，等更新多的场景，容易出问题，垃圾回收不及时，就会膨胀

什么时候回收不及时

看看有没有2pc

看看垃圾回收设置的内存是不是过小

垃圾回收工作进程太少了

磁盘性能

膨胀之后会有什么问题

1.存储空间不足

2.访问的时候io的范围会增加，本来访问一个数据块的现在需要访问2，3个数据块了

3.内存的消耗增加了，因为内存buffer要去缓存block

4.性能下降

解决：

设置参数 https://blog.csdn.net/weixin_34360651/article/details/90504302

表膨胀解决方法

https://ctypyb2002.blog.csdn.net/article/details/82774684?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1.control

新引入的问题

1、需要io延时更低的硬盘

2、牺牲了长事务

3、需要增加监控项

有没有希望解决这个坑 ：基于专门的回归段的存储引擎

13、postgresql避免oom方法

操作系统层：

vm.overcommit_memory设置为2：sysctl -w vm.overcommit_memory=2 修改postgres进程的oom_score：echo -1000 > /proc/self/oom_score_adj

参考文章

https://weibo.com/ttarticle/p/show?id=2309404665709073662196&sudaref=www.baidu.com

https://cdn.modb.pro/db/43944

14、wal记录

数据先到wal buffer–>在落盘保证数据 lsn不断放大的号，确保回放，把最新的lsn，从上一次检查点重放 保证数据库不丢数据 是如何恢复的 重放xlog从磁盘到wal buffer ，redo poin它中有lsn号，去和shared buffer中的lsn做对比，大的就回放 块折断 full_page_writes作用：是否开启全页写入，为了防止块折断（块损坏）的一种策略 判断：checksum值 采用机制：在checkponit后的一个块第一次变脏后就要整块写入到wal日志中， 后续修改这个块，只要把修改信息写入到wal日志 断电重启，有块折断， 则在全页写入的块为基础进行恢复 最后覆盖磁盘上的折断块 参数checkpoint_segments对checkpoint影响 作用：控制checkpoint的间隔 过大：恢复时间会变长 过小：造成频繁的checkpoint进而导致写入了过多的全页，可能wal日志暴增 检查点作用 1.将事务提交的修改写进disk（写脏数据）；保证数据库的完整性和一致性。 2.缩短恢复时间,将脏页写入相应的数据文件,确保修改后的文件通过fsync()写入到磁盘。 触发条件： 1、checkpoint_timeout设置时间 2、为checkpoint_segments设置的 WAL 段文件的数量自上一个检查点以来已经被消耗（默认3） 3、WAL 段文件的总大小已超过参数max_wal_size的值（默认值为 1GB（64 个文件）） 4、PostgreSQL 服务器在smart或fast模式下停止 5、手动执行CHECKPOINT 6、写入WAL的数据量已达到参数max_wal_size（默认值：1GB） 7、执行pg_start_backup函数时 8、在进行数据库配置时

pg_control文件 包含检查点的基本信息，如果损坏或无法读取，则无法启动恢复过程，从而无法获得起点。

WAL段切换 1、wal段文件被写满 2、函数pg_switch——wal()被调用 3、启用了archive_mode,且已经超过archive_timeout配置的超时时间。 archive_timeout时间短会导致空间膨胀

15、postgresql中lock_timeout设置

设置为10s

session1：测试插入更新数据

img

session2:同时更新事务一中的数据，锁超时事务被杀掉

img

继续在seasion2中操作会报错

img

session2选择rollback或者commit

rollback回滚当前事务，commit提交报错之前执行的操作

session1执行commit提交成功；

img

监控指标postgresql原理