Reader and Writer DB endpoints and slave lag

So, we have an Aurora MySQL cluster in AWS that Laravel talks to.

We had it configured to use the cluster endpoint URL for everything. In theory, SELECTs are supposed to go to the reader(s) through the cluster endpoint, and INSERT, UPDATE, DELETE are supposed to go to the writer. All is fine and dandy.

Until we run a job in PHP CLI that does a write and then immediately a read from the same table, like a $item->save()->fresh();

The issue we're having is that reloading it fails, because the SELECT that runs 2ms later is sent to the slave which is 10ms to 100ms behind, so it fails to reload correctly. This is extremely difficult to diagnose and debug because the slave has been updated by the time you go and run the query that failed.

Of course the default config appears to be amazing, simple and automatic for all other use cases.

So we look up in Laravel config, it's possible to specify a reader and a writer separately in the config/database.php file.

So we did that, but now for our jobs to work we have to put the writer/master endpoint into the config for both reader and writer. Now our SELECTs all go to the writer also.

Has anyone else run into this? Is there any other automatic solution to it?

I am thinking I have to define two separate connections in Laravel to the same database, one for the reader and one for the writer, and be specific with anywhere ->fresh( ) type query runs in my code to specify for the read to go to the master I just wrote on.

Fun fact: We just also discovered that when you do a master failover in RDS, Amazon internal DNS name for your writer has to change, so rather than the luxury of using the cluster endpoint you have to also change your endpoints in .env file if you're configured to go directly to the write master.

nickkiermaier

7 years ago

Level 1

I just ran into the same thing.

Basically what I decided was the only way to easily handle this is add two database connections for the same database, one using the cluster endpoint (which automatically directs writes to the master and reads to the slave) and another that specifically uses the writer endpoint.

Then with your situation of hot reloading (e.g. $model->save()->fresh()) don't do it that way, rather, $model->save() and then Model::connection('writer')->where()->first() to reload it.

The issue I'm still having is failover. If the cluster failsover from reader to writer then you either have to change the DNS in the cluster or change the DNS in the code.

phpguru

7 years ago

Level 1

Whoa, I missed this before - from the Laravel 5.5+ docs:

The sticky Option The sticky option is an optional value that can be used to allow the immediate reading of records that have been written to the database during the current request cycle. If the sticky option is enabled and a "write" operation has been performed against the database during the current request cycle, any further "read" operations will use the "write" connection. This ensures that any data written during the request cycle can be immediately read back from the database during that same request. It is up to you to decide if this is the desired behavior for your application.

Please or to participate in this conversation.