Postgresql MVCC架构对从库长查询的影响

公司有一台pg从数据库，连接的是位于上海的主库，最近使用过程中遇到了一个报错：

ERROR: canceling statement due to conflict with recovery
Detail: User query might have needed to see row versions that must be removed
复制代码

原因分析

Google一番后，总结原因如下：

Pg属于经典的MVCC(multi version concurrency control)架构，在这种模式下，更新一行不是直接修改那一行的内容，而是会创建一个新的行，对应的这两行属于不同的版本，这也就是MVCC中multi version的含义。显然，如果这么一直创建新的行，磁盘迟早会被撑爆，所以pg内部会有一个 vacuum进程，专门用来清理老数据。

Pg每个row都有两个特殊的字段xmin,xmax

• xmin表示row创建时的transaction id
• xmax表示row删除时的transaction id，如果还没有被删除，就是NULL 在一个事务里面，只能看到一部分数据，还有一部分是不可见的。那么 pg是如何判断对一个事务，哪些数据是可见的，哪些数据是不可见的呢？ 简单来说，可以通过下面这个伪函数来判断：

  

简单来说，也就是如果该row创建时的transaction id大于当前事务的transaction id，那么将不可见；如果该row删除时的transaction id小于当前事务的transaction id，也不可见。

MVCC目的是提高并发度，读操作不会阻塞写操作，写操作也不会阻止读操作。

这一特性和从数据库放在一起的时候，会遇到一些问题： 比如在从数据库上有一个耗时很长的查询操作，在查询的过程中，一些需要的row在主数据库上面已经被更新或者删除了。主数据库并不知道当前在从数据库在进行一个查询，它觉得自己可以vacuum掉那些比较老的数据行。对于从数据，他必须replay这个vacuum操作，所以他必须取消所有结果包含这些数据行的查询。

解决方案

方案1： hot_standby_feedback = on

默认情况下，主数据库是看不到从数据库上的查询操作的，设置hot_standby_feedback为on，可以防止主数据库的vacuum操作把从数据库查询需要的数据行给清除掉。

但是这个方案可能会影响主数据库的性能，因为主数据库现在得考虑从数据库的查询，势必会是一种负担，如在pg官方文档中写道：

Well, the disadvantage of it is that the standby can bloat the master, which might be surprising to some people, too
复制代码

方案2：max_standby_streaming_delay

pg中有一个max_standby_streaming_delay参数，意思是从数据库replay主数据库操作可以等待的最长时间。默认是30s，将其设置为一个相对比较大的数，或者-1，也就是可以无限等待，可以在所有查询完成之后再replay主数据库的操作，这样长查询就不会被取消了。

这个参数相比hot_standby_feedback，只会影响从服务区，对主服务区没影响，但是有可能会加大主从数据不一致。

可见，从数据库长耗时查询并没有一个十全十美的方案，这也算是一个困扰社区已久的难题。

关注我的微信公众号