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
