Re: [GENERAL] Explanantion on pgbouncer please
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
Re: [GENERAL] Explanantion on pgbouncer please
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
Re: [GENERAL] Explanantion on pgbouncer please
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
Re: [GENERAL] Explanantion on pgbouncer please
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
Re: [GENERAL] Explanantion on pgbouncer please
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
Re: [GENERAL] Explanantion on pgbouncer please
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. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Explanantion on pgbouncer please
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
Re: [GENERAL] Explanantion on pgbouncer please
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. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Explanantion on pgbouncer please
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 smrcoutt...@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)
Re: [GENERAL] Explanantion on pgbouncer please
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@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
Re: [GENERAL] Explanantion on pgbouncer please
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 smrcoutt...@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
Re: [GENERAL] Explanantion on pgbouncer please
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
Re: [GENERAL] Explanantion on pgbouncer please
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
Re: [GENERAL] Explanantion on pgbouncer please
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
Re: [GENERAL] Explanantion on pgbouncer please
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 smrcoutt...@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.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] Explanantion on pgbouncer please
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
Re: [GENERAL] Explanantion on pgbouncer please
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
Re: [GENERAL] Explanantion on pgbouncer please
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 signature.asc Description: Digital signature
Re: [GENERAL] Explanantion on pgbouncer please
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
