Re: [GENERAL] connection pooling, many users, many datasources

[Michael McInness]

Pgbouncer worked fine for this. Thanks.

On Wed, Jun 8, 2016 at 1:12 PM, Sameer Kumar 
wrote:

>
>
> On Thu, 9 Jun 2016, 12:50 a.m. Michael McInness, 
> wrote:
>
>> I am working with a system that uses JDBC and JNDI-based connection
>> pooling. There are currently many organizations that use the system. Each
>> of the organizations has multiple, individual system users.
>>
>> Currently, each entity has its own database and a corresponding
>> application-based datasource and connection pool.
>>
>> Because of the nature of the data, it is of utmost importance that each
>> organization's data is secure and inaccessible to other entities.
>>
>> Maintaining individual datasources/connection pools for each entity is
>> extremely cumbersome. It just doesn't seem like a good solution as the
>> number of entities grows to many dozens.
>>
>> And while handling connections on-demand would provide a dynamic
>> alternative, it also feels like a bad option as I would hate to lose the
>> performance and robustness of pooling connections.
>>
>
> Consider using a database side pool which can pool connections for each
> user and dbname pair. You might want to check pgpool and pgbouncer.
> With one of them you can get benefits of pooling and yet let
> applicationmake connection on the go as request comes in
>
>
>> How about using SET/RESET ROLE so I could connect to the db under a group
>> role and then changing roles per session to restrict access to
>> entity-specific schema (assuming permissions are set correctly in the
>> database)?
>>
>
> I am not sure if this is the reason or right use case for SET ROLE.
>
> Using different users (either with same app server or different) is the
> best way if ensuring data is not accessible across organizations.
>
>
>> Are there other options?
>>
>> Profuse appreciation for your thoughts and suggestions,
>>
>> Mick
>>
> --
> --
> Best Regards
> Sameer Kumar | DB Solution Architect
> *ASHNIK PTE. LTD.*
>
> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
>
> T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
>

Re: [GENERAL] connection pooling, many users, many datasources

[Sameer Kumar]

On Thu, 9 Jun 2016, 12:50 a.m. Michael McInness, 
wrote:

> I am working with a system that uses JDBC and JNDI-based connection
> pooling. There are currently many organizations that use the system. Each
> of the organizations has multiple, individual system users.
>
> Currently, each entity has its own database and a corresponding
> application-based datasource and connection pool.
>
> Because of the nature of the data, it is of utmost importance that each
> organization's data is secure and inaccessible to other entities.
>
> Maintaining individual datasources/connection pools for each entity is
> extremely cumbersome. It just doesn't seem like a good solution as the
> number of entities grows to many dozens.
>
> And while handling connections on-demand would provide a dynamic
> alternative, it also feels like a bad option as I would hate to lose the
> performance and robustness of pooling connections.
>

Consider using a database side pool which can pool connections for each
user and dbname pair. You might want to check pgpool and pgbouncer.
With one of them you can get benefits of pooling and yet let
applicationmake connection on the go as request comes in


> How about using SET/RESET ROLE so I could connect to the db under a group
> role and then changing roles per session to restrict access to
> entity-specific schema (assuming permissions are set correctly in the
> database)?
>

I am not sure if this is the reason or right use case for SET ROLE.

Using different users (either with same app server or different) is the
best way if ensuring data is not accessible across organizations.


> Are there other options?
>
> Profuse appreciation for your thoughts and suggestions,
>
> Mick
>
-- 
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

[GENERAL] connection pooling, many users, many datasources

[Michael McInness]

I am working with a system that uses JDBC and JNDI-based connection
pooling. There are currently many organizations that use the system. Each
of the organizations has multiple, individual system users.

Currently, each entity has its own database and a corresponding
application-based datasource and connection pool.

Because of the nature of the data, it is of utmost importance that each
organization's data is secure and inaccessible to other entities.

Maintaining individual datasources/connection pools for each entity is
extremely cumbersome. It just doesn't seem like a good solution as the
number of entities grows to many dozens.

And while handling connections on-demand would provide a dynamic
alternative, it also feels like a bad option as I would hate to lose the
performance and robustness of pooling connections.

How about using SET/RESET ROLE so I could connect to the db under a group
role and then changing roles per session to restrict access to
entity-specific schema (assuming permissions are set correctly in the
database)?

Are there other options?

Profuse appreciation for your thoughts and suggestions,

Mick

Re: [GENERAL] Connection pooling

[andy]

On 10/31/2013 3:24 PM, John R Pierce wrote:

On 10/31/2013 1:09 PM, andy wrote:

I'm not sure if geoserver is like openLayers/mapserver, but in the
later case (which I use), you can set it up to have the browser
(running openLayers) request multiple layers at the same time... and
on top of that each layer can be requested in tiles.  (That way when
you drag the map it only has to load new tiles, and not the entire
image, which makes the animations smooth)

Anyway, one webclient * 5 layers * tiles = lot and lots and lots of
web requests (which, would make lots of db hits).


sure, but those hits shouldn't each be using a new connection, no more
than 2-4 of them should be processed concurrently following http 1.1
guidelines, so no more than 2-4 connections should be required.

the behavior the OP descibed sounded like the tomcat applets aren't
closing their connections, thats broken behavior.





Ah, yes, you're right.  Lots and lots of requests, but not all of them 
concurrently.


-Andy



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[John R Pierce]

On 10/31/2013 1:09 PM, andy wrote:
I'm not sure if geoserver is like openLayers/mapserver, but in the 
later case (which I use), you can set it up to have the browser 
(running openLayers) request multiple layers at the same time... and 
on top of that each layer can be requested in tiles.  (That way when 
you drag the map it only has to load new tiles, and not the entire 
image, which makes the animations smooth)


Anyway, one webclient * 5 layers * tiles = lot and lots and lots of 
web requests (which, would make lots of db hits). 


sure, but those hits shouldn't each be using a new connection, no more 
than 2-4 of them should be processed concurrently following http 1.1 
guidelines, so no more than 2-4 connections should be required.


the behavior the OP descibed sounded like the tomcat applets aren't 
closing their connections, thats broken behavior.




--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[andy]

On 10/31/2013 11:15 AM, John R Pierce wrote:

On 10/31/2013 5:34 AM, si24 wrote:

It seems for each person that they seem to get about +-20 or more
connections each depending on there use of the map if they add the layers
that are overlyed over the map like zones etc...


that sounds really broken.





I'm not sure if geoserver is like openLayers/mapserver, but in the later 
case (which I use), you can set it up to have the browser (running 
openLayers) request multiple layers at the same time... and on top of 
that each layer can be requested in tiles.  (That way when you drag the 
map it only has to load new tiles, and not the entire image, which makes 
the animations smooth)


Anyway, one webclient * 5 layers * tiles = lot and lots and lots of web 
requests (which, would make lots of db hits).


If geoserver is like openLayers/mapserver, that is.

-Andy


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[John R Pierce]

On 10/31/2013 5:34 AM, si24 wrote:

It seems for each person that they seem to get about +-20 or more
connections each depending on there use of the map if they add the layers
that are overlyed over the map like zones etc...


that sounds really broken.



--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[si24]

for some reason it not always pooling the connections for reuse so I'm not
sure what or if I have left something out.

As I only have a 100 postgres connections and when you have six people
working on it at the same time the connections neary go all the way. Which
in turn starts making pink tiles from geoserver so then its not reading the
data properly.

Geoserver itself seems to use 17 connections from postgres which is reading
the 17 different tables that each layer reads to create the map.

has it some thing to do with geoserver do I need to change something there
first. I can extend the max connections in postgres but if we ended up
having alot more people looking at the map I'm not to sure where the balance
would be.

It seems for each person that they seem to get about +-20 or more
connections each depending on there use of the map if they add the layers
that are overlyed over the map like zones etc...



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776498.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[Gavin Flower]

On 01/11/13 00:10, Rémi Cura wrote:

Hey,
I might be completly wrong, but when you say

"get the connections to close if they are not being used",

I'd say that it is a bad client design to not close a connection when 
it doesn't need it anymore.
The client should retrieve the data or close when not using after a 
certain amount of time.


What you are trying to do is garbage collector.

Cheers,
Rémi-C


2013/10/31 si24 mailto:smrcoutt...@gmail.com>>

I'm not 100% sure I follow in that part of if its the client cause
currently
when I run it on my own computer it does the same thing. Only when
I stop
tomcat and start it again then i get the 3 default connection that
postgres
has set up. our server does the same thing.





--
View this message in context:

http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776490.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


making & breaking connections costs elapsed time & processor activity 
etc. - which is why they should be pooled for reuse



Cheers,
Gavin

Re: [GENERAL] Connection pooling

[Rémi Cura]

Hey,
I might be completly wrong, but when you say

"get the connections to close if they are not being used",

I'd say that it is a bad client design to not close a connection when it
doesn't need it anymore.
The client should retrieve the data or close when not using after a certain
amount of time.

What you are trying to do is garbage collector.

Cheers,
Rémi-C


2013/10/31 si24 

> I'm not 100% sure I follow in that part of if its the client cause
> currently
> when I run it on my own computer it does the same thing. Only when I stop
> tomcat and start it again then i get the 3 default connection that postgres
> has set up. our server does the same thing.
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776490.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Connection pooling

[si24]

I'm not 100% sure I follow in that part of if its the client cause currently
when I run it on my own computer it does the same thing. Only when I stop
tomcat and start it again then i get the 3 default connection that postgres
has set up. our server does the same thing.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776490.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[si24]

currently my pg bouncer.ini looks like this :


[databases]
manifold = host=localhost port=5432 dbname=manifold user=postgrest
password=123ert
[pgbouncer]
logfile = C:\Program Files\PostgreSQL\log\pgbouncer.log
pidfile = C:\Program Files\PostgreSQL\log\pgbouncer.pid

listen_addr = *
listen_port = 6432
admin_users = test
stats_users = test
pool_mode = transaction
server_reset_query = DISCARD ALL
ignore_startup_parameters = application_name
max_client_conn = 400
default_pool_size = 20

not to sure if I need to change any of these:

; how many additional connection to allow in case of trouble
;reserve_pool_size = 5

; if a clients needs to wait more than this many seconds, use reserve pool
;reserve_pool_timeout = 3

; log if client connects or server connection is made
;log_connections = 1

; log if and why connection was closed
;log_disconnections = 1

; log error messages pooler sends to clients
;log_pooler_errors = 1


; If off, then server connections are reused in LIFO manner
;server_round_robin = 0

;;;
;;; Timeouts
;;;

;; Close server connection if its been connected longer.
;server_lifetime = 1200

;; Close server connection if its not been used in this time.
;; Allows to clean unnecessary connections from pool after peak.
;server_idle_timeout = 60

;; Cancel connection attempt if server does not answer takes longer.
;server_connect_timeout = 15

;; If server login failed (server_connect_timeout or auth failure)
;; then wait this many second.
;server_login_retry = 15

;; Dangerous.  Server connection is closed if query does not return
;; in this time.  Should be used to survive network problems,
;; _not_ as statement_timeout. (default: 0)
;query_timeout = 0

;; Dangerous.  Client connection is closed if the query is not assigned
;; to a server in this time.  Should be used to limit the number of queued
;; queries in case of a database or network failure. (default: 0)
;query_wait_timeout = 0

;; Dangerous.  Client connection is closed if no activity in this time.
;; Should be used to survive network problems. (default: 0)
;client_idle_timeout = 0

;; Disconnect clients who have not managed to log in after connecting
;; in this many seconds.
;client_login_timeout = 60

;; Clean automatically created database entries (via "*") if they
;; stay unused in this many seconds.
; autodb_idle_timeout = 60

;;;
;;; Low-level tuning options
;;;

;; buffer for streaming packets
;pkt_buf = 2048

;; man 2 listen
;listen_backlog = 128

;; networking options, for info: man 7 tcp

;; Linux: notify program about new connection only if there
;; is also data received.  (Seconds to wait.)
;; On Linux the default is 45, on other OS'es 0.
;tcp_defer_accept = 0

;; In-kernel buffer size (Linux default: 4096)
;tcp_socket_buffer = 0

;; whether tcp keepalive should be turned on (0/1)
;tcp_keepalive = 1

;; following options are Linux-specific.
;; they also require tcp_keepalive=1

;; count of keepaliva packets
;tcp_keepcnt = 0

;; how long the connection can be idle,
;; before sending keepalive packets
;tcp_keepidle = 0

;; The time between individual keepalive probes.
;tcp_keepintvl = 0

;; DNS lookup caching time
;dns_max_ttl = 15

;; DNS zone SOA lookup period
;dns_zone_check_period = 0


I am currently running postgres 9.2 with geoserver 2.4.1 apache tomcat.

I need to know how to close the connections that is being used or is the
pgbouncer not setup correctly.
I followed a pdf tutorial on how to set it up and all seemed to be working
including in the adminatrator command section.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776489.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[Rémi Cura]

Isn'it a client problem?

It should be client application closing connection when done with data
retrieval, and not the other way around?

Cheers,
Rémi-C


2013/10/31 Jayadevan 

> I have never used pgbouncer myself. But my guess is you have to look at the
> Timeout parameters in the configuration file.
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776481.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Connection pooling

[Jayadevan]

I have never used pgbouncer myself. But my guess is you have to look at the
Timeout parameters in the configuration file.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776481.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[si24]

I have now installed the pgbouncer and it seemed to help a little but it did
not bring the connections back down once the connections ended or people
stopped using it. although it would fluctuate up and down on occasion but it
still reached 100 connections.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776479.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[Gavin Flower]

On 31/10/13 03:13, si24 wrote:

I have geoserver connected to a postgres database and so far I have been
reading that I would need some sort of connection pooling but how do I go
about doing that when it seems most of the connection pooling
aplications/programs seem to only run on linux. I have a windows machine.

otherwise is there some other sort of way that i can get the connections to
close if they are not being used so as not to use all the connection on
postgresql which is currently at 100 even if I set the max connections to
1 I don't think that's going to be enough and I don't think thats a
proper way to solve the problem I am having currently.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


I woulds serious suggest that you upgrade to Linux as soon as 
practicable - for performance and security, amongst many other reasons!



Cheers,
Gavin


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[Adrian Klaver]

On 10/30/2013 07:13 AM, si24 wrote:

I have geoserver connected to a postgres database and so far I have been
reading that I would need some sort of connection pooling but how do I go
about doing that when it seems most of the connection pooling
aplications/programs seem to only run on linux. I have a windows machine.


If you installed Postgres from the Graphical Installer would the 
following be of help. Note: at the bottom of the page is a downloadable PDF:


http://www.enterprisedb.com/resources-community/tutorials-quickstarts/all-platforms/how-setup-pgbouncer-connection-pooling-postg



--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[Igor Neyman]

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of si24
> Sent: Wednesday, October 30, 2013 10:14 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Connection pooling
> 
> I have geoserver connected to a postgres database and so far I have been
> reading that I would need some sort of connection pooling but how do I go
> about doing that when it seems most of the connection pooling
> aplications/programs seem to only run on linux. I have a windows machine.
> 
> otherwise is there some other sort of way that i can get the connections to
> close if they are not being used so as not to use all the connection on
> postgresql which is currently at 100 even if I set the max connections to
> 1 I don't think that's going to be enough and I don't think thats a proper
> way to solve the problem I am having currently.
> 

PgBouncer works fine on Windows, and does pretty good job.

Regards,
Igor Neyman


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[si24]

yes they are as well as the tomcat which is where geoserver is running from.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776386.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[Rémi Cura]

Are the geoserver and postgres on same computer?

Cheers,

Rémi-C


2013/10/30 si24 

> I'm not sure if its suppose to be under general so please let me know if I
> need to move it to another topic area please.
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776382.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Connection pooling

[si24]

I'm not sure if its suppose to be under general so please let me know if I
need to move it to another topic area please.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378p5776382.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Connection pooling

[si24]

I have geoserver connected to a postgres database and so far I have been
reading that I would need some sort of connection pooling but how do I go
about doing that when it seems most of the connection pooling
aplications/programs seem to only run on linux. I have a windows machine.

otherwise is there some other sort of way that i can get the connections to
close if they are not being used so as not to use all the connection on
postgresql which is currently at 100 even if I set the max connections to
1 I don't think that's going to be enough and I don't think thats a
proper way to solve the problem I am having currently.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Connection-pooling-tp5776378.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Guillaume Lelarge]

On Fri, 2011-10-07 at 15:45 -0400, Brandon Phelps wrote:
> Forgive me, I'm still a bit confused by how max_pool works with 
> num_init_children.  First you said that at most I can have 32 clients sending 
> queries, but then you said that each process can handle 4 different 
> connections... so does this mean that I can have 128 connections from pgpool 
> to my postgresql database?
> 

That's right. You'll have, from pgpool, a maximum of
num_init_children*max_pool connections (IOW, 128 with your connections).
Out of these 128 connections, only 32 will be active at one time.

I'll try to explain it another way. When client C1 connects to
PostgreSQL via pgpool, it will use one of the four possible connections
of a pooler process.


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Brandon Phelps]

Forgive me, I'm still a bit confused by how max_pool works with 
num_init_children.  First you said that at most I can have 32 clients sending 
queries, but then you said that each process can handle 4 different 
connections... so does this mean that I can have 128 connections from pgpool to 
my postgresql database?

Sorry, not quite understanding.

On 10/07/2011 02:05 PM, Guillaume Lelarge wrote:

On Fri, 2011-10-07 at 13:51 -0400, Brandon Phelps wrote:

So we decided to go with pgpool-II.  The documentation is a little lacking for 
pgpool-II so I have one question:

How are connections handled once the default levels are reached?  Here are my 
pgpool settings:

num_init_children = 32
max_pool = 4



This configuration means you can have at most 32 clients sending queries
at the same time to PostgreSQL via pgpool. With a max_pool of 4, each
pgpool process can handle four different connections to the same server:
they could differ either by the database name or by the user name.


This creates 32 child processes when we start pgpool which I understand.  Each 
time I browse to a page from our web app and do a netstat -an on the web server 
(running pgpool) I see an additional connection to the database server, which 
looks good.  I assume that once 32 connections are opened at once then pgpool 
will start re-using them, based on the num_init_children * max_pool... But 
since 32 * 4 = 128, what will happen on the 129th connection?  Will a new child 
get created, allowing for 4 more connections (1 * max_pool), or will that 
connection be denied?



Connection is not denied (that would be the behaviour of PostgreSQL).
Connection is on hold waiting for a pgpool process to be available.
Meaning you don't want long connections.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Guillaume Lelarge]

On Fri, 2011-10-07 at 13:51 -0400, Brandon Phelps wrote:
> So we decided to go with pgpool-II.  The documentation is a little lacking 
> for pgpool-II so I have one question:
> 
> How are connections handled once the default levels are reached?  Here are my 
> pgpool settings:
> 
> num_init_children = 32
> max_pool = 4
> 

This configuration means you can have at most 32 clients sending queries
at the same time to PostgreSQL via pgpool. With a max_pool of 4, each
pgpool process can handle four different connections to the same server:
they could differ either by the database name or by the user name.

> This creates 32 child processes when we start pgpool which I understand.  
> Each time I browse to a page from our web app and do a netstat -an on the web 
> server (running pgpool) I see an additional connection to the database 
> server, which looks good.  I assume that once 32 connections are opened at 
> once then pgpool will start re-using them, based on the num_init_children * 
> max_pool... But since 32 * 4 = 128, what will happen on the 129th connection? 
>  Will a new child get created, allowing for 4 more connections (1 * 
> max_pool), or will that connection be denied?
> 

Connection is not denied (that would be the behaviour of PostgreSQL).
Connection is on hold waiting for a pgpool process to be available.
Meaning you don't want long connections.


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Brandon Phelps]

So we decided to go with pgpool-II.  The documentation is a little lacking for 
pgpool-II so I have one question:

How are connections handled once the default levels are reached?  Here are my 
pgpool settings:

num_init_children = 32
max_pool = 4

This creates 32 child processes when we start pgpool which I understand.  Each 
time I browse to a page from our web app and do a netstat -an on the web server 
(running pgpool) I see an additional connection to the database server, which 
looks good.  I assume that once 32 connections are opened at once then pgpool 
will start re-using them, based on the num_init_children * max_pool... But 
since 32 * 4 = 128, what will happen on the 129th connection?  Will a new child 
get created, allowing for 4 more connections (1 * max_pool), or will that 
connection be denied?

Thanks in advance,
Brandon

On 10/06/2011 04:07 PM, Adam Cornett wrote:


On Thu, Oct 6, 2011 at 3:57 PM, Brandon Phelps mailto:bphe...@gls.com>> wrote:

Can anyone recommend a good solution for connection pooling?  Here is our 
setup:

2 PostgreSQL 9.1 servers (1 master, 1 hot standby).
1 Apache 2.2.17

We have a pretty extensive web application running on the apache server 
that talks to both of the database servers.  Updates and small tasks (simple 
selects, etc) are directed to the master DB server while large reports that can 
take a while to run are directed to the hot standby, as not to affect 
performance of the master.

Each page of the web app generally make a single connection to the database 
with the exception being the complicated reports which first make a connection 
to the master, verify that the user is allowed to access the page in question, 
close that connection, then open another connection to the hot standby for the 
report itself.

One connection per page is not all that bad however the end users who make 
use of the web app are quite familiar with it and often fly through the pages 
very fast.  We would like to implement some type of connection pooling so that 
these database connections (from web server to the DB servers) do not have to 
get created and torn down constantly.

I have checked out the pg_pool website however was not very impressed with 
the documentation provided.  Is pg_pool going to be our best solution or is 
there something better?  Any advice would be appreciated.

Thanks,
Brandon

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org 
)
To make changes to your subscription:
http://www.postgresql.org/__mailpref/pgsql-general 




If you want to do load balancing between your database servers (split reads 
between the master and slave) or have auto failover then Pgpool-II is going to 
be your best bet.  I just did a similar setup and the documentation isn't the 
best, but some googling turned up a few guides that I was able to jump between 
to get it setup and going.

If you just want all of the DB traffic to go to the master (and deal with 
failover on your own) you can use pgbouncer, which is much simpler, but also 
less feature-rich.

Adam


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Toby Corkindale]

On 07/10/11 06:57, Brandon Phelps wrote:

Can anyone recommend a good solution for connection pooling? Here is our
setup:

2 PostgreSQL 9.1 servers (1 master, 1 hot standby).
1 Apache 2.2.17

We have a pretty extensive web application running on the apache server
that talks to both of the database servers. Updates and small tasks
(simple selects, etc) are directed to the master DB server while large
reports that can take a while to run are directed to the hot standby, as
not to affect performance of the master.

Each page of the web app generally make a single connection to the
database with the exception being the complicated reports which first
make a connection to the master, verify that the user is allowed to
access the page in question, close that connection, then open another
connection to the hot standby for the report itself.

One connection per page is not all that bad however the end users who
make use of the web app are quite familiar with it and often fly through
the pages very fast. We would like to implement some type of connection
pooling so that these database connections (from web server to the DB
servers) do not have to get created and torn down constantly.

I have checked out the pg_pool website however was not very impressed
with the documentation provided. Is pg_pool going to be our best
solution or is there something better? Any advice would be appreciated.


Depending on what software your application is written in, there will 
probably be support for persistent, pooled database connections.
I think you should look at using those for at least the master 
connections, if not both.


I know the DB connections in things like the Catalyst or Dancer 
frameworks will do that by default; if you've rolled your own web 
framework then you may have some more work to do though.


Cheers,
Toby


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Adam Cornett]

On Thu, Oct 6, 2011 at 3:57 PM, Brandon Phelps  wrote:

> Can anyone recommend a good solution for connection pooling?  Here is our
> setup:
>
> 2 PostgreSQL 9.1 servers (1 master, 1 hot standby).
> 1 Apache 2.2.17
>
> We have a pretty extensive web application running on the apache server
> that talks to both of the database servers.  Updates and small tasks (simple
> selects, etc) are directed to the master DB server while large reports that
> can take a while to run are directed to the hot standby, as not to affect
> performance of the master.
>
> Each page of the web app generally make a single connection to the database
> with the exception being the complicated reports which first make a
> connection to the master, verify that the user is allowed to access the page
> in question, close that connection, then open another connection to the hot
> standby for the report itself.
>
> One connection per page is not all that bad however the end users who make
> use of the web app are quite familiar with it and often fly through the
> pages very fast.  We would like to implement some type of connection pooling
> so that these database connections (from web server to the DB servers) do
> not have to get created and torn down constantly.
>
> I have checked out the pg_pool website however was not very impressed with
> the documentation provided.  Is pg_pool going to be our best solution or is
> there something better?  Any advice would be appreciated.
>
> Thanks,
> Brandon
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general
>


If you want to do load balancing between your database servers (split reads
between the master and slave) or have auto failover then Pgpool-II is going
to be your best bet.  I just did a similar setup and the documentation isn't
the best, but some googling turned up a few guides that I was able to jump
between to get it setup and going.

If you just want all of the DB traffic to go to the master (and deal with
failover on your own) you can use pgbouncer, which is much simpler, but also
less feature-rich.

Adam

[GENERAL] Connection Pooling

[Brandon Phelps]

Can anyone recommend a good solution for connection pooling?  Here is our setup:

2 PostgreSQL 9.1 servers (1 master, 1 hot standby).
1 Apache 2.2.17

We have a pretty extensive web application running on the apache server that 
talks to both of the database servers.  Updates and small tasks (simple 
selects, etc) are directed to the master DB server while large reports that can 
take a while to run are directed to the hot standby, as not to affect 
performance of the master.

Each page of the web app generally make a single connection to the database 
with the exception being the complicated reports which first make a connection 
to the master, verify that the user is allowed to access the page in question, 
close that connection, then open another connection to the hot standby for the 
report itself.

One connection per page is not all that bad however the end users who make use 
of the web app are quite familiar with it and often fly through the pages very 
fast.  We would like to implement some type of connection pooling so that these 
database connections (from web server to the DB servers) do not have to get 
created and torn down constantly.

I have checked out the pg_pool website however was not very impressed with the 
documentation provided.  Is pg_pool going to be our best solution or is there 
something better?  Any advice would be appreciated.

Thanks,
Brandon

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Merlin Moncure]

On Tue, Apr 6, 2010 at 1:09 PM, David Kerr  wrote:
> On Mon, Apr 05, 2010 at 10:44:53PM -0400, Merlin Moncure wrote:
> - pgbouncer is totally transparent.  I manage quite a few databases and
> - I use it (w/session mode) so I can psql to a single host (localhost),
> - and bounce between different databases.  Like I said earlier, you have
> - discreet pools based on role -- otherwise there would be no really
> - good way to control the role your queries would operate under.  This
> - will keep your etl from drilling your box, and if you keep your
> - pool_size under the number of cores you have, you will have some
> - available if things get really dicey, which is nice.
>
> right, so that's kind of my issue, the ETL process will log in as 1 user, and 
> connect to 1 database
> wouldn't that mean that if i set max_pool_size = 30 then i'd have at max 30 
> connections allowed to
> the DB from that user?

yes. pool_size connections per role per db. unless you hardcode role
in the bouncer->backend connection string then it's just per db, and
the libpq->bouncer role is only used to auth to bouncer than ignored
iirc.

bouncer uses libevent, so with epoll on linux can give you excellent
performance.  that also makes it single threaded, which is extremely
nice.   it's pretty hard to max out a bouncer cpu core before the
database bottlenecks you, but on your hardware things might be
different.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[David Kerr]

On Mon, Apr 05, 2010 at 09:46:45PM -0600, Scott Marlowe wrote:
- On Mon, Apr 5, 2010 at 2:36 PM, David Kerr  wrote:
- > My app will have over 10k concurrent users. I have huge servers 32 cores 
(64bit), 64GB ram. RedHat linux.
- >
- > Those 10k users will all be logging in as one of 5 application users.
- 
- You should probably also look into memcached to take a lot of the read
- load off of your databases.
- 

Definitely, we've got a memcache hooked into Hibernate (or so i'm told) for the 
biggest app.

thanks!

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[David Kerr]

On Mon, Apr 05, 2010 at 10:44:53PM -0400, Merlin Moncure wrote:
- On Mon, Apr 5, 2010 at 4:36 PM, David Kerr  wrote:
- > On Sat, Apr 03, 2010 at 09:32:25PM -0400, Merlin Moncure wrote:
- > Based on a lot of the comments i've gotten here, I'm starting to think that 
I've got the wrong idea about
- > connection pools and pooling in general. So, let me lay out some of my 
assumptions and my problem and
- > maybe we can go from there...
- >
- > My app will have over 10k concurrent users. I have huge servers 32 cores 
(64bit), 64GB ram. RedHat linux.
- >
- > Those 10k users will all be logging in as one of 5 application users.
- >
- > From following this list, and talking to our PG consultants, I know that 
for that many connecitons I need
- > to have a connection pooler. Because with postgres you shouldn't set 
max_connections much higher than 2000
- > (which is pushing it)
- 
- This is correct.  If you go with pgbouncer, you would want to use
- transaction pooling mode obviously.

oh, interesting, I had been looking at session mode, I'll have to check out 
transaction mode.

- > For the 5th application, an ETL job that could have as many as 1000 
concurrent processes/connections,
- > i don't have a java container. it's just a raw jar file that gets run via 
java .
- >
- > That's what I'm aiming to pool, and i'd like to do it without modifying the 
code if possible.
- 
- pgbouncer is totally transparent.  I manage quite a few databases and
- I use it (w/session mode) so I can psql to a single host (localhost),
- and bounce between different databases.  Like I said earlier, you have
- discreet pools based on role -- otherwise there would be no really
- good way to control the role your queries would operate under.  This
- will keep your etl from drilling your box, and if you keep your
- pool_size under the number of cores you have, you will have some
- available if things get really dicey, which is nice.

right, so that's kind of my issue, the ETL process will log in as 1 user, and 
connect to 1 database
wouldn't that mean that if i set max_pool_size = 30 then i'd have at max 30 
connections allowed to
the DB from that user?

- caveats:
- *) no openssl, but stunnel works well (you may have to grab a recent stunnel)
- *) transaction mode blocks use of certain database features, like
- notifies.  again, doesn't sound too bad for you
- 
- doesn't sound like you need openssl though.  If you have the ability
- to set up a test environment, I'd set it up and give it a shot.

right i wouldn't need either of those.

Thanks!

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Scott Marlowe]

On Mon, Apr 5, 2010 at 2:36 PM, David Kerr  wrote:
> My app will have over 10k concurrent users. I have huge servers 32 cores 
> (64bit), 64GB ram. RedHat linux.
>
> Those 10k users will all be logging in as one of 5 application users.

You should probably also look into memcached to take a lot of the read
load off of your databases.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Merlin Moncure]

On Mon, Apr 5, 2010 at 4:36 PM, David Kerr  wrote:
> On Sat, Apr 03, 2010 at 09:32:25PM -0400, Merlin Moncure wrote:
> -
> - I have a lot of respect for pgbouncer (haven't used pgpool).  One
> - possible way to do what you're thinking is to rotate the pool on user.
> -  In bouncer each database role gets its own pool (if you understand
> - how transaction mode works you can see why it has to work this way).
> - Not sure if this is helpful.  Why are you trying to separate the pools
> - like that?
> -
> - merlin
>
> Based on a lot of the comments i've gotten here, I'm starting to think that 
> I've got the wrong idea about
> connection pools and pooling in general. So, let me lay out some of my 
> assumptions and my problem and
> maybe we can go from there...
>
> My app will have over 10k concurrent users. I have huge servers 32 cores 
> (64bit), 64GB ram. RedHat linux.
>
> Those 10k users will all be logging in as one of 5 application users.
>
> From following this list, and talking to our PG consultants, I know that for 
> that many connecitons I need
> to have a connection pooler. Because with postgres you shouldn't set 
> max_connections much higher than 2000
> (which is pushing it)

This is correct.  If you go with pgbouncer, you would want to use
transaction pooling mode obviously.

> For the 5th application, an ETL job that could have as many as 1000 
> concurrent processes/connections,
> i don't have a java container. it's just a raw jar file that gets run via 
> java .
>
> That's what I'm aiming to pool, and i'd like to do it without modifying the 
> code if possible.

pgbouncer is totally transparent.  I manage quite a few databases and
I use it (w/session mode) so I can psql to a single host (localhost),
and bounce between different databases.  Like I said earlier, you have
discreet pools based on role -- otherwise there would be no really
good way to control the role your queries would operate under.  This
will keep your etl from drilling your box, and if you keep your
pool_size under the number of cores you have, you will have some
available if things get really dicey, which is nice.

caveats:
*) no openssl, but stunnel works well (you may have to grab a recent stunnel)
*) transaction mode blocks use of certain database features, like
notifies.  again, doesn't sound too bad for you

doesn't sound like you need openssl though.  If you have the ability
to set up a test environment, I'd set it up and give it a shot.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[David Kerr]

On Sat, Apr 03, 2010 at 09:32:25PM -0400, Merlin Moncure wrote:
- On Fri, Mar 26, 2010 at 5:17 PM, David Kerr  wrote:
- > Howdy all,
- >
- > I have some apps that are connecting to my DB via direct JDBC and I'd like 
to pool their connections.
- >
- > I've been looking at poolers for a while, and pgbouncer and pgpool-ii seem 
to be some of the most popular, so
- > i've started with those.
- >
- >
- > I'm setting up pgbouncer, and i've hit a bit of a snag. Hopefully someone 
can tell me if pgbouncer or pgpool are
- > capable of this (and if so, how to do it) or alternatly a pooler that can...
- >
- > What I'd like to be able to do is this (not using pooler syntax, this is 
just a high level of what i want to achive)
- >
- > Say i set max pool size = 10 connections. and max # of pools = 5.
- >
- > That means that i should have 5 connections to my database covering 50 
connections total.
- >
- > I can't really seem to make that work with pgbouncer without naming the 
pools separetly. (pool1 = dbname = a, pool2 = dbname =a)
- > which means my app is tied to a pool (or has to specifically code to rotate 
pools...) which is not really desireable.
- 
- I have a lot of respect for pgbouncer (haven't used pgpool).  One
- possible way to do what you're thinking is to rotate the pool on user.
-  In bouncer each database role gets its own pool (if you understand
- how transaction mode works you can see why it has to work this way).
- Not sure if this is helpful.  Why are you trying to separate the pools
- like that?
- 
- merlin

Based on a lot of the comments i've gotten here, I'm starting to think that 
I've got the wrong idea about
connection pools and pooling in general. So, let me lay out some of my 
assumptions and my problem and
maybe we can go from there...

My app will have over 10k concurrent users. I have huge servers 32 cores 
(64bit), 64GB ram. RedHat linux.

Those 10k users will all be logging in as one of 5 application users. 

>From following this list, and talking to our PG consultants, I know that for 
>that many connecitons I need 
to have a connection pooler. Because with postgres you shouldn't set 
max_connections much higher than 2000 
(which is pushing it) 

For 4 out of the 5 applications, we're using Geronimo which has it's own pooler 
in the manner that
I've described above. 

For the 5th application, an ETL job that could have as many as 1000 concurrent 
processes/connections,
i don't have a java container. it's just a raw jar file that gets run via java 
.

That's what I'm aiming to pool, and i'd like to do it without modifying the 
code if possible.

Thanks

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Halil Türker Özdamar]

We are using proxool for many deployments, we were using DBCP but proxool is
better in terms of features.

On Mon, Mar 29, 2010 at 10:34 AM, Wappler, Robert wrote:

> On 2010-03-29, David Kerr wrote:
>
> > On 3/27/2010 12:46 AM, John R Pierce wrote:
> >> Allan Kamau wrote:
> >>> You may also have a look at Commons DBCP from Apache software
> >>> foundation, "http://commons.apache.org/dbcp/";. I have used it for a
> >>> few projects and have had no problems.
> >>
> >> for that matter, JDBC has its own connection pooling in java.
> >>
> >>
> >>
> >
> > It looks like both of those solutions require a coding change. I'm
> > hoping for a middleware solution similar to pgpool/pgbouncer.
> >
>
> I'm using proxool for JDBC-connection pooling. It behaves as a usual
> JDBC-Driver or DataSource should do. The configuration can be loaded
> statically when starting the application.
>
> --
> Robert...
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Connection Pooling

[Merlin Moncure]

On Fri, Mar 26, 2010 at 5:17 PM, David Kerr  wrote:
> Howdy all,
>
> I have some apps that are connecting to my DB via direct JDBC and I'd like to 
> pool their connections.
>
> I've been looking at poolers for a while, and pgbouncer and pgpool-ii seem to 
> be some of the most popular, so
> i've started with those.
>
>
> I'm setting up pgbouncer, and i've hit a bit of a snag. Hopefully someone can 
> tell me if pgbouncer or pgpool are
> capable of this (and if so, how to do it) or alternatly a pooler that can...
>
> What I'd like to be able to do is this (not using pooler syntax, this is just 
> a high level of what i want to achive)
>
> Say i set max pool size = 10 connections. and max # of pools = 5.
>
> That means that i should have 5 connections to my database covering 50 
> connections total.
>
> I can't really seem to make that work with pgbouncer without naming the pools 
> separetly. (pool1 = dbname = a, pool2 = dbname =a)
> which means my app is tied to a pool (or has to specifically code to rotate 
> pools...) which is not really desireable.

I have a lot of respect for pgbouncer (haven't used pgpool).  One
possible way to do what you're thinking is to rotate the pool on user.
 In bouncer each database role gets its own pool (if you understand
how transaction mode works you can see why it has to work this way).
Not sure if this is helpful.  Why are you trying to separate the pools
like that?

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Lew]

Allan Kamau wrote:

You may also have a look at Commons DBCP from Apache software
foundation, "http://commons.apache.org/dbcp/";. I have used it for a
few projects and have had no problems.


John R Pierce wrote:

for that matter, JDBC has its own connection pooling in java.


David Kerr wrote:
It looks like both of those solutions require a coding change. I'm 
hoping for a middleware solution similar to pgpool/pgbouncer.


How's that?

If JDBC has its own connection pooling and you're already using JDBC, voilà! 
no change needed, right?


If you're using a DataSource for JDBC, then it's a configuration change, not a 
code change, to switch to different databases, including to a different brand 
like Oracle instead of PG, never mind to switch between a pooled or unpooled 
connection.


Most app servers, including Tomcat, provide DBCP inbuilt with no further 
effort on the programmer's or even the deployer's part.


So to what coding changes do you refer?

--
Lew

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Wappler, Robert]

On 2010-03-29, David Kerr wrote:
 
> On 3/27/2010 12:46 AM, John R Pierce wrote:
>> Allan Kamau wrote:
>>> You may also have a look at Commons DBCP from Apache software
>>> foundation, "http://commons.apache.org/dbcp/";. I have used it for a
>>> few projects and have had no problems.
>> 
>> for that matter, JDBC has its own connection pooling in java.
>> 
>> 
>> 
> 
> It looks like both of those solutions require a coding change. I'm
> hoping for a middleware solution similar to pgpool/pgbouncer.
> 

I'm using proxool for JDBC-connection pooling. It behaves as a usual
JDBC-Driver or DataSource should do. The configuration can be loaded
statically when starting the application.

-- 
Robert...
 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[David Kerr]

On 3/27/2010 12:46 AM, John R Pierce wrote:

Allan Kamau wrote:

You may also have a look at Commons DBCP from Apache software
foundation, "http://commons.apache.org/dbcp/";. I have used it for a
few projects and have had no problems.


for that matter, JDBC has its own connection pooling in java.





It looks like both of those solutions require a coding change. I'm 
hoping for a middleware solution similar to pgpool/pgbouncer.


Thanks!

Dave

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[John R Pierce]

Allan Kamau wrote:

You may also have a look at Commons DBCP from Apache software
foundation, "http://commons.apache.org/dbcp/";. I have used it for a
few projects and have had no problems.
  


for that matter, JDBC has its own connection pooling in java.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection Pooling

[Allan Kamau]

On Fri, Mar 26, 2010 at 11:17 PM, David Kerr  wrote:
> Howdy all,
>
> I have some apps that are connecting to my DB via direct JDBC and I'd like to 
> pool their connections.
>
> I've been looking at poolers for a while, and pgbouncer and pgpool-ii seem to 
> be some of the most popular, so
> i've started with those.
>
>
> I'm setting up pgbouncer, and i've hit a bit of a snag. Hopefully someone can 
> tell me if pgbouncer or pgpool are
> capable of this (and if so, how to do it) or alternatly a pooler that can...
>
> What I'd like to be able to do is this (not using pooler syntax, this is just 
> a high level of what i want to achive)
>
> Say i set max pool size = 10 connections. and max # of pools = 5.
>
> That means that i should have 5 connections to my database covering 50 
> connections total.
>
> I can't really seem to make that work with pgbouncer without naming the pools 
> separetly. (pool1 = dbname = a, pool2 = dbname =a)
> which means my app is tied to a pool (or has to specifically code to rotate 
> pools...) which is not really desireable.
>
> is what i'm looking to do possible in pgbouncer or pgpool? or anything?
>
> Thanks
>
> Dave
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


You may also have a look at Commons DBCP from Apache software
foundation, "http://commons.apache.org/dbcp/";. I have used it for a
few projects and have had no problems.


Allan.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Connection Pooling

[David Kerr]

Howdy all,

I have some apps that are connecting to my DB via direct JDBC and I'd like to 
pool their connections.

I've been looking at poolers for a while, and pgbouncer and pgpool-ii seem to 
be some of the most popular, so
i've started with those.


I'm setting up pgbouncer, and i've hit a bit of a snag. Hopefully someone can 
tell me if pgbouncer or pgpool are 
capable of this (and if so, how to do it) or alternatly a pooler that can...

What I'd like to be able to do is this (not using pooler syntax, this is just a 
high level of what i want to achive)

Say i set max pool size = 10 connections. and max # of pools = 5. 

That means that i should have 5 connections to my database covering 50 
connections total.

I can't really seem to make that work with pgbouncer without naming the pools 
separetly. (pool1 = dbname = a, pool2 = dbname =a)
which means my app is tied to a pool (or has to specifically code to rotate 
pools...) which is not really desireable.

is what i'm looking to do possible in pgbouncer or pgpool? or anything?

Thanks

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection pooling

[Max Zorloff]

On Sat, 08 Sep 2007 19:28:52 +0400, Scott Marlowe  
<[EMAIL PROTECTED]> wrote:



On 9/7/07, Max Zorloff <[EMAIL PROTECTED]> wrote:
On Fri, 07 Sep 2007 10:58:36 +0400, Marko Kreen <[EMAIL PROTECTED]>  
wrote:


>> The pgpool (I tried 3.1, 3.4 and pgpool-II 1.2) works fine but has  
the

>> following problem - after some time it
>> just "hangs", and if I try to connect to it with psql it just hangs
>> indefinitely. After restart
>> it works fine again. I turned off persistent connections in php so  
it's

>> not that. Does anybody
>> have the same problem?
>
> All symptoms point to the same problem - your app fails to
> release server connections for reuse.
>
> If the problem is that PHP fails to disconnect connection,
> although the transaction is finished, you could run pgbouncer
> in more relaxed mode - pool_mode=transaction.  Also setting
> client_idle_timeout to something may help debugging.

> If the problem is uncommitted transactions, you could set
> query_timeout to some small number (1-3) to see where
> errors appear.
>
> Both timeouts are not something I would put into productions
> config, so the code should be fixed still...

pgbouncer does not have this problem, only pgpool does.
pgbouncer has the problem of being very slow.

i thought php released connections at the end of script?
and also if i had this problem pgpool would hang in a few seconds
because the server has some load.


It does if you're running without pg_pconnect (i.e. using regular
pg_connects) and if the script doesn't crash the apache/php backend
it's running in.

Are you using pg_pconnect and / or having crashing apache backends?


I specifically turn off pconnects and I don't think I crash backends,
never saw mistakes of that type.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Connection pooling

[Scott Marlowe]

On 9/7/07, Max Zorloff <[EMAIL PROTECTED]> wrote:
> On Fri, 07 Sep 2007 10:58:36 +0400, Marko Kreen <[EMAIL PROTECTED]> wrote:
>
> >> The pgpool (I tried 3.1, 3.4 and pgpool-II 1.2) works fine but has the
> >> following problem - after some time it
> >> just "hangs", and if I try to connect to it with psql it just hangs
> >> indefinitely. After restart
> >> it works fine again. I turned off persistent connections in php so it's
> >> not that. Does anybody
> >> have the same problem?
> >
> > All symptoms point to the same problem - your app fails to
> > release server connections for reuse.
> >
> > If the problem is that PHP fails to disconnect connection,
> > although the transaction is finished, you could run pgbouncer
> > in more relaxed mode - pool_mode=transaction.  Also setting
> > client_idle_timeout to something may help debugging.
>
> > If the problem is uncommitted transactions, you could set
> > query_timeout to some small number (1-3) to see where
> > errors appear.
> >
> > Both timeouts are not something I would put into productions
> > config, so the code should be fixed still...
>
> pgbouncer does not have this problem, only pgpool does.
> pgbouncer has the problem of being very slow.
>
> i thought php released connections at the end of script?
> and also if i had this problem pgpool would hang in a few seconds
> because the server has some load.

It does if you're running without pg_pconnect (i.e. using regular
pg_connects) and if the script doesn't crash the apache/php backend
it's running in.

Are you using pg_pconnect and / or having crashing apache backends?

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Connection Pooling directly on Postgres Server

[Filip Rembiałkowski]

2007/9/8, Denis Gasparin <[EMAIL PROTECTED]>:
>
> > This has certainly been discussed before.
> >
> > IIRC the real argument against that was, that fork() isn't the most
> > expensive thing to do anymore. And Postgres does lots of other stuff
> > after accept(), namely connecting to a certain database,
> > authenticating the user, etc..
> Ok. I knew that. I made the question because it seems that, for example,
> Oracle in release 11g is moving to a similar solution in order to solve
> connection pooling problems.
>
> For example look at the following link:
>
> http://pbarut.blogspot.com/2007/08/oracle-11g-drcp-database-resident.html
>

sure... regarding Oracle, it's different story because of their
development model which is not opensource and has to rely on own
solutions instead of following unix tradition of modularity.

regarding Apache, it's different story because HTTP is stateless
protocol! which enables random backend switching, in contrary to
postgres backend protocol.

Anyway, stateless connection pooling is already implemented (pgpool/pgbouncer/?)

Stateful connection pooling is hard to implement without rewriting the
protocol itself or disrupting existing behaviour.


-- 
Filip Rembiałkowski

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Connection Pooling directly on Postgres Server

[Denis Gasparin]

> This has certainly been discussed before.
>
> IIRC the real argument against that was, that fork() isn't the most
> expensive thing to do anymore. And Postgres does lots of other stuff
> after accept(), namely connecting to a certain database,
> authenticating the user, etc..
Ok. I knew that. I made the question because it seems that, for example,
Oracle in release 11g is moving to a similar solution in order to solve
connection pooling problems.

For example look at the following link:

http://pbarut.blogspot.com/2007/08/oracle-11g-drcp-database-resident.html

Denis


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Connection Pooling directly on Postgres Server

[Markus Schiltknecht]

Hi,

Denis Gasparin wrote:

Why not to implement a connection pooling server side as apache for
example does?


This has certainly been discussed before.

IIRC the real argument against that was, that fork() isn't the most 
expensive thing to do anymore. And Postgres does lots of other stuff 
after accept(), namely connecting to a certain database, authenticating 
the user, etc..


If you still want to optimize that, you'd end up having n spare backends 
*per database*. I do that in Postgres-R - not for connection pooling, 
but for application of remote transactions.


Regards

Markus


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Connection Pooling directly on Postgres Server

[Gavin M. Roy]

You'll want to evaluate pgBouncer to see if it meets your needs.  It
works very well for general proxying, connection pooling.

On 9/7/07, Denis Gasparin <[EMAIL PROTECTED]> wrote:
> I'm looking for connection pooling solutions for our php/apache server.
>
> I already checked pgpool and pgbouncer but during the tests, I had the
> following (mad) idea...
>
> Why not to implement a connection pooling server side as apache for
> example does?
>
> I try to explain my idea...
>
> The postgres server maintains a number of connections always alive (as
> apache for example does)
> even if a client disconnects.
>
> The following parameters could be used to tune the number of connections
> kept alive server side:
>
> StartServers: number of postgres already active connections at server start
> MinSpareServers: If there are fewer than MinSpareServers, it creates a
> new spare (connection)
> MaxSpareServers: If there are more than MaxSpareServers, some of the
> spares (connections) die off.
>
> The parameters has been taken directly from an apache httpd.conf sample...
>
> Could it be possible to implement a similar solution on postgres?
>
> What to do you think about this?
>
> Thank you,
> Denis
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Connection Pooling directly on Postgres Server

[Hannes Dorbath]

On 07.09.2007 10:16, Denis Gasparin wrote:

What to do you think about this?


I think there is no need for this and it's the wrong place to implement it.

Why should PostgreSQL do something to work around broken application 
design? An application that closes its database connection just to 
create a new one a second later is broken.


In this particular case the thing to fix is Apache's legacy 
architecture. Prefork based web servers are really something from the 
last decade.


Consider Lighttpd, Litespeed or Zeus and connect a fixed amount of 
PHP/Perl/Python/Ruby/Lua workers via FCGI. Each worker keeps a 
persistent connection to PG and load is distributed over the workers.



--
Regards,
Hannes Dorbath

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Connection pooling

[Marko Kreen]

On 9/7/07, Max Zorloff <[EMAIL PROTECTED]> wrote:
> On Fri, 07 Sep 2007 10:58:36 +0400, Marko Kreen <[EMAIL PROTECTED]> wrote:
> >> The pgpool (I tried 3.1, 3.4 and pgpool-II 1.2) works fine but has the
> >> following problem - after some time it
> >> just "hangs", and if I try to connect to it with psql it just hangs
> >> indefinitely. After restart
> >> it works fine again. I turned off persistent connections in php so it's
> >> not that. Does anybody
> >> have the same problem?
> >
> > All symptoms point to the same problem - your app fails to
> > release server connections for reuse.
> >
> > If the problem is that PHP fails to disconnect connection,
> > although the transaction is finished, you could run pgbouncer
> > in more relaxed mode - pool_mode=transaction.  Also setting
> > client_idle_timeout to something may help debugging.
>
> > If the problem is uncommitted transactions, you could set
> > query_timeout to some small number (1-3) to see where
> > errors appear.
> >
> > Both timeouts are not something I would put into productions
> > config, so the code should be fixed still...
>
> pgbouncer does not have this problem, only pgpool does.
> pgbouncer has the problem of being very slow.

pgbouncer gets slower because there are decreasing amount
of usable server connections.  it will hang too in the end.

You can study SHOW POOLS; command output on pgbouncer
console, that should give good picture whether pgbouncer
itself is slow or clients are waiting on server connection.

> i thought php released connections at the end of script?
> and also if i had this problem pgpool would hang in a few seconds
> because the server has some load.

There have been some reports in this list that PHP connection
handling is not very robust.  You can work around that by
serring pool_mode = transaction.  Then only open transactions
can cause problems.

-- 
marko

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Connection pooling

[Max Zorloff]

On Fri, 07 Sep 2007 10:58:36 +0400, Marko Kreen <[EMAIL PROTECTED]> wrote:


The pgpool (I tried 3.1, 3.4 and pgpool-II 1.2) works fine but has the
following problem - after some time it
just "hangs", and if I try to connect to it with psql it just hangs
indefinitely. After restart
it works fine again. I turned off persistent connections in php so it's
not that. Does anybody
have the same problem?


All symptoms point to the same problem - your app fails to
release server connections for reuse.

If the problem is that PHP fails to disconnect connection,
although the transaction is finished, you could run pgbouncer
in more relaxed mode - pool_mode=transaction.  Also setting
client_idle_timeout to something may help debugging.



If the problem is uncommitted transactions, you could set
query_timeout to some small number (1-3) to see where
errors appear.

Both timeouts are not something I would put into productions
config, so the code should be fixed still...


pgbouncer does not have this problem, only pgpool does.
pgbouncer has the problem of being very slow.

i thought php released connections at the end of script?
and also if i had this problem pgpool would hang in a few seconds
because the server has some load.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Connection Pooling directly on Postgres Server

[Denis Gasparin]

I'm looking for connection pooling solutions for our php/apache server.

I already checked pgpool and pgbouncer but during the tests, I had the
following (mad) idea...

Why not to implement a connection pooling server side as apache for
example does?

I try to explain my idea...

The postgres server maintains a number of connections always alive (as
apache for example does)
even if a client disconnects.

The following parameters could be used to tune the number of connections
kept alive server side:

StartServers: number of postgres already active connections at server start
MinSpareServers: If there are fewer than MinSpareServers, it creates a
new spare (connection)
MaxSpareServers: If there are more than MaxSpareServers, some of the
spares (connections) die off.

The parameters has been taken directly from an apache httpd.conf sample...

Could it be possible to implement a similar solution on postgres?

What to do you think about this?

Thank you,
Denis

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Connection pooling

[Marko Kreen]

On 9/6/07, Max Zorloff <[EMAIL PROTECTED]> wrote:
> Hello. I'm using Apache + PHP + Postgres for my project. I've tried the
> two poolers people
> usually recommend here - pgbouncer and pgpool.
>
> I have a problem with pgbouncer - under the load the query execution
> becomes ~10 times slower
> than it should be - basically to test it, I connect with psql
> (establishing connection becomes
> somewhat slow under load) and use \timing to measure execution time. The
> basic query of
> "select * from aaa where id = 1" runs 10 times slower than it should. If I
> connect with psql
> directly to postgres, the execution time is acceptable. Does anyone know
> what is the problem?
>
> The pgpool (I tried 3.1, 3.4 and pgpool-II 1.2) works fine but has the
> following problem - after some time it
> just "hangs", and if I try to connect to it with psql it just hangs
> indefinitely. After restart
> it works fine again. I turned off persistent connections in php so it's
> not that. Does anybody
> have the same problem?

All symptoms point to the same problem - your app fails to
release server connections for reuse.

If the problem is that PHP fails to disconnect connection,
although the transaction is finished, you could run pgbouncer
in more relaxed mode - pool_mode=transaction.  Also setting
client_idle_timeout to something may help debugging.

If the problem is uncommitted transactions, you could set
query_timeout to some small number (1-3) to see where
errors appear.

Both timeouts are not something I would put into productions
config, so the code should be fixed still...

-- 
marko

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] Connection pooling

[Max Zorloff]

Hello. I'm using Apache + PHP + Postgres for my project. I've tried the  
two poolers people

usually recommend here - pgbouncer and pgpool.

I have a problem with pgbouncer - under the load the query execution  
becomes ~10 times slower
than it should be - basically to test it, I connect with psql  
(establishing connection becomes
somewhat slow under load) and use \timing to measure execution time. The  
basic query of
"select * from aaa where id = 1" runs 10 times slower than it should. If I  
connect with psql
directly to postgres, the execution time is acceptable. Does anyone know  
what is the problem?


The pgpool (I tried 3.1, 3.4 and pgpool-II 1.2) works fine but has the  
following problem - after some time it
just "hangs", and if I try to connect to it with psql it just hangs  
indefinitely. After restart
it works fine again. I turned off persistent connections in php so it's  
not that. Does anybody

have the same problem?

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Connection Pooling for Postgres

[Philip Hallstrom]

Is there any inbuilt facility in postgres for connection pooling .. ??


Not built in, but...

http://pgpool.projects.postgresql.org/

Never used it myself...

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Connection Pooling for Postgres

[Sandeep Kumar Jakkaraju]

Hi All

Is there any inbuilt facility in postgres for connection pooling .. ??

Thanks
-
sandeep

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] Connection pooling with C and libpq

[Kassu Valtakari]

Hei!

How do I do the connection pooling with libpq? I'm trying to modify
a radius server to fork or thread and pick a connection from the pool
and in that way be able to store 100..500 small records in a second
into postgresql.
I can create a global array of connections, but how is it done after
that? I'm sure there's people who think it's trivial and simple, but I
also think that seeing someone's ready tested code helps me a lot.

Also an example with threads or forks in the code could be interesting.
-- 
--  Kassu Valtakari, kassu at iki.fi, +49 89 722 43448
--  Quidquid latine dictum sit, altum viditur 

Re: [GENERAL] Connection pooling

[Frank Joerdens]

On Thu, Jan 25, 2001 at 05:14:50PM +0100, Gilles DAROLD wrote:
> Hi,
> 
> With Apache/mod_perl it is very simple to enable DB connection
> persistance.
> 
> in your perl script :
> 
> use vars qw($dbh);
> 
> $dbh ||= DBI::connect($datasrc, $dbuser, $dbpwd);
> 
> That create a persistance connection to your DB and you do not have to
> care
> about a pool of connection. Double pipe do all for you :-)
> 
> But if you really want a pool just create an array of this kind of global
> var at startup
> and switch to any indices as you want.

How's that supposed to work? Apache is a multi-process web server.
Connection pooling over all Apache children that are currently alive on
a web server would require some kind of inter-process communication,
which I don't think Apache supports (am I mistaken?).

Regards, Frank

Re: [GENERAL] Connection pooling

[Neil Conway]

On Thu, Jan 25, 2001 at 01:12:48PM -0500, Brett W. McCoy wrote:
> Apache::DBI is supposed to be supporting connection pooling in the near
> future.

I believe this is scheduled (and only technically possible) with mod_perl
2.0, which will work with Apache 2.0 -- and they're both still in alpha.
So I wouldn't hold your breath...

But I agree -- mod_perl 2.0 will be really good, when it's released.

-- 
Neil Conway <[EMAIL PROTECTED]>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

Good composers borrow. Great composers steal.
-- Igor Stravinsky

Re: [GENERAL] Connection pooling

[Brett W. McCoy]

On Thu, 25 Jan 2001, Gilles DAROLD wrote:

> Don't forget to use Apache::DBI on top of DBI.pm. See mod_perl
> documentation
> for a complete setting.

Apache::DBI is supposed to be supporting connection pooling in the near
future.

-- Brett
 http://www.chapelperilous.net/~bmccoy/
---
The universe seems neither benign nor hostile, merely indifferent.
-- Sagan

Re: [GENERAL] Connection pooling

[Dan Lyke]

Gilles DAROLD writes:
> With Apache/mod_perl it is very simple to enable DB connection
> persistance.
> 
> in your perl script :
> 
> use vars qw($dbh);
> 
> $dbh ||= DBI::connect($datasrc, $dbuser, $dbpwd);

Even better, Apache::DBI will pool across Perl programs, and you don't
have to change anything in your scripts.

Re: [GENERAL] Connection pooling

[Peter T Mount]

Quoting Guillaume Lémery <[EMAIL PROTECTED]>:

> Hi,
> 
> my database has to handle quickly several number of queries per second 
> from numerous clients.
> Because of the waste of time for connection/disconnection, Im' looking 
> for a connection pooling tool.
> 
> Where can I find a such tool or can somebody send me one ?

What interface are you using? JDBC/ODBC/LibPQ?

Peter

> 
> Many thanks in advance,
> 
> Guillaume.
> 
> 



-- 
Peter Mount [EMAIL PROTECTED]
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/

Re: [GENERAL] Connection pooling

[Gilles DAROLD]

Hi,

With Apache/mod_perl it is very simple to enable DB connection
persistance.

in your perl script :

use vars qw($dbh);

$dbh ||= DBI::connect($datasrc, $dbuser, $dbpwd);

That create a persistance connection to your DB and you do not have to
care
about a pool of connection. Double pipe do all for you :-)

But if you really want a pool just create an array of this kind of global
var at startup
and switch to any indices as you want.

Don't forget to use Apache::DBI on top of DBI.pm. See mod_perl
documentation
for a complete setting.

Hope this help.

Regards,


Guillaume Lémery wrote:

> Hi,
>
> my database has to handle quickly several number of queries per second
> from numerous clients.
> Because of the waste of time for connection/disconnection, Im' looking
> for a connection pooling tool.
>
> Where can I find a such tool or can somebody send me one ?
>
> Many thanks in advance,
>
> Guillaume.