Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

i am experiencing some trouble testing Postgres cluster and load balancer on pgpool.
I i wrote script on bash which asynchronously uses Postgres testing tool pgbench causing number of clients with -c option

find . -name "pgbench*" -exec rm -f {} \;
for (( j=1; j<=$k1; j++))
        pgbench -c $k2    -n -f /tmp/standart2.sql -l -t 1 -h -p 9999  mydb &            


/tmp/standart.sql is sql script which consists of some sql logic (select, insert, update)
This script has 2 parameters: 1) number of parallel queries invoked 2) number of pgbench clients

When i run 10 000 or 5000 parallel queries with 1 or 2 pgbench clients like

./ 10000 1
./ 10000 1

i get connection errors on pgpool

ERROR: pid 20371: "MD5" authentication with pgpool failed for user "postgres"
Client 0 aborted in establishing connection.
Connection to database "mydb" failed:
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
could not connect to server: Connection timed out
        Is the server running on host "" and accepting
        TCP/IP connections on port 9999?
Client 0 aborted in establishing connection.
ERROR:  connection terminated due to client idle limit reached
Client 0 aborted in state 1. Probably the backend died while processing.

i have pgpool.conf

# POOLS              

# - Pool size -

num_init_children = 1000
                                   # Number of pools
                                   # (change requires restart)
max_pool = 10
                                   # Number of connections per pool
                                   # (change requires restart)

# - Life time -

child_life_time = 120
                                   # Pool exits after being idle for this many seconds
child_max_connections = 1000
                                   # Pool exits after receiving that many connections
                                   # 0 means no exit
connection_life_time = 120
                                   # Connection to backend closes after being idle for this many seconds
                                   # 0 means no close
client_idle_limit = 60
                                   # Client is disconnected after being idle for that many seconds
                                   # (even inside an explicit transactions!)
                                   # 0 means no disconnection


max_connections = 20000


kernel.shmmax = 68719476736
kernel.shmall = 6442450944
kernel.sem = 250 512000 256 2048
fs.file-max = 100000


postgres           soft    nproc          65536
postgres           hard    nproc          65536
postgres           soft    nofile         65536
postgres           hard    nofile         65536

I need that all scripts execute without causing errors. My goal is to find bottlenecks increasing number of queries and connections. Could anyone tell me what to tune? Will pgbouncer help this way?

share|improve this question

migrated from Jan 29 '14 at 14:33

This question came from our site for professional and enthusiast programmers.

When you try with a much smaller number, is it working? – Jayadevan Jan 30 '14 at 3:58

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.