Login/Register
Rails: How to split write/read query across master/slave database
Asked Answered
Solved mysqlruby-on-railsdatabase-performancemaster-slaveoctopus
A

2

11

My website has a very heavy read traffic. A lot heavier than write traffic.

To improve the performance of my website I have thought of going with master/slave database configuration.

The octupus gem seems to provide what I want, but since my app is huge I can't go though a millions of source code line to change the query distribution(sending read query to slave server and write query to master server).

MySQL Proxy seems to be a great way to resolve this issue but since it is in alpha version I don't want to use it.

So my question is what is the best way to split read/write queries across master/slave server?

Is it possible to split read/write query without using any gems in rails?

Against answered 4/4, 2015 at 10:46 Comment(4)
Can we assume your DB is in fact MySQL? – Trotman
@PhilipHallstrom --- Yes my DB is MySQL – Against
NOTE: Rails 6+ will support this natively. – Basic
NOTE: Rails now natively supports Replica and Sharding guides.rubyonrails.org/… – Thomajan
O
5

With octopus gem, you don't have to change much of your code to make write queries go to the master db server and read queries go to the slaves.

It's a simple configuration file, as stated here.

I've tried this in the past and It worked quite well. The only problem for me is that when the slave is down, it doesn't "redirect" the queries to the master db server, as I asked here.

But, if you want to configure each individual query destination, it will take some work.

I would suggest you to start mapping your more frequent queries and those that are taking longer to respond. Knowing those queries, you can optimize them individually. This may already solve part of the problem.

If you still need master-slave replication after that, use the octopus gem to change the behaviour only those few complicated queries.

Oxymoron answered 14/4, 2015 at 13:33 Comment(1)
Is there any tool in the market such as mysql proxy which will automatically split the read/write query's without doing any configurations(like in octopus gem). – Against
K
11

I spiked out 2 gems, Octopus and Makara. I have written a blog comparing these 2 gems : https://ypoonawala.wordpress.com/2015/11/15/octopus-vs-makara-read-write-adapters-for-activerecord-2/

In my opinion, Makara works well and makes up for the issues with Octopus.

Kingsly answered 22/11, 2015 at 7:51 Comment(0)
O
5

With octopus gem, you don't have to change much of your code to make write queries go to the master db server and read queries go to the slaves.

It's a simple configuration file, as stated here.

I've tried this in the past and It worked quite well. The only problem for me is that when the slave is down, it doesn't "redirect" the queries to the master db server, as I asked here.

But, if you want to configure each individual query destination, it will take some work.

I would suggest you to start mapping your more frequent queries and those that are taking longer to respond. Knowing those queries, you can optimize them individually. This may already solve part of the problem.

If you still need master-slave replication after that, use the octopus gem to change the behaviour only those few complicated queries.

Oxymoron answered 14/4, 2015 at 13:33 Comment(1)
Is there any tool in the market such as mysql proxy which will automatically split the read/write query's without doing any configurations(like in octopus gem). – Against

© 2022 - 2024 — McMap. All rights reserved.