[sqlalchemy] How to determine the right number of pooled connections to use
Good day. I tried to perform a load test on my python web app using sqlalchemy as follows: 1000 requests 20 concurrent connections Towards the end, I started getting an error from sqlalchemy module: TimeoutError: QueuePool limit of size 40 overflow 10 reached, connection timed out, timeout 30 I'm not a load testing expert neither am I a sqlalchemy expert, but I thought that perhaps 20 concurrent connections would be comfortably handled by 40 pooled connections (+ the 10 overflowed) even if there are 1000 requests. Does this mean that the connections are not being returned to the pool quick enough? I noticed in my results that my http server is handling 65 requests p/sec (got 20 out of 1000 failed requests towards the end and I take it its from the above error as that the only thing in the logs). Perhaps thats why it can't return the connections to the pool quick enough, to many requests to quickly. Could anyone suggest a way I could perhaps determine the memory requirement per pooled connection? I'd probably then take the amount of memory I'd dedicate to this app and divide it by memory required per connection. Regards, -Alen Ribic --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to determine the right number of pooled connections to use
On Sep 19, 5:21 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Sep 19, 2008, at 11:16 AM, Alen Ribic wrote: if the thread in which the request was served continues to run, not serve further requests, and does not clean up after itself, then the connections held open local to that thread don't get returned to the pool. This answers my question 100%. I'm pretty sure I found the problem in my WSGI application stack. It seems to let some mako template exception in my app leek through my exception handling middleware and hence not return any relevant HTTP response. That'd explain it. Thanks again. -Alen --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Can't shake off the 'MySQL server has gone away' Error
I still seem to get the 'MySQL server has gone away' Error. I am suspecting that the problem is in my use of FCGI in production. Thats the only difference between my Development environment and Production and it works 100% without the error in Development env. I guess I'll have to put some more debugging in my application in Production setup. Oh and, after the 'MySQL server has gone away' Error, I immediately get the InvalidRequestError: Can't reconnect until invalid transaction is rolled back. SQLAlchemy version I'm running by the way is '0.4.6'. I created a WSGI middleware class that handles the SQLAlchemy Connections and Sessions like so: Note: the middleware instance gets loaded only once on start of the application. And the get_engine() method lazy-loads the engine once and then returns existing thereafter. sql.py class SQLAlchemyMiddleware(object): Middleware for providing clean SQLAlchemy Session objects for each Request. def __init__(self, application): self.application = application self.__engine = None def get_engine(self): if self.__engine is None: self.__engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) return self.__engine def init_model(self, engine): Call before using any of the tables or classes in the model. sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine) meta.engine = engine meta.Session = orm.scoped_session(sm) def __call__(self, environ, start_response): try: engine = self.get_engine() self.init_model(engine) return self.application(environ, start_response) finally: if meta.Session is not None: meta.Session.remove() models/meta.py from sqlalchemy import MetaData __all__ = ['engine', 'metadata', 'Session'] engine = None# Global metadata. If you have multiple databases with overlapping table # names, you'll need a metadata for each database. Session = None metadata = MetaData() Regards, -Alen On Aug 15, 4:44 pm, Alen Ribic [EMAIL PROTECTED] wrote: one-per-application level That seems to have worked. I'll monitor the log for the next day or so and see how it goes. Thx -Alen On Aug 15, 4:27 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 15, 2008, at 10:21 AM, Alen Ribic wrote: To me it seems like the connections are not being returned back to the pool. Here is my engine code which gets called on each user HTTP request: engine = create_engine( settings.SQLALCHEMY_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) if you call create_engine() on every request, then you're creating a new connection pool for every request. move this up to the module one- per-application level. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Can't shake off the 'MySQL server has gone away' Error
The way you have it, a concurrent thread can easily interrupt the ScopedSession instance attached to meta and replace with a new one, with the old one being lost. Ouch, that would be no good. Thank goodness my prod env aint really prod yet. Thank you for your help again. Much appreciated. -Alen On Aug 18, 3:13 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 18, 2008, at 5:08 AM, Alen Ribic wrote: sql.py class SQLAlchemyMiddleware(object): Middleware for providing clean SQLAlchemy Session objects for each Request. def __init__(self, application): self.application = application self.__engine = None def get_engine(self): if self.__engine is None: self.__engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) return self.__engine def init_model(self, engine): Call before using any of the tables or classes in the model. sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine) meta.engine = engine meta.Session = orm.scoped_session(sm) def __call__(self, environ, start_response): try: engine = self.get_engine() self.init_model(engine) return self.application(environ, start_response) finally: if meta.Session is not None: meta.Session.remove() The big mistake here is creating a brand new ScopedSession on each request. This is not how ScopedSession was intended to be used; its created, like Engine, once per application. Some details on this pattern are here: http://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual_... The way you have it, a concurrent thread can easily interrupt the ScopedSession instance attached to meta and replace with a new one, with the old one being lost. Here's a more reasonable approach: class SQLAlchemyMiddleware(object): def __init__(self, application): self.application = application meta.engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) meta.Session = orm.scoped_session(orm.sessionmaker(autoflush=True, transactional=True, bind=meta.engine)) def __call__(self, environ, start_response): try: return self.application(environ, start_response) except: meta.Session.rollback() raise finally: meta.Session.remove() --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Logger problem
The SQLAlchemy default logging seems to use StreamHandler to write to sys.stdout. This cause a problem when writing Python CGI scripts I (unfortunately) have to. The problem is that the SA logger writes to sys.stdout before HTTP headers get writen by my homegrown web cgi framework. See snippet from SA log.py below: [[ default_enabled = False def default_logging(name): global default_enabled if logging.getLogger(name).getEffectiveLevel() logging.WARN: default_enabled = True if not default_enabled: default_enabled = True handler = logging.StreamHandler(sys.stdout) handler.setFormatter(logging.Formatter( '%(asctime)s %(levelname)s %(name)s %(message)s')) rootlogger.addHandler(handler) ]] To get my cgi app to work, I can either: 1.) set Echo = False or 2.) I comment out the handler lines in log.py above and from there my own logger directs the SA log to my app's log file. Both of the above ways do the trick. Anyone with any idea how to do this in a more cleaner way? If not, should this perhaps be suggested as change to current SA source? Kind regards, -Alen Ribic --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Logger problem
dont use echo at all, configure logging through Python logging. echo corresponds to sqlalchemy.engine/INFO. http://www.sqlalchemy.org/docs/05/dbengine.html#dbengine_logging Thanks for the ref Michael. That will work perfectly. -Al --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
