Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Tue, Nov 8, 2011 at 6:59 AM, Michael Bayer mike...@zzzcomputing.com wrote: OK, here's my attempt. Takes place pretty much at the pool level and is not too intrusive, also leaves in place all the existing reconnect stuff which should just work as is, not to mention leaves creator() in place which also has some exception handling. I'm hoping you can test it out and grok the general idea in case it needs adjustment, and if we can write full tests it can be part of the distribution too, maybe as an ext. Oh wow, thanks a lot Michael. That looks much cleaner. I'll integrate it in our codebase and see if I can complete the tests. We will bench it in our infra to see how it goes with the real Sync app/data. For the reconnect stuff, I am not sure to understand how things currently work: in case of a connection error in MySQL (2013 and the likes) the engine.execute() method will throw the error and unless I have done things wrong, the error bubbles up and the pool does not attempt to recreate a new connection and run the query again. The small block I have here: https://hg.mozilla.org/services/server-core/file/2.6.1/services/util.py#l621 Does exactly this. Cheers Tarek -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Nov 8, 2011, at 2:05 AM, Tarek Ziadé wrote: On Tue, Nov 8, 2011 at 6:59 AM, Michael Bayer mike...@zzzcomputing.com wrote: OK, here's my attempt.Takes place pretty much at the pool level and is not too intrusive, also leaves in place all the existing reconnect stuff which should just work as is, not to mention leaves creator() in place which also has some exception handling. I'm hoping you can test it out and grok the general idea in case it needs adjustment, and if we can write full tests it can be part of the distribution too, maybe as an ext. Oh wow, thanks a lot Michael. That looks much cleaner. I'll integrate it in our codebase and see if I can complete the tests. We will bench it in our infra to see how it goes with the real Sync app/data. For the reconnect stuff, I am not sure to understand how things currently work: in case of a connection error in MySQL (2013 and the likes) the engine.execute() method will throw the error and unless I have done things wrong, the error bubbles up and the pool does not attempt to recreate a new connection and run the query again. OK so statement executions occur in engine/base.py, usually inside of _execute_context(): http://hg.sqlalchemy.org/sqlalchemy/file/ff6c45b45e60/lib/sqlalchemy/engine/base.py#l1583 Exceptions are all caught here, and passed to _handle_dbapi_exception: http://hg.sqlalchemy.org/sqlalchemy/file/ff6c45b45e60/lib/sqlalchemy/engine/base.py#l1634 http://hg.sqlalchemy.org/sqlalchemy/file/ff6c45b45e60/lib/sqlalchemy/engine/base.py#l1727 the exception itself is run through the dialect's is_disconnect() method. if this returns True, the entire pool is disposed: http://hg.sqlalchemy.org/sqlalchemy/file/ff6c45b45e60/lib/sqlalchemy/engine/base.py#l1754 and the exception, re-raised, will have the connection_invalidated flag set: http://hg.sqlalchemy.org/sqlalchemy/file/ff6c45b45e60/lib/sqlalchemy/exc.py#l219 the MySQL dialects all have a DBAPI-specific way to get at the error message, and examine whether or not its a disconnect. The 2013 code is then checked here: http://hg.sqlalchemy.org/sqlalchemy/file/ff6c45b45e60/lib/sqlalchemy/dialects/mysql/base.py#l1826 There was a bug regarding this on the MySQL side that was fixed in 0.6.3 in case you're on a very old version. There's no feasible way a database tool could transparently try the query again - a new connection means the transaction has been discarded. It would also require silently squashing very severe errors which can't be assumed to be recoverable.But your own code can check the connection_invalidated flag on the raised exception. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
Here's my v3, with a mechanism to refresh the shared pool when an engine rebuilds it via dispose http://tarek.pastebin.mozilla.org/1376367 It also have a few thread locks to try to make it thread-safe. I've written some tests with hundreds of threads and it seems to work fine, but I don't really know if there are other places where I should do something. On Fri, Nov 4, 2011 at 4:45 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 4, 2011, at 3:41 AM, Tarek Ziadé wrote: On Fri, Nov 4, 2011 at 10:33 AM, Tarek Ziadé ziade.ta...@gmail.com wrote: ... Will try and come back ;) Hello, I am back \o/ Here's my v2: http://tarek.pastebin.mozilla.org/1373520 This time I have created a custom Strategy and Engine classes, and the get_engine() function takes care of instanciating a pool for the server if needed, and an engine instance per sqlurl, using that pool/ I have used Engine.contextual_connect for the schema switching, and Engine.execute has our custom execution things. I guess this is much cleaner since anyone can use create_engine() with my new strategy. The only assumption I am making is that there's a shared pool on the same host. Does this look better ? Thanks for your help so far OK this is clearer to me. So you're really trying to get it so that any number of create_engine() calls all use the same pool based on hostname, OK. If i have time today I might want to try paring this down a lot more. Also not sure why you need the disconnect check stuff in execute() , SQLA has that stuff built in. But it does raise an important point that the mechanism by which we dispose the pool when a disconnect is detected probably should be different here, since the disposal is per-engine, just gives itself a new pool. The other engines will still point to the old, disposed pool, probably causing some kind of pileup. Cheers Tarek -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
I've got some thoughts on this but time is scant today to come up with what I'm thinking. Basically doing this totally with the pool itself. I'll try to get some time to work on it On Nov 7, 2011, at 3:32 AM, Tarek Ziadé wrote: Here's my v3, with a mechanism to refresh the shared pool when an engine rebuilds it via dispose http://tarek.pastebin.mozilla.org/1376367 It also have a few thread locks to try to make it thread-safe. I've written some tests with hundreds of threads and it seems to work fine, but I don't really know if there are other places where I should do something. On Fri, Nov 4, 2011 at 4:45 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 4, 2011, at 3:41 AM, Tarek Ziadé wrote: On Fri, Nov 4, 2011 at 10:33 AM, Tarek Ziadé ziade.ta...@gmail.com wrote: ... Will try and come back ;) Hello, I am back \o/ Here's my v2: http://tarek.pastebin.mozilla.org/1373520 This time I have created a custom Strategy and Engine classes, and the get_engine() function takes care of instanciating a pool for the server if needed, and an engine instance per sqlurl, using that pool/ I have used Engine.contextual_connect for the schema switching, and Engine.execute has our custom execution things. I guess this is much cleaner since anyone can use create_engine() with my new strategy. The only assumption I am making is that there's a shared pool on the same host. Does this look better ? Thanks for your help so far OK this is clearer to me. So you're really trying to get it so that any number of create_engine() calls all use the same pool based on hostname, OK. If i have time today I might want to try paring this down a lot more. Also not sure why you need the disconnect check stuff in execute() , SQLA has that stuff built in.But it does raise an important point that the mechanism by which we dispose the pool when a disconnect is detected probably should be different here, since the disposal is per-engine, just gives itself a new pool. The other engines will still point to the old, disposed pool, probably causing some kind of pileup. Cheers Tarek -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
OK, here's my attempt.Takes place pretty much at the pool level and is not
too intrusive, also leaves in place all the existing reconnect stuff which
should just work as is, not to mention leaves creator() in place which also
has some exception handling. I'm hoping you can test it out and grok the
general idea in case it needs adjustment, and if we can write full tests it can
be part of the distribution too, maybe as an ext.
On Nov 7, 2011, at 3:32 AM, Tarek Ziadé wrote:
Here's my v3, with a mechanism to refresh the shared pool when an
engine rebuilds it via dispose
http://tarek.pastebin.mozilla.org/1376367
It also have a few thread locks to try to make it thread-safe.
I've written some tests with hundreds of threads and it seems to work
fine, but I don't really know if there are other places where I should
do something.
On Fri, Nov 4, 2011 at 4:45 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Nov 4, 2011, at 3:41 AM, Tarek Ziadé wrote:
On Fri, Nov 4, 2011 at 10:33 AM, Tarek Ziadé ziade.ta...@gmail.com wrote:
...
Will try and come back ;)
Hello, I am back \o/
Here's my v2: http://tarek.pastebin.mozilla.org/1373520
This time I have created a custom Strategy and Engine classes, and the
get_engine() function takes care of instanciating a pool for the
server if needed, and an engine instance per sqlurl, using that pool/
I have used Engine.contextual_connect for the schema switching, and
Engine.execute has our custom execution things.
I guess this is much cleaner since anyone can use create_engine() with
my new strategy.
The only assumption I am making is that there's a shared pool on the same
host.
Does this look better ?
Thanks for your help so far
OK this is clearer to me. So you're really trying to get it so that any
number of create_engine() calls all use the same pool based on hostname, OK.
If i have time today I might want to try paring this down a lot more.
Also not sure why you need the disconnect check stuff in execute() , SQLA
has that stuff built in.But it does raise an important point that the
mechanism by which we dispose the pool when a disconnect is detected
probably should be different here, since the disposal is per-engine, just
gives itself a new pool. The other engines will still point to the old,
disposed pool, probably causing some kind of pileup.
Cheers
Tarek
--
Tarek Ziadé | http://ziade.org
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
Tarek Ziadé | http://ziade.org
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
from sqlalchemy import event, pool, create_engine
from sqlalchemy.engine import url
class ProxyRecord(pool._ConnectionRecord):
def __init__(self, pool):
super(ProxyRecord, self).__init__(pool)
self.proxied = self.connection._connection_record
def close(self):
self.proxied.close()
def invalidate(self, e=None):
self.proxied.invalidate(e=e)
class ProxyPool(pool.Pool):
def __init__(self, proxying):
self.proxying = proxying
pool.Pool.__init__(self, self.__creator)
def __creator(self):
return self.proxying.connect()
def status(self):
return ProxyPool(%s) % self.proxying.status()
def _do_return_conn(self, conn):
self.proxying._do_return_conn(conn.proxied)
def _do_get(self):
return ProxyRecord(self)
def dispose(self):
self.proxying.dispose()
def recreate(self):
return self
def singleton_engine(key_from_url, new_engine):
_reg = {}
def create_engine_singleton(name_or_url, **kw):
u = url.make_url(name_or_url)
key = key_from_url(u)
if key in _reg:
eng = _reg[key]
else:
eng = _reg[key] = create_engine(u, **kw)
kw['pool'] = pool = ProxyPool(eng.pool)
eng = create_engine(u, **kw)
new_engine(eng)
return eng
return create_engine_singleton
if __name__ ==
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Thu, Nov 3, 2011 at 11:31 PM, Michael Bayer mike...@zzzcomputing.com wrote: ... I should look at this more closely, took a brief glance. One thought I had was why not do the switch the schema thing within Engine.connect(), at least there you know which engine you're dealing with. Ok I'll try this. I was not sure it was the proper place to do the initialization of the schema Though I don't really understand how this is organized anyway, the query() function for example seems a little weird, wouldn't you want this to be transparent at the Engine level ? Yeah sure -- that's the optimal goal. I will try to refactor everything as a custom Engine I guess, that handles/initialize its own set of pools, There should be a simple way to make two engines talk to one pool and switch the schema based on each engine. Maybe some context to be provided to the checkout event- possibly a small API change. Will try and come back ;) Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Fri, Nov 4, 2011 at 10:33 AM, Tarek Ziadé ziade.ta...@gmail.com wrote: ... Will try and come back ;) Hello, I am back \o/ Here's my v2: http://tarek.pastebin.mozilla.org/1373520 This time I have created a custom Strategy and Engine classes, and the get_engine() function takes care of instanciating a pool for the server if needed, and an engine instance per sqlurl, using that pool/ I have used Engine.contextual_connect for the schema switching, and Engine.execute has our custom execution things. I guess this is much cleaner since anyone can use create_engine() with my new strategy. The only assumption I am making is that there's a shared pool on the same host. Does this look better ? Thanks for your help so far Cheers Tarek -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Nov 4, 2011, at 3:41 AM, Tarek Ziadé wrote: On Fri, Nov 4, 2011 at 10:33 AM, Tarek Ziadé ziade.ta...@gmail.com wrote: ... Will try and come back ;) Hello, I am back \o/ Here's my v2: http://tarek.pastebin.mozilla.org/1373520 This time I have created a custom Strategy and Engine classes, and the get_engine() function takes care of instanciating a pool for the server if needed, and an engine instance per sqlurl, using that pool/ I have used Engine.contextual_connect for the schema switching, and Engine.execute has our custom execution things. I guess this is much cleaner since anyone can use create_engine() with my new strategy. The only assumption I am making is that there's a shared pool on the same host. Does this look better ? Thanks for your help so far OK this is clearer to me. So you're really trying to get it so that any number of create_engine() calls all use the same pool based on hostname, OK. If i have time today I might want to try paring this down a lot more. Also not sure why you need the disconnect check stuff in execute() , SQLA has that stuff built in.But it does raise an important point that the mechanism by which we dispose the pool when a disconnect is detected probably should be different here, since the disposal is per-engine, just gives itself a new pool. The other engines will still point to the old, disposed pool, probably causing some kind of pileup. Cheers Tarek -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Fri, Nov 4, 2011 at 4:45 PM, Michael Bayer mike...@zzzcomputing.com wrote: ... OK this is clearer to me. So you're really trying to get it so that any number of create_engine() calls all use the same pool based on hostname, OK. If i have time today I might want to try paring this down a lot more. Also not sure why you need the disconnect check stuff in execute() , SQLA has that stuff built in. I did not know. Do you have any pointer ? But it does raise an important point that the mechanism by which we dispose the pool when a disconnect is detected probably should be different here, since the disposal is per-engine, just gives itself a new pool. The other engines will still point to the old, disposed pool, probably causing some kind of pileup. Ah I see.. maybe some kind of event the engine can register to, to refresh its pool variable ? Cheers Tarek -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
So, I have worked on a first prototype, and was unable to use the event system as-is, because of a lack of context to know which database the user is trying to access. So, after a bit of hacking, here's what I have done: http://tarek.pastebin.mozilla.org/1372473 - the url is passed along the ConnectionFairy checkout method, so I can decide if I have to switch the database - I keep a few globals to decide lazily if a database should be initialized - I keep one engine and one pool *per server* it seems to work, here's an example of usage with the query() function: http://tarek.pastebin.mozilla.org/1372476 So, now I am pretty sure most of this code is crappy, and there's a better way to do this. I still need to make sure all of this is thread-safe. Thoughts ? Feedback ? Cheers Tarek -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Thu, Nov 3, 2011 at 2:28 PM, Tarek Ziadé ziade.ta...@gmail.com wrote: So, I have worked on a first prototype, and was unable to use the event system as-is, because of a lack of context to know which database the user is trying to access. So, after a bit of hacking, here's what I have done: http://tarek.pastebin.mozilla.org/1372473 ooops, I missed a piece : def _raw(self): return self.pool.unique_connection(self.url) -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Nov 3, 2011, at 9:28 AM, Tarek Ziadé wrote: So, I have worked on a first prototype, and was unable to use the event system as-is, because of a lack of context to know which database the user is trying to access. So, after a bit of hacking, here's what I have done: http://tarek.pastebin.mozilla.org/1372473 - the url is passed along the ConnectionFairy checkout method, so I can decide if I have to switch the database - I keep a few globals to decide lazily if a database should be initialized - I keep one engine and one pool *per server* it seems to work, here's an example of usage with the query() function: http://tarek.pastebin.mozilla.org/1372476 So, now I am pretty sure most of this code is crappy, and there's a better way to do this. I still need to make sure all of this is thread-safe. Thoughts ? Feedback ? I should look at this more closely, took a brief glance. One thought I had was why not do the switch the schema thing within Engine.connect(), at least there you know which engine you're dealing with. Though I don't really understand how this is organized anyway, the query() function for example seems a little weird, wouldn't you want this to be transparent at the Engine level ? There should be a simple way to make two engines talk to one pool and switch the schema based on each engine. Maybe some context to be provided to the checkout event- possibly a small API change. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] per-host pooling vs per-engine pooling
Hello
I was wondering if there's a simple way to do per-host pooling.
Here's my use case: I have several hosts and on each one of them has
several databases hosted in mysql.
the engine is tied to a specific database when you create it, so the
default pool will pool connection on a specific database.
what I'd like to do is share the same pool of connectors across all
engines that are working with a given host.f
Example in pseudo-code:
pool_server1 = Pool('mysql://server1', size=10)
engine1 = create_engine('mysql://server1/database1', pool=pool_server1)
engine2 = create_engine('mysql://server1/database2', pool=pool_server1)
The goal is to reduce the number of sockets open for every host
Is there a way to do it with the existing pools ? If not, I guess
I'll work on a custom Pool,
Thanks
Tarek
--
Tarek Ziadé | http://ziade.org
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Jun 24, 2011, at 11:01 AM, Tarek Ziadé wrote:
Hello
I was wondering if there's a simple way to do per-host pooling.
Here's my use case: I have several hosts and on each one of them has
several databases hosted in mysql.
the engine is tied to a specific database when you create it, so the
default pool will pool connection on a specific database.
what I'd like to do is share the same pool of connectors across all
engines that are working with a given host.f
Example in pseudo-code:
pool_server1 = Pool('mysql://server1', size=10)
engine1 = create_engine('mysql://server1/database1', pool=pool_server1)
engine2 = create_engine('mysql://server1/database2', pool=pool_server1)
The goal is to reduce the number of sockets open for every host
Is there a way to do it with the existing pools ? If not, I guess
I'll work on a custom Pool,
well a pool is geared towards one specific connection which already has a
database set up on it.I guess maybe you'd use pool events to switch the
database on checkout.
You can share a pool between engines using the pool argument to
create_engine(), but one problem with that right now is that the dialect which
is per-engine uses the on first checkout event to handle some important
dialect initialization. That step would have to be accommodated in some way.
Also this solution is only local to one Python process, not even a fork. You
might have a simpler time limiting TCP connections using a product like
PGBouncer, and turn pooling way down or off for each SQLAlchemy pool, that way
you can truly limit connections per host.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Fri, Jun 24, 2011 at 7:04 PM, Michael Bayer mike...@zzzcomputing.com wrote: ... Is there a way to do it with the existing pools ? If not, I guess I'll work on a custom Pool, well a pool is geared towards one specific connection which already has a database set up on it. I guess maybe you'd use pool events to switch the database on checkout. do you known if the switching can be done without closing the socket ? You can share a pool between engines using the pool argument to create_engine(), but one problem with that right now is that the dialect which is per-engine uses the on first checkout event to handle some important dialect initialization. That step would have to be accommodated in some way. Also this solution is only local to one Python process, not even a fork. You might have a simpler time limiting TCP connections using a product like PGBouncer, and turn pooling way down or off for each SQLAlchemy pool, that way you can truly limit connections per host. Ok thanks for the tip I'll investigate on this Cheers Tarek -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Jun 24, 2011, at 2:51 PM, Tarek Ziadé wrote: On Fri, Jun 24, 2011 at 7:04 PM, Michael Bayer mike...@zzzcomputing.com wrote: ... Is there a way to do it with the existing pools ? If not, I guess I'll work on a custom Pool, well a pool is geared towards one specific connection which already has a database set up on it.I guess maybe you'd use pool events to switch the database on checkout. do you known if the switching can be done without closing the socket ? yeah this is not a problem, if this is PG there's a default schema setting, i forget what it's called, with MySQL its use. Also, I realized that if you set up the Pool on all the Engines at once before any connections occur, the on first connect event will get called for all of them, so you could get it to work that way. You can share a pool between engines using the pool argument to create_engine(), but one problem with that right now is that the dialect which is per-engine uses the on first checkout event to handle some important dialect initialization. That step would have to be accommodated in some way. Also this solution is only local to one Python process, not even a fork. You might have a simpler time limiting TCP connections using a product like PGBouncer, and turn pooling way down or off for each SQLAlchemy pool, that way you can truly limit connections per host. Ok thanks for the tip I'll investigate on this Cheers Tarek -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
