> On Nov 7, 2014, at 5:56 AM, Enrico Bottani <[email protected]> wrote: > > Hello everybody, > > I'm developing a web service and I'm using SQLAlchemy as an ORM backed by > MySql, I'm using flask to handle web requests, and redis-rq as queue system. > > Most of my service is about reading data from db so performance is fairly > good. Now I'm adding a feature where I would like to store access log for > all the request I got, I've seen a drastic degradation of performance when > starting to write to db, it's normal, but I wasn't expecting such a huge > degradation. > The solution that I came up with is to reply to request while starting a > background job, using redis rq, to write my accesses to the log, given there > is no needs that this happen synchronously I think is a good approach. > > The queue is implemented and locally was working just fine, the problem came > when I upload my service to heroku. Now I got a mysql max_user_connection > error as my application is using all the available connections. > > I understand that this is a problem with pool, it look like when a job is > executed by redis, the connection is then not closed. This is my feeling at > least. > I've tried to change the type of pool, the number of pools, the timeout of > the pool. I've tried all the available pool, single threaded, static, > assertion. None of them works out. I got the service working using the > NullPool, that keep closing and opening connections at each request, but the > performance degrade so much that the service is just not usable any more. > > Does anyone has a suggestion of how to deal with this problem?
this doesn’t sound like a pool issue, it sounds like your ORM sessions aren’t being closed at the end of a request. There’s some background on this here: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#session-faq-whentocreate <http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#session-faq-whentocreate> - have you got all those bases covered? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
