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 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. I
also checked pg log and I only found there are one "incomplete message
from client" error message every several minute. 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 . So after a while, the connection limit is
reached again. Besides those inserts, there are no other long run
queries and auto vacuums. I also checked the locks of the inserts and
found they were all granted. The insert statement itself is very simple
and it only inserts one row but there are some triggers involved. They
might impact the performance but I have never experience any since the
majority of the inserts are fine. The problem persisted about 1-2 hours.
I didn't do anything except recycling pgbouncer a few times. After that
period, everything goes back to normal. It's has been 24 hours and it
didn't happen again.
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? 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?
