Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

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

done

/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

./script.sh 10000 1
./script.sh 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 "192.168.1.112" 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
..

postgresql.conf

..
max_connections = 20000
..

/etc/systlc.conf

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

/etc/security/limits.conf

..
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 stackoverflow.com Jan 29 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 at 3:58

Your Answer

 
discard

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