0

I installed pgbouncer 1.5.4 on my pc so that I could sort out the using to much connections in postgre problem in a way.

I need to be able to have many people run a web page that has a map application on it which reads from a postgresql database, I had noticed that because of the limited default connections with postgres that I needed pgbouncer to manage the connections but for some reason I don't think pgbouncer is working per say.

I had put in the pgbouncer.ini file my database details which is :manifold = host=127.0.0.1 port=6432 dbname=manifold user=postgrest password=password

I changed my postgres port from 5432 to 6432 and my pgbouncer port no to 5432.

It seems when I check on the connection activity in postgres the connections keep going up and not down as I thought it should if using the pgbouncer.

If it hits 100 or over connections then my web map applications starts getting pink tiles and if someone gets off then the map will show up.

Any help would be great to try and solve this problem I am having. I have been struggleing with this issue for weeks now.

are these the settings your looking for when I type show config in the pgbouncer admin then i get this:

pgbouncer=# show config;
 key  |                value                                    | changeable
 job_name          | pgbouncer                                  | no
 service_name      | pgbouncer                                  | no
 conffile  | C:\ProgramFiles\PostgreSQL\share\pgbouncer.ini     | yes
 logfile   | C:\ProgramFiles\PostgreSQL\log\pgbouncer.log       | yes
 pidfile   | C:\ProgramFiles\PostgreSQL\log\pgbouncer.pid       | no
 listen_addr               | *                                  | no
 listen_port               | 5432                               | no
 listen_backlog            | 128                                | no
 auth_type                 | md5                                | yes
 auth_file | C:\ProgramFiles\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)
Ravyn
  • 157
  • 4
  • 21
  • So - what settings do you have for pgbouncer? What have you got it logging and what do the logs say? – Richard Huxton Nov 04 '13 at 10:48
  • Which kind of settings are you wanting to see? the pgbouncer logs give: a whole lot of this 2013-11-08 09:57:09.235 10780 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us. – Ravyn Nov 08 '13 at 07:57
  • when I type show servers; in the admin console of pgbouncer it show no servers at all : pgbouncer=# show servers; type | user | database | state | addr | port | local_addr | local_port | connec t_time | request_time | ptr | link it shows rows (0) – Ravyn Nov 08 '13 at 08:07
  • So what are your pgbouncer settings? – Richard Huxton Nov 08 '13 at 08:35
  • Im not sure which settings you are looking for I posted a config file on there. the only settings I would know of is the installed setting which was originally port 6432 and user was postgres. – Ravyn Nov 08 '13 at 09:22
  • is possibly got somethinh to do with my dns connections, what I get from the admin console pgbouncer=# show dns_hosts; hostname | ttl | addrs ----------+-----+------- (0 rows) pgbouncer=# show dns_zones; zonename | serial | count ----------+--------+------- (0 rows) – Ravyn Nov 08 '13 at 09:32
  • Why is default_pool_size set to 100? Do you have the sort of hardware available to support 100 concurrent queries? – Richard Huxton Nov 08 '13 at 10:27
  • I had originally at the default 20 which still seemed to do nothing. I was testing it out and it didn't seem to make a difference to it. – Ravyn Nov 08 '13 at 10:29
  • If all this is running on the same PC, set it to 2, make sure your application connects to pgbouncer's port and the new settings are active. Open three terminal windows and one by one connect to pgbouncer from psql. Check the logs as you do so and you should be able to see what's happening. – Richard Huxton Nov 08 '13 at 10:35
  • I managed to get it working in a way. I fixed the geoserver stores to read the pgbouncer port instead of reading postgre directly but now I seem to be getting pink tiles on my map. – Ravyn Nov 08 '13 at 13:30
  • But in your question you said your app *was* using the pgbouncer port (or implied so anyway). Pink tiles - can't help. – Richard Huxton Nov 08 '13 at 14:03

1 Answers1

0

I am guessing that pgbouncer is not the solution you need, but rather you have a connection leak in your application. pgbouncer will not be able to solve that issue for you and so you need to fix it in your program. I am further betting that you will see the exact same behavior without pgbouncer.

In this case you run out of connections with or without a connection pool. The problem is likely that your application is not closing connections as you expect.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182