From: Rural Hunter <ruralhunter@gmail.com>
Message-ID: <53D7597E.2080209@gmail.com>
Date: Tue, 29 Jul 2014 16:21:18 +0800
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:31.0) Gecko/20100101 Thunderbird/31.0
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Very slow planning performance on partition table
References: <53CFB6BC.4050606@gmail.com>	<25010.1406253203@sss.pgh.pa.us>
	<53D26875.2080604@gmail.com>	<53D51E49.102@gmail.com>
	<24564.1406478487@sss.pgh.pa.us>
 <CAMkU=1ydmpt9XRMxt0sPNnQsXEoF_c7bgp2kHxtDbPNGg5Vj5w@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1ydmpt9XRMxt0sPNnQsXEoF_c7bgp2kHxtDbPNGg5Vj5w@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

More information found. After the hang connection appears, I noticed 
there were several hundreds of connections of the same user. Since I use 
pgbouncer and I only set the pool size to 50 for each user, this is very 
strange.  I checked the pgbouncer side, 'show pools' showed the active 
server connection count is less than 50(only 35 actually). I also 
checked the client port which is shown in pg process list. It is not 
used at pgbouncer side when I did the check. So I stopped pgbouncer then 
the connection count from the user drops slowly. Finally all those 
connections disappeared. After that I restarted pgbouncer and it looks 
good again.
With this solution, I at least don't have to kill pg when the problem 
happens. But anyone has a clue why this happens? What I need to check 
for the root cause? One thing I forgot to check is the network status of 
those orphan connections at pg side. I will check it next time and see 
if they are in abnormal status.


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