0

I'm considering to use Amazon MySQL RDS with Read Replicas. The only thing disturbing me is Replica Lag and eventual inconsistency. For example, image the case when user modifies his profile (UPDATE will be performed on main DB instance) and then refreshes the page to see changed info (SELECT might be performed from Replica which has not received changes yet due to Replica Lag).

By accident, I found Amazon article which mentions its possible to perform targeted queries. For me it sounds like we can add some parameter or other to tell Amazon to execute select on the main DB instance instead of on Replica. The example with user profile is quite trivial but the same problem occurs in more realistic cases, for example checkout, when a user performs several steps and he needs to see updated info on then next screens. Yes, application could cache entire data set on its own, however it would be great if anybody knows how to perform targeted queries on main DB instance.

Centurion
  • 14,106
  • 31
  • 105
  • 197

1 Answers1

1

I read the link you referenced and didn't find any mention of "target" or anything like that.

But this line might be what you're referring to:

Otherwise, you should spread out the load and read from one of the Read Replicas. You can make this decision on a query-by-query basis within your application. You will probably want to maintain some sort of registry of available Read Replicas within your application, choosing from among them on a round-robin or randomly distributed basis.

If so, then I interpret that line to suggest that you can balance reads in your application by just picking one server from a pool and hitting that one. But it would be all in your application logic.

Cody Caughlan
  • 32,456
  • 5
  • 63
  • 68
  • Yes, you are right. There's no magic but hard work on application side. However, I will need to find out is it possible to maintain so called list of IP or other identifiers of Read Replicas and Main DB instance, that won't change even after deleting and recreating Replica. Because otherwise such list will be very hardcoded and will be needed to update every time IP of Main DB instance or Replica changes (if any). – Centurion Dec 28 '14 at 18:03