`
LJ你是唯一LT
  • 浏览: 243003 次
社区版块
存档分类
最新评论

canceling statement due to conflict with recovery

阅读更多
报错:
canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

Hot Standby 环境下的 standby 节点执行查询时报错,报错信息如下:
1、执行长时间查询时报错。
   根据错误信息,初步估计当在从库上执行查询时,与主库发生了冲突。
          
2、网上GOOGLE ,信息如下  
   Long running queries on the standby are a bit tricky, because they
might need to see row versions that are already removed on the master.  
备注:意思是说,长时间SQL如果跑在 standby 节点上是一个笑话,因为 standby 节点有可能需要读取主库上被 removed 的数据。   

3、解决方法,修改参数
   根据实际情况,调大参数:max_standby_streaming_delay = 30min;
 
   当在 Standby 提供应用时,如果 Standby 节点上的SQL 与接收主库日志发生冲突时,
这个参数决定了从库等侍这个查询的时间,默认值为 30s, 有SQL执行时间估计在二分钟左
右,从而被 Standby库主动 Cancel 了。也可以将这个参数设置成 -1. 表示 standby 节点永远等侍这个查询,
这无疑是有风险的,如果这个查询不结束,那么从库一直处于与主库的中断状态,不会同步主库数据,而会一
直等从库这个SQL执行完成。

4、其他说明
   PostgreSQL 的 Hot Standby常被用来当只读的数据库提供查询或者统计服务, 但是由于各种原因查询SQL可能被cancel掉。
例如:
1)主库执行了DROP某TABLE的操作, 而standby库上某用户正在查询这个表的数据. 当standby接收到了这些XLOG的信息并且
准备在standby库上apply的时候, 如果这个SQL还在执行, 就发生冲突了, 这个时候standby 要判断是否要cancel掉这个SQL
使得apply可以正常进行下去, 或者standby选择等待多长时间.

2)主库执行删除表空间或者删除库等相关的操作也会遇到上面的问题.

3)更隐晦的是主库上执行了vacuum操作, 这些被vacuum掉的tuple, 可能是standby库上的SQL可见的TUPLE, 也会发生冲突.

4)还有其他原因

那么Standby根据什么来判断是CANCEL SQL还是选择等待, 如果等待, 等多长时间?有两个参数控制:
max_standby_archive_delay
max_standby_streaming_delay

    如果值等于-1表示永不因为recovery 和SQL冲突原因cancel standby上的SQL.
    如果设置了一个值如30秒, 那么在cancel 冲突的SQL前, 会等待,通过GetStandbyLimitTime来判断是否要触发CANCEL SQL, 当GetStandbyLimitTime返回的值为0 时表示永不触发CANCEL, 如果返回的是一个时间值, 这个时间值和当前时间进行比较,如果小于当前时间则进行CANCEL操作.
分享到:
评论

相关推荐

    VclZip pro v3.10.1

    This was due to a problem where it would be freed automatically if there was a problem with the ArchiveStream when trying to open it as a zip file (possibly corrupt). Best practice is that ...

    Modeling and Canceling Tremor in Human-Machine Interfaces

    Modeling and Canceling Tremor in Human-Machine Interfaces

    Delphi7.1 Update

    * Due to a Windows Help engine limitation on Windows 98 and Millennium, the Help system Index tab will be empty if the index exceeds 32,767 keywords. If you encounter an empty Help Index tab after ...

    5G/ADC/BIO

    5G/ADC/BIOA transistor level implementation of a 3 bit digital harmonic canceling (DHC) digital to analog converter (DAC), and a 10 bit successive approximation register (SAR) analog to digital ...

    Solaris 10 System Administration Essentials

    3.5.1 How to Install Packages with the pkgadd Command 51 3.5.2 Adding Frequently Installed Packages to a Spool Directory 54 3.5.3 Removing Software Packages 56 Chapter 4 Software Management: Patches ...

    Developing Software with ClearCase 2002

    ### Developing Software with ClearCase 2002 #### 1. ClearCase的概念 ClearCase 是一个功能强大的软件配置管理系统,由 IBM Rational 开发。它主要用于版本控制和变更管理,能够帮助开发团队有效地管理源代码、...

    ZipForge v2.76 For Delphi

    With this toolkit you can easily add archive functionality to your projects. ZipForge includes our new unique techonology, a transaction system. This innovative solution gives you a fast and easy ...

    网页制作完全手册

    If a control has properties with these same names, you will not be able to access the properties unless you preface the name with the object property. For example, assume that an ActiveX control is ...

    ov7740 datasheet

    hue control, defective pixel canceling, noise canceling, etc., are programmable through the serial camera control bus (SCCB) interface. In addition, the OV7740 uses proprietary technology to improve ...

    OV13850&1300万像素OV影像传感器.pdf

    The OV13850 has an image array capable of operating at up to 24 frames per second (fps) in 10-bit 13.2megapixel resolution with complete user control over image quality, formatting and output data ...

    2019_2020学年高中英语Module2TheRenaissanceSectionⅤWriting知能演练轻松闯关外研版选修

    3. "Despite a strong wind, children are still looking forward to not canceling(cancel) the outdoor activity at the weekend." "not canceling" 是动名词的否定形式,作介词"to"的宾语,表示孩子们期待着不要...

    Interference_canceling_LMS.zip_matlab例程_matlab_

    这个压缩包包含了一个名为"Interference_canceling_LMS.m"的MATLAB文件,它演示了如何利用LMS算法来实现语音信号中的高斯白噪声干扰对消。 LMS算法是自适应滤波理论中的一个重要工具,由Steele于1960年提出,其目标...

    Book6Unit4语言点复习PPT教学课件.pptx

    - "There are those who are opposed to canceling the meeting." "opposed to"后接动名词"cancelling"。 - "The temperature here has increased 5 degrees in the last 50 years." "increase"应用过去分词...

    Gain Flattening Filter Canceling Temperature Dependence of EDFA's gain

    This GFF, which consists of temperature-sensitive long period gratings (LPGs) and a temperature compensated slanted fiber Bragg grating (SFBG),follows the gain shift of EDF with temperature....

    ZipForge_6.80_XE8_DX10.2_Full_Source

    - Performs regularly archive updates using transactions that allows canceling all modifications at any time in case of some failures. Also this technology optimizes the speed of the archive ...

    JSSC_202107

    第一篇论文《Crystal-Less BLE Transmitter With Clock Recovery From GFSK-Modulated BLE Packets》介绍了无需晶体振荡器的BLE(蓝牙低功耗)发射器,其创新之处在于能从GFSK(高斯频移键控)调制的BLE包中恢复时钟...

    Active Noise Control: A Tutorial Review.pdf

    Active Noise Control(ANC)是一种有源噪声控制技术,通过引入canceling“antinoise”波来消除噪声。这种技术广泛应用于制造、工业操作和消费品等领域。 ANC的基本概念 ANC通过引入一个canceling“antinoise”波...

Global site tag (gtag.js) - Google Analytics