Can we force read-only transactions to go to the slave?

[javaluva2]

This question may be more appropriate in the mysql forums (so I've asked it there too) but I wanted to see if anyone knew how to do it here.

I'm setting up replication with mysql and have it successfully using the ReplicationDriver & the @Transactional annotations.

I'm seeing it utilize both the master and the slave, but it's not only using the slave for reads (sometimes it reads from the master). This is only a problem because we'd like to use replication to enable a foreign site to query our web-app quicker (we'll set up a local slave & webserver so their queries are quick but writes are still going to the master, so they'll be slower)

Does anyone have any ideas or somewhere to point me to? Thanks!

[dr_pompeii]

Hello

I'm setting up replication with mysql and have it successfully using the ReplicationDriver & the @Transactional annotations.

Are you working with Local @Transactional control?, I mean, perhaps you are working with XA too?

I'm seeing it utilize both the master and the slave, but it's not only using the slave for reads (sometimes it reads from the master)

Do realize yourself about each operation is different, I think always both master/slave should be working in parallel (replication ), it depends about your own Mysql Configuration, if you want a complete replication or a type of loading balance

(we'll set up a local slave & webserver so their queries are quick but writes are still going to the master, so they'll be slower)

I am not an expert about replication, but I assume all the slaves depends of the Master and this always should do some part of the job to let know to the slaves what is happening

Could you be agree discuss your advance about code here perhaps?
Configuration for Master/Slave ReplicationDriver, How?

This question may be more appropriate in the mysql forums (so I've asked it there too)

BTW, have you received a reply from MySQL?, coud you share such post link?

[javaluva2]

Dr. Pompeii,
I'm not sure if I'm working with XA too? I just have @Transactional(readOnly=true) in each of my business logic (service layer) implementation classes (specific methods that have to go to the master have @Transactional(readOnly=false) ) and I also added the <tx:annotation-driven /> tag in the spring context file.
Since many of our queries are just getting data from the database, those are the queries we're hoping can always go to the slave. The master would not be involved at all unless we're creating, updating or deleting data.
I'm not sure what you mean by "Could you be agree discuss your advance about code here perhaps?"
I didn't recieve any (useful) replies yet on the MySQL forum. This is the link to that discussion: http://forums.mysql.com/read.php?26,...050#msg-427050

[dr_pompeii]

Hello

I'm not sure if I'm working with XA too? I just have @Transactional(readOnly=true) in each of my business logic (service layer) implementation classes (specific methods that have to go to the master have @Transactional(readOnly=false) ) and I also added the <tx:annotation-driven /> tag in the spring context file.

Seems not, I dont know if XA would be necessary since you are working with Replication

Since many of our queries are just getting data from the database, those are the queries we're hoping can always go to the slave.

I think this bold expected behaviour depends more about your MySQL configuration and not by Spring.

The master would not be involved at all unless we're creating, updating or deleting data.

I see

I'm not sure what you mean by "Could you be agree discuss your advance about code here perhaps?"

You wrote a reply in the other post, Am I correct?, therefore I thought your code configuration could help to the other user and get better results, I have a book of MySQL about replication and I am sure I would get perhaps the same experiences like you

Have you consider do a monitoring and debug control with and without Spring? to confirm that Spring affect your expected behaviour?

[javaluva2]

I think this bold expected behaviour depends more about your MySQL configuration and not by Spring.

That could be -- I'll look more into the mysql replication driver to see how it works.

You wrote a reply in the other post, Am I correct?, therefore I thought your code configuration could help to the other user and get better results, I have a book of MySQL about replication and I am sure I would get perhaps the same experiences like you

It's not easy for me to copy my code to the post because I'm working on a separate network that's not connected to the internet. If he\she still needs help, I should see the reply and I can help.

Have you consider do a monitoring and debug control with and without Spring? to confirm that Spring affect your expected behaviour?

I'm not strong enough in Spring I think to do this --- it's probably not a spring-specific thing, our app just uses the Spring MVC framework, and I think the set-up lies somewhere in the configuration of the datasource, sessionfactory or transaction manager.

Now I'm pretty sure something isn't set up correctly somewhere because we're getting extra connections opened that aren't being returned to the connection pool. (I'm testing with jMeter)