Re: How to investiage slow insert problem - Mailing list pgsql-performance

On Mon, Aug 19, 2013 at 6:44 PM, Rural Hunter <ruralhunter@gmail.com> wrote:
> 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?

What do you mean by recycling pgbouncer?

Haven't you noticed what was in the state column of the
pg_state_activity view? In 9.2 the query column in this view shows the
last statement that was executed in this connection, and it does not
mean that this statement is working at the moment of monitoring. If
the state is active, than it was working, however, my assumption is
that it was IDLE in transaction. You mentioned the "incomplete message
from client" error, so it might somehow be a network problem that led
to a hunging connection to pgbouncer, that made pgbouncer kept a
connection to postgres after transaction was started.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com