Read queries to DB slave, write queries go master DB

Questions, tips and tricks and techniques for scripting in Servoy

Read queries to DB slave, write queries go master DB

Postby jcarlos » Wed Dec 21, 2011 8:54 am

I have this Master-Slave replication configuration. The master is the database server of the solution. The slave is the database server of a website in PHP. All of the Website's read queries go to the slave.

I’d like to have the Servoy solution’s read queries go to the slave too. I think that to do that I would have to change the database server at run time every time a find is triggered. Has anybody else done this successfully? Any other approach? Any method sample?

Best,
JC
jcarlos
 
Posts: 531
Joined: Thu May 04, 2006 8:55 pm
Location: Palo Alto, California USA

Re: Read queries to DB slave, write queries go master DB

Postby ROCLASI » Wed Dec 21, 2011 11:25 am

Hi Juan Carlos,

I have not seen this setup with Servoy yet. I think you might have to use some middleware to make this work. Perhaps Tungsten does this, I dunno.
But why split the reads and writes ? Do you have such a load on the database server that you need this? Perhaps there is a better solution for this that you don't need to split up the reads and writes.
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 4833
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Read queries to DB slave, write queries go master DB

Postby jcarlos » Wed Dec 21, 2011 10:10 pm

ROCLASI wrote:Hi Juan Carlos,

I have not seen this setup with Servoy yet. I think you might have to use some middleware to make this work. Perhaps Tungsten does this, I dunno.


Thanks for this tip. Could be a useful feature to be able to change database server on the fly? What do you think?

ROCLASI wrote:But why split the reads and writes ? Do you have such a load on the database server that you need this? Perhaps there is a better solution for this that you don't need to split up the reads and writes.


I use MySQL InnoDB, but the Full-text indexes can be used only with MyISAM tables. I'm thinking of having the slave as a MyISAM database, so that I could easily direct read queries to this suitable Full-text searchable database. I'll do this if the setup is easy. My other alternative is SmartDoc, although it's more work than just using MyISAM. Also, I cannot used MyISAM as the main/CRUD back-end database because it is not ACID.

Thanks. JC
jcarlos
 
Posts: 531
Joined: Thu May 04, 2006 8:55 pm
Location: Palo Alto, California USA

Re: Read queries to DB slave, write queries go master DB

Postby ryanparrish » Tue Dec 27, 2011 6:20 pm

jcarlos wrote:
I use MySQL InnoDB, but the Full-text indexes can be used only with MyISAM tables. I'm thinking of having the slave as a MyISAM database, so that I could easily direct read queries to this suitable Full-text searchable database. I'll do this if the setup is easy. My other alternative is SmartDoc, although it's more work than just using MyISAM. Also, I cannot used MyISAM as the main/CRUD back-end database because it is not ACID.

Thanks. JC


Not really knowing all your requirements, but if Full-text indexes are your main goal why not just continue with a Master InnoDB /Slave MyISAM and setup a separate server connection in servoy for the queries that require Full-text? Do the search on the MyISAM table, then go back to the InnoDB with a .loadRecords([pk]) type of thing, surely not *all* your searches need Full-text? See what I have below. Automatic read/write splitting can introduce a whole world of headaches, replication latency being a huge one when a future write action depends on the results of a previous read.

(just writing this off the top of my head, so there may be syntax errors)
Code: Select all
slave_foundset = databaseManager.getFoundSet("slave-conn", "a_table");
master_foundset = databaeManager.getFoundset("master-conn", "a_table");
if (slave_foundset.find())
    slave_foundset.some_col = "something to search for"
    if (slave_foundset.search() > 0) {
        var pks = databaseManager.getFoundSetDataProviderAsArray(slave_foundset, "table_pk")
        master_foundset.loadRecords(pks);
    }
}
ryanparrish
 
Posts: 160
Joined: Thu May 17, 2007 7:49 pm
Location: Miami, FL


Return to Methods

Who is online

Users browsing this forum: No registered users and 1 guest