On 11/20/2011 06:21 PM, Phoenix Kiula wrote:
*SNIP*
Forgive me if I accidentally rehash something already discussed...
Divide an conquer:
First, verify that you can connect directly to your database *using
TCP*, i.e. "psql -h 127.0.0.1 -U youruser -p 5432 yourdb". If you are
using psql without -h for this test you will use unix sockets. We need
to be sure that you can connect in the same way that pgbouncer will
connect. Note that pg_hba.conf can have different settings for socket
connections than for TCP connections and you must be sure that
postgresql.conf is set to listen for TCP connections on localhost.
Next, your settings are too grand for learning/testing purposes. Set the
pool size in pgbouncer to something small - perhaps 5 for testing
purposes and make sure that you actually have 5 available connections on
the database. Note, pgbouncer will not actually make a server connection
until it gets a client request. But it will keep that connection open to
serve the next request. And it won't make a second server connection
till it actually needs two simultaneous connections so you won't see a
sudden flood of connections when you start pgbouncer.
Now try using psql to connect to pgbouncer - again using -h 127.0.0.1 as
you were doing. If it doesn't work, check pgbouncer's log and PostgreSQL's.
Once you get an actual working pgbouncer connection, work your settings
upward. I'd probably start with something like 30-40 for the pool size
and perhaps 60-80 clients.
The information in the pgbouncer pseudo-database is helpful, here (psql
-U youradminuser -h 127.0.0.1 pgbouncer).
The "SHOW HELP;" statement will get you started but "show lists" will
give you an idea of your utilization:
show lists;
list | items
---------------+-------
databases | 2
users | 2
pools | 2
free_clients | 0
used_clients | 1
login_clients | 0
free_servers | 1
used_servers | 0
If, after watching for a while, you see you always have lots of free
servers then you can increase the number of clients connecting to
pg_bouncer. If you are running close to the edge or running out of
server connections altogether, you will need to decrease clients or
increase the pool size.
Cheers,
Steve
