Thread: Replication conflicts despite hot_standby_feedback = on?
I'm seeing the following at a customer site: SELECT confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock FROM pg_stat_database_conflicts WHERE datname = 'something' \gx -[ RECORD 1 ]----+------ confl_tablespace | 0 confl_lock | 0 confl_snapshot | 84990 confl_bufferpin | 0 confl_deadlock | 0 SHOW hot_standby_feedback; hot_standby_feedback ---------------------- on (1 row) This is PostgreSQL 11.7, the standby didn't disconnect from the primary, and the number of replication conflicts is growing. I had thought that "hot_standby_feedback = on" would get rid of such conflicts. In the code I see a lot of call sites for ResolveRecoveryConflictWithSnapshot, so it is hard for me to track this down. Does anybody know what could cause these replication conflicts? Yours, Laurenz Albe
On Wed, Jun 3, 2020 at 1:07 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > I'm seeing the following at a customer site: > > SELECT confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock > FROM pg_stat_database_conflicts > WHERE datname = 'something' \gx > > -[ RECORD 1 ]----+------ > confl_tablespace | 0 > confl_lock | 0 > confl_snapshot | 84990 > confl_bufferpin | 0 > confl_deadlock | 0 > > SHOW hot_standby_feedback; > > hot_standby_feedback > ---------------------- > on > (1 row) > > This is PostgreSQL 11.7, the standby didn't disconnect from the primary, and > the number of replication conflicts is growing. > > I had thought that "hot_standby_feedback = on" would get rid of such > conflicts. > > In the code I see a lot of call sites for ResolveRecoveryConflictWithSnapshot, > so it is hard for me to track this down. Does anybody know what could cause > these replication conflicts? One of the frequent causes is the lock acquired by (auto)vacuum when truncating the trailing empty blocks, maybe you can correlate your conflicts with autovacuum activity?
On Wed, 2020-06-03 at 13:41 +0200, Julien Rouhaud wrote: > > I'm seeing the following at a customer site: > > > > SELECT confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock > > FROM pg_stat_database_conflicts > > WHERE datname = 'something' \gx > > > > -[ RECORD 1 ]----+------ > > confl_tablespace | 0 > > confl_lock | 0 > > confl_snapshot | 84990 > > confl_bufferpin | 0 > > confl_deadlock | 0 > > > > SHOW hot_standby_feedback; > > > > hot_standby_feedback > > ---------------------- > > on > > (1 row) > > One of the frequent causes is the lock acquired by (auto)vacuum when > truncating the trailing empty blocks, maybe you can correlate your > conflicts with autovacuum activity? Yes, but that would show up as a lock conflict, not a snapshot conflict, right? Yours, Laurenz Albe
On Wed, Jun 3, 2020 at 2:05 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > On Wed, 2020-06-03 at 13:41 +0200, Julien Rouhaud wrote: > > > I'm seeing the following at a customer site: > > > > > > SELECT confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock > > > FROM pg_stat_database_conflicts > > > WHERE datname = 'something' \gx > > > > > > -[ RECORD 1 ]----+------ > > > confl_tablespace | 0 > > > confl_lock | 0 > > > confl_snapshot | 84990 > > > confl_bufferpin | 0 > > > confl_deadlock | 0 > > > > > > SHOW hot_standby_feedback; > > > > > > hot_standby_feedback > > > ---------------------- > > > on > > > (1 row) > > > > One of the frequent causes is the lock acquired by (auto)vacuum when > > truncating the trailing empty blocks, maybe you can correlate your > > conflicts with autovacuum activity? > > Yes, but that would show up as a lock conflict, not a snapshot conflict, > right? Oh yes indeed, sorry for the noise.