CODEWITHSUNDEEP
sqldatabasepostgresqlbenchmarkingpostgresql-9.2
Hsiao
Hsiao

Reputation: 839

Database temporarily disconnected after a lots of transactions by pgbench

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?

Upvotes: 7

Views: 2483

Answers (4)

scofier
scofier

Reputation: 41

I soleved by setting to /etc/sysctl.conf:

net.ipv4.ip_local_port_range = 32768 65000
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10

Upvotes: 0

Hsiao
Hsiao

Reputation: 839

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.

Upvotes: 4

MicSim
MicSim

Reputation: 26796

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).

Upvotes: 1

Edmund
Edmund

Reputation: 10809

It's interesting that 16381 is so close to a power of 2.

This is largely speculation:

I'm wondering whether it's an OS thing. Looking at the TPS figures, is a new connection being created for every transaction? [Edit yes, now that I read your question properly.]

Perhaps the OS has only so many connection resources it can use, and it cannot immediately create a new connection after having made 16381 (plus a few additional ones) in the recent past?

There may be an OS setting for specifying the number of connection resources to make available, which could allow more connections to be used. Can you add some OS details to the question?

In particular I would suspect that the port number you connect from is increasing all the time and you're hitting a limit. Try "lsof -i" and see if you can catch a connection as-it-happens and see if the number is going up.

Upvotes: 0

Related Questions