Conversation between Melissa Kaulfuss and George Sheppard.
mel [11:36 AM]
joined #aws
mel
[11:37 AM]
sup
mel
[11:39 AM]
Does anyone have any experience with AWS Data Migration Service? I get an error when I’m running complex SQL queries. I’ve found some posts that suggest setting `max_standby_streaming_delay` to `-1` so that the vacuum is delayed indefinitely but that’s not working for us.
[11:40]
Can’t seem to find any answers in the docs
wolfeidau [11:45 AM]
@mel Are you doing this for a customer?
mel
[11:46 AM]
We’re executing the SQL on our DR db (edited)
wolfeidau [11:46 AM]
@mel do you have an account manager at amazon?
mel
[11:46 AM]
And getting an error: ERROR: canceling statement due to conflict with recovery
wolfeidau [11:46 AM]
I would step 1, raise a support ticket in your main account
mel
[11:46 AM]
We’re trying to figure it out ourselves. It’s 10% time :slightly_smiling_face:
wolfeidau [11:47 AM]
Step two is hassle your Amazon account rep
mel
[11:47 AM]
Okay, hahah
[11:47]
I don’t know if we have one, that’s the Ops peeps thing.
fuzzmonkey [11:47 AM]
@mel are you doing this on a replica ?
mel
[11:47 AM]
I just love this stuff.
fuzzmonkey [11:47 AM]
because "canceling statement due to conflict with recovery" happens when your query takes too long and postgres cancels the query because it can't guarantee rows haven't changed
mel
[11:48 AM]
Yeah, that’s what I’ve discovered
[11:49]
So it’s a postgres error, we’ve tried to fix this with the suggested `max_standby_streaming_delay = -1` but no luck
[11:49]
Says you can do that on the server but not so
[11:49]
May have to go in a config file?
fuzzmonkey [11:50 AM]
i'm not sure that will fix it but i'm not a postgres expert :smile:
mel
[11:51 AM]
hehe well, you’re probably more an expert than me
fuzzmonkey [11:56 AM]
so my understanding is, that postgres cancels the query on the replica because rows have changed on the master so it can't guarantee the result of the query, the time is takes before cancelling is based on max_standby_streaming_delay and -1 basically lets the replica wait indefinitely.
[11:56]
but only applies on hot standby configurations
[11:57]
where is the replica? RDS?
mel
[11:57 AM]
yeah that’s what I read. I have no idea whether we have a hot standby config
[11:57]
Yeah RDS I think
fuzzmonkey [11:59 AM]
how did you set max_standby_streaming_delay to -1, via the parameter group assigned to that RDS instance?
fuzzmonkey [12:00 PM]
also this:
>Note that max_standby_streaming_delay is not the same as the maximum length of time a query can run before cancellation; rather it is the maximum total time allowed to apply WAL data once it has been received from the primary server. Thus, if one query has resulted in significant delay, subsequent conflicting queries will have much less grace time until the standby server has caught up again.
fuzzmonkey [12:11 PM]
@mel so i just tried that setting max_standby_streaming_delay = -1 on my read replica, updated parameter group assigned to both primary + replica. it was a dynamic update so didn't require restarting either instance. Seems to have fixed the cancelling query due to conflict problem so you are on the right tracks i think. Also thanks for the info!
mel
[1:35 PM]
Thanks @fuzzmonkey!
wolfeidau [1:59 PM]
@mel welcome to devops :wink:
mel
[1:59 PM]
thank you, I do really like this stuff.
[2:04]
TIL hot standby is the same as a recovery database?
[2:04]
Sounds much more serious.
[2:05]
Oh wait, no it’s not.
wolfeidau [7:50 AM]
@mel @fuzzmonkey you need to gist this conversation or it will be lost forever!