Re: [sqlalchemy] sorry, too many clients already
On Thu, Jul 25, 2013 at 8:39 PM, kris kkvilek...@gmail.com wrote: On Thursday, July 25, 2013 4:12:50 PM UTC-7, Klauss wrote: On Thu, Jul 25, 2013 at 7:58 PM, kris kkvil...@gmail.com wrote: My postgres.conf has a parameter max_connections = 100 That's not only the default, but it's also not really recommended to push it much higher, so only do so if you really need a big pool on each machine, and if you're sure that pool will be mostly idle all of the time. Hmm.. I just bumped it 200 and modified the shared_buffers to be 32MB Perhaps a bit OT on this list, but you probably want to increase that further. Check out postgres' tuning wiki[0] [0] http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] sorry, too many clients already
We are running a replicated (multiple machines behind ngnix) Turbogears 2.1.5 App With SA 0.7.8 and postgres 8.4 After a lot of recent usage the system ground to halt and we are receiving (OperationalError) FATAL: sorry, too many clients already while trying to any DBSession.query. Any ideas what could cause this? -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] sorry, too many clients already
On Thu, Jul 25, 2013 at 7:32 PM, kris kkvilek...@gmail.com wrote: We are running a replicated (multiple machines behind ngnix) Turbogears 2.1.5 App With SA 0.7.8 and postgres 8.4 After a lot of recent usage the system ground to halt and we are receiving (OperationalError) FATAL: sorry, too many clients already while trying to any DBSession.query. Any ideas what could cause this? As the message says, you have too many open connections. When you have multiple machines, you must either provision your postgres to be able to handle a full connection pool (max_overflow preferrably, pool_size at least) for each nginx worker for each machine, or have SQLA connect to a pgbouncer that will do the shared pooling for you. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] sorry, too many clients already
Thanks for the quick reply. On Thursday, July 25, 2013 3:39:17 PM UTC-7, Klauss wrote: As the message says, you have too many open connections. When you have multiple machines, you must either provision your postgres to be able to handle a full connection pool (max_overflow preferrably, pool_size at least) for each nginx worker for each machine, or have SQLA connect to a pgbouncer that will do the shared pooling for you. I should have mentioned that all the connections are idle.. My postgres.conf has a parameter max_connections = 100 I was led to believe the SA would manage pool of connections to some good default (http://docs.sqlalchemy.org/en/rel_0_7/core/pooling.html?highlight=connection%20pool), but couldn't find the default values? Just so I can relate the two figures (SA Pool parameters and PG max_connections) Also Is there a good way to get it too close some idle connections? Thx, Kris -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] sorry, too many clients already
On Thu, Jul 25, 2013 at 7:58 PM, kris kkvilek...@gmail.com wrote: My postgres.conf has a parameter max_connections = 100 That's not only the default, but it's also not really recommended to push it much higher, so only do so if you really need a big pool on each machine, and if you're sure that pool will be mostly idle all of the time. I was led to believe the SA would manage pool of connections to some good default (http://docs.sqlalchemy.org/en/rel_0_7/core/pooling.html?highlight=connection%20pool), but couldn't find the default values? Just so I can relate the two figures (SA Pool parameters and PG max_connections) pool_size, see create_engine[0], the default is 5 Also Is there a good way to get it too close some idle connections? Yes, decrease pool_size. Just how many workers do you have? With the defaults, you'd need a little under 20 workers to start getting those errors. With that many workers, your only real solution is to install a pgbouncer to manage those connections globally. SQLA cannot manage a pool of connections across workers, only a shared pool (pgbouncer) can do that. SQLA's pool is local to each process (worker). [0] http://docs.sqlalchemy.org/en/rel_0_7/core/engines.html#sqlalchemy.create_engine -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] sorry, too many clients already
On Thursday, July 25, 2013 4:12:50 PM UTC-7, Klauss wrote: On Thu, Jul 25, 2013 at 7:58 PM, kris kkvil...@gmail.com javascript: wrote: My postgres.conf has a parameter max_connections = 100 That's not only the default, but it's also not really recommended to push it much higher, so only do so if you really need a big pool on each machine, and if you're sure that pool will be mostly idle all of the time. Hmm.. I just bumped it 200 and modified the shared_buffers to be 32MB I was led to believe the SA would manage pool of connections to some good default ( http://docs.sqlalchemy.org/en/rel_0_7/core/pooling.html?highlight=connection%20pool), but couldn't find the default values? Just so I can relate the two figures (SA Pool parameters and PG max_connections) pool_size, see create_engine[0], the default is 5 Thanks.. Also Is there a good way to get it too close some idle connections? Yes, decrease pool_size. Just how many workers do you have? With the defaults, you'd need a little under 20 workers to start getting those errors. Here's the weird bit.. I have 2 remote machine and local access.. after some light usage I already see 9 Idle connections from the local machine and a few from the other machines. It may actually be that we are using uWSGI, each which has 8 workers configured, so really I have 24 workers already. With that many workers, your only real solution is to install a pgbouncer to manage those connections globally. SQLA cannot manage a pool of connections across workers, only a shared pool (pgbouncer) can do that. SQLA's pool is local to each process (worker). Thanks for the help.. I will check out pgbouncer at this point. [0] http://docs.sqlalchemy.org/en/rel_0_7/core/engines.html#sqlalchemy.create_engine -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
