5 ms·

In 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 dat

In 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.

alexpopescu 14y ago
"I'm not a Django person" 1. Using PGBouncer seems to be the ideal solution, but I'm not sure other databases actually provide a proxy. I haven't heard of something like this for MySQL (a) I might be wrong, please provide a link, b) I'm know some will say "just don't use MySQL") This aside, I would expect a connection pool implementation to support at least the following: 1. min: minimum number of connections 2. max: maximum number of connections 3. idleTime: time interval after which idle connections are automatically closed 4. connection cleanup (most often done when returned to the pool). Basically making sure that the connection object is pristine If it doesn't do all these, then it's probably not something you'd want to use.
zzzeek 14y ago
Sorry, the points you make here are all very much like the FUD I read about ORMs - all based on experiences with inferior software, not an approach. > 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. Suppose you use PGBouncer, and you configure it to allow 50 connections. Or, you configure your web servers to allow 10 concurrent processes each with 5 connections. Total number of connections is...the same ! > 2. It is very easy to improperly reset a database connection before giving it back out of the pool. This leads to two problems. It is actually not possible at all when using a pool like that of SQLAlchemy's, as any connection that gets returned to the pool is properly reset by the pool itself. > 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. The pool emits the ROLLBACK whether or not the application did so. Problem solved. > problem 2: It is inevitable that somewhere in your code you will not issue a rollback on a connection after starting a transaction. It would be crazy to leave connections idle in the pool with transactional state on them. No connection idle in the pool has transactional state, as they do not get returned without a rollback. If a catastrophic error occurred on the connection, it's tossed out of the pool. This is all detected at a layer of abstraction below what the application sees, so the programmer has no chance of messing it up. > 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. this is all the same non-issue. > 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. right, that's why multiprocessing always provides hooks for an init function that runs right as the new child process starts. In this function, you basically create a new pool (or reset the one you have). I'd disagree with the characterization that one must be "very careful to completely remove and recreate all the connections", this makes it sound like some dramatic programming effort; it's not. It's called pool.dispose(), it's designed for this case, and it does it in one step. SQLAlchemy's pool is designed such that it doesn't pre-allocate connections, so if your subprocess only uses one connection, the pool will only do so as well. It's true that in this scenario, you're losing the usual benefits of a pool if your subprocess lives for only the length of a single transaction, but it doesn't make things worse, as long as you remember to start up the new pool across process boundaries. > In application pools of database connections is something that will bite you over and over its obvious you've used some very poorly written pooling solutions, but the good news is that these problems have been solved. PGBouncer is great but isn't strictly necessary, and also can be combined with in-app pools. Only an in-app pool gives you the means to limit the connections used by an individual process (as I mentioned elsewhere, if PGBouncer allows this, it would have to be through a more complex and inconvenient system of configuring individual servers to connect differently so that they can be differentiated). SQLAlchemy's pool is deployed in thousands of production environments including some big ones like Reddit, Yelp and Dropbox. There is no issue.

alexpopescu 14y ago

"I'm not a Django person" 1. Using PGBouncer seems to be the ideal solution, but I'm not sure other databases actually provide a proxy. I haven't heard of something like this for MySQL (a) I might be wrong, please provide a link, b) I'm know some will say "just don't use MySQL") This aside, I would expect a connection pool implementation to support at least the following: 1. min: minimum number of connections 2. max: maximum number of connections 3. idleTime: time interval after which idle connections are automatically closed 4. connection cleanup (most often done when returned to the pool). Basically making sure that the connection object is pristine If it doesn't do all these, then it's probably not something you'd want to use.

zzzeek 14y ago

Sorry, the points you make here are all very much like the FUD I read about ORMs - all based on experiences with inferior software, not an approach. > 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. Suppose you use PGBouncer, and you configure it to allow 50 connections. Or, you configure your web servers to allow 10 concurrent processes each with 5 connections. Total number of connections is...the same ! > 2. It is very easy to improperly reset a database connection before giving it back out of the pool. This leads to two problems. It is actually not possible at all when using a pool like that of SQLAlchemy's, as any connection that gets returned to the pool is properly reset by the pool itself. > 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. The pool emits the ROLLBACK whether or not the application did so. Problem solved. > problem 2: It is inevitable that somewhere in your code you will not issue a rollback on a connection after starting a transaction. It would be crazy to leave connections idle in the pool with transactional state on them. No connection idle in the pool has transactional state, as they do not get returned without a rollback. If a catastrophic error occurred on the connection, it's tossed out of the pool. This is all detected at a layer of abstraction below what the application sees, so the programmer has no chance of messing it up. > 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. this is all the same non-issue. > 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. right, that's why multiprocessing always provides hooks for an init function that runs right as the new child process starts. In this function, you basically create a new pool (or reset the one you have). I'd disagree with the characterization that one must be "very careful to completely remove and recreate all the connections", this makes it sound like some dramatic programming effort; it's not. It's called pool.dispose(), it's designed for this case, and it does it in one step. SQLAlchemy's pool is designed such that it doesn't pre-allocate connections, so if your subprocess only uses one connection, the pool will only do so as well. It's true that in this scenario, you're losing the usual benefits of a pool if your subprocess lives for only the length of a single transaction, but it doesn't make things worse, as long as you remember to start up the new pool across process boundaries. > In application pools of database connections is something that will bite you over and over its obvious you've used some very poorly written pooling solutions, but the good news is that these problems have been solved. PGBouncer is great but isn't strictly necessary, and also can be combined with in-app pools. Only an in-app pool gives you the means to limit the connections used by an individual process (as I mentioned elsewhere, if PGBouncer allows this, it would have to be through a more complex and inconvenient system of configuring individual servers to connect differently so that they can be differentiated). SQLAlchemy's pool is deployed in thousands of production environments including some big ones like Reddit, Yelp and Dropbox. There is no issue.