Database temporarily disconnected after a lots of transactions by pgbench

Question

I am using (PostgreSQL) 9.2.1 and test the database with pgbench.

pgbench -h 192.168.39.38 -p 5433 -t 1000 -c 40 -j 8 -C -U admin testdb

When I use the -C parameter(Establish a new connection for each transaction), the transactions are always lost after the 16381th transaction.

Connection to database "testdb" failed
could not connect to server: Can't assign requested address
    Is the server running on host "192.168.39.38" and accepting
    TCP/IP connections on port 5433?
Client 19 aborted in establishing connection.
Connection to database "testdb" failed
could not connect to server: Can't assign requested address
    Is the server running on host "192.168.39.38" and accepting
    TCP/IP connections on port 5433?
Client 19 aborted in establishing connection.
....

transaction type: TPC-B (sort of)
scaling factor: 30
query mode: simple
number of clients: 40
number of threads: 8
number of transactions per client: 1000
number of transactions actually processed: 16381/40000
tps = 1665.221801 (including connections establishing)
tps = 9487.779510 (excluding connections establishing)

And the number of transactions actually processed is always 16381 in each test. However, pgbench can success and all transactions are processed in the circumstances that

-C is not used

or

the total transactions are less than 16381

After dropping these transactions, the database can continue to accept connection in few seconds. I wonder if I miss some configuration of PostgreSQL.

Thanks

---

Edit I found that the client is blocked to connect for few seconds, but the others still can access the database. Does that mean the same client cannot send too many transactions in a short time?

Answer 1

I found the reason why it losses the connections after about 16000 transactions. TCP wait_time takes the blame for this mistake. The following command will show the status of TCP connections:

$ netstat -n | awk '/^tcp/ {++S[$NF]} END {for(a in S) print a, S[a]}'

Nevertheless, it does NOT show the TIME_WAIT in MAC OS X. Therefore I missed it. After I adjust the TCP wait_time by the following command, pgbench works properly.

$ sudo sysctl -w net.inet.tcp.msl=1500
net.inet.tcp.msl: 15000 -> 1500

Thanks for helping.

Answer 2

There is indeed a limit of maximum connections imposed by the OS. Read up on max-connections in the documentation: (bolded relevant parts)

Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.

Increasing this parameter might cause PostgreSQL to request more System V shared memory or semaphores than your operating system's default configuration allows. See Section 17.4.1 for information on how to adjust those parameters, if necessary.

That you can open only 16381 connections, is explicable by there being 2^14 (=16384) possible maximum connections minus 3 connections reserved by default for super-user connections (see documentation).