Dynamically switching database with select_db with a custom sharded Mysql.

[Eric S]

Hi everyone,

First some terminology to prevent confusion:

  Database - Mysql database server; currently I have two of them. 

  Catalog -    Also typically called databases, but in this situation it is the thing created by the database when you issue this command "create database foo";  Currently I have 256 of them spread evenly over the databases.

  Shard - Lives in my application code and allows me to know in which database the catalog is currently living, and helps me create connections to it.

My problem is how do I tell the sqlalchemy engines to switch to the correct catalog?

I only want to have one engine per database and share its connection pool.

My current solution is to use a before_execute event which changes the catalog.  

This works, but is not great because the catalog isn't passed in, it exists "globally", and 

given that my app is multithreaded now I have to use thread local storage just to make sure the correct value is used.

    current_catalog = SOME_CHANGING_VALUE_SAVED_IN_THREAD_LOCAL

    def before_execute(conn, clauseelement, multiparams, params):

        conn.connection.select_db(current_catalog)

I was trying to figure out adding my own event, similar to before_execute, but that would somehow be passed the catalog.  Is this possible?

I was also looking into using sqlalchemy sharding, but it seems the same problem exists there.

Eric

SQLAlchemy 0.7.8

python 2.7

mysql 5.5

[Michael Bayer]

On Oct 22, 2012, at 9:42 PM, Eric S wrote:

> Hi everyone,
>
> First some terminology to prevent confusion:
>
> Database - Mysql database server; currently I have two of them.
>
> Catalog - Also typically called databases, but in this situation it is the thing created by the database when you issue this command "create database foo"; Currently I have 256 of them spread evenly over the databases.
>
> Shard - Lives in my application code and allows me to know in which database the catalog is currently living, and helps me create connections to it.
>
> My problem is how do I tell the sqlalchemy engines to switch to the correct catalog?
> I only want to have one engine per database and share its connection pool.
>
> My current solution is to use a before_execute event which changes the catalog.
> This works, but is not great because the catalog isn't passed in, it exists "globally", and
> given that my app is multithreaded now I have to use thread local storage just to make sure the correct value is used.

This depends a lot on how you're using SQLA, if you are using the Session for example and want it to be set per-session, or similar. I was going to suggest the connection pool "on checkout" event but if before_execute() is working for you, that's fine too - what I would suggest is that you store along with the Connection what the last setting was, and don't actually emit a catalog change statement if you're already on it.

To associate the desired value with the Connection so that it's available to your before_execute() method in a non-global way, you should associate the desired value with the Connection itself. An easy enough way is to use an execution option:

conn = conn.execution_options(my_shard=some_shard)

your event can then read it from the _execution_options dictionary:

shard = conn._execution_options['my_shard']

if you want to do this with the ORM Query object, execution_options() is available:

query = query.execution_options(my_shard=some_shard)

if you're looking to do this at the Session level, you can make a new Session at the start of an operation, bind it with a connection:

sess = Session(bind=conn.execution_options(my_shard=some_shard))

you can stuff it in the .info dictionary of the Session's existing connection:

sess.connection().info['my_shard'] = some_shard

lots of ways. depends on usage.

[Michael Bayer]

On Oct 22, 2012, at 10:01 PM, Michael Bayer wrote:

>
> On Oct 22, 2012, at 9:42 PM, Eric S wrote:
>
>> Hi everyone,
>>
>> First some terminology to prevent confusion:
>>
>> Database - Mysql database server; currently I have two of them.
>>
>> Catalog - Also typically called databases, but in this situation it is the thing created by the database when you issue this command "create database foo"; Currently I have 256 of them spread evenly over the databases.
>>
>> Shard - Lives in my application code and allows me to know in which database the catalog is currently living, and helps me create connections to it.
>>
>> My problem is how do I tell the sqlalchemy engines to switch to the correct catalog?
>> I only want to have one engine per database and share its connection pool.
>>
>> My current solution is to use a before_execute event which changes the catalog.
>> This works, but is not great because the catalog isn't passed in, it exists "globally", and
>> given that my app is multithreaded now I have to use thread local storage just to make sure the correct value is used.
>
>

> you can stuff it in the .info dictionary of the Session's existing connection:
>
> sess.connection().info['my_shard'] = some_shard
>
> lots of ways. depends on usage.

let me just continue, stressing that the idea of connection.execution_options() means you actually get a new Connection object back, which is linked to the original and uses the same DBAPI connection. The ORM's flush procedure can work with this to use a distinct Connection for each individual object instance it flushes.

The approach taken in horizontal_shard.py offers some clues how to do this - in that system, a special hook called "connection_callable" is set on the Session, which is significant to the ORM unit of work process when it flushes objects. This callable is invoked for every mapper/object being flushed, and has the job of returning a Connection that will be used to emit INSERT/UPDATE/DELETE for that object specifically. You can hook into this callable and return new Connection objects using execution_options(my_shard=some_shard) to produce the new Connection object. I'd probably try storing them in a dictionary-per-shard id so that you aren't creating one Connection object per object being flushed, but that's an optimization - you'd have to make sure they get closed out when the lead connection does.

It might also be possible to use horizontal_shard's approach more fully, though poking around a bit it might give you some resistance. The simplest way would be to provide a new get_bind() method, but that method usually intends to return an Engine object - maybe building a "mock" Engine that will return a Connection with a given shard id applied as an execution_option() would work.

[Eric S]

Firstly, thank you so much for the quick and amazing response, that almost never happens for me.

I am indeed using a session object per request so this approach looks most appealing:

          sess.connection().info['my_shard'] = some_shard

I'm new to sqlalchemy and as such had no idea that you could add arbitrary values to the execution_options/info fields.  

Where in the docs does it say this?  I only found things such as "autocommit" and "stream_results" shown.

Also, I guess I'll have to do some research on horizontal_shard, but that looks like it might provide the most sane solution as well.

thanks again,

eric

[Michael Bayer]

On Oct 22, 2012, at 11:20 PM, Eric S wrote:

Firstly, thank you so much for the quick and amazing response, that almost never happens for me.

I am indeed using a session object per request so this approach looks most appealing:

          sess.connection().info['my_shard'] = some_shard

I'm new to sqlalchemy and as such had no idea that you could add arbitrary values to the execution_options/info fields.  

Where in the docs does it say this?  I only found things such as "autocommit" and "stream_results" shown.

Also, I guess I'll have to do some research on horizontal_shard, but that looks like it might provide the most sane solution as well.

well for a lot of this we have to rely on API documentation, and possibly API docs are looking better in 0.8 than in 0.7.

here's execution options:

http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.Connection.execution_options

but yes, those docs don't mention that you can really put whatever you want in there and pull it from _execution_options, that hasn't been documented as public API.   It just seemed like a good solution to your issue as that API is pretty stable in any case.  I should probably add a fully public attribute to read the current execution options.

and here's info:

http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.Connection.info

which yeah, doesn't say much.    More detail in this version regarding usage:

http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.Connection.info

thanks again,

eric

On Monday, October 22, 2012 7:21:26 PM UTC-7, Michael Bayer wrote:

On Oct 22, 2012, at 10:01 PM, Michael Bayer wrote:

>
> On Oct 22, 2012, at 9:42 PM, Eric S wrote:
>
>> Hi everyone,
>>
>> First some terminology to prevent confusion:
>>
>>  Database - Mysql database server; currently I have two of them.
>>
>>  Catalog -    Also typically called databases, but in this situation it is the thing created by the database when you issue this command "create database foo";  Currently I have 256 of them spread evenly over the databases.
>>
>>  Shard - Lives in my application code and allows me to know in which database the catalog is currently living, and helps me create connections to it.
>>
>> My problem is how do I tell the sqlalchemy engines to switch to the correct catalog?
>> I only want to have one engine per database and share its connection pool.
>>
>> My current solution is to use a before_execute event which changes the catalog.  
>> This works, but is not great because the catalog isn't passed in, it exists "globally", and
>> given that my app is multithreaded now I have to use thread local storage just to make sure the correct value is used.
>
>
> you can stuff it in the .info dictionary of the Session's existing connection:
>
>         sess.connection().info['my_shard'] = some_shard
>
> lots of ways.  depends on usage.

let me just continue, stressing that the idea of connection.execution_options() means you actually get a new Connection object back, which is linked to the original and uses the same DBAPI connection.   The ORM's flush procedure can work with this to use a distinct Connection for each individual object instance it flushes.

The approach taken in horizontal_shard.py offers some clues how to do this - in that system, a special hook called "connection_callable" is set on the Session, which is significant to the ORM unit of work process when it flushes objects.   This callable is invoked for every mapper/object being flushed, and has the job of returning a Connection that will be used to emit INSERT/UPDATE/DELETE for that object specifically.   You can hook into this callable and return new Connection objects using execution_options(my_shard=some_shard) to produce the new Connection object.  I'd probably try storing them in a dictionary-per-shard id so that you aren't creating one Connection object per object being flushed, but that's an optimization - you'd have to make sure they get closed out when the lead connection does.

It might also be possible to use horizontal_shard's approach more fully, though poking around a bit it might give you some resistance.    The simplest way would be to provide a new get_bind() method, but that method usually intends to return an Engine object - maybe building a "mock" Engine that will return a Connection with a given shard id applied as an execution_option() would work.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/d-fca69a5pMJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.