Re: automatic vacuum on pg_statistic pg_toast area blocks all queries in hot standby

Hello.

What happens on the master:
1. A typical request puts a AccessShareLock on the table.
2. Autovacuum puts cleans dead entries from the table. This is possible
because AccessShareLock does not conflict with ExclusiveLock.
http://www.postgresql.org/docs/9.1/static/explicit-locking.html
3. Autovacuum finish successful.
4. Unlock AccessShareLock.

What happens on the slave:
1. From WAL puts a AccessShareLock on table.
2. From WAL recovery Autovacuum result, but there is a conflict. WAL don't
recovery, waiting time seconds from parameter max_standby_streaming_delay
before canceling queries.
3. Queries waiting unlock AccessShareLock. Unlock does not occur because of
paragraph 2.
4. Queries canceling by statement_timeout or conflict recovery.
5. WAL recovery and unlock AccessShareLock.

If enable hot_standby_feedback then master also waiting slave time seconds
from max_standby_streaming_delay parameter.

Solution:
1. Disable hot_standby_feedback.
2. select txid_current() into res1
3. Waiting seconds.
4. select txid_current() into res2
5. Change postgresql.conf on master. vacuum_defer_cleanup_age = res2 - res1
+ reserve.

Good luck.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/automatic-vacuum-on-pg-statistic-pg-toast-area-blocks-all-queries-in-hot-standby-tp5807143p5820168.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.