On Monday, August 19, 2013, Rural Hunter wrote:
Hi,
I'm on 9.2.4 with Ubuntu server. There are usually hundereds of connections doing the same insert with different data from different networks every minute, through pgbouncer in the same network of the database server. The database has been running for about one year without problem. Yesterday I got a problem that the connection count limit of the database server is reached.
I think that this should generally not happen at the server if you are using pgbouncer, as you should configure it so that pgbouncer has a lower limit than postgresql itself does. What pooling method (session, transaction, statement) are you using?
I checked the connections and found that there are many inserts hanging there. I checked the load(cpu,memory,io) of the db server but seems everything is fine.
Can you provide some example numbers for the io load?
I also checked pg log and I only found there are one "incomplete message from client" error message every several minute.
Could you post the complete log message and a few lines of context around it?
The I recycled pgbouncer and kept monitoring the connections. I found the majority of the inserts finish quickly but every minute there are several inserts left and seems hanging there .
How long had they been hanging there? It makes a big difference whether there are several hanging there at one moment, but a few milliseconds later there are several different ones, versus the same few that hang around of many seconds or minutes at a time.
...
From the error message in pg log, I supect it might be the network problem from some clients. Could anyone point out if there are other possible causes?
If the identities of the "hung" processes are rapidly changing, it could just be that you are hitting a throughput limit. When you do a lot of inserts into indexed the tables, the performance can drop precipitously once the size of the actively updated part of the indexes exceeds shared_buffers. This would usually show up in the io stats, but if you always have a lot of io going on, it might not be obvious.
If it is the same few processes hung for long periods, I would strace them, or gdb them and get a backtrace.
I'm also wondering what those inserts are doing actually when they are hanging there, such as if they are in the trigger or not. Anything I can get similar with the connection snapshots in db2?
Sorry, I don't know what a connection snapshot in db2 looks like.
Cheers,
Jeff
