Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X4YGD-0007vp-E4 for pgsql-performance@arkaria.postgresql.org; Tue, 08 Jul 2014 16:28:53 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1X4YGC-0000Qn-TF for pgsql-performance@arkaria.postgresql.org; Tue, 08 Jul 2014 16:28:52 +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 ) id 1X4YGB-0000Qg-Kf for pgsql-performance@postgresql.org; Tue, 08 Jul 2014 16:28:51 +0000 Received: from cerberus.pinpointresearch.com ([66.7.238.130] helo=polaris.pinpointresearch.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X4YG8-0007cT-Ke for pgsql-performance@postgresql.org; Tue, 08 Jul 2014 16:28:49 +0000 Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 73325E00EC84; Tue, 8 Jul 2014 09:28:47 -0700 (PDT) Message-ID: <53BC1C3F.1000407@pinpointresearch.com> Date: Tue, 08 Jul 2014 09:28:47 -0700 From: Steve Crawford User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 MIME-Version: 1.0 To: "Huang, Suya" , "pgsql-performance@postgresql.org" Subject: Re: DB sessions 100 times of DB connections References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------080107040400050908030905" X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------080107040400050908030905 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 07/03/2014 06:59 PM, Huang, Suya wrote: > > BTW, I'm using the pgbadger report to check for peak connections/sessions. > > *From:*pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] *On Behalf Of *Huang, Suya > *Sent:* Friday, July 04, 2014 11:44 AM > *To:* pgsql-performance@postgresql.org > *Subject:* [PERFORM] DB sessions 100 times of DB connections > > Hi, > > We've experienced a DB issue yesterday and after checked the log found > that the peak sessions is 3000 while the peak DB connections is only > around 30. The application is having problem of pulling data but no > warnings in DB log as it doesn't exceed max_connections. > > How could this happen? How does sessions/connections work in Postgres? > > As handy as pgbadger is, I have found that its max-connections values don't pass the "sniff test" as it generally shows peak values that exceed the configured number of connections. I haven't dug in to find out why but could conjecture that the fact that log entries are generally truncated to the nearest second could cause this sort of thing. Unexpected connection buildup is often a side-effect of something else like a large resource-intensive query, a query holding locks that prevent the other connections' queries from completing or a variety of other things. If you are looking to solve/prevent the undescribed "issue", please provide more detail. -Steve --------------080107040400050908030905 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 07/03/2014 06:59 PM, Huang, Suya wrote:

BTW, I’m using the pgbadger report to check for peak connections/sessions.

 

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Huang, Suya
Sent: Friday, July 04, 2014 11:44 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] DB sessions 100 times of DB connections

 

Hi,

 

We’ve experienced a DB issue yesterday and after checked the log found that the peak sessions is 3000 while the peak DB connections is only around 30. The application is having problem of pulling data but no warnings in DB log as it doesn’t exceed max_connections.

 

 

How could this happen? How does sessions/connections work in Postgres?

 


As handy as pgbadger is, I have found that its max-connections values don't pass the "sniff test" as it generally shows peak values that exceed the configured number of connections. I haven't dug in to find out why but could conjecture that the fact that log entries are generally truncated to the nearest second could cause this sort of thing.

Unexpected connection buildup is often a side-effect of something else like a large resource-intensive query, a query holding locks that prevent the other connections' queries from completing or a variety of other things.

If you are looking to solve/prevent the undescribed "issue", please provide more detail.

-Steve

--------------080107040400050908030905--