3 ms·

EDIT: I had written a really long and specific reply, full of bombast and insults. EDIT2: Hey zzzeek, sorry you saw that before I edited it. I realized my mis

EDIT: I had written a really long and specific reply, full of bombast and insults. EDIT2: Hey zzzeek, sorry you saw that before I edited it. I realized my mistake about the 2012 thing, and then when I re-read what I had written I realized it was over the top. PGBouncer has all the features that any in application connection pool has, and it doesn't have any of the pitfalls. It's easier to set up, and once set up gives you lots of benefits that an in-application pool cannot give you. Your example stating that the total number of connections is the same is just incorrect. PGBouncer shares connections between processes, and will release connections beyond it's minimum gradually if they aren't used within a few minutes. SQLAlchemy's connection pool doesn't release connections below it's minimum, ever. If there is a load spike, SQLAlchemy's pool will either become a bottleneck, or will create more connections as needed, which are immediately destroyed and recreated if the traffic spike isn't completely uniform (say if it goes up and down even a little). If I said to you, you have two choices. They both take about the same amount of effort to set up, except one performs far better than the other, consumes less resources, and gives you lots of other benefits, why would you choose the lesser option?

zzzeek 14y ago
Edit: apparently as I finished typing this, the parent post edited itself out...though there's some decent info about how to reset sessions here. I'll take the more heated quotes/responses out. > However, lets say you use the SQLAlchemy pool's overflow setting. In that case, new connections will be made as needed, except they will be discarded as they go back to the pool, if the pool has 10 back in it already. Since connections aren't shared between processes, this means many, many new connections will be made and discarded, adding a sizable amount of extra load to a database server already heavily taxed by the high level of traffic. That's the key, that the app is going to be using N number of connections constantly, and the "max overflow" is used for spikes. Nothing is stopping you from combining the in app pools with PGBouncer if you're optimizing for this particular case. There's nothing wrong with PGBouncer. But there's also nothing wrong with in-app pools. > Nothing, the PGBouncer solution is superior in every respect. the two approaches are not mutually exclusive. >> The pool emits the ROLLBACK whether or not the application did so. Problem solved. > 2. Really? When did you add this feature? in 2007. Were you using SQLAlchemy in version 0.2 or 0.3 or something? That would be pretty unfortunate, it was more or less alpha software at that time. > I see it, it is called reset_on_return, and was added to SQLAlchemy in version 0.7.6, which was released in 2012. You are incorrect. reset_on_return is a setting that allows the behavior to be configurable - before that, it was on in all cases. Users of MySQL with MyISAM wanted to turn it off as they weren't using transactions anyway and it was taking up time they didn't want to spend. > Also, ROLLBACK is completely insufficient here, as things like temporary tables, query planner variable changes, and many other very common things persist on the SESSION level. Those are unusual and exotic cases, which if one were using (haven't seen too many web apps that create temporary tables) would have to be planned for. If someone really wants to make session level changes, we have an event system by which those changes can be reset when the connection's state is finished. I've gone and checked PGBouncer's documentation - they deal with it in exactly the same way, which is that you need to set up your own query using "server_reset_query". > Additionally, SQLAlchemy doesn't check that the database connection is valid before letting it out of the pool, just when it is returned. by default this would waste a lot of time. But if you want to do that, here is the documentation to enable that feature: http://docs.sqlalchemy.org/en/rel_0_8/core/pooling.html#disconnect-handling-pessimistic http://docs.sqlalchemy.org/en/rel_0_8/core/pooling.html#disc... > If the database is restarted, SQLAlchemy will gladly hand you database 'connections' where the TCP connection has been closed. if you're not using pessimistic handling (which most people don't), you will get at most one of these. the entire pool is flushed when this condition is detected. > Right. Or you could just use something like PGBouncer, and not have to do this. Also, PGBouncer will have it's connections pooled and ready to go across application restarts, forks, whatever. so use PGBouncer as well. Nothing is wrong with PGBouncer, it's simply not universally necessary. > That is something that has never come up for me, and honestly I think it is YAGNI. honestly that's what I think about your entire first paragraph, the vast majority of apps don't really need that level of detailed tuning. The app servers all hold onto a handful of connections, make some new ones as needed, and it works just fine. But I won't say that your scenario doesn't have merit, partially because it's too late at night for me to parse all that and it's obvious that very high performance scenarios can benefit from having more automation at more levels, nobody is saying PGBouncer is a bad product. I've just never had to bother installing/starting/configuring a whole new daemon just to have a medium size web application run adequately. > Well, there are a bunch of issues: worse performance, many more database connections that just sit idle (doesn't matter with mysql, matters a lot with postgres), If the applications are idle, then you'd have idle connections, but I haven't experienced that being an issue - the database is idle too ! If you have some servers spiking and other ones idling, then its an issue, so use PGBouncer for that case. But that's not typical in my experience.
- justin_vanw 14y ago
  PGBouncer uses "DISCARD ALL" as it's default method to recycle connections, which has all the effects of creating a new connection. Of course it is configurable. I would strongly advise you to do this as well. As for idle connections, Postgresql uses a process-per-connection model, and therefore idle connections use a fair amount of resources, and having too many connections can have other bad consequences for performance. (Too many is generally considered to be more than 100.) I don't see any reason to use an in-application connection pool in addition to pgbouncer. However, if I were configuring SQLAlchemy, I would just use the NullPool. EDIT: HN won't let me reply to your reply to this comment, but here is how I would make DISCARD ALL the default behavior. Add a method reset_connection() to the connection proxy base class, with the default implementation just being self.rollback() Then, on the dialect specific postgresql one, make the implementation do something better, like DISCARD ALL (which isn't supported on postgres older than 8.2 btw).
  - zzzeek 14y ago
    > PGBouncer uses "DISCARD ALL" as it's default method to recycle connections, which has all the effects of creating a new connection. Of course it is configurable. I would strongly advise you to do this as well. yes I'm glad we had this discussion so that I've learned PG has this handy command. We can't do it by default because it's a PG-only behavior and I don't have an analogue for all the other databases, but we can add it to the Postgresql dialect documentation as a recommended addition for apps that are modifying session state. > As for idle connections, Postgresql uses a process-per-connection model, and therefore idle connections use a fair amount of resources, and having too many connections can have other bad consequences for performance. (Too many is generally considered to be more than 100.) If I'm going over 100 connections for a single PG database, which is not something that occurs with the typical medium-size web applications I work with (they are more like in the 20 connection range), then I'd use PGBouncer. But in-app pooling gives me something that works immediately in any situation and on any database equally well. > I don't see any reason to use an in-application connection pool in addition to pgbouncer. However, if I were configuring SQLAlchemy, I would just use the NullPool. Probably not, though I'd want to see what kind of overhead psycopg2 has starting up connections and such, I think it's not much. But this is not the case for other DBAPIs and databases that don't have a product like PGBouncer (like cx_oracle, which is dog slow on connect, ODBC over unix, etc.)

zzzeek 14y ago

Edit: apparently as I finished typing this, the parent post edited itself out...though there's some decent info about how to reset sessions here. I'll take the more heated quotes/responses out. > However, lets say you use the SQLAlchemy pool's overflow setting. In that case, new connections will be made as needed, except they will be discarded as they go back to the pool, if the pool has 10 back in it already. Since connections aren't shared between processes, this means many, many new connections will be made and discarded, adding a sizable amount of extra load to a database server already heavily taxed by the high level of traffic. That's the key, that the app is going to be using N number of connections constantly, and the "max overflow" is used for spikes. Nothing is stopping you from combining the in app pools with PGBouncer if you're optimizing for this particular case. There's nothing wrong with PGBouncer. But there's also nothing wrong with in-app pools. > Nothing, the PGBouncer solution is superior in every respect. the two approaches are not mutually exclusive. >> The pool emits the ROLLBACK whether or not the application did so. Problem solved. > 2. Really? When did you add this feature? in 2007. Were you using SQLAlchemy in version 0.2 or 0.3 or something? That would be pretty unfortunate, it was more or less alpha software at that time. > I see it, it is called reset_on_return, and was added to SQLAlchemy in version 0.7.6, which was released in 2012. You are incorrect. reset_on_return is a setting that allows the behavior to be configurable - before that, it was on in all cases. Users of MySQL with MyISAM wanted to turn it off as they weren't using transactions anyway and it was taking up time they didn't want to spend. > Also, ROLLBACK is completely insufficient here, as things like temporary tables, query planner variable changes, and many other very common things persist on the SESSION level. Those are unusual and exotic cases, which if one were using (haven't seen too many web apps that create temporary tables) would have to be planned for. If someone really wants to make session level changes, we have an event system by which those changes can be reset when the connection's state is finished. I've gone and checked PGBouncer's documentation - they deal with it in exactly the same way, which is that you need to set up your own query using "server_reset_query". > Additionally, SQLAlchemy doesn't check that the database connection is valid before letting it out of the pool, just when it is returned. by default this would waste a lot of time. But if you want to do that, here is the documentation to enable that feature: http://docs.sqlalchemy.org/en/rel_0_8/core/pooling.html#disconnect-handling-pessimistic http://docs.sqlalchemy.org/en/rel_0_8/core/pooling.html#disc... > If the database is restarted, SQLAlchemy will gladly hand you database 'connections' where the TCP connection has been closed. if you're not using pessimistic handling (which most people don't), you will get at most one of these. the entire pool is flushed when this condition is detected. > Right. Or you could just use something like PGBouncer, and not have to do this. Also, PGBouncer will have it's connections pooled and ready to go across application restarts, forks, whatever. so use PGBouncer as well. Nothing is wrong with PGBouncer, it's simply not universally necessary. > That is something that has never come up for me, and honestly I think it is YAGNI. honestly that's what I think about your entire first paragraph, the vast majority of apps don't really need that level of detailed tuning. The app servers all hold onto a handful of connections, make some new ones as needed, and it works just fine. But I won't say that your scenario doesn't have merit, partially because it's too late at night for me to parse all that and it's obvious that very high performance scenarios can benefit from having more automation at more levels, nobody is saying PGBouncer is a bad product. I've just never had to bother installing/starting/configuring a whole new daemon just to have a medium size web application run adequately. > Well, there are a bunch of issues: worse performance, many more database connections that just sit idle (doesn't matter with mysql, matters a lot with postgres), If the applications are idle, then you'd have idle connections, but I haven't experienced that being an issue - the database is idle too ! If you have some servers spiking and other ones idling, then its an issue, so use PGBouncer for that case. But that's not typical in my experience.

justin_vanw 14y ago
PGBouncer uses "DISCARD ALL" as it's default method to recycle connections, which has all the effects of creating a new connection. Of course it is configurable. I would strongly advise you to do this as well. As for idle connections, Postgresql uses a process-per-connection model, and therefore idle connections use a fair amount of resources, and having too many connections can have other bad consequences for performance. (Too many is generally considered to be more than 100.) I don't see any reason to use an in-application connection pool in addition to pgbouncer. However, if I were configuring SQLAlchemy, I would just use the NullPool. EDIT: HN won't let me reply to your reply to this comment, but here is how I would make DISCARD ALL the default behavior. Add a method reset_connection() to the connection proxy base class, with the default implementation just being self.rollback() Then, on the dialect specific postgresql one, make the implementation do something better, like DISCARD ALL (which isn't supported on postgres older than 8.2 btw).
- zzzeek 14y ago
  > PGBouncer uses "DISCARD ALL" as it's default method to recycle connections, which has all the effects of creating a new connection. Of course it is configurable. I would strongly advise you to do this as well. yes I'm glad we had this discussion so that I've learned PG has this handy command. We can't do it by default because it's a PG-only behavior and I don't have an analogue for all the other databases, but we can add it to the Postgresql dialect documentation as a recommended addition for apps that are modifying session state. > As for idle connections, Postgresql uses a process-per-connection model, and therefore idle connections use a fair amount of resources, and having too many connections can have other bad consequences for performance. (Too many is generally considered to be more than 100.) If I'm going over 100 connections for a single PG database, which is not something that occurs with the typical medium-size web applications I work with (they are more like in the 20 connection range), then I'd use PGBouncer. But in-app pooling gives me something that works immediately in any situation and on any database equally well. > I don't see any reason to use an in-application connection pool in addition to pgbouncer. However, if I were configuring SQLAlchemy, I would just use the NullPool. Probably not, though I'd want to see what kind of overhead psycopg2 has starting up connections and such, I think it's not much. But this is not the case for other DBAPIs and databases that don't have a product like PGBouncer (like cx_oracle, which is dog slow on connect, ODBC over unix, etc.)