From: Rural Hunter <ruralhunter@gmail.com>
To: pgsql-performance@postgresql.org
Subject: How to investiage slow insert problem
Date: Tue, 20 Aug 2013 09:44:39 +0800
Message-ID: <5212CA07.6020509@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ruralhunter@gmail.com
Subject: Re: How to investiage slow insert problem
In-Reply-To: <5212CA07.6020509@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox