Thread: 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
10000 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'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.

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.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@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
> 10000 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

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.klaver@gmail.com

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
> 10000 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

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.

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.

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.

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.

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 <smrcoutts24@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

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.

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

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

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

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