pool_size==number of threads in a web2py process. I suggest to work around the problem by setting the number of threads to 1 in you flup server. I.e. no threading. You should also see smoother performance across applications on higher loads.
2014-11-30 15:51 GMT+01:00 Lisandro Rostagno <rostagnolisan...@gmail.com>: > 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. > -- 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.
