pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: User concurrency thresholding: where do I look?
Date: Thu, 19 Jul 2007 11:49:12 -0400
Message-ID: <20070719154912.GG6538@alvh.no-ip.org> (raw)
In-Reply-To: <200707190828.59088.josh@agliodbs.com>
References: <200707190828.59088.josh@agliodbs.com>

Josh Berkus wrote:
> Folks,
> 
> I've run into this a number of times with various PostgreSQL users, so we 
> tested it at Sun.  What seems to be happening is that at some specific number 
> of connections average throughput drops 30% and response time quadruples or 
> worse.  The amount seems to vary per machine; I've seen it as variously 95, 
> 1050, 1700 or 2800 connections.  Tinkering with postgresql.conf parameters 
> doesn't seem to affect this threshold.
> 
> As an example of this behavior:
> 
> Users	Txn/User  Resp. Time
> 50	105.38	0.01
> 100	113.05	0.01
> 150	114.05	0.01
> 200	113.51	0.01
> 250	113.38	0.01
> 300	112.14	0.01
> 350	112.26	0.01
> 400	111.43	0.01
> 450	110.72	0.01
> 500	110.44	0.01
> 550	109.36	0.01
> 600	107.01	0.02
> 650	105.71	0.02
> 700	106.95	0.02
> 750	107.69	0.02
> 800	106.78	0.02
> 850	108.59	0.02
> 900	106.03	0.02
> 950	106.13	0.02
> 1000	64.58	0.15
> 1050	52.32	0.23
> 1100	49.79	0.25
> 
> Tinkering with shared_buffers has had no effect on this threholding (the above 
> was with 3gb to 6gb of shared_buffers).   Any ideas on where we should look 
> for the source of the bottleneck?

Have you messed with max_connections and/or max_locks_per_transaction
while testing this?  The lock table is sized to max_locks_per_xact times
max_connections, and shared memory hash tables get slower when they are
full.  Of course, the saturation point would depend on the avg number of
locks acquired per user, which would explain why you are seeing a lower
number for some users and higher for others (simpler/more complex
queries).

This is just a guess though.  No profiling or measuring at all, really.

-- 
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"How amazing is that? I call it a night and come back to find that a bug has
been identified and patched while I sleep."                (Robert Davidson)
               http://archives.postgresql.org/pgsql-sql/2006-03/msg00378.php



view thread (58+ messages)  latest in thread

Message-ID: <20070719154912.GG6538@alvh.no-ip.org>
Permalink:  ../20070719154912.GG6538@alvh.no-ip.org/
Also on:    postgresql.org/message-id/20070719154912.GG6538@alvh.no-ip.org

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: alvherre@commandprompt.com, josh@agliodbs.com
  Subject: Re: User concurrency thresholding: where do I look?
  In-Reply-To: <20070719154912.GG6538@alvh.no-ip.org>
  References: <200707190828.59088.josh@agliodbs.com> <20070719154912.GG6538@alvh.no-ip.org>

* 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