p.s. by "no threading" I mean to use processes in place of threads. The number of processes is something you must tune based on server resources, 2xn where n is the number of cores is a safe choice.
2014-11-30 20:04 GMT+01:00 Michele Comitini <michele.comit...@gmail.com>: > 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.
