Read queries to DB slave, write queries go master DB

[jcarlos]

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

[ROCLASI]

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.

[jcarlos]

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?

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

[ryanparrish]

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);
    }
}