From: Rural Hunter <ruralhunter@gmail.com>
Message-ID: <53D86F05.5060007@gmail.com>
Date: Wed, 30 Jul 2014 12:05:25 +0800
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:31.0) Gecko/20100101 Thunderbird/31.0
MIME-Version: 1.0
CC: "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>
	<53D7597E.2080209@gmail.com>
 <CAMkU=1wHC_w6D2kc1V6y1PDkRxDV3B+1fJBk3Z2L=SprGw7omw@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1wHC_w6D2kc1V6y1PDkRxDV3B+1fJBk3Z2L=SprGw7omw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This happened again. This time I got the connection status(between 
pgbouncer host to pgsql host) at postgresql side. When the problem 
happens, the connection status is this:
ESTABLISHED: 188
CLOSE_WAIT: 116

The count of connections in CLOSE_WAIT is abnormal. Comparing with 
normal situation, there is usually no close_wait connection. The 
connection status sample is like this:
ESTABLISHED: 117
CLOSE_WAIT: 0

I have 4 users configured in pgbouncer and the pool_size is 50. So the 
max number of connections from pgbouncer should be less than 200.
The connection spike happens very quickly. I created a script to check 
the connections from pgbouncer. The script checks the connections from 
pgbouncer every 5 mins. This is the log:
10:55:01 CST pgbouncer is healthy. connection count: 73
11:00:02 CST pgbouncer is healthy. connection count: 77
11:05:01 CST pgbouncer is healthy. connection count: 118
11:10:01 CST pgbouncer is healthy. connection count: 115
11:15:01 CST pgbouncer is healthy. connection count: 75
11:20:01 CST pgbouncer is healthy. connection count: 73
11:25:02 CST pgbouncer is healthy. connection count: 75
11:30:01 CST pgbouncer is healthy. connection count: 77
11:35:01 CST pgbouncer is healthy. connection count: 84
11:40:10 CST Problematic connection count: 292, will restart pgbouncer...

Now I suspect there is some network problem between the hosts of 
pgbouncer and pgsql. Will check more.


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