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.
