MySQL replication speed

Question

Lets say we have 3 servers on a local area network:

APP - php app server

MASTER - master Mysql db server

SLAVE - slave Mysql db server (replicating MASTER)

In the one code block (php) APP inserts into MASTER, retrieving the last_insert_id which it then uses to select the data it just inserted from SLAVE.

So the question is, will the replication have happened in time for SLAVE to have the data from MASTER? Is this influenced by load put on either MASTER or SLAVE?

And lastly, if there is a issue with the data being available, is there a way to ensure the latest data is received from SLAVE?

Answer 1

That depends on how fast your SLAVE server replicates data from your MASTER server.

If it is directly tied with the INSERT, UPDATE, and DELETE commands, meaning when you insert something, you are actually inserting it on both, most likely it will be available on either.

If the SLAVE server replicates MASTER by a task that is run every so often to grab the data from MASTER, which would be less costly than making 2 Queries for every 1 query, it would mean that the data isn't available until the task was ran.

Answer 2

Because MySQL Replication time is an uncontrollable variable (even in proper implementations, problems can arise that stall replication, or block it up), it's best to not rely on the data replicating. You can break down the problem into two situations:

1) I'm the user doing a CUD operation, I expect the R operation afterwords to mirror the change I just made. In this case you absolutely need to pull the data off the master, because the user expects that his change had an effect.

2) I'm an outside user, I don't know that a CUD command happened and I generally don't care, so returning stale data is acceptable (in 99% of implementations, otherwise you probably wouldn't be using PHP*).

An alternative to #1 would be to inform the user that his change is propagating, and is acceptable in some situations, e.g. many websites will tell you that x may take up to y minutes to appear, this is this situation in action.

• If you need instant updates, you could try writing to a cache layer that is polled along with the (potentially unreliable) DB data. Something like memcached would be a prime candidate.