Message-ID: <5212CA07.6020509@gmail.com>
Date: Tue, 20 Aug 2013 09:44:39 +0800
From: Rural Hunter <ruralhunter@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1;
 rv:17.0) Gecko/20130801 Thunderbird/17.0.8
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: How to investiage slow insert problem
Content-Type: text/plain; charset=GB18030; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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?


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance