Explanantion on pgbouncer please

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of si24
Sent: Thursday, October 31, 2013 10:25 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Explanantion on pgbouncer please

Can some one please give me a bit more of a better explanation on how
exactly the pgbouncer works as I am now lost.

I'm not sure if it is pooling the connections cause surely if its not being used
the connections should go down not up i.e i run the webpage which has my
map running which is an open layers map reading geoserver all my data on
geoserver is from a database in postgres. When you start the web page it
goes to 46 connections and after moving around for a while and selecting the
different overlays that I have on the map it goes up to 75 connections after
not touching it for a while nothing happens the connections don't go up or
down, but when I move the map around and zoom then the connections
increase again to 84 connections.

Please help I'm stuck

First, when you are describing your situation, please be specific what connections you are talking about: client connections to PgBouncer or pool connections from PgBouncer to Postgres server.

Second, you could learn a lot about status of your connections, when you connect as administrator to PgBouncer and use commands such as "show pools", "show clients", etc...

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

Where are you measuring the connections? From your app to PGBouncer, or
from PGBouncer to PostgreSQL?

If it is from your app to PGBouncer, that sounds strange, and like the app
is not properly releasing connections as it should. If it is from
PGBouncer to PostgreSQL, that sounds normal. I haven't used PGBouncer, but
i've used other connection poolers in the past. They would start out with
a set number of minimum connections, and scale up to the max number
specified as needed. The pooler wouldn't release a connection to the DB
once it has made one, and just kept it available in the pool.

On Thu, Oct 31, 2013 at 10:25 AM, si24 <smrcoutts24@gmail.com> wrote:

I'm not a 100% sure but when i type : select count (*) from pg_stat_activity;
in postgres it give me the a number be 3 or 75 or higher depending on what
is runnung at the time if its the webpage with the map or just postgres
itself.

I had thought that the client connections to PgBouncer and pool connections
from PgBouncer to Postgres server was pretty much the same the same thing.
that could also be a problem maybe i'm not understanding the full length of
the connection term to well. I just know that I have been struggleing with
this for a few days now and am still have not come to much closer to
understanding it (i think). The pdf part that I had read that showed you how
to set it up and shows you the command section on the show pools and all. I
had did that. but will check it out again then.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776522.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

I use a check in postgres to give the active connections being used.

But what happens if the connection pooler goes all the way to 100 for
example and say that 100 is your postgres maximum connections at the time. I
know I can change the maximum connections in postgres but am not to sure on
the right balance. If I have say 600 people looking at my map is that not
then several calles to postgres to show them the map that each person is
looking at on the computer?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776524.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

I don't know if this will help in terms of my problem?

this came from the admin pgbouncer console.

pgbouncer=# show pools;
database | user | cl_active | cl_waiting | sv_active | sv_idle |
sv_used| sv_tested | sv_login | maxwait
-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------
pgbouncer | pgbouncer | 1 | 0 | 0 | 0 |
0| 0 | 0 | 0
(1 row)

pgbouncer=# show pools;
database | user | cl_active | cl_waiting | sv_active | sv_idle |
sv_used| sv_tested | sv_login | maxwait
-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------
pgbouncer | pgbouncer | 1 | 0 | 0 | 0 |
0| 0 | 0 | 0
(1 row)

pgbouncer=# show pools;
database | user | cl_active | cl_waiting | sv_active | sv_idle |
sv_used| sv_tested | sv_login | maxwait
-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------
pgbouncer | pgbouncer | 1 | 0 | 0 | 0 |
0| 0 | 0 | 0
(1 row)

pgbouncer=# show clients;
type | user | database | state | addr | port | local_addr |
local_port | connect_time | request_time | ptr | link
------+----------+-----------+--------+------+-------+------------+------------+---------------------+---------------------+----------+------
C | postgres | pgbouncer | active | ::1 | 65126 | ::1 |
6432 | 2013-10-31 16:52:44 | 2013-10-31 16:55:13 | 018507b0 |
(1 row)

pgbouncer=# show servers;
type | user | database | state | addr | port | local_addr | local_port |
connect_time | request_time | ptr | link
------+------+----------+-------+------+------+------------+------------+--------------+--------------+-----+------
(0 rows)

pgbouncer=# show databases;
name | host | port | database | force_user | pool_size |
reserve_poo
-----------+-----------+------+-----------+------------+-----------+--------------
manifold | localhost | 5432 | manifold | postgrest | 20 |0
pgbouncer | | 6432 | pgbouncer | pgbouncer | 2 |0
postgres | 127.0.0.1 | 5432 | postgres | | 20 |0
(3 rows)

pgbouncer=# show list;
ERROR: invalid command 'show list;', use SHOW HELP;
pgbouncer=# show lists;
list | items
---------------+-------
databases | 3
users | 2
pools | 1
free_clients | 67
used_clients | 1
login_clients | 0
free_servers | 0
used_servers | 0
dns_names | 0
dns_zones | 0
dns_queries | 0
dns_pending | 0
(12 rows)

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776530.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 where you are measuring, everything looks normal to me.

Your application will make connections to the pooler as needed, and the
pooler will assign the application connection to a database connection it
has available in it's pool. This gets rid of the overhead of creating a
brand new connection to the DB right from the application for a small
query, as connecting to the pooler is much less overhead.

The pooler should eventually get to the maximum number of allowed
connections. That is just how it's supposed to work. It is not supposed
to any connections after your application doesn't need the them any more.
It just keeps it open, and adds it to the available pool for your
application to use.

On Thu, Oct 31, 2013 at 10:49 AM, si24 <smrcoutts24@gmail.com> wrote:

On 10/31/2013 7:38 AM, Adam Brusselback wrote:

If it is from your app to PGBouncer, that sounds strange, and like the
app is not properly releasing connections as it should. If it is from
PGBouncer to PostgreSQL, that sounds normal. I haven't used
PGBouncer, but i've used other connection poolers in the past. They
would start out with a set number of minimum connections, and scale up
to the max number specified as needed. The pooler wouldn't release a
connection to the DB once it has made one, and just kept it available
in the pool.

except, there's no way a single web page access should be using 70 or 80
connections. 2-3, ok, maybe.

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

si24 wrote

Can some one please give me a bit more of a better explanation on how
exactly the pgbouncer works as I am now lost.

Working from theory here but:

Pool (PGBouncer) Connections: 1
PostgreSQL Connections: 1
Container Threads: 2 [A, B]

Thread A: get connection -> OK
Thread B: get connection -> waiting for available connection
Thread A: do something with connection
Thread B: still waiting
Thread A: ***RELEASE CONNECTION*** <goes back into the pool>
Thread B: got a connection now
Thread A: get connection -> waiting for available connection
Thread B: do something with connection
Thread A: still waiting
Thread B: ***RELEASE CONNECTION*** <goes back into the pool>
Thread A: got a connection now

Each pool connection maintains a persistent connection in PostgreSQL

The number of pool connections can fluctuate between a minimum and maximum

If a pool connection is "checked out" it will never go away. Only after the
caller has "released/closed the connection" can it be potentially removed
from the pool. If there is not activity for a long while, and all pool
connections have been "closed", the number of open connections should
eventually go back down to the minimum. My guess is that a failure to close
these connections is causing the pooler to think they are still in use and
thus cannot release them from the pool and close the matching connection to
PostgreSQL.

I'd suggest you try some interactive use of your application by pretending
that you are two users and there is only a single connection available for
use. Only once that scenario works correctly should you go ahead and
increase the number of available connections. These connections, generally,
only aid in concurrency performance and should not be required to make you
application work properly.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776554.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

On czw, paź 31, 2013 at 07:25:21 -0700, si24 wrote:

Can some one please give me a bit more of a better explanation on how exactly
the pgbouncer works as I am now lost.

I'm not sure if it is pooling the connections cause surely if its not being
used the connections should go down not up i.e i run the webpage which has
my map running which is an open layers map reading geoserver all my data on
geoserver is from a database in postgres. When you start the web page it
goes to 46 connections and after moving around for a while and selecting the
different overlays that I have on the map it goes up to 75 connections after
not touching it for a while nothing happens the connections don't go up or
down, but when I move the map around and zoom then the connections increase
again to 84 connections.

You probably are using "session pooling" mode in pgbouncer, and
persistent connections in app.

This is generally not so good idea.
Reasons, explanation, and suggestions:

http://www.depesz.com/2012/12/02/what-is-the-point-of-bouncing/

depesz

On 10/31/2013 10:02 AM, si24 wrote:

I don't know if this will help in terms of my problem?

this came from the admin pgbouncer console.

pgbouncer=# show pools;
database | user | cl_active | cl_waiting | sv_active | sv_idle |
sv_used| sv_tested | sv_login | maxwait
-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------
pgbouncer | pgbouncer | 1 | 0 | 0 | 0 |
0| 0 | 0 | 0
(1 row)

This looks wrong. There should be another pool listed for your actual
database. I don't think geoserver is hitting pgbouncer. You changed
the port number for the database connection in geoserver, right?

-Andy

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

geoserver uses the same port number as tomcat does which 8080 as the moment
should I change it to something else?

postgres uses 5432(default port number to postgresql) and pgbouncer uses
6432(default port number given to pgbouncer)

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776609.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

On 01/11/2013 09:07, si24 wrote:

geoserver uses the same port number as tomcat does which 8080 as the moment
should I change it to something else?

postgres uses 5432(default port number to postgresql) and pgbouncer uses
6432(default port number given to pgbouncer)

Am I missed something or your application connect to postgresql server
directly ... not through pgbouncer

Your application need to connect to pgbouncer .. to port 6432... you
changed this?

So should I rather change the 6432 to 5432 like the postgresql port or 8080
like the geoserver tomcat port.

Geoserver is on 8080 and reads all the tables that postgres has cause you
need to add the port no that postgres is on to be able for it read the
tables.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776616.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

On 01/11/2013 09:46, si24 wrote:

So should I rather change the 6432 to 5432 like the postgresql port or 8080
like the geoserver tomcat port.

Yes, you could do like this... but have to change psql server's port too
... to other than 5432... and tell pgbouncer to which port connect to
postgresql.

pgbouncer is on the same host with postgresql?

Again, this output indicates that pgbouncer is not connecting to postgres
server.

Regards,
Igor Neyman

On Thu, Oct 31, 2013 at 11:02 AM, si24 <smrcoutts24@gmail.com> wrote:

everytime I change something it seems when i type in show servers in the
admin console, it keeps showing 0 rows.

I have gone back and forth several time trying to get this thing sorted out
nut to no avail i keep getting the same message.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776822.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

Why don't you try adding layers one by one?
1) Ensure you can connect to PostgreSQL from psql client (on another
machine)
2) Configure pgbouncer
3) See if you can connect from psql > pgbouncer > PostgreSQL
Check the data in pgbpuncer and PostgreSQL (number of sessions, idle
connections etc). You could also try the effect of the timeout parameters in
pgbouncer.
If the above is working fine, try connecting from tomcat/map server/whatever

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5776952.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

Does any of this help this is the config file i get when I type in show
config in the pgbouncer admin console:

pgbouncer=# show config;
key | value
| changeable
---------------------------+-------------------------------------------------+------------
job_name | pgbouncer
| no
service_name | pgbouncer
| no
conffile | C:\Program Files\PostgreSQL\share\pgbouncer.ini
| yes
logfile | C:\Program Files\PostgreSQL\log\pgbouncer.log
| yes
pidfile | C:\Program Files\PostgreSQL\log\pgbouncer.pid
| no
listen_addr | *
| no
listen_port | 5432
| no
listen_backlog | 128
| no
auth_type | md5
| yes
auth_file | C:\Program Files\PostgreSQL\etc\userlist.txt
| yes
pool_mode | transaction
| yes
max_client_conn | 400
| yes
default_pool_size | 100
| yes
min_pool_size | 0
| yes
reserve_pool_size | 0
| yes
reserve_pool_timeout | 5
| yes
syslog | 0
| yes
syslog_facility | daemon
| yes
syslog_ident | pgbouncer
| yes
autodb_idle_timeout | 3600
| yes
server_reset_query | DISCARD ALL
| yes
server_check_query | select 1
| yes
server_check_delay | 30
| yes
query_timeout | 0
| yes
query_wait_timeout | 0
| yes
client_idle_timeout | 0
| yes
client_login_timeout | 60
| yes
idle_transaction_timeout | 0
| yes
server_lifetime | 1200
| yes
server_idle_timeout | 60
| yes
server_connect_timeout | 15
| yes
server_login_retry | 15
| yes
server_round_robin | 0
| yes
suspend_timeout | 10
| yes
ignore_startup_parameters | application_name,extra_float_digits
| yes
disable_pqexec | 0
| no
dns_max_ttl | 15
| yes
dns_zone_check_period | 0
| yes
max_packet_size | 2147483647
| yes
pkt_buf | 2048
| no
sbuf_loopcnt | 5
| yes
tcp_defer_accept | 0
| yes
tcp_socket_buffer | 0
| yes
tcp_keepalive | 1
| yes
tcp_keepcnt | 0
| yes
tcp_keepidle | 0
| yes
tcp_keepintvl | 0
| yes
verbose | 0
| yes
admin_users | postgres
| yes
stats_users | postgres
| yes
stats_period | 60
| yes
log_connections | 1
| yes
log_disconnections | 1
| yes
log_pooler_errors | 1
| yes
(54 rows)

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5777452.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

has it got to do with the DNS I have checked on the admin consol and a
recieved this:

pgbouncer=# show dns_hosts;
hostname | ttl | addrs
----------+-----+-------
(0 rows)

pgbouncer=# show dns_zones;
zonename | serial | count
----------+--------+-------
(0 rows)

Not to sure if this helps.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515p5777456.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