Issue Details (XML | Word | Printable)

Key: NUCRDBMS-438
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Unassigned
Reporter: Fernando Padilla
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

properly call Connection.setReadOnly when entering/leaving transactions

Created: 26/Jul/10 04:39 AM   Updated: 20/Oct/10 09:30 AM   Resolved: 06/Oct/10 02:35 PM
Component/s: Connection
Affects Version/s: None
Fix Version/s: 2.2.0.m2


 Description  « Hide
If you can the connection.setReadOnly appropriately, it gives the jdbc mysql driver a hint that can be used for very easy zero cost read/write splitting. Read the details below:

http://blogs.sun.com/cmani/entry/connecting_to_a_mysql_master

http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-replication-connection.html



If this read/write splitting excites you, here is another enhancement. Here is the source of inspiration:

http://www.facebook.com/note.php?note_id=23844338919

Allow us to pin a particular PersistenceManager to be in "WriteMode", thus pinning it against the master database for the remainder of the PM's life (not just during a transaction). This would allow the application to easily pin a particular user against the master db (for some short amount of time), to allow for replicant propagation; like facebook did, but within one datacenter... :)



Sort Order: Ascending order - Click to sort in descending order
Andy Jefferson added a comment - 06/Oct/10 02:35 PM
setReadOnly calls added at open of a connection.

Fernando Padilla added a comment - 06/Oct/10 04:21 PM
thank you for this fix, but it's not quite what I had in mind.

It looks like your change (r10581) calls connection.setReadOnly(true) only when the we have set the whole datastore as readonly through datanucleus.readOnlyDatastore property.

What I was talking about, is even for normal datastore operation.. I want DN to flip between setReadOnly(true) and setReadOnly(false) appropriately. For example:

1) open PM

2) execute query
 a) open connection (set read only true)
 b) execute query

3) execute query
 a) open connection (set read only true)
 b) execute query

4) begin transaction
 a) open connection (set read only false)
 b) use readOnlyFalse connection all you like
5) commit transaction
 b) flush/commit/close connection

6) execute query
 a) open connection (set read only true)
 b) execute query


The reason for this, is that the mysql driver will automagically connect to either the master or the replicants depending on setReadOnly: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-replication-connection.html

We haven't used this yet, but still it's a very interesting idea, and would love DN to support it, so we can explore pros/cons of it. Most likely anyone running without an L2 Cache could use this right away and get some nice benefits.

Andy Jefferson added a comment - 06/Oct/10 04:26 PM
If that's what you had in mind (which was not obvious from the description) then provide a patch :-P

Fernando Padilla added a comment - 06/Oct/10 04:39 PM
so sorry. :)

I thought the links would give an idea; but I guess I should have used the term "Read/Write split" or something along those lines to better convey what I was talking about.

As to the patch, I was hoping that you would have a better idea of where to place the setReadOnly instrumentation points. I could make some guesses, but I might not be 100% correct. Since the ConnectionManager/Factory and transaction code is a bit intertwined..

If you have any suggestions, that would be great. I'll eventually get to submit a patch then. :)

thank you

Andy Jefferson added a comment - 06/Oct/10 05:22 PM
The only place any connection stuff is touched is in ConnectionFactoryImpl, and in SQLController. SQLController knows if the statement for the connection is for a query (no write), or an update so is likely the best place for anything.