postgresql数据库mvcc

MVCC如何实现?

简单说，就是更新数据时，保留原先版本的数据，即一行数据存在多个版本。

常见的两种实现方法:

• 回滚段的实现方法(Oracle、 MySQL Innodb的实现方法)
• 在原先的数据文件中的旧版本数据不删除，生成新版本的数据(PostgreSQL 的实现方法)

 通常与事务的功能集成在一起，但MVCC时事物回滚或提交之后，旧版本的数据仍然需要保留一段时间，是延迟清除的。

旧版本的数据延迟清除的原因：

• 就是为了提供快照读(一致性致性读)的功能
• 对于Read Commited隔离级别:需要保证当开始执行一个SQL之后，这个SQL是读一个不变的快照的数据，即使在这个SQL执行过程中有其它的事物提交了，这些提交的数据对于这个SQL也是看不到了，这样保证了数据的一致性。
• 对于Read Repeatable隔离级别: 从这个事物开始之后，看到的数据就是一个不变的数据，执行相同的SQL总是可以看到相同的数据。所以叫"可重复读"

MVCC的总结
查询和更新、删除、插入操作互相不阻塞当开始一个查询后，读到的数据总是查询开始时那个时间点的快照

• 在查询开始后，发生的变更(即使已提交)，这次查询也是看不到的。
• 一个事务无论运行多长时间，看到数据都是相同的
• 不同开始时间的事务中相同的查询，返回的数据也可能不同

PostgreSQL MVCC实现

PostgreSQL 没有回滚段。旧数据是放在原有数据文件中的。如果放在原有的数据文件中，旧数据越来越多怎么办?

• 垃圾回收操作vacuum
• 有自动垃圾回收autovacuum

autovacuum_max_workers = 3 # max number of autovacuum subprocesses启动多少个autovacuum进程进行垃圾回收，默认值是3，建议在写压力大的数据库中，调整成6或者更大的值
autovacuum_naptime = 1min  # time between autovacuum runs调度的频繁程度，默认是1min,可以保持不变。
autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum，默认是0.2即20%
autovacuum_vacuum_threshold = 50       # min number of row updates before，默认是50行
即当表的变更数据的行数超过：20%的行+50行时，vacuum就会开始操作这张表。
对于大表来说，20%可能太大了，需要设置的小一些。alter table test01 set (autovacuum_vacuum_scale_factor=0.05)

autovacuum是尽量控制表的年龄在autovacuum_freeze_max_age的范围内，但如果因各种原因（如autovacuum整理的过慢），还是有事务ID回卷问题导致数据库宕机的风险。
所以需要监控数据库中表的年龄。select datname,age(datfrozenxid) from pg_database;(在pg_database视图中记录了数据库中最旧事务的年龄，这个值是在vacuum时更新上去的，包括autovacuum)
pg_database只记录了这个数据库中最旧的年龄，没有记录是哪张表。表的年龄是记录在pg_class中的relfrozenxid字段中
select relname,relfrozenxid,age(relfrozenxid) from pg_class where relfrozenxid <>0 order by age(relfrozenxid) desc limit 10;

lazy vacuum与aggressive vacuum
如果表的年龄没有超过vacuum_freeze_table_age则会做lazy vacuum,vacuum_freeze_max_age默认是1亿5千万，autovacuum_freeze_max_age默认是2亿 即autovacuum_freeze_max_age>vacuum_freeze_max_age

降低vacuum对生成性能影响
vacuum_cost_delay是指sleep的时间，默认值是0，建议设置为1ms
vacuum_cost_limit:当工作量到达一定时，就休眠一下，默认是200,在SSD下建议设置10000，Raid盘的机械硬盘下建议设置为1000
vacuum_cost_page_hit=1
vacuum_cost_page_miss=10
vacuum_cost_page_dirty=20即内存中整理一个块工作量为1，磁盘整理一个块代价是10，如果是脏块是20

产生表膨胀的原因:
pg_stat_all_tables.n_dead_tup查看具体的表有多少行旧数据没有清理

数据库上有长时间没有提交的事务
-select * from pg_stat_activity,看状态是“Idle in transaction”的长时间事务
-设置参数：idle_in_transaction_session_timeout (超过了这个时间之后，查询就会报错快照过旧)

未结束的20PC事务
-select gid,prepared,owner,database,transaction as xmin from pg_prepared_xacts order by age(transaction) desc;
如何解决？
-rollack prepared transaction_id;或者
-commit  prepared transaction_id;

放弃的复制槽（replication slots）
select slot_name,slot_tye,database,xmin from pg_replication_slots order by age(xmin) desc;
--特别是逻辑复制

设置old_snapshot_threshold
-强制回收旧版本数据
-会导致类似oracle的“ora-1555”错误

表太大，vacuum花了很久还没有整理完。
-vacuum在单张表上无法并发
-使用分区表，在PG11及以下版本，建议使用pg_pathman做分区
-加大maintenance_work_men或autovacuum_work_men的值
vacuum_defer_cleanup_age的值设置太大
--同时设置了hot_standby_feedback
--这种情况出现的概率很低

vacuum命令的一些有用选项
FREEZE:如果表没有更新，建议加这个选项，这样执行完后，表上的旧xid都被回收了
DISABLE_PAGE_SKIPPING:visibility map文件的内容可能有问题时，可以加这个选项。9.6版本及之后的功能
SKIP_LOCKED: vacuum跳做一些冲突的锁。12版本的功能
INDEX_CLEANUP：清理索引。12版本的功能

copy命令加freeze参数减少vacuum
--直接在表上不放当前的事务id
--需要把create table或truncate table与copy命令在同一个事务中执行
--注意数据可以在立即被其他session看到了（违背了mvcc的可见性）

手工做vacuum
关闭autovacuum
或让autovacuum不整理一些大表
--可以在大表上关闭autovacuum参数，手工整理这些大表
--alter table test01 set(autovacuum_enable=off);