6 ms·
Fixing Database Connections in Django
- jeremyw 14y agoCan anyone recommend a Django connection pool for MySQL that -- and this is the important part -- closes local pool connections to the database on a routine basis. Reason: load balancers and machine maintenance. In per-connection mode (i.e. no connection pooling), operations folk can take machines out for service quickly: put the box in MAINT (ala HAProxy) or equivalent, wait a few moments, see the connections drain away to zero. But I haven't been able to find connection pooling logic that recycles backend sockets on a short basis. For instance, 60s is long enough to amortize the cost of connection setup, but short enough to make your operations people very happy.
- zzzeek 14y agoseems like I'll have to reply to every post on this thread but sqlalchemy's pool does this, either optimistically or pessimistically, see http://docs.sqlalchemy.org/en/rel_0_8/core/pooling.html#dealing-with-disconnects http://docs.sqlalchemy.org/en/rel_0_8/core/pooling.html#deal... (in particular pool_recycle).
- jeremyw 14y agoYes, SQLAlchemy is excellent in this respect, but without getting into a Django ORM vs SQLAlchemy debate, it is the less-preferred option among Django developers.
- zzzeek 14y agothe pool can be used in isolation of the rest of SQLAlchemy, which is what Kenneth's Django-Pool library is doing.
- est 14y agoI think Django db connection are doing it plain wrong. Connection are established and gone with a request, but in fact we can simply use a global variable for any SQL/NoSQL connection pool in views.py, which is basically a WSGI global variable. In this case, connections are established when a gunicorn/uWSGI worker process starts, and shutdown when the worker is respawned.
- jeremyw 14y agoRight, the problem is all the failure modes. You want a battle-tested library that hands you a valid connection, that will work to the 99th percentile. Lots of edge cases.
- pjenvey 14y agoUse SQLAlchemy.
- zzzeek 14y agoread the source, it is !
- rosser 14y agoIf you're using a connection pooler with Django — or really, anything SQL database related in Python — please be mindful that the Python DB API is implicitly transactional. That is, if you say: SELECT COUNT(*) FROM users; DBAPI says: BEGIN; SELECT COUNT(*) FROM users; ...and leaves the COMMIT or ROLLBACK of that transaction up to you. Aside from the severe risk of resource leaks, that has implications for which pooling modes you can use. Particularly, you can't use statement-level pooling, and unless you're scrupulous about managing those implicitly created transactions, transaction-level pooling is pretty much the same thing as connection-level pooling. Adjust your expectations accordingly, or turn on auto-commit. (EDIT: clarification.)
- zzzeek 14y agothe SQLAlchemy connection pool (which is what this is using) calls connection.rollback() when the connection is checked back in. as far as "leaking" transactional state it's not different from "autocommit", which is what Postgresql connections normally do (and what psycopg2 will do if you set `connection.autocommit=True`).
- zzzeek 14y agoDjango-PostgresPool? What, I don't even... https://github.com/kennethreitz/django-postgrespool/blob/master/django_postgrespool/base.py#L7 https://github.com/kennethreitz/django-postgrespool/blob/mas... from sqlalchemy import event from sqlalchemy.pool import manage, QueuePool Oh, yes now that makes sense ! :)
- craigkerstiens 14y agoIf it works why not just reuse it :)
- ldng 14y agoThe third solution, django-db-pool, doesn't look like it's using SA (at quick glance). Even has a postgis driver.
- hcarvalhoalves 14y agoThe diagnostic is right, but the remedy given in the article seems worse than the disease. Using backends to make Django pool the connections is sub-optimal. You'll have a different pool per worker process, and they won't persist when you restart your workers - right when you need low latency the most. Use pgbouncer/pgpool instead. On top of that, these poolers have some nice features.
- zzzeek 14y agoif all worker processes have an evenly distributed workload, how is pool-per-worker process a disadvantage? it's still a fixed-size pool, just one that's an aggregate of several smaller pools.
- hcarvalhoalves 14y agoAppended to my comment: > You'll have a different pool per worker process, and they won't persist when you restart your workers - right when you need low latency the most
- zzzeek 14y agoSo, pooling that has TCP/DB initialize connect overhead during a restart only is "worse than" having TCP/DB initialize connect overhead on restart as well as every subsequent request ? There's pros/cons to in-app pooling vs. middleware pooling, but calling in-app pooling "worse" than no pooling at all is kind of misleading. If I were using PGBouncer, I'd probably leave SQLA's pooling on anyway, so at least you have no TCP latency at all on connect. unless you're dealing with apps that go unevenly idle/active.
- hcarvalhoalves 14y ago> but calling in-app pooling "worse" than no pooling You don't need to get all worked up. Nowhere I affirm it's worse than no polling. I'm talking about not reinventing the wheel, specially since pooling inside the worker is inferior than just setting up pgpool.
- jtchang 14y agoI love sqlalchemy!
- bjourne 14y agoThis old thread about it is interesting: https://groups.google.com/forum/?fromgroups=#!topic/django-users/m1jeE4Cxr9A https://groups.google.com/forum/?fromgroups=#!topic/django-u... I really hope Django gets support for persistent connections and/or connection pooling. pgbouncer isn't always an option on shared hosting (and is a royal PITA to setup on Windows) and there are other databases than postgres.
- jaytaylor 14y agoHere @ SendHub we've been using django-db-pool[0] since Q3 of 2012 in production and it's been pretty smooth sailing. NB: I highly recommend verifying via postgres logs that the module is doing what you expect, because it may not be immediately clear whether the driver has bee activated or not. [0] https://github.com/gmcguire/django-db-pool https://github.com/gmcguire/django-db-pool
- craigkerstiens 14y agoAs per the bottom of the post persistent connections have already been committed for Django 1.6 - https://github.com/django/django/commit/2ee21d https://github.com/django/django/commit/2ee21d
- ltbarcly3 14y agoIn application connection pools are a BadThing™. Why is pooling connections in your application a bad thing you ask? 1. It keeps open N connections to the database for each one of your M server processes. This is especially bad in Postgresql, as that means you have N * M idle processes on the database server. 2. It is very easy to improperly reset a database connection before giving it back out of the pool. This leads to two problems. problem 1: The first is that if code using a db connection has an error and doesn't issue a rollback, the connection will still be in a bad state when it is handed back out of the pool again. On a webserver, this means that you will see strange exceptions where a query failed, and is very hard to debug, because the actual error was on the previous request to use that connection. problem 2: It is inevitable that somewhere in your code you will not issue a rollback on a connection after starting a transaction. In fact, if you are using Python the default dp-api behavior is to "transactional mode" where a BEGIN is sent immediately before any query on a new connection. It will automatically start the transaction, but it will not COMMIT or ROLLBACK automatically. The connection will be returned to the pool with a transaction started, and the transaction will be kept open, potentially for days or weeks. This can cause an incredible number of headaches, performance problems, and cause your database's on disk size to grow much more quickly. 3. If you use a server like gunicorn, or a library like multiprocessing, open file handles (including sockets) are shared upon fork(). This means that if you aren't very careful to completely remove and recreate all the connections in your connection pool after a fork, you will have multiple processes sharing the same sockets. The processes will then step all over each other and corrupt each others use of the database. I have seen all of these problems multiple times in production code. So, what should you do? If you are using postgres, install PGBouncer on every server. Configure your application to make a new connection to PGBouncer whenever it needs a new connection. The overhead of this is tiny, since you connect to PGBouncer through a filesystem unix socket. PGBouncer keeps open as many connections as it needs to the database server, maintains those connections, and puts them in a good state before giving them to you. PGBouncer will also gradually reduce it's connections to the database server if they are idle and unused long enough. I'm sure other databases have some similar database proxy. Taking this advice will give you better performance, far fewer bugs, and hold many less connections to the database server. Additionally, all your database uses can benefit from connection pooling in this way, even shell scripts which call psql. In application pools of database connections is something that will bite you over and over, just say no.
- daGrevis 14y agoWhat are the odds. Friday my task was to implement this for our project that uses Django. I learned it all the hard way, even the South trick. The only difference was that we are using MySQL and _django-mysqlpool_. If almost all solution are using SQLAlchemy components and SQLAlchemy is NOT DBMS-specific, it should be possible to create one package that works for SQLite3, MySQL and Postgres at the same time. In theory, of course. :)