pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHot standby with hot_standny_feedback enabled: cancelling statement issues
3+ messages / 2 participants
[nested] [flat]
* Hot standby with hot_standny_feedback enabled: cancelling statement issues
@ 2020-11-25 09:46 Игорь Выскорко <vyskorko.igor@yandex.ru>
2020-11-25 14:46 ` Re: Hot standby with hot_standny_feedback enabled: cancelling statement issues Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 3+ messages in thread
From: Игорь Выскорко @ 2020-11-25 09:46 UTC (permalink / raw)
To: pgsql-admin
<div>Hi!<br /> </div><div>I'm trying to configure hot standby replica for analytics and simple backup purposes. So, I have long queries which constantly cancelled without proper configuring.<br />I do not want large lag on replica (more than 30 mins) so setting max_standby_streaming_delay to -1 is not my choice. I have to avoid large lag because of analytic queries which must be run on almost fresh copy of data.</div><div>I found the panacea (I thought I found actually) in setting hot_standby_feedback = on (I know about possible master bloating) but it also doesn't help.<br />Just to clarify about "simple backup purposes": it means run pg_dump in daily basis. Our database is about 250Gb<br /> <div>2 test run of pg_dump failed with:<div>pg_dump: Dumping the contents of table "table" failed: PQgetResult() failed.</div><div>pg_dump: Error message from server: ERROR: canceling statement due to conflict with recovery</div><div>DETAIL: User was holding a relation lock for too long.</div><div>pg_dump: The command was: COPY...<br /> <div>postgres=# select * from pg_stat_database_conflicts ;</div><div> datid | datname | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock </div><div>-----------+---------------+------------------+------------+----------------+-----------------+----------------</div><div> 12445 | dbname | 0 | 2 | 0 | 0 | 0</div><div><br />Not sure about what kind of lock was holding too long...<br /><br />So, my questions are:<br />1. Why "hot_standby_feedback = on" is not helping?<br />2. What is proper way to use replica for tasks as mine?<br /><br />Thanks</div></div></div><br /><br />Our current setup:<div><div>postgres=# select version();</div><div> version </div><div>---------------------------------------------------------------------------------------------------------------------------------------</div><div> PostgreSQL 9.6.15 on x86_64-pc-linux-gnu (Ubuntu 9.6.15-1.pgdg18.04+1), compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit</div><div>(1 row)</div><div> </div><div>postgres=# select name, setting, unit from pg_settings where category = 'Replication / Standby Servers'; </div><div> name | setting | unit </div><div>------------------------------+---------+------</div><div> hot_standby | on | </div><div> hot_standby_feedback | on | </div><div> max_standby_archive_delay | 930000 | ms</div><div> max_standby_streaming_delay | 900000 | ms</div><div> wal_receiver_status_interval | 10 | s</div><div> wal_receiver_timeout | 60000 | ms</div><div> wal_retrieve_retry_interval | 5000 | ms</div><div>(7 rows)</div></div></div><div> </div>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Hot standby with hot_standny_feedback enabled: cancelling statement issues
2020-11-25 09:46 Hot standby with hot_standny_feedback enabled: cancelling statement issues Игорь Выскорко <vyskorko.igor@yandex.ru>
@ 2020-11-25 14:46 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-11-26 06:54 ` Re: Hot standby with hot_standny_feedback enabled: cancelling statement issues Игорь Выскорко <vyskorko.igor@yandex.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2020-11-25 14:46 UTC (permalink / raw)
To: Игорь Выскорко <vyskorko.igor@yandex.ru>; pgsql-admin
On Wed, 2020-11-25 at 16:46 +0700, Игорь Выскорко wrote:
> I'm trying to configure hot standby replica for analytics and simple backup purposes. So, I have long queries which constantly cancelled without proper configuring.
> I do not want large lag on replica (more than 30 mins) so setting max_standby_streaming_delay to -1 is not my choice. I have to avoid large lag because of analytic queries which must be run on
> almost fresh copy of data.
> I found the panacea (I thought I found actually) in setting hot_standby_feedback = on (I know about possible master bloating) but it also doesn't help.
> Just to clarify about "simple backup purposes": it means run pg_dump in daily basis. Our database is about 250Gb
>
> 2 test run of pg_dump failed with:
> pg_dump: Dumping the contents of table "table" failed: PQgetResult() failed.
> pg_dump: Error message from server: ERROR: canceling statement due to conflict with recovery
> DETAIL: User was holding a relation lock for too long.
> pg_dump: The command was: COPY...
>
> postgres=# select * from pg_stat_database_conflicts ;
> datid | datname | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock
> -----------+---------------+------------------+------------+----------------+-----------------+----------------
> 12445 | dbname | 0 | 2 | 0 | 0 | 0
>
> Not sure about what kind of lock was holding too long...
>
> So, my questions are:
> 1. Why "hot_standby_feedback = on" is not helping?
Because is was not a conflict with a tuple deleted by VACUUM ("snapshot conflict"),
but with a lock.
ACCESS EXCLUSIVE locks are replicated, because they are necessary for safe
replication of commands like TRUNCATE or ALTER TABLE.
If you ran none of these commands on the primary, you probably have fallen
prey to "vacuum truncation", where a brief ACCESS EXCLUSIVE lock is taken on
a table so that VACUUM can truncate some empty pages from a table.
These locks don't disrupt operation on the primary, but they can conflict
with queries on the standby.
> 2. What is proper way to use replica for tasks as mine?
The best thing is to have two standbys: one for high availability that does
not lag (hot_standby = off), and one for pg_dump and queries.
You may want to read my blog on the topic:
https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Hot standby with hot_standny_feedback enabled: cancelling statement issues
2020-11-25 09:46 Hot standby with hot_standny_feedback enabled: cancelling statement issues Игорь Выскорко <vyskorko.igor@yandex.ru>
2020-11-25 14:46 ` Re: Hot standby with hot_standny_feedback enabled: cancelling statement issues Laurenz Albe <laurenz.albe@cybertec.at>
@ 2020-11-26 06:54 ` Игорь Выскорко <vyskorko.igor@yandex.ru>
0 siblings, 0 replies; 3+ messages in thread
From: Игорь Выскорко @ 2020-11-26 06:54 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
<div> </div><div> </div><div>25.11.2020, 21:46, "Laurenz Albe" <laurenz.albe@cybertec.at>:</div><blockquote><p>On Wed, 2020-11-25 at 16:46 +0700, Игорь Выскорко wrote:</p><blockquote> I'm trying to configure hot standby replica for analytics and simple backup purposes. So, I have long queries which constantly cancelled without proper configuring.<br /> I do not want large lag on replica (more than 30 mins) so setting max_standby_streaming_delay to -1 is not my choice. I have to avoid large lag because of analytic queries which must be run on<br /> almost fresh copy of data.<br /> I found the panacea (I thought I found actually) in setting hot_standby_feedback = on (I know about possible master bloating) but it also doesn't help.<br /> Just to clarify about "simple backup purposes": it means run pg_dump in daily basis. Our database is about 250Gb<br /> <br /> 2 test run of pg_dump failed with:<br /> pg_dump: Dumping the contents of table "table" failed: PQgetResult() failed.<br /> pg_dump: Error message from server: ERROR: canceling statement due to conflict with recovery<br /> DETAIL: User was holding a relation lock for too long.<br /> pg_dump: The command was: COPY...<br /> <br /> postgres=# select * from pg_stat_database_conflicts ;<br /> datid | datname | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock<br /> -----------+---------------+------------------+------------+----------------+-----------------+----------------<br /> 12445 | dbname | 0 | 2 | 0 | 0 | 0<br /> <br /> Not sure about what kind of lock was holding too long...<br /> <br /> So, my questions are:<br /> 1. Why "hot_standby_feedback = on" is not helping?</blockquote><p><br />Because is was not a conflict with a tuple deleted by VACUUM ("snapshot conflict"),<br />but with a lock.<br /><br />ACCESS EXCLUSIVE locks are replicated, because they are necessary for safe<br />replication of commands like TRUNCATE or ALTER TABLE.<br /><br />If you ran none of these commands on the primary, you probably have fallen<br />prey to "vacuum truncation", where a brief ACCESS EXCLUSIVE lock is taken on<br />a table so that VACUUM can truncate some empty pages from a table.<br /><br />These locks don't disrupt operation on the primary, but they can conflict<br />with queries on the standby.<br /> </p><blockquote> 2. What is proper way to use replica for tasks as mine?</blockquote><p><br />The best thing is to have two standbys: one for high availability that does<br />not lag (hot_standby = off), and one for pg_dump and queries.<br /><br />You may want to read my blog on the topic:<br /><a href="https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/"; rel="noopener noreferrer">https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/</a><b... /><br />Yours,<br />Laurenz Albe</p></blockquote><div><div>Thanks for reply and link to topic. It's make sense now why replication conflicts are raised.</div><div>I found hourly executed task which uses truncate operation...</div><div>So, as you suggested - the best way I see is to set hot_standby_feedback = off and set max_standby_streaming_delay to rather high value. </div></div>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-11-26 06:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-25 09:46 Hot standby with hot_standny_feedback enabled: cancelling statement issues Игорь Выскорко <vyskorko.igor@yandex.ru>
2020-11-25 14:46 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-11-26 06:54 ` Игорь Выскорко <vyskorko.igor@yandex.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox