[Twisted-Python] adbapi MySQL read/write splitting
Is anyone doing read/write splitting with adbapi? Looking for advice if anyone has tackled this before. Thank you in advance.
-J
Sending writes (e.g. INSERTs and UPDATEs) to a master MySQL server and reads (SELECTs) to a slave.
-J
On Tue, Aug 31, 2010 at 8:32 PM, Itamar Turner-Trauring itamar@itamarst.org wrote:
On Tue, 2010-08-31 at 15:17 -0600, Jason J. W. Williams wrote:
Is anyone doing read/write splitting with adbapi? Looking for advice if anyone has tackled this before. Thank you in advance.
What is "read/write splitting"?
Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
That's one way of handling it. Another way is to wrap the library so it does the splitting automatically. The advantage to the latter is not making mistakes where you accidentally use the READ connection for a write. For non-async Python there are some tools like SQL Relay which will do this for you, but you have to setup SQL Relay servers and use the SQL Relay dbapi driver. MySQL Proxy will also do it, but it's not very stable.
I was somewhat hoping someone had already written a wrapper for doing the splitting. Which brings me to a related question...if I were to write a wrapper to do the splitting, would wrapping MySQLdb be sufficient? Or would wrapping adbapi be the better path?
-J
On Tue, Aug 31, 2010 at 8:40 PM, Itamar Turner-Trauring itamar@itamarst.org wrote:
On Tue, 2010-08-31 at 20:36 -0600, Jason J. W. Williams wrote:
Sending writes (e.g. INSERTs and UPDATEs) to a master MySQL server and reads (SELECTs) to a slave.
So... two adbapi.ConnectionPool instances, one for the server, one for the slave?
Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Used to wrap MySQLdb for something like except that I did it for sharding. It worked well on distributing load across multiple database servers. I think the guys from my former company updated the wrapper to use adbapi.
- Alvin
On Wed, Sep 1, 2010 at 10:54 AM, Jason J. W. Williams < jasonjwwilliams@gmail.com> wrote:
That's one way of handling it. Another way is to wrap the library so it does the splitting automatically. The advantage to the latter is not making mistakes where you accidentally use the READ connection for a write. For non-async Python there are some tools like SQL Relay which will do this for you, but you have to setup SQL Relay servers and use the SQL Relay dbapi driver. MySQL Proxy will also do it, but it's not very stable.
I was somewhat hoping someone had already written a wrapper for doing the splitting. Which brings me to a related question...if I were to write a wrapper to do the splitting, would wrapping MySQLdb be sufficient? Or would wrapping adbapi be the better path?
-J
On Tue, Aug 31, 2010 at 8:40 PM, Itamar Turner-Trauring itamar@itamarst.org wrote:
On Tue, 2010-08-31 at 20:36 -0600, Jason J. W. Williams wrote:
Sending writes (e.g. INSERTs and UPDATEs) to a master MySQL server and reads (SELECTs) to a slave.
So... two adbapi.ConnectionPool instances, one for the server, one for the slave?
Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Hey Alvin,
Thank you for the info. Any idea what kind of changes they had to make to the MySQLdb wrapper for adbapi to like it?
-J
On Tue, Aug 31, 2010 at 9:13 PM, Alvin Delagon adelagon@gmail.com wrote:
Used to wrap MySQLdb for something like except that I did it for sharding. It worked well on distributing load across multiple database servers. I think the guys from my former company updated the wrapper to use adbapi.
- Alvin
On Wed, Sep 1, 2010 at 10:54 AM, Jason J. W. Williams jasonjwwilliams@gmail.com wrote:
That's one way of handling it. Another way is to wrap the library so it does the splitting automatically. The advantage to the latter is not making mistakes where you accidentally use the READ connection for a write. For non-async Python there are some tools like SQL Relay which will do this for you, but you have to setup SQL Relay servers and use the SQL Relay dbapi driver. MySQL Proxy will also do it, but it's not very stable.
I was somewhat hoping someone had already written a wrapper for doing the splitting. Which brings me to a related question...if I were to write a wrapper to do the splitting, would wrapping MySQLdb be sufficient? Or would wrapping adbapi be the better path?
-J
On Tue, Aug 31, 2010 at 8:40 PM, Itamar Turner-Trauring itamar@itamarst.org wrote:
On Tue, 2010-08-31 at 20:36 -0600, Jason J. W. Williams wrote:
Sending writes (e.g. INSERTs and UPDATEs) to a master MySQL server and reads (SELECTs) to a slave.
So... two adbapi.ConnectionPool instances, one for the server, one for the slave?
Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
-- http://www.alvinatorsplayground.blogspot.com/
Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
On Tue, 2010-08-31 at 20:54 -0600, Jason J. W. Williams wrote:
That's one way of handling it. Another way is to wrap the library so it does the splitting automatically. The advantage to the latter is not making mistakes where you accidentally use the READ connection for a write.
That sounds like a bad idea. You want to send reads to the write server if you're doing so as part of a transaction that does writes, otherwise in some cases you'll end up with wrong results.
For example, consider a transaction that inserts a row into a table, and then does a select to count the number of rows in that table. If you send the latter to a replicated read-only server, the result will be incorrect, since it will not include the insert (which hasn't been committed yet).
So, to repeat: you should only use the read server for operations that are read-only. Which means some reads will go to the write server.
That is correct. In our case we've audited our code for transactions and all of ours are single statement trans.
It's pretty typical for read/write splitting to forbid multiple-statement transactions as a cost of doing it.
-J
Sent via iPhone
Is your e-mail Premiere?
On Sep 1, 2010, at 7:15, Itamar Turner-Trauring itamar@itamarst.org wrote:
On Tue, 2010-08-31 at 20:54 -0600, Jason J. W. Williams wrote:
That's one way of handling it. Another way is to wrap the library so it does the splitting automatically. The advantage to the latter is not making mistakes where you accidentally use the READ connection for a write.
That sounds like a bad idea. You want to send reads to the write server if you're doing so as part of a transaction that does writes, otherwise in some cases you'll end up with wrong results.
For example, consider a transaction that inserts a row into a table, and then does a select to count the number of rows in that table. If you send the latter to a replicated read-only server, the result will be incorrect, since it will not include the insert (which hasn't been committed yet).
So, to repeat: you should only use the read server for operations that are read-only. Which means some reads will go to the write server.
Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
participants (3)
-
Alvin Delagon -
Itamar Turner-Trauring -
Jason J. W. Williams