Обсуждение: 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 -- View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515.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: 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 connectionsto 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 commandssuch as "show pools", "show clients", etc... Regards, Igor Neyman
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:
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
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Explanantion-on-pgbouncer-please-tp5776515.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'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.
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.
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.
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:
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.htmlSent 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 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
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.
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
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.
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? > > > -- > 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. > >
Вложения
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.
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? > > 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. > >
Вложения
Again, this output indicates that pgbouncer is not connecting to postgres server.
Regards, On Thu, Oct 31, 2013 at 11:02 AM, si24 <smrcoutts24@gmail.com> wrote:
I don't know if this will help in terms of my problem?
this came from the admin pgbouncer console.
pgbouncer=# show servers;
type | user | database | state | addr | port | local_addr | local_port |
connect_time | request_time | ptr | link
------+------+----------+-------+------+------+------------+------------+--------------+--------------+-----+------
(0 rows)
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.
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.
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.
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.