9.2 streaming replication issue and solution strategy
Hello,
We are running 9.2 w/ streaming replication.
The slave is used for heavy tsearch based data mining.
Apparently depending on the business of the master the slave queries fail with different frequency with the following message —
ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
Initially we resolved the problem by introducing a retry (wait 1 second, try), which works in some cases, and fails on other due to too many retries necessary (10+).
After that we introduced a second slave, and told the try to switch slaves (try here, if not, wait 1 second and try there).
Which works almost every time.
So apparently the issues are not synchronous (probably logical as the streaming replication is asynchronous).
Has anybody else experience such an issue and are there different approaches to it?
Than you,
T.E.L.
On 11/17/2012 06:08 AM, T. E. Lawrence wrote:
Hello,
We are running 9.2 w/ streaming replication.
The slave is used for heavy tsearch based data mining.
Apparently depending on the business of the master the slave queries fail with different frequency with the following message �
ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.Initially we resolved the problem by introducing a retry (wait 1 second, try), which works in some cases, and fails on other due to too many retries necessary (10+).
After that we introduced a second slave, and told the try to switch slaves (try here, if not, wait 1 second and try there).
Which works almost every time.
So apparently the issues are not synchronous (probably logical as the streaming replication is asynchronous).
Has anybody else experience such an issue and are there different approaches to it?
Have you looked at the below?:
http://www.postgresql.org/docs/9.2/interactive/hot-standby.html#HOT-STANDBY-CONFLICT
25.5.2. Handling Query Conflicts
Than you,
T.E.L.
--
Adrian Klaver
adrian.klaver@gmail.com
Have you looked at the below?:
http://www.postgresql.org/docs/9.2/interactive/hot-standby.html#HOT-STANDBY-CONFLICT
25.5.2. Handling Query Conflicts
Yes, thank you!
I am hoping to hear more from people who have running 9.2 systems w/ between 100m and 1b records, w/ streaming replication and heavy data mining on the slaves (5-50m records read per hour by multiple parallel processes), while from time to time (2-3 times/week) between 20 and 50m records are inserted/updated within 24 hours.
How do they resolve this situation.
For us retry + switch slave works quite well right now, without touching the db configuration in this respect yet.
But may be there are different approaches.
On 11/17/2012 07:33 AM, T. E. Lawrence wrote:
Have you looked at the below?:
http://www.postgresql.org/docs/9.2/interactive/hot-standby.html#HOT-STANDBY-CONFLICT
25.5.2. Handling Query Conflicts
Yes, thank you!
I am hoping to hear more from people who have running 9.2 systems w/ between 100m and 1b records, w/ streaming replication and heavy data mining on the slaves (5-50m records read per hour by multiple parallel processes), while from time to time (2-3 times/week) between 20 and 50m records are inserted/updated within 24 hours.
How do they resolve this situation.
For us retry + switch slave works quite well right now, without touching the db configuration in this respect yet.
But may be there are different approaches.
The conditions you cite above are outside my experience and I will leave
it to others for specific suggestions.
On a more conceptual level, assuming asynchronous replication, I see the
following.
1) In a given database data changes are like wave fronts flowing across
a sea of data.
2) Replication introduces another wave front in the movement of data
from parent to child.
3) Querying that wave front in the child becomes a timing issue.
4) There are settings to mitigate that timing issue but not eliminate
it. To do so would require more information exchange between the
parent/child then takes place currently.
5) If it is essential to work the wave front then turbulence is to be
expected and dealt with. See your solution. I too would be interested in
method that is not some variation of what you do.
6) If working the wave front is not essential, then other strategies
come into play. For instance partitioning, where older more 'settled'
data can be segregated and worked on.
--
Adrian Klaver
adrian.klaver@gmail.com
Adrian Klaver wrote:
I am hoping to hear more from people who have running 9.2 systems
w/ between 100m and 1b records, w/ streaming replication and heavy
data mining on the slaves (5-50m records read per hour by multiple
parallel processes), while from time to time (2-3 times/week)
between 20 and 50m records are inserted/updated within 24 hours.
I've run replication on that general scale. IMV, when you are using
PostgreSQL hot standby and streaming replication you need to decide
whether a particular replica is primarily for recovery purposes, in
which case you can't run queries which take very long without getting
canceled for this reason, or primarily for reporting, in which case
long-running queries can finish, but the data in the database may get
relatively stale while they run. If you have multiple replicas, you
probably want to configure them differently in this regard.
-Kevin
Import Notes
Resolved by subject fallback