SQLAlchemy + MySQLdb + Eventlets, proper way to do it?

[Pedro Werneck]

I'm using SQLAlchemy with MySQLdb for processing dozen million daily tasks with Celery. Most of my queries are very quick and the tasks don't wait for I/O for too long, so I had great results using the eventlet pool for Celery. However, whenever I hit a chunk of data which is expected to lead to slower queries, the overall performance suffers a lot, so I guess I can't just let it block like that.

I found the eventlet.db_pool, and there's this Nova example using it with SQLAlchemy and MySQLdb:

http://bazaar.launchpad.net/~rackspace-titan/nova/sqlalchemy-eventlet/view/head:/nova/db/sqlalchemy/session.py

However, in an old topic here asking the same about psycopg someone mentions that this isn't a good example and the guys probably don't know SA very well.

https://groups.google.com/d/msg/sqlalchemy/CU_kknlnksQ/wI1Xw21vcLgJ

So, any pointers on the proper way to do it?

[Michael Bayer]

the recipe seems to make use of a connection pool provided by eventlet, the rest of what's there isn't very interesting (or necessary).

I haven't used eventlet but seems fine to me ?    I wouldn't say connection pooling is even very critical.   If slow queries are the issue, eventlet would just need to make sure that network requests don't block.

It's possible that gevent is more popular, I've had more experience playing with that.

[Pedro Werneck]

So, basically it's just passing the eventlet pool as the creator for the create_engine call? Good.

Right, it isn't critical at all. My system is working perfectly fine and much faster than without eventlets most of the time, but since I went over the trouble of changing it to using eventlets, I'd like to try getting the database connection right too and see if there's any significant improvemente.

Thanks!

[Michael Bayer]

On Apr 25, 2013, at 9:26 PM, Pedro Werneck <pjwe...@gmail.com> wrote:

>
>
> So, basically it's just passing the eventlet pool as the creator for the create_engine call? Good.
>
> Right, it isn't critical at all. My system is working perfectly fine and much faster than without eventlets most of the time, but since I went over the trouble of changing it to using eventlets, I'd like to try getting the database connection right too and see if there's any significant improvemente.

I did have the thought that if eventlet needs a special connection pool, you might want to disable SQLAlchemy's own pooling within create_engine() by passing "poolclass=NullPool". Otherwise it will hold onto a small set of connections persistently, not sure if that's what eventlet's pool is trying to implement in a different way.

[Pedro Werneck]

That definitely makes sense, but when I set the NullPool while using the eventlet db_pool, I get the whole application stuck after a while, then I get a connection timeout on every thread. I just removed it and using a small SA pool instead, with no problems. 

Anyway, the eventlet db_pool works beautifully, and increased my throughput significantly. 

 
---
Pedro Werneck