Yes in deed. I've restarted the webserver and the database server. Recently I've tried setting pool_size to 1 for every app, that is, for every website. Restarted postgresql and webserver (lighttpd). And then I used this SQL statement to check the total count of connections for every database (or what it is the same, for every app, because every app has its own database):
select datname, count(*) from pg_stat_activity group by datname order by datname; Just to remind, I have around 13 apps running, that is, 13 websites, 13 databases. With this new configuration of every app using a pool size of 1, I restarted the database server and the webserver, and then I ran the previous SQL statement to see the total connections for every app, and I see 5 idle connections for every app, that is, for every website that has some visitors browsing the site. A couple of the websites almost never have visitors, so, for those websites, there were no idle connections. Then I go to the homepage of those websites, rechecked connections, and there I see 5 idle connections for those websites. I already checked and re-checked the code of my app to be shure that I'm setting "pool_size" parameter correctly. In the other hand, I've been testing pgbouncer on localhost, reading about it, and I'll be setting it for production. For what I've read, independently of the postgresql max connections, I can set pgbouncer to a max_client_conn of 2000 (for example) with a default_pool_size of 20. Then all the apps connect to pgbouncer, and pgbouncer will multiplex connections to postgres. However I don't want to mix things in this post, regardless of pgbouncer, I would like to understand why I can't get to work web2py's pooling mechanism. I'm really grateful for your help! I'll continue trying to figure it out. Any comment or suggestion will be appreciated. Thanks! 2014-11-30 9:48 GMT-03:00 Niphlod <niph...@gmail.com>: > did you restart the webserver ? I don't think that changing pool_size at > runtime when connections are still open will make the number of active > connection dropped. > > On Friday, November 28, 2014 8:48:07 PM UTC+1, Lisandro wrote: >> >> Mmm... I see. That was my understanding in the first place. >> At that time I did the maths, I had 10 apps, each one using a >> pool_size of 3. In postgresql.conf max_connections was set to 80. >> However this morning, with those numbers, almost every of my websites >> was throwing intermitent HTTP 500 errors, and the error tickets were >> all the same: FATAL: remaining connection slots are reserved for >> non-replication superuser connections. >> >> Right now, I have almost 13 websites, all of them with pool_size in 3, >> and max_connections in 80. >> However, if I check the table "pg_stat_activity" I can see 65 >> connections, and I can see there is 5 connections per app. >> >> I've tried even setting pool_size to 1 for one of the apps, restarted >> database server and webserver, but again I check pg_stat_activity and >> I see 5 connections for that app. ¿Am I missing something too ovbious? >> >> >> 2014-11-28 14:25 GMT-03:00 Niphlod <nip...@gmail.com>: >> > >> > >> > On Friday, November 28, 2014 3:31:02 PM UTC+1, Lisandro wrote: >> >> >> >> I go back to this thread because today I ran with the same problem: >> >> postgresql reaching max_connection limits and, therefor, some of my >> websites >> >> throwing intermitent HTTP 500 errors (because web2py couldn't connect >> to the >> >> database). >> >> >> >> To remind, we are talking of a VPS with multiple instances of web2py >> >> running, all of them serving the same web2py app, each one connecting >> to a >> >> different postgresql database (however the database structure is the >> same >> >> accross all the databases). Each web2py instance is served by a >> lighttpd >> >> virtual host through fastcgi. Each virtual host (that is, each web2py >> >> instance) receives a different volume of traffic (that is obvious, >> they are >> >> different websites with different public). >> >> >> >> The original problem (the one that caused I post this question in the >> >> first place) was that the postgresql database server was reaching the >> >> "max_connections" limit and, in consecuence, some of the websites were >> >> throwing intermitent HTTP 500 errors (web2py couldn't connect to >> database). >> >> >> >> Then, the user oriented me with "pool_size" parameter of DAL >> constructor. >> >> Thanks again! >> >> I've been reading the web2py documentation about pooling [1] and I >> notice >> >> that it says that "When the next http request arrives, web2py tries to >> >> recycle a connection from the pool and use that for the new >> transaction. If >> >> there are no available connections in the pool, a new connection is >> >> established". >> >> So, if I didn't get it wrong, I deduce that with web2py's pooling >> >> mechanism I can't overcome the "max_connections" postgresql limit. >> That is >> >> because, no matter the size of the pool, if the pool is full and the >> website >> >> is receiving a lot of requests, new connetions will be created, and >> >> eventually the database server will reach the "max_conectios" limit. >> > >> > >> > no, you got it wrong again. pool_size=5 will create AT MOST 5 >> connections . >> > if a 6th is needed, users will wait for a connection to be freed. >> > if your postgresql accept at most 50 connections, do the math. >> > Every db = DAL(, pool_size=5) lying around will create AT MOST 5 >> > connections, and that means you can host 10 apps. >> > If you need 50 apps, set pool_size=1 and let users wait, or set >> > max_connections in postgres to a higher value. >> > >> > -- >> > Resources: >> > - http://web2py.com >> > - http://web2py.com/book (Documentation) >> > - http://github.com/web2py/web2py (Source code) >> > - https://code.google.com/p/web2py/issues/list (Report Issues) >> > --- >> > You received this message because you are subscribed to a topic in the >> > Google Groups "web2py-users" group. >> > To unsubscribe from this topic, visit >> > https://groups.google.com/d/topic/web2py/5RTO_RqCsus/unsubscribe. >> > To unsubscribe from this group and all its topics, send an email to >> > web2py+un...@googlegroups.com. >> > For more options, visit https://groups.google.com/d/optout. >> > -- > Resources: > - http://web2py.com > - http://web2py.com/book (Documentation) > - http://github.com/web2py/web2py (Source code) > - https://code.google.com/p/web2py/issues/list (Report Issues) > --- > You received this message because you are subscribed to a topic in the > Google Groups "web2py-users" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/web2py/5RTO_RqCsus/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > web2py+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
