On 2/26/2015 9:44 AM, David F. Skoll wrote:
Hi,
I posted this a couple of weeks ago and no response... I guess it's
quite mysterious.
Anyway, behavior is consistent. It seems that long-running
transactions on the primary (even read-only ones, because in my case
it's a pg_dump) can block subsequent transactions from appearing on
the hot-standby until the first transaction finishes. Is this a known
limitation? I run 9.1; is it still present in 9.4?
Regards,
David.
========================================================================
[...]
I have a monitoring script that tests the actual delay for a
transaction on the master to appear on the hot-standby. Every few
minutes, my script runs an update on the master and then sits in a
loop checking how long it takes to appear on the hot-standby. 99% of
the time, it's less than a second.
But every once in a while, the time spikes dramatically, to hundreds
or thousands of seconds, and that's too long... the delay-tolerant
queries are not *that* delay-tolerant, so we switch to sending them
all to the master.
See the graph: http://ibin.co/1rdm4ekiWmpM
[...]
Apologies, David, but as I don't check this list very often, I thought you should have received an answer. Have you tried watching the transaction with tcpdump or wireshark? To me
this sounds like either a network problem or like you're thinking a contention problem. Also, are you running streaming replication? If so, there shouldn't be ANY delay as the
standby gets the commit before the primary. Otherwise, how are you replicating?
--
Jay
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
