設定
- RDS PostgreSQL の DB Parameter Group で max_standby_streaming_delay を最小値の 1 秒に設定。
max_standby_streaming_delay=1
手順
- Primary
postgres=> create table test1 (col1 int); postgres=> insert into test1 values (generate_series(1,100000)); postgres=> delete from test1 where col1 > 50000; --Replica で select 実行後に vacuum を実行 postgres=> vacuum test1;
- Replica
postgres=> select count(*) from test1 a , test1 b, test1 c; ERROR: canceling statement due to conflict with recovery DETAIL: User was holding a relation lock for too long.
補足
- vacuum full では RDS でも Aurora でも "User was holding a relation lock for too long." が発生。
環境
- RDS PostgreSQL 11.7
- db.m5.xlarge
- Primary: 1 、Replica: 1
参考
プライマリサーバとスタンバイサーバは、多方面でゆるく結合しています。 プライマリサーバの動作はスタンバイサーバに影響します。 その結果、負の相互作用またはコンフリクトの可能性があります。 最も分かりやすいコンフリクトは性能です。 プライマリサーバで巨大なデータがロードされた場合、スタンバイサーバにおいて同様に巨大なWALレコードが生成されるので、スタンバイサーバにおける問い合わせは互いにI/Oなどのシステム資源を奪い合います。
ホットスタンバイで発生する可能性があるコンフリクトの種類には他にもあります。 これらのコンフリクトは、問い合わせをキャンセルしなければならない可能性があり、解消させるためにはセッションの接続を閉ざすことになる場合もあるため、致命的なコンフリクトです。 ユーザにはこうしたコンフリクトを扱うための複数の方法が提供されます。 コンフリクトする状況には以下があります。
- プライマリサーバで獲得されたアクセス排他ロックは、スタンバイの問い合わせにおけるテーブルアクセスとコンフリクトします。 明示的なLOCKコマンドおよび各種DDL操作を含みます。
- プライマリでテーブル空間を削除することは、一時作業ファイル用にそのテーブル空間を使用するスタンバイ側の問い合わせとコンフリクトします。
- プライマリでデータベースを削除することは、スタンバイ側でそのデータベースに接続するセッションとコンフリクトします。
- WALからのバキュームクリーンアップレコードの適用は、その適用により削除される行のどれか1つでも「見る」ことができるスナップショットを持つスタンバイでのトランザクションとコンフリクトします。
- WALからのバキュームクリーンアップレコードは、消去されるデータが可視か否かに関係なく、スタンバイで対象ページにアクセスする問い合わせとコンフリクトします。
プライマリサーバでは、こうした状況は単に待たされるだけです。 ユーザはコンフリクトする操作をキャンセルすることを選ぶことができます。 しかし、スタンバイ側には選択肢がありません。 WALに記録された操作はすでにプライマリで発生したものですので、スタンバイではその適用に失敗してはなりません。 さらに、適用したいWALを無制限に待機させることを許すことは、まったく望まない結果になってしまうかもしれません。 なぜなら、スタンバイの状態がプライマリの状態とだんだんとかけ離れてしまうからです。 したがって適用すべきWALレコードとコンフリクトするスタンバイの問い合わせを強制的に取り消す仕組みが用意されています。
26.5. ホットスタンバイ
スタンバイでは、マスタから転送されるWALを適用しながら、クライアントからのSQLを受付けるので(hot_standby = trueの場合)、よくある例として、スレーブでの参照とスレーブが参照しているタプルを物理削除するWAL(VACUUM等で出力される)の適用がコンフリクトします。
PostgreSQLのレプリケーションのコンフリクトについて言いたい - Qiita
ここまでの説明を見た感じ、多少vacuumが実行されるのが遅くなろうと、hot_standby_feedback が最強かなと思って使ってみたのですが、pg_dumpを流していたらこんなエラーに遭遇。。
pg_dump: Error message from server: ERROR: canceling statement due to conflict with recovery DETAIL: User was holding a relation lock for too long.色々と検索して、このQAを見つけたのですが、
Yes, you are confusing block and relation level locks. The contention
PostgreSQL - admin - Query cancellation on hot standby because of buffer pins
is at block level.block level lock というのと、relation level locksというのの違いがわからず、行き詰まる & 結局 max_standby_streaming_delay, max_standby_archive_delayを指定しなければいけないのであれば、hot_standby_feedbackの存在意義が無いような気がしてきている <- 今ここ
引き続き調べてみて、何か分かったら更新したいと思います。。
postgresqlで "terminating connection due to conflict with recovery" に遭遇した時に辿りつく、vacuum_defer_cleanup_age, max_standby_streaming_delay, hot_standby_feedback について (調査進行中) - Qiita
ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed.これは字の如く、レプリケーション側でWALの反映時にコンフリクトして強制的にクエリを殺されています。 理由は下記のスライドがわかりやすいです。
PostgreSQLのレプリケーションのコンフリクトについて言いたい - Qiita
このパラメータを有効にすることで、ソースでの以下のエラーメッセージをキュレートし、関連するテーブルでの VACUUM を延期します (リードレプリカでリードクエリが完了した場合を除く)。
ERROR: canceling statement due to conflict with recovery Detail: User query might have needed to see row versions that must be removedこの方法では、hot_standby_feedback が有効化されたレプリカインスタンスは長時間実行される SQL に対応できるものの、ソースインスタンスのテーブルを増大させる場合があります。レプリカインスタンスで長時間実行されるクエリを監視しなければ、ソースインスタンスでストレージ不足、およびトランザクション ID 周回などの深刻な問題に直面する可能性があります。
Amazon RDS PostgreSQL レプリケーションのベストプラクティス | Amazon Web Services ブログ
/* * errdetail_recovery_conflict * * Add an errdetail() line showing conflict source. */ static int errdetail_recovery_conflict(void) { switch (RecoveryConflictReason) { case PROCSIG_RECOVERY_CONFLICT_BUFFERPIN: errdetail("User was holding shared buffer pin for too long."); break; case PROCSIG_RECOVERY_CONFLICT_LOCK: errdetail("User was holding a relation lock for too long."); ★ break; case PROCSIG_RECOVERY_CONFLICT_TABLESPACE: errdetail("User was or might have been using tablespace that must be dropped."); break; case PROCSIG_RECOVERY_CONFLICT_SNAPSHOT: errdetail("User query might have needed to see row versions that must be removed."); break; case PROCSIG_RECOVERY_CONFLICT_STARTUP_DEADLOCK: errdetail("User transaction caused buffer deadlock with recovery."); break; case PROCSIG_RECOVERY_CONFLICT_DATABASE: errdetail("User was connected to a database that must be dropped."); break; default: break; /* no errdetail */ } return 0; }
/* * ResolveRecoveryConflictWithLock is called from ProcSleep() * to resolve conflicts with other backends holding relation locks. * * The WaitLatch sleep normally done in ProcSleep() * (when not InHotStandby) is performed here, for code clarity. * * We either resolve conflicts immediately or set a timeout to wake us at * the limit of our patience. * * Resolve conflicts by canceling to all backends holding a conflicting * lock. As we are already queued to be granted the lock, no new lock * requests conflicting with ours will be granted in the meantime. * * Deadlocks involving the Startup process and an ordinary backend process * will be detected by the deadlock detector within the ordinary backend. */ void ResolveRecoveryConflictWithLock(LOCKTAG locktag) { TimestampTz ltime; Assert(InHotStandby); ltime = GetStandbyLimitTime(); if (GetCurrentTimestamp() >= ltime) { /* * We're already behind, so clear a path as quickly as possible. */ VirtualTransactionId *backends; backends = GetLockConflicts(&locktag, AccessExclusiveLock); ★ /* * Prevent ResolveRecoveryConflictWithVirtualXIDs() from reporting * "waiting" in PS display by disabling its argument report_waiting * because the caller, WaitOnLock(), has already reported that. */ ResolveRecoveryConflictWithVirtualXIDs(backends, PROCSIG_RECOVERY_CONFLICT_LOCK, ★ false); } else { /* * Wait (or wait again) until ltime */ EnableTimeoutParams timeouts[1]; timeouts[0].id = STANDBY_LOCK_TIMEOUT; timeouts[0].type = TMPARAM_AT; timeouts[0].fin_time = ltime; enable_timeouts(timeouts, 1); } /* Wait to be signaled by the release of the Relation Lock */ ProcWaitForSignal(PG_WAIT_LOCK | locktag.locktag_type); /* * Clear any timeout requests established above. We assume here that the * Startup process doesn't have any other outstanding timeouts than those * used by this function. If that stops being true, we could cancel the * timeouts individually, but that'd be slower. */ disable_all_timeouts(false); }
/* * visibilitymap_truncate - truncate the visibility map * * The caller must hold AccessExclusiveLock on the relation, to ensure that * other backends receive the smgr invalidation event that this function sends * before they access the VM again. * * nheapblocks is the new size of the heap. */ void visibilitymap_truncate(Relation rel, BlockNumber nheapblocks) { BlockNumber newnblocks; /* last remaining block, byte, and bit */ BlockNumber truncBlock = HEAPBLK_TO_MAPBLOCK(nheapblocks); uint32 truncByte = HEAPBLK_TO_MAPBYTE(nheapblocks); uint8 truncOffset = HEAPBLK_TO_OFFSET(nheapblocks); #ifdef TRACE_VISIBILITYMAP elog(DEBUG1, "vm_truncate %s %d", RelationGetRelationName(rel), nheapblocks); #endif RelationOpenSmgr(rel); /* * If no visibility map has been created yet for this relation, there's * nothing to truncate. */ if (!smgrexists(rel->rd_smgr, VISIBILITYMAP_FORKNUM)) return; /* * Unless the new size is exactly at a visibility map page boundary, the * tail bits in the last remaining map page, representing truncated heap * blocks, need to be cleared. This is not only tidy, but also necessary * because we don't get a chance to clear the bits if the heap is extended * again. */ if (truncByte != 0 || truncOffset != 0) { Buffer mapBuffer; Page page; char *map; newnblocks = truncBlock + 1; mapBuffer = vm_readbuf(rel, truncBlock, false); if (!BufferIsValid(mapBuffer)) { /* nothing to do, the file was already smaller */ return; } page = BufferGetPage(mapBuffer); map = PageGetContents(page); LockBuffer(mapBuffer, BUFFER_LOCK_EXCLUSIVE); /* NO EREPORT(ERROR) from here till changes are logged */ START_CRIT_SECTION(); /* Clear out the unwanted bytes. */ MemSet(&map[truncByte + 1], 0, MAPSIZE - (truncByte + 1)); /*---- * Mask out the unwanted bits of the last remaining byte. * * ((1 << 0) - 1) = 00000000 * ((1 << 1) - 1) = 00000001 * ... * ((1 << 6) - 1) = 00111111 * ((1 << 7) - 1) = 01111111 *---- */ map[truncByte] &= (1 << truncOffset) - 1; /* * Truncation of a relation is WAL-logged at a higher-level, and we * will be called at WAL replay. But if checksums are enabled, we need * to still write a WAL record to protect against a torn page, if the * page is flushed to disk before the truncation WAL record. We cannot * use MarkBufferDirtyHint here, because that will not dirty the page * during recovery. */ MarkBufferDirty(mapBuffer); if (!InRecovery && RelationNeedsWAL(rel) && XLogHintBitIsNeeded()) log_newpage_buffer(mapBuffer, false); END_CRIT_SECTION(); UnlockReleaseBuffer(mapBuffer); } else newnblocks = truncBlock; if (smgrnblocks(rel->rd_smgr, VISIBILITYMAP_FORKNUM) <= newnblocks) { /* nothing to do, the file was already smaller than requested size */ return; } /* Truncate the unused VM pages, and send smgr inval message */ smgrtruncate(rel->rd_smgr, VISIBILITYMAP_FORKNUM, newnblocks); /* * We might as well update the local smgr_vm_nblocks setting. smgrtruncate * sent an smgr cache inval message, which will cause other backends to * invalidate their copy of smgr_vm_nblocks, and this one too at the next * command boundary. But this ensures it isn't outright wrong until then. */ if (rel->rd_smgr) rel->rd_smgr->smgr_vm_nblocks = newnblocks; }
