Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52016=pgsql-performance-ar@postgresql.org>)
 id 1XCISl-0004XT-1O
 for pgsql-performance@arkaria.postgresql.org; Wed, 30 Jul 2014 01:13:51 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52016=pgsql-performance-ar@postgresql.org>)
 id 1XCISj-0008Nt-QK
 for pgsql-performance@arkaria.postgresql.org; Wed, 30 Jul 2014 01:13:49 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <ruralhunter@gmail.com>)
 id 1XCISi-0008Ni-IM
 for pgsql-performance@postgresql.org; Wed, 30 Jul 2014 01:13:48 +0000
Received: from mail-pa0-x231.google.com ([2607:f8b0:400e:c03::231])
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <ruralhunter@gmail.com>)
 id 1XCISf-0000FV-Bn
 for pgsql-performance@postgresql.org; Wed, 30 Jul 2014 01:13:46 +0000
Received: by mail-pa0-f49.google.com with SMTP id hz1so551323pad.8
 for <pgsql-performance@postgresql.org>; Tue, 29 Jul 2014 18:13:44 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=from:message-id:date:user-agent:mime-version:to:cc:subject
 :references:in-reply-to:content-type:content-transfer-encoding;
 bh=BjkxkqVmVecfgjuFfrQrczoCuqlbF4+QnImHv8RrGLk=;
 b=AmjvplwygoX1hTBMB4HlRf8KG3j7a7D1elBs5wi6dAYNHi9NN5aGi0ZumnzKQ7EBkv
 iS9DmSEW0AzXdJA9jpIqEI3lTjMLQlAxVK3qdudldoKglf4jh7OQcZcuhkbpxq0WvjWI
 BGAFc8EctDA3KJDjvRKI4s1xsjCn9MVhz8HRck8p8HTQxJm+clMHPkxaCFE/uSJwHEM1
 VA+76TxwfUjI0yhHo0q8ov7JXHXxUwtbVAvKLDerp5GgetYuYjETHxps1+FtR0XiQOrp
 H5a7Qr4Vys3WANM7DoJmiOjmOKXjDxLrSgKpw0E6nTgIZ+XwWKQyz/ju3DQS6wi5gzG9
 xlyw==
X-Received: by 10.68.103.66 with SMTP id fu2mr637635pbb.133.1406682823933;
 Tue, 29 Jul 2014 18:13:43 -0700 (PDT)
Received: from [192.168.2.104] ([116.226.74.154])
 by mx.google.com with ESMTPSA id tu10sm424553pbc.43.2014.07.29.18.13.41
 for <multiple recipients>
 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128);
 Tue, 29 Jul 2014 18:13:43 -0700 (PDT)
From: Rural Hunter <ruralhunter@gmail.com>
X-Google-Original-From: Rural Hunter <RuralHunter@gmail.com>
Message-ID: <53D846C4.3020503@gmail.com>
Date: Wed, 30 Jul 2014 09:13:40 +0800
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:31.0) Gecko/20100101 Thunderbird/31.0
MIME-Version: 1.0
To: Jeff Janes <jeff.janes@gmail.com>
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: 8bit
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

在 2014/7/30 1:27, Jeff Janes 写道:
>
>
> It sounds like someone is bypassing your pgbouncer and connecting 
> directly to your database.  Maybe they tried to create their own 
> parallelization and have a master connection going through pgbouncer 
> and create many auxiliary connections that go directly to the database 
> (probably because pgbouncer wouldn't let them create as many 
> connections as they wanted through it).  That would explain why the 
> connections slowly drain away once pgbouncer is shut down.
>
> Can you change your pg_hba.conf file so that it only allows 
> connections from pgbouncer's IP address?  This should flush out the 
> culprit pretty quickly.
>
> Cheers,
>
> Jeff
I suspected that first. But after I checked a few things, I am quite 
sure this is not someone bypassing the pgbouncer.
1. The connections were all from the host of pgbouncer.
2. The id is an application id and no human has access to it. There was 
no other suspect applications running on the host of pgbouncer when the 
problem happened.
3. When I found the problem and checked the connections on the host of 
pgbouncer, those network connection actually didn't exist on the client 
side while they were still hanging at pg server side.


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