Master-slave

[ngocdaothanh]

Hi,

I would like to ask how to run master-slave architecture with Squeryl.

Thanks

[Maxime Lévesque]

 By Master-Slave do you mean a setup where all writes are serviced by a database
instance while reads are routed to a replication (or cluster of replications) of it ?

[ngocdaothanh]

Yes. I don't know if it must be done at the application layer, Squeryl
layer, JDBC driver layer, or something else. Is there a JDBC driver
that split master and slave traffic automatically?

On Aug 3, 12:46 am, Maxime Lévesque <maxime.leves...@gmail.com> wrote:
>  By Master-Slave do you mean a setup where all writes are serviced by a
> database
> instance while reads are routed to a replication (or cluster of
> replications) of it ?
>

[ngocdaothanh]

For MySQL, I found this link:
http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-replication-connection.html

[Maxime Lévesque]

For the sake of brainstorming, if it were to be implemented in Squeryl, that's
how I would do it :

1) By adding this field to Session :

  val readSlaveConnectionFactory: Option[()=>Connection] = None

and these methods :

 def doesSlaveReading = readSlaveConnectionFactory != None
 def createSlaveReadConnection = {
    // save the connection somewhere to cleanup upon close....
    readSlaveConnectionFactory.get.apply
  }

2) And modifying the executeQuery method in org.squeryl.DatabaseAdaptor trait :

------------------------------------ before ------------------------------------
  def executeQuery(s: Session, sw: StatementWriter) = exec(s, sw) {
    val st = prepareStatement(s.connection, sw, s)
    (st.executeQuery, st)
  }


------------------------------------ after ------------------------------------
  def executeQuery(s: Session, sw: StatementWriter) = exec(s, sw) {

    val c =
      if(s.doesSlaveReading)
         s.createSlaveReadConnection
      else
         s.connection

    val st = prepareStatement(c, sw, s)
    (st.executeQuery, st)
  }
---------------------------------------------------------------------------------

 This way, update, inserts and deletes would user the connection tied to the Session,
while read queries would use the ones created by the readSlaveConnectionFactory when != None.

For clustering, it would be up to the readSlaveConnectionFactory closure to
implement whatever policy is needed.

As for where it should be implemented, I think I just convinced myself that it's
trivial enough to make it worth to implement it in Squeryl.

Doing it in the application code will make things verbose as hell, in the
JDBC layer sounds hackish or at best more complex than necessary when I start thinking about
how it would have to be implemened at this level


Cheers