Thank you very much Niphlod and Michele for your help! Now I have a more
clear understanding of all this. I understand now why I was seeing 5
fastcgi processes regardless of the "max-procs" configuration (that was
because I was using fcgi_fork, and I was actually seeing one process and
its 5 children).
Michele, abount using pgbouncer when having more clients connections than
max postgresql connections, I think that's my case (correct me if I'm
wronk). All the sites I'm hosting are news sites, and ocassionally some of
them have sudden spikes in traffic. The last time one of the sites
presented a high peak on traffic, connections reached the postgresql
max_connections limit and, therefor, **all** the applications started
throwing intermitent HTTP 500 errors.
So I started my research to see what could I do to **avoid compromising
some apps when one of them is getting high traffic**. Above all, I want to
be able to **control "the limits" for every app**. I started setting
server.kbytes-per-second and connection.kbytes-per-second for every app.
However that didn't resolve the problem of high database connection
requests.
With the last info you gave me, I think now I can achieve my goal "playing
around" a little with this settings (please correct me if I'm worng):
- max-procs for the fastcgi of every app: I could set it to 1 for apps
with lower traffic, and set it to a higher value for apps with higher
traffic (of course, always checking stats to see what happens).
- pool_size pgbouncer parameter: I mean modifying this value for every
database, using a larger pool for apps with higher traffic, and viceversa.
The cool stuff here is that with pgbouncer I can check stats of clients,
pools, etc. My goal here is to set a limit for an app, and if a new
connection is requested but the pool is full, the client keeps waiting
until a connection is freed (no http 500 error).
I'll be trying these changes and I'll be posting here the results.
As always, any comment is really appreciated. Thank you again for the help!
2014-12-01 17:56 GMT-03:00 Michele Comitini <michele.comit...@gmail.com>:
>
> What you have here is lighttpd starting 1 forking flup server. The
> important part is this:
>
> from flup.server.fcgi_fork import WSGIServer
>
> With that you have a python interpreter for each request, up to a
> maximum. The default is 5 spare children, the default maximum is 50.
> Under lighttpd is also possible to use fcgi_single and "max_procs" = 5
> with similar results [I'd expect a slightly bigger memory footprint].
> Since you have a forking/multiprocess configuration, you need to have a
> single connection in the web2py connection pool so pool_size=1 is what you
> need, anything more is just a waste of resources and postgres connections.
> The max number of open connections should be (max_num_wsgi_proc[flup] *
> pool_size[web2py DAL]) * (max_procs[lighttpd] * num_applications[lighttpd]).
>
> About pgbouncer, IMHO you should use it only if you have n client and m
> max postgresql connections, and you have n > m. To speedup things you can
> use memcache/redis and/or a more complex setup with pgpoolII and multiple
> postgres backends.
>
> 2014-12-01 17:46 GMT+01:00 Lisandro Rostagno <rostagnolisan...@gmail.com>:
>
>> Sorry about the delay. I recently installed pgbouncer. I let postgresql
>> max_connection set to 80, and configure pgbouncer with a max_client_conn of
>> 1000 and a default_pool_size of 20.
>> Now when I check pg_stat_activity I can see different amounts of idle
>> connections per app, more accordingly with that app's traffic. What I mean
>> is that I can see more connections for the apps with higher volumes of
>> traffic and less connections for the apps with lower volumes of traffic.
>>
>> What I still don't understand is the "max_connections" setting of
>> postgresql vs the "max_client_conn" of pgbouncer. For what I've read in
>> [1] and [2] it's ok to set those variables for example in the way I did,
>> leting postgresql max_connections in an appropiated value (in my case,
>> using pgtune, 80 max_connections) and using a high value for
>> "max_client_conn" on pgbouncer configuration.
>>
>> What isnt' clear to me is: what will happen when one of the apps has more
>> than 20 active connections to pgbouncer and requests keep coming in? The
>> ideal (for me, in this case) would be that next requests just stay waiting
>> (browser saying "waiting for domain.com....").
>>
>>
>> In the other hand, related to Michele comment, right now I have every
>> flup server running with "max-procs" set to 1, this is how my lighttpd
>> virtual hosts look like:
>>
>> $HTTP["host"] == "diarioprimicia.com.ar" {
>> server.document-root = "/var/www/diarioprimicia"
>> server.dir-listing = "disable"
>> server.error-handler-404 = "/diarioprimicia.fcgi"
>> server.kbytes-per-second = 256
>> connection.kbytes-per-second = 128
>> accesslog.filename = "/var/log/lighttpd/diarioprimicia_access.log"
>> fastcgi.server = (
>> ".fcgi" => ("localhost" => (
>> "check-local" => "disable",
>> "max-procs" => 1,
>> "socket" => "/var/tmp/lighttpd/diarioprimicia.sock",
>> "bin-path" => "/var/www/diarioprimicia/diarioprimicia.fcgi")
>> )
>> )
>> }
>>
>>
>> Then the file indicated by "bin-path" contains the following:
>>
>> #!/usr/bin/python
>> import sys
>> import gluon.main
>> from flup.server.fcgi_fork import WSGIServer
>> application=gluon.main.wsgibase
>> WSGIServer(application).run()
>>
>>
>> Another "strange" thing I see (strange for me, because I don't fully
>> understand in) is that, regardless of setting "max-procs" to 1, when I use
>> pgrep to check for fastcgi processes I can see exactly 5 processes for
>> every app.
>>
>> I'm sorry to mix all this stuff in this post, if you think that I should
>> move it to other forums, let me know.
>> Thank you very much!
>>
>>
>>
>>
>> 2014-11-30 18:00 GMT-03:00 Michele Comitini <michele.comit...@gmail.com>:
>>
>> 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 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 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.
