[Request] Implement automated read/write connections in Eloquent

[ipalaus]

For those we use master/slave connections in our applications it will be cool to have some way we can setup so Eloquent auto-detects it.

There is various scenarios on how this can be implemented but a basic SELECT => slave connection, INSERT, DELETE, UPDATE => master will be fantastic.

Thanks!

[JavierMartinz]

+1

[mitchellvanw]

+1

[bencorlett]

+1

[taylorotwell]

Frankly I think this is already fairly easy:

User::on('connection-name')->where('foo', 1)->get();

Baking throughout each and every insert throughout the framework would be tedious, and I think it is pretty manageable by the application.

[philsturgeon]

@taylorotwell: When I proposed this feature to you a few months ago you suggested it would be a feature you'd consider for 4.1. This was fair enough, because its not a fundamental feature and I can imagine other things are of a higher priority, but other people are asking for the same thing. I don't think its as easy as you suggest to implement in applications, but if the feature was implemented then usage would be as simple as changing your config to this:

'connections' => [
    'mysql' => [
        'read' => [
            'driver'   => 'mysql',
            'host' => 'slave-server',
            'database' => 'kapture',
            'username' => 'kapture',
            'password' => 'password',
            'prefix' => '',
            'charset'  => 'utf8',
            'collation'  => 'utf8_unicode_ci',
        ],
        'write' => [
            'driver'   => 'mysql',
            'host' => 'primary-server',
            'database' => 'kapture',
            'username' => 'kapture',
            'password' => 'password',
            'prefix' => '',
            'charset'  => 'utf8',
            'collation'  => 'utf8_unicode_ci',
        ]
    ],
],

This would make it amazingly easy for a ORMed up Laravel application to suddenly scale up their entire architecture without having to recode their entire application. I could switch Kapturefrom using one database server to using a Read-only Replica on RDS and use the primary server for slower writes.

I'd really like this feature to be re-opened, even if its not worked on straight away. Somebody else might want to add it as a pull request if they get the time - but an outright "no" doesn't help anyone.

[taylorotwell]

Re-opening so that Phil doesn't hit me at Laracon. I really will consider it further down the line for things like 4.1.

[philsturgeon]

:D

Phil Sturgeon

On Friday, 25 January 2013 at 10:02, Taylor Otwell wrote:

Re-opening so that Phil doesn't hit me at Laracon. I really will consider it further down the line for things like 4.1.

—
Reply to this email directly or view it on GitHub (#5 (comment)).

[ipalaus]

Thanks :)

[taylorotwell]

This needs to be discussed further in terms of configuration... What if you have multiple read slaves? Multiple write servers? What would the configuration look like? Someone who is actually doing or needs this in production please contribute to these questions.

[maximebeaudoin]

We will need something like this in production soon. I'll discuse this with my team and give you some feeback and, hopefully, with great ideas :)

[philsturgeon]

Didn't we completely work out the entire plan for this already on Skype?

Sent from my iPhone

On Jun 1, 2013, at 9:13 PM, Taylor Otwell notifications@github.com wrote:

This needs to be discussed further in terms of configuration... What if you have multiple read slaves? Multiple write servers? What would the configuration look like? Someone who is actually doing or needs this in production please contribute to these questions.

—
Reply to this email directly or view it on GitHub.

[taylorotwell]

We need more input than just us :)

On Jun 1, 2013, at 8:59 PM, Phil Sturgeon notifications@github.com wrote:

Didn't we completely work out the entire plan for this already on Skype?

Sent from my iPhone

On Jun 1, 2013, at 9:13 PM, Taylor Otwell notifications@github.com wrote:

This needs to be discussed further in terms of configuration... What if you have multiple read slaves? Multiple write servers? What would the configuration look like? Someone who is actually doing or needs this in production please contribute to these questions.

—
Reply to this email directly or view it on GitHub.
—
Reply to this email directly or view it on GitHub.

[ipalaus]

@taylorotwell for multiple read slaves you will use a load balancer so the app doesn't have to handle more than the IP of the load balancer. As @philsturgeon pointed, a write and a slave connection will be perfectly for a basic master/slave.

I'm sure that there is other solutions like: if you have already open a connection to the master, don't open a new one to the slave to do some queries... just hit the master. But for those people that need more complicated scenarios probably they should build their own connector.

Edit: Ah! Multi-write servers will be also handled by a load balancer so you will end with one IP on the configuration too.

[philsturgeon]

Prominent uses cases:

1 server handles both
1 write, 1 read

I have in the past used/

1 write, X reads

I can potentially see that people would also need:

X wrItes, X reads

You could assume everyone is using a load balancer but I don't think using an array is a nice elegant solution to allow this functionality for folks that have no idea how to do that, but have replication set up.

Sent from my iPhone

On Jun 1, 2013, at 10:20 PM, Isern Palaus notifications@github.com wrote:

@taylorotwell for multiple read slaves you will use a load balancer so the app doesn't have to handle more than the IP of the load balancer. As @philsturgeon pointed, a write and a slave connection will be perfectly for a basic master/slave.

I'm sure that there is other solutions like: if you have already open a connection to the master, don't open a new one to the slave to do some queries... just hit the master. But for those people that need more complicated scenarios probably they should build their own connector.

—
Reply to this email directly or view it on GitHub.

[taylorotwell]

OK Let's say you aren't using a load balancer, and have multiple reads and multiple write servers. I assume they would all be listed in an array in the database configuration, and Laravel would pick one at random for each request?

[philsturgeon]

Correct

Sent from my iPhone

On Jun 5, 2013, at 11:38 PM, Taylor Otwell notifications@github.com wrote:

OK Let's say you aren't using a load balancer, and have multiple reads and multiple write servers. I assume they would all be listed in an array in the database configuration, and Laravel would pick one at random for each request?

—
Reply to this email directly or view it on GitHub.

[maximebeaudoin]

Sounds great

[lchski]

Is there a better way to implement the picking of the database than just at random? I know this gets back into load-balancing land, but what about assigning preference to individual databases (ex: pick this one to write to 75% of the time, this one 25%). If it's not specified, each database could be weighted equally.

[philsturgeon]

Why?

Sent from my iPhone

On Jun 9, 2013, at 8:41 PM, Lucas Cherkewski notifications@github.com wrote:

Is there a better way to implement the picking of the database than just at random? I know this gets back into load-balancing land, but what about assigning preference to individual databases (ex: pick this one to write to 75% of the time, this one 25%). If it's not specified, each database could be weighted equally.

—
Reply to this email directly or view it on GitHub.

[ipalaus]

@asdfgh746 your comment makes a lot of sense but I don't think it's something that must be application side. Mostly because, how you will know how many times to hit server A or server B? Of course you can do that some way if you're only using 1 server app but if you have 10 servers hitting those 2 database servers I would suggest you using HAProxy that let allows you specify the weight of the servers. Normally in my HAProxy I configure that the master receives 1/5 of the SELECTs and the other 4/5 goes to the slave. Is the slave is down, it will continuously hit the master.

@taylorotwell If you read my previous comment you will see that having a database configuration array with writes and reads is not the way to go. If someone is using database replication, he should be using a load balancer. It will make our lives easier. How you will know which server to hit? You can't add weight to your servers, the only thing you can do is a array_rand() from the config file. Or most important than that: how you will handle when a server is down or unresponsive?

Also with HAProxy you can know if your slave has been lagged and pull it out from the farm until the slave lag is 0. You can't do that in PHP.

[lchski]

Phil: I was suggesting we offer a preference/weighting property to allow
for a semblance of load balancing within the app itself.

Thanks for the feedback, Isern. I think it's still important to allow the
option for different read/write databases within the app itself, though
including load balancing like I suggested may not be the most useful.

[philsturgeon]

To everyone: I think adding "dumb" load balancing to the application with array_rand() is fine. Folks can just replicate a server and have the load spread (yay) but when they want to get complicated and fancy they'll need to put a little more work in. Weighting, servers being down, etc are all outside of the scope of this initial feature suggestion.

CodeIgniter added a "failover" feature in 3.0-dev, maybe in this instance if the server cannot connect to a read server it tries another server in the read array until it does? If it tries to write and cannot write it will try another write server? If none then throw a massive error, because FUCK ALL MY SERVERS ARE DOWN!

That would be handy "non-Hadoop" functionality, and it can log an error so folks could even be alerted about it depending on how their logging is configured.

This is all about getting the most useful functionality out of the box without forcing a PHP developer to be a sysadmin too. It's not going to replace load balancers in all instances, but it can easily replace them in many.

[lchski]

Fair enough. Thanks for clearing up/justifying that direction, Phil. 👍

[andrew13]

👍 I really like the direction of this ticket. Keep it simple, if there's more complex functionality desired, let that developer tackle it. I feel like this approach will be acceptable for the vast majority.

[tariquesani]

Would be happy to see the read write separation but I agree with @ipalaus about using HAProxy for load balancing is the way to go...

[philsturgeon]

Not for the average Laravel developer it isn't. Remember we're trying to keep a low barrier to entry, and offering some basic load balancing out of the box is awesome.

By the time it's not good enough they'll know how to do better (or will HAVE to learn), but being able to let any joe bloggs set up basic load balancing with an array cannot be sniffed at, even if HAProxy is shiny and awesome.

Sent from my iPhone

On Jul 19, 2013, at 12:39 AM, "Dr. Tarique Sani" notifications@github.com wrote:

Would be happy to see the read write separation but I agree with @ipalaus about using HAProxy for load balancing is the way to go...

—
Reply to this email directly or view it on GitHub.

[andrew13]

@philsturgeon exactly

[andrewmclagan]

We currently have two projects where we would implement this as soon as it was pushed to master.

[ctadlock]

+1

[mohangk]

+1

[Kindari]

Seriously guys, quit with the +1, it really helps nothing with getting this into the code base. Instead you could provide alternate implementations, ask questions about something that might not be very clear, provide a use case or reason why you would want to use this over something like haproxy. Actually help contribute to the discussion if we have any hope of this actually being implemented.

[KennedyTedesco]

👍 I use Amazon RDS (with MySQL) and that implementation would be amazing.

[silvervest]

I've written a basic implementation supporting this, which you can find in this commit in my forked repo: https://github.com/silvervest/framework/commit/b04c9ec8d23432bbcbc5fd6452d4acfb6c52b206
It still needs work, and has some undefined behavior (like when using different drivers for read/write)

Anyway, since this is my first foray into the Laravel framework, I'd like to get some feedback from @taylorotwell / @philsturgeon et al as to whether this is suitable or a terrible implementation.

This adds support for read/write splitting by adding a configuration setting like so:

'readwrite' => [
    'r' => 'slaves',
    'w' => 'master'
],

This will use the 'slaves' connection for reads, and the 'master' connection for writes.

This also adds the basic support for load balancing connections, implementing the configuration suggested by @philsturgeon.

Any feedback is welcome!

[taylorotwell]

Done.

[philsturgeon]

[ipalaus]

Thanks Taylor! :)

[KennedyTedesco]

Amazing. Thank you!

[ivansjg]

Sorry, what is the merge which add this feature?

[crynobone]

@ivansjg a3b0e87

[ivansjg]

Thanks

[mandersondesign]

Is there any documentation on this yet? Not sure exactly how to use this yet, but would love it!

[taylorotwell]

Not right now… I’m working on it this week...

On December 4, 2013 at 1:35:28 PM, Michael Anderson (notifications@github.com) wrote:

Is there any documentation on this yet? Not sure exactly how to use this yet, but would love it!

—
Reply to this email directly or view it on GitHub.

[mandersondesign]

@taylorotwell thanks very much for the update! Looking forward to getting my models back to static land. I will see what I can get to work via checking the source for now.

[sporchia]

Looking for a way to implement this. not sure I 100% follow the commit and how to enforce it in Eloquent?

[ipalaus]

@sporchia just configure your read/write in database.php: http://laravel.com/docs/database#read-write-connections