From flavio@4linux.com.br Wed Mar  4 00:28:56 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 03117634B13
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  3 Mar 2009 20:29:06 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 86579-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  3 Mar 2009 20:29:00 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mta.4linux.com.br (boca.4linux.com.br [200.212.122.137])
	by mail.postgresql.org (Postfix) with ESMTP id F0ECC634AA5
	for <pgsql-performance@postgresql.org>;
	Tue,  3 Mar 2009 20:28:59 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mta.4linux.com.br (Postfix) with ESMTP id 9E5AFCA3A
	for <pgsql-performance@postgresql.org>;
	Tue,  3 Mar 2009 21:28:55 -0300 (BRT)
X-Virus-Scanned: amavisd-new at mta.4linux.com.br
Received: from mta.4linux.com.br ([127.0.0.1])
	by localhost (mta.4linux.com.br [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id vDYWut62lAsE for <pgsql-performance@postgresql.org>;
	Tue,  3 Mar 2009 21:28:55 -0300 (BRT)
Received: from mail.4linux.com.br (unknown [192.168.0.11])
	by mta.4linux.com.br (Postfix) with ESMTP id 24C20CA1A
	for <pgsql-performance@postgresql.org>;
	Tue,  3 Mar 2009 21:28:55 -0300 (BRT)
Date: Tue, 3 Mar 2009 21:28:56 -0300 (BRT)
From: Flavio Henrique Araque Gurgel <flavio@4linux.com.br>
To: pgsql-performance@postgresql.org
Message-ID: <30792576.96971236126536868.JavaMail.root@mail.4linux.com.br>
In-Reply-To: <29144661.96951236126415852.JavaMail.root@mail.4linux.com.br>
Subject: work_mem in high transaction rate database
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_3862_22609697.1236126536867"
X-Originating-IP: [189.120.19.157]
X-Mailer: Zimbra 5.0.13_GA_2791.UBUNTU6 (ZimbraWebClient - [unknown]
	(Linux)/5.0.13_GA_2791.UBUNTU6)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200903/38
X-Sequence-Number: 32960

------=_Part_3862_22609697.1236126536867
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


Hello all 

In a dedicated server with 16 cores and 16GB of RAM running PostgreSQL 8.2.5 we have a database with basically two kinds of transactions: 
- short transactions with a couple of updates and inserts that runs all the day; 
- batch data loads with hundreds of inserts that runs several times a day; 
- one delete for thousands of lines after each batch; 
- selects are made when users need reports, low concurrency here. 

Today the max_connections is ~2500 where the application is a cluster of JBoss servers with a pool a bit smaller then this total. 
work_mem = 1GB 
maintenance_work_mem = 1GB 
shared_buffers = 4GB 

autovacuum takes a lot of time running in the largest tables (3 large tables in 50) causing some connections to have to wait for it to finish to start transactioning again. 

I see a few processes (connections) using 10 ~ 20% of total system memory and the others using no more then 1%. 

What I want to ask is: is it better to keep the work_mem as high as it is today or is it a safe bet triyng to reduce this number, for example, to 1 or 2MB so I can keep the distribution of memory more balanced among all connections? 

Thanks! 

Flavio Henrique A. Gurgel 


------=_Part_3862_22609697.1236126536867
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<html><head><style type='text/css'>p { margin: 0; }</style></head><body><div style='font-family: Times New Roman; font-size: 12pt; color: #000000'><style>p { margin: 0; }</style><div style="font-family: Times New Roman; font-size: 12pt; color: rgb(0, 0, 0);">Hello all<br><br>In a dedicated server with 16 cores and 16GB of RAM
running PostgreSQL 8.2.5 we have a database with basically two kinds of
transactions:<br>- short transactions with a couple of updates and inserts that runs all the day;<br>- batch data loads with hundreds of inserts that runs several times a day;<br>- one delete for thousands of lines after each batch;<br>- selects are made when users need reports, low concurrency here.<br><br><span class="Object" id="OBJ_PREFIX_DWT201">Today</span> the max_connections is ~2500 where the application is a cluster of JBoss servers with a pool a bit smaller then this total.<br>work_mem = 1GB<br>maintenance_work_mem = 1GB<br>shared_buffers = 4GB<br><br>autovacuum
takes a lot of time running in the largest tables (3 large tables in
50) causing some connections to have to wait for it to finish to start
transactioning again.<br><br>I see a few processes (connections) using 10 ~ 20% of total system memory and the others using no more then 1%.<br><br>What I want to ask is: is it better to keep the work_mem as high as it is <span class="Object" id="OBJ_PREFIX_DWT202">today</span>
or is it a safe bet triyng to reduce this number, for example, to 1 or
2MB so I can keep the distribution of memory more balanced among all
connections?<br><br>Thanks!<br><br>Flavio Henrique A. Gurgel<br><br></div></div></body></html>
------=_Part_3862_22609697.1236126536867--



From scott.marlowe@gmail.com Wed Mar  4 01:37:42 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 71B3D633788
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  3 Mar 2009 21:37:47 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 60715-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  3 Mar 2009 21:37:43 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.29])
	by mail.postgresql.org (Postfix) with ESMTP id DC1DC632330
	for <pgsql-performance@postgresql.org>;
	Tue,  3 Mar 2009 21:37:43 -0400 (AST)
Received: by yx-out-2324.google.com with SMTP id 3so1776170yxj.73
	for <pgsql-performance@postgresql.org>;
	Tue, 03 Mar 2009 17:37:42 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=nhI1lTDblheUyQv0SbVY1WPKEdVAJ4TcvthfEra5hc4=;
	b=A4xDw8DG1qh3XgJ1ChPY62zXSTBjjPuVe136TTeIICqvo7Kp1YSUTp9FYbRDwpiOlq
	f161IEOECNFP5W2N8grdX74yWd1DPxweiPk4qVK1G0LTRkllgZG7iV4RXdbtTmm9NQmx
	IJ3n8ubXEmEeGrUxWD9hGibTlTm9WDkV8PkNE=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=FTGfWb/1wt6sN8+uA8X0i2tH1EMGlj6f4e+tC1hp8BwM3+OmCw+XFHryMuRIgG8ukz
	/7URl2KPDfKtiw1xipCaS2pq0lDtaFAZdjmdiLEWP62ozB+rYeYe52TttzxKjfib8sVB
	B7G+Q5hrQ7sZi/+y1+yanC5TKEL4Z8WV6eGrM=
MIME-Version: 1.0
Received: by 10.151.101.20 with SMTP id d20mr13553071ybm.185.1236130662611;
	Tue, 03 Mar 2009 17:37:42 -0800 (PST)
In-Reply-To: <30792576.96971236126536868.JavaMail.root@mail.4linux.com.br>
References: <29144661.96951236126415852.JavaMail.root@mail.4linux.com.br>
	<30792576.96971236126536868.JavaMail.root@mail.4linux.com.br>
Date: Tue, 3 Mar 2009 18:37:42 -0700
Message-ID: <dcc563d10903031737ne79c349r4740dbad68e04640@mail.gmail.com>
Subject: Re: work_mem in high transaction rate database
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Flavio Henrique Araque Gurgel <flavio@4linux.com.br>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200903/39
X-Sequence-Number: 32961

On Tue, Mar 3, 2009 at 5:28 PM, Flavio Henrique Araque Gurgel
<flavio@4linux.com.br> wrote:
> Hello all
>
> In a dedicated server with 16 cores and 16GB of RAM running PostgreSQL 8.2.5
> we have a database with basically two kinds of transactions:
> - short transactions with a couple of updates and inserts that runs all the
> day;
> - batch data loads with hundreds of inserts that runs several times a day;
> - one delete for thousands of lines after each batch;
> - selects are made when users need reports, low concurrency here.
>
> Today the max_connections is ~2500 where the application is a cluster of
> JBoss servers with a pool a bit smaller then this total.
> work_mem = 1GB
> maintenance_work_mem = 1GB
> shared_buffers = 4GB

Oh my lord, that is a foot gun waiting to go off.  Assuming 2k
connections, and somehow a fair number of them went active with big
sorts, you'd be able to exhaust all physical memory  with about 8 to
16 connections.  Lower work_mem now. To something like 1 to 4 Meg.  Do
not pass go.  If some oddball query really needs a lot of work_mem,
and benchmarks show something larger work_mem helps, consider raising
the work_mem setting for that one query to something under 1G (way
under 1G) That makes it noticeably faster.  Don't allocate more than a
test shows you helps.

> autovacuum takes a lot of time running in the largest tables (3 large tables
> in 50) causing some connections to have to wait for it to finish to start
> transactioning again.

Vacuum does not block transactions.  unless you're dropping tables or something.

> I see a few processes (connections) using 10 ~ 20% of total system memory
> and the others using no more then 1%.

This is commonly misread.  It has to do with the vagaries of shared
memory allocation and accounting.  The numbers quite likely don't mean
what you think they mean.  Post the first 20 or so lines from top to
show us.

> What I want to ask is: is it better to keep the work_mem as high as it is
> today or is it a safe bet triyng to reduce this number, for example, to 1 or
> 2MB so I can keep the distribution of memory more balanced among all
> connections?

You're work_mem is dangerously high.  Your current reading of top may
not actually support lowering it directly.  Since you've got 4G
shared_buffers allocated, any process that's touched all or most of
shared_buffer memory will show as using 4G of ram.  That's why you
should post output of top, or google on linux virtual memory and top
and what the numbers mean.

Let's say that 1% of your queries can benefit from > 100Meg work_mem,
and 5% with 60M, and 10% with 40M, and 20% with 20M, and 30% with 16M,
and 50% 8M and 4M is enough for all the else to do well.

If, somehow, 100 queries fired off that could use > 100Meg, they
might, with your current settings use all your memory and start using
swap til swap ran out and they started getting out of memory errors
and failing.  This would affect all the other queries on the machine
as well.

OTOH, if you had work_mem limited to 16M, and 100 of those same
queries fired off, they'd individually run a little slower, but they
wouldn't be able to run the machine out of memory.

If your work_mem and max_connections multiplied is > than some
fraction of memory you're doing it wrong, and setting your machine up
for mysterious, heavy load failures, the worst kind.



From akos.gabriel@i-logic.hu Wed Mar  4 07:04:57 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 0EE7F634E74
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Mar 2009 03:05:09 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 98468-01-2
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Mar 2009 03:05:05 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smarty.gabriel.co.hu (smarty.gabriel.co.hu [195.70.37.16])
	by mail.postgresql.org (Postfix) with ESMTP id 3D654634307
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Mar 2009 03:05:02 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by smarty.gabriel.co.hu (Postfix) with ESMTP id 71D3A40ED3
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Mar 2009 08:04:59 +0100 (CET)
X-Virus-Scanned: Debian amavisd-new at 
Received: from smarty.gabriel.co.hu ([127.0.0.1])
	by localhost (smarty.i-logic.hu [127.0.0.1]) (amavisd-new, port 10024)
	with LMTP id Vdfcvd4degod for <pgsql-performance@postgresql.org>;
	Wed,  4 Mar 2009 08:04:59 +0100 (CET)
Received: from gabriel-laptop (catv-80-99-23-11.catv.broadband.hu
	[80.99.23.11])
	(using TLSv1 with cipher DHE-RSA-AES128-SHA (128/128 bits))
	(No client certificate requested)
	by smarty.gabriel.co.hu (Postfix) with ESMTPSA id 37655358AF
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Mar 2009 08:04:59 +0100 (CET)
Date: Wed, 4 Mar 2009 08:04:57 +0100
From: Akos Gabriel <akos.gabriel@i-logic.hu>
To: pgsql-performance@postgresql.org
Subject: Re: work_mem in high transaction rate database
Message-ID: <20090304080457.50578136@gabriel-laptop>
In-Reply-To: <dcc563d10903031737ne79c349r4740dbad68e04640@mail.gmail.com>
References: <29144661.96951236126415852.JavaMail.root@mail.4linux.com.br>
	<30792576.96971236126536868.JavaMail.root@mail.4linux.com.br>
	<dcc563d10903031737ne79c349r4740dbad68e04640@mail.gmail.com>
Organization: I-Logic Kft
X-Mailer: Claws Mail 3.7.0 (GTK+ 2.14.4; i486-pc-linux-gnu)
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-2
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200903/40
X-Sequence-Number: 32962

Tue, 3 Mar 2009 18:37:42 -0700 -n
Scott Marlowe <scott.marlowe@gmail.com> =EDrta:


> Oh my lord, that is a foot gun waiting to go off.  Assuming 2k
> connections, and somehow a fair number of them went active with big

I absolutely agree with Scott. Plus set effective_cache_size
accordingly, this would help the planner. You can read a lot about
setting this in the mailing list archives.

--=20
=DCdv=F6zlettel,
G=E1briel =C1kos
-=3DE-Mail :akos.gabriel@i-logic.hu|Web:  http://www.i-logic.hu=3D-
-=3DTel/fax:+3612367353            |Mobil:+36209278894         =3D-



From dfontaine@hi-media.com Wed Mar  4 09:16:07 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id F00D16339B7
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Mar 2009 05:18:23 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 34540-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Mar 2009 05:18:20 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp1.hi-media-techno.com (smtp1.hi-media-techno.com
	[195.158.241.85])
	by mail.postgresql.org (Postfix) with ESMTP id 2C5A76323E3
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Mar 2009 05:18:19 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by smtp1.hi-media-techno.com (Postfix) with ESMTP id E326293D40;
	Wed,  4 Mar 2009 10:18:17 +0100 (CET)
X-Virus-Scanned: Debian amavisd-new at smtp1.backbone.hi-media-techno.com
Received: from smtp1.hi-media-techno.com ([127.0.0.1])
	by localhost (smtp1.backbone.hi-media-techno.com [127.0.0.1])
	(amavisd-new, port 10024)
	with ESMTP id te1KQMv2teCE; Wed,  4 Mar 2009 10:18:13 +0100 (CET)
Received: from mail1.hi-media-techno.com (unknown [192.168.27.12])
	by smtp1.hi-media-techno.com (Postfix) with ESMTP id 4AF0593BB0;
	Wed,  4 Mar 2009 10:18:13 +0100 (CET)
Received: from localhost (localhost [127.0.0.1])
	by mail1.hi-media-techno.com (Postfix) with ESMTP id 456A76D8588;
	Wed,  4 Mar 2009 10:18:13 +0100 (CET)
X-Virus-Scanned: amavisd-new at 
Received: from mail1.hi-media-techno.com ([127.0.0.1])
	by localhost (mail1.hi-media-techno.com [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id M+BGeDSIUhdL; Wed,  4 Mar 2009 10:18:13 +0100 (CET)
Received: from dim.localnet (unknown [83.167.62.196])
	by mail1.hi-media-techno.com (Postfix) with ESMTP id 0E0E26D8589;
	Wed,  4 Mar 2009 10:18:13 +0100 (CET)
From: Dimitri Fontaine <dfontaine@hi-media.com>
Organization: Hi-Media
To: pgsql-performance@postgresql.org
Subject: Re: work_mem in high transaction rate database
Date: Wed, 4 Mar 2009 10:16:07 +0100
User-Agent: KMail/1.11.0 (Linux/2.6.26-1-686; KDE/4.2.0; i686; ; )
Cc: Scott Marlowe <scott.marlowe@gmail.com>,
	Flavio Henrique Araque Gurgel <flavio@4linux.com.br>
References: <29144661.96951236126415852.JavaMail.root@mail.4linux.com.br>
	<30792576.96971236126536868.JavaMail.root@mail.4linux.com.br>
	<dcc563d10903031737ne79c349r4740dbad68e04640@mail.gmail.com>
In-Reply-To: <dcc563d10903031737ne79c349r4740dbad68e04640@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart8787068.HPSRusOfh6";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200903041016.09765.dfontaine@hi-media.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200903/41
X-Sequence-Number: 32963

--nextPart8787068.HPSRusOfh6
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,

On Wednesday 04 March 2009 02:37:42 Scott Marlowe wrote:
> If some oddball query really needs a lot of work_mem,
> and benchmarks show something larger work_mem helps, consider raising
> the work_mem setting for that one query to something under 1G (way
> under 1G) That makes it noticeably faster. =A0Don't allocate more than a
> test shows you helps.

The probably easiest way to integrate this into an existing application is=
=20
this way, in my experience:

 BEGIN;
  SET LOCAL work_mem TO '650MB';
  SELECT -- the query requiring such a large setting
 COMMIT;

Right after the commit the global configured work_mem (or the previous=20
session's one, in fact) will be in effect, you won't have to reset it yours=
elf.

Regards,
=2D-=20
dim

--nextPart8787068.HPSRusOfh6
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkmuRtcACgkQlBXRlnbh1bnkegCgiHfS/kliSBevBotRmaYnY9bO
zkMAnidr2LL5i9+u4gdO1tXoniYgXg4o
=urWC
-----END PGP SIGNATURE-----

--nextPart8787068.HPSRusOfh6--




From flavio@4linux.com.br Wed Mar  4 12:46:27 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 2E139632220
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Mar 2009 08:46:34 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 54703-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Mar 2009 08:46:29 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mta.4linux.com.br (boca.4linux.com.br [200.212.122.137])
	by mail.postgresql.org (Postfix) with ESMTP id 5A05B62DDA8
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Mar 2009 08:46:27 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mta.4linux.com.br (Postfix) with ESMTP id 0D610CA5D
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Mar 2009 09:46:25 -0300 (BRT)
X-Virus-Scanned: amavisd-new at mta.4linux.com.br
Received: from mta.4linux.com.br ([127.0.0.1])
	by localhost (mta.4linux.com.br [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id Vw6OtVdYNbU6 for <pgsql-performance@postgresql.org>;
	Wed,  4 Mar 2009 09:46:23 -0300 (BRT)
Received: from mail.4linux.com.br (unknown [192.168.0.11])
	by mta.4linux.com.br (Postfix) with ESMTP id 3DE96CA5B
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Mar 2009 09:46:23 -0300 (BRT)
Date: Wed, 4 Mar 2009 09:46:27 -0300 (BRT)
From: Flavio Henrique Araque Gurgel <flavio@4linux.com.br>
To: pgsql-performance <pgsql-performance@postgresql.org>
Message-ID: <13904207.99291236170787203.JavaMail.root@mail.4linux.com.br>
In-Reply-To: <31859115.99181236170659339.JavaMail.root@mail.4linux.com.br>
Subject: Re: work_mem in high transaction rate database
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_3920_1913431.1236170787202"
X-Originating-IP: [192.168.0.189]
X-Mailer: Zimbra 5.0.13_GA_2791.UBUNTU6 (ZimbraWebClient - [unknown]
	(Linux)/5.0.13_GA_2791.UBUNTU6)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200903/42
X-Sequence-Number: 32964

------=_Part_3920_1913431.1236170787202
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

----- "Scott Marlowe" <scott.marlowe@gmail.com> escreveu: 
> Oh my lord, that is a foot gun waiting to go off. Assuming 2k 
> connections, and somehow a fair number of them went active with big 
> sorts, you'd be able to exhaust all physical memory with about 8 to 
> 16 connections. Lower work_mem now. To something like 1 to 4 Meg. Do 
> not pass go. If some oddball query really needs a lot of work_mem, 
> and benchmarks show something larger work_mem helps, consider raising 
> the work_mem setting for that one query to something under 1G (way 
> under 1G) That makes it noticeably faster. Don't allocate more than a 
> test shows you helps. 

Thanks a lot Scott. That's what I thought in the beginning but was very doubtful since the documentation is a bit odd regarding this point and several bloggers talk about increasing this value up to 250MB. I really think that separating regular non pooled distributed applications and pooled application servers makes a lot of difference in this point. 

> Vacuum does not block transactions. unless you're dropping tables or something. 

I'll try to separate things and check if the DELETE queries have something related here. 

(...) 
> what you think they mean. Post the first 20 or so lines from top to 
> show us. 

Unfortunately I can't do it. The data there is very sensitive (it's a public company here in Brazil) and the server is operated only by selected personal. I just ask for information and give written recomendations. Anyway, I'm going to pay some more attention in this topic. 

This is a very interesting implementation of PostgreSQL (3 large databases, heavy load, things growing all the time) and I'll let you all know what happened when tuning it. I'll feedback you after lowering work_mem and changing related settings. 

Thanks 
Flavio 

------=_Part_3920_1913431.1236170787202
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><style type=3D'text/css'>p { margin: 0; }</style></head><body><=
div style=3D'font-family: Times New Roman; font-size: 12pt; color: #000000'=
>----- "Scott Marlowe" &lt;scott.marlowe@gmail.com&gt; escreveu:
<br>&gt; Oh my lord, that is a foot gun waiting to go off. &nbsp;Assuming 2=
k<br>&gt; connections, and somehow a fair number of them went active with b=
ig<br>&gt; sorts, you'd be able to exhaust all physical memory &nbsp;with a=
bout 8 to<br>&gt; 16 connections. &nbsp;Lower work_mem now. To something li=
ke 1 to 4 Meg. &nbsp;Do<br>&gt; not pass go. &nbsp;If some oddball query re=
ally needs a lot of work_mem,<br>&gt; and benchmarks show something larger =
work_mem helps, consider raising<br>&gt; the work_mem setting for that one =
query to something under 1G (way<br>&gt; under 1G) That makes it noticeably=
 faster. &nbsp;Don't allocate more than a<br>&gt; test shows you helps.<br>=
<br>Thanks a lot Scott. That's what I thought in the beginning but was very=
 doubtful since the documentation is a bit odd regarding this point and sev=
eral bloggers talk about increasing this value up to 250MB. I really think =
that separating regular non pooled distributed applications and pooled appl=
ication servers makes a lot of difference in this point.<br><br>&gt; Vacuum=
 does not block transactions. &nbsp;unless you're dropping tables or someth=
ing.<br><br>I'll try to separate things and check if the DELETE queries hav=
e something related here.<br><br>(...)<br>&gt; what you think they mean. &n=
bsp;Post the first 20 or so lines from top to<br>&gt; show us.<br><br>Unfor=
tunately I can't do it. The data there is very sensitive (it's a public com=
pany here in Brazil) and the server is operated only by selected personal. =
I just ask for information and give written recomendations. Anyway, I'm goi=
ng to pay some more attention in this topic.<br><br>This is a very interest=
ing implementation of PostgreSQL (3 large databases, heavy load, things gro=
wing all the time) and I'll let you all know what happened when tuning it. =
I'll feedback you after lowering work_mem and changing related settings.<br=
><br>Thanks<br>Flavio<br></div></body></html>
------=_Part_3920_1913431.1236170787202--



From scott@richrelevance.com Wed Mar  4 18:18:45 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 372AF632FD8
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Mar 2009 16:09:50 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 20655-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Mar 2009 16:09:37 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from EXHUB018-3.exch018.msoutlookonline.net
	(exhub018-3.exch018.msoutlookonline.net [64.78.17.18])
	by mail.postgresql.org (Postfix) with ESMTP id 71AE06326B5
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Mar 2009 16:09:37 -0400 (AST)
Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by
	EXHUB018-3.exch018.msoutlookonline.net ([64.78.17.18]) with mapi;
	Wed, 4 Mar 2009 10:18:49 -0800
From: Scott Carey <scott@richrelevance.com>
To: Flavio Henrique Araque Gurgel <flavio@4linux.com.br>, pgsql-performance
	<pgsql-performance@postgresql.org>
Date: Wed, 4 Mar 2009 10:18:45 -0800
Subject: Re: work_mem in high transaction rate database
Thread-Topic: [PERFORM] work_mem in high transaction rate database
Thread-Index: Acmcx0zkHDNDAwxPTcyjpbW0uyqW4AALlq/g
Message-ID: <C5D40605.2F56%scott@richrelevance.com>
In-Reply-To: <13904207.99291236170787203.JavaMail.root@mail.4linux.com.br>
Accept-Language: en-US
Content-Language: en
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: en-US
Content-Type: multipart/alternative;
	boundary="_000_C5D406052F56scottrichrelevancecom_"
MIME-Version: 1.0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200903/44
X-Sequence-Number: 32966

--_000_C5D406052F56scottrichrelevancecom_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

You may have decreased performance in your batch jobs with the lower work_m=
em setting.
Additionally, the fact that you haven't had swap storm issues so far means =
that although there is certain risk of an issue, its probably a lot lower t=
han what has been talked about here so far.
Without a change in client behavior (new queries or large change in data) a=
 change in load alone is very unlikely to cause a problem.  So take your ti=
me to do it right.  I disagree with the knee-jerk "change it now!" response=
.  The very fact you have gotten this far means it is not as risky as the b=
are settings indicate.
Definitely plan on improving and testing out work_mem variants soon, but a =
hasty change to a small value might cause your batch jobs to take much long=
er - what is your risk if they take too long or don't complete in time?  Th=
at risk is yours to assess - if its not much of a risk, then by all means l=
ower work_mem soon.  But if it is business critical for those batch jobs to=
 complete within some time frame, be careful.

If you know what you are doing, and are careful,  your work_mem is probably=
, but not necessarily too high.
It all depends on how much you know about your clients.
For example 2010 connections with 500MB work_mem is not always a problem.  =
If you know 2000 of those are from an application that accesses with a user=
 that can only see small tables, and you know what queries those are, it ma=
y be perfectly safe.  For example, I've dealt with an application that had =
a couple thousand connections, 95% were idle at any time (connection pool m=
uch like those likely from your Jboss apps).  The queries these ran were a =
limited set of about 20 statements that all accessed by unique key on small=
-ish sized tables (<30MB) with few joins.  There were tons of connections, =
but they:
1: hardly did anything, most were idle.  On 75% of the connections, a query=
 set was run exactly once every 15 minutes selecting * from small (sub 1MB)=
 tables.
2: the more active connections (20%) did small select queries on single row=
s accessed by primary key.

So, the calculation  max connections * work_mem is utterly inappropriate fo=
r that sort of workload.   Yes, in theory, those connections could use work=
_mem * some factor of memory - if they changed their queries, and accessed =
other tables.  In practice - nowhere close.

The remaining few connections(~5) were batch jobs that needed ~800MB of wor=
k_mem or else the performance would stink.  And they didn't need 800MB of w=
ork_mem for real (the hashes used ~250MB) they needed a SETTING of 800MB be=
cause the planner is incapable of estimating row counts properly with parti=
tioned table access.
Both applications were not able to configure their own work_mem for quite s=
ome time (getting client applications to change is not always a quick proce=
ss).
But the risk of having a large setting, even with 2000 connections was low.=
  The risk of changing it too low was very high (batch jobs taking 3 hours =
instead of 10 minutes).  Only 5 ish connections even accessed schemas/table=
s with lots of data.  The remaining couple thousand were constrained in man=
y ways other than work_mem.

In the end I did have swap storms... However it was not caused by work_mem.=
  It was the query planner, which can use GBs of memory per connection plan=
ning a query on partitioned tables.

So, my point is that if you don't know a lot about the database or its clie=
nts be very wary of large work_mem settings.  If you do, and have a lot of =
control or knowledge about your clients, the work_mem * max_connections cal=
culation is inappropriate.

The detailed formula is along the lines of:
SUM_i [work_mem_i * active_connecions_i]    (for each 'type' of connection =
i).
If you don't know enough about your connections, then the conservative esti=
mate is work_mem * max_connections.

A single query has the potential of using multiples of work_mem depending o=
n how many concurrent hashes / sorts are in a query, so the above is not qu=
ite right either.

Is there a way to make a particular database user have a user-local work_me=
m setting without having the client change their code? You could then have =
each application have its own user, with its own default setting.  The batc=
h jobs with few connections can get much larger work_mem than the Jboss one=
s.  This would be especially powerful for applications that can't change or=
 that use higher level tools for db access that make it impossible or very =
difficult to send non-standard commands like "SET".

On 3/4/09 4:46 AM, "Flavio Henrique Araque Gurgel" <flavio@4linux.com.br> w=
rote:

----- "Scott Marlowe" <scott.marlowe@gmail.com> escreveu:
> Oh my lord, that is a foot gun waiting to go off.  Assuming 2k
> connections, and somehow a fair number of them went active with big
> sorts, you'd be able to exhaust all physical memory  with about 8 to
> 16 connections.  Lower work_mem now. To something like 1 to 4 Meg.  Do
> not pass go.  If some oddball query really needs a lot of work_mem,
> and benchmarks show something larger work_mem helps, consider raising
> the work_mem setting for that one query to something under 1G (way
> under 1G) That makes it noticeably faster.  Don't allocate more than a
> test shows you helps.

Thanks a lot Scott. That's what I thought in the beginning but was very dou=
btful since the documentation is a bit odd regarding this point and several=
 bloggers talk about increasing this value up to 250MB. I really think that=
 separating regular non pooled distributed applications and pooled applicat=
ion servers makes a lot of difference in this point.

> Vacuum does not block transactions.  unless you're dropping tables or som=
ething.

I'll try to separate things and check if the DELETE queries have something =
related here.

(...)
> what you think they mean.  Post the first 20 or so lines from top to
> show us.

Unfortunately I can't do it. The data there is very sensitive (it's a publi=
c company here in Brazil) and the server is operated only by selected perso=
nal. I just ask for information and give written recomendations. Anyway, I'=
m going to pay some more attention in this topic.

This is a very interesting implementation of PostgreSQL (3 large databases,=
 heavy load, things growing all the time) and I'll let you all know what ha=
ppened when tuning it. I'll feedback you after lowering work_mem and changi=
ng related settings.

Thanks
Flavio


--_000_C5D406052F56scottrichrelevancecom_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<HTML>
<HEAD>
<TITLE>Re: [PERFORM] work_mem in high transaction rate database</TITLE>
</HEAD>
<BODY>
<FONT FACE=3D"Calibri, Verdana, Helvetica, Arial"><SPAN STYLE=3D'font-size:=
11pt'>You may have decreased performance in your batch jobs with the lower =
work_mem setting.<BR>
Additionally, the fact that you haven&#8217;t had swap storm issues so far =
means that although there is certain risk of an issue, its probably a lot l=
ower than what has been talked about here so far.<BR>
Without a change in client behavior (new queries or large change in data) a=
 change in load alone is very unlikely to cause a problem. &nbsp;So take yo=
ur time to do it right. &nbsp;I disagree with the knee-jerk &#8220;change i=
t now!&#8221; response. &nbsp;The very fact you have gotten this far means =
it is not as risky as the bare settings indicate.<BR>
Definitely plan on improving and testing out work_mem variants soon, but a =
hasty change to a small value might cause your batch jobs to take much long=
er &#8212; what is your risk if they take too long or don&#8217;t complete =
in time? &nbsp;That risk is yours to assess &#8212; if its not much of a ri=
sk, then by all means lower work_mem soon. &nbsp;But if it is business crit=
ical for those batch jobs to complete within some time frame, be careful.<B=
R>
<BR>
If you know what you are doing, and are careful, &nbsp;your work_mem is pro=
bably, but not necessarily too high. <BR>
It all depends on how much you know about your clients. &nbsp;<BR>
For example 2010 connections with 500MB work_mem is not always a problem. &=
nbsp;If you know 2000 of those are from an application that accesses with a=
 user that can only see small tables, and you know what queries those are, =
it may be perfectly safe. &nbsp;For example, I&#8217;ve dealt with an appli=
cation that had a couple thousand connections, 95% were idle at any time (c=
onnection pool much like those likely from your Jboss apps). &nbsp;The quer=
ies these ran were a limited set of about 20 statements that all accessed b=
y unique key on small-ish sized tables (&lt;30MB) with few joins. &nbsp;The=
re were tons of connections, but they: <BR>
1: hardly did anything, most were idle. &nbsp;On 75% of the connections, a =
query set was run exactly once every 15 minutes selecting * from small (sub=
 1MB) tables. &nbsp;<BR>
2: the more active connections (20%) did small select queries on single row=
s accessed by primary key.<BR>
<BR>
So, the calculation &nbsp;max connections * work_mem is utterly inappropria=
te for that sort of workload. &nbsp;&nbsp;Yes, in theory, those connections=
 could use work_mem * some factor of memory &#8212; if they changed their q=
ueries, and accessed other tables. &nbsp;In practice &#8212; nowhere close.=
<BR>
<BR>
The remaining few connections(~5) were batch jobs that needed ~800MB of wor=
k_mem or else the performance would stink. &nbsp;And they didn&#8217;t need=
 800MB of work_mem for real (the hashes used ~250MB) they needed a SETTING =
of 800MB because the planner is incapable of estimating row counts properly=
 with partitioned table access. <BR>
Both applications were not able to configure their own work_mem for quite s=
ome time (getting client applications to change is not always a quick proce=
ss).<BR>
But the risk of having a large setting, even with 2000 connections was low.=
 &nbsp;The risk of changing it too low was very high (batch jobs taking 3 h=
ours instead of 10 minutes). &nbsp;Only 5 ish connections even accessed sch=
emas/tables with lots of data. &nbsp;The remaining couple thousand were con=
strained in many ways other than work_mem.<BR>
<BR>
In the end I did have swap storms... However it was not caused by work_mem.=
 &nbsp;It was the query planner, which can use GBs of memory per connection=
 planning a query on partitioned tables.<BR>
<BR>
So, my point is that if you don&#8217;t know a lot about the database or it=
s clients be very wary of large work_mem settings. &nbsp;If you do, and hav=
e a lot of control or knowledge about your clients, the work_mem * max_conn=
ections calculation is inappropriate.<BR>
<BR>
The detailed formula is along the lines of:<BR>
SUM_i [work_mem_i * active_connecions_i] &nbsp;&nbsp;&nbsp;(for each &#8216=
;type&#8217; of connection i).<BR>
If you don&#8217;t know enough about your connections, then the conservativ=
e estimate is work_mem * max_connections.<BR>
<BR>
A single query has the potential of using multiples of work_mem depending o=
n how many concurrent hashes / sorts are in a query, so the above is not qu=
ite right either.<BR>
<BR>
Is there a way to make a particular database user have a user-local work_me=
m setting without having the client change their code? You could then have =
each application have its own user, with its own default setting. &nbsp;The=
 batch jobs with few connections can get much larger work_mem than the Jbos=
s ones. &nbsp;This would be especially powerful for applications that can&#=
8217;t change or that use higher level tools for db access that make it imp=
ossible or very difficult to send non-standard commands like &#8220;SET&#82=
21;.<BR>
<BR>
On 3/4/09 4:46 AM, &quot;Flavio Henrique Araque Gurgel&quot; &lt;<a href=3D=
"flavio@4linux.com.br">flavio@4linux.com.br</a>&gt; wrote:<BR>
<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE=3D"Times New Roman"><SPAN STYLE=3D'fon=
t-size:12pt'>----- &quot;Scott Marlowe&quot; &lt;<a href=3D"scott.marlowe@g=
mail.com">scott.marlowe@gmail.com</a>&gt; escreveu: <BR>
&gt; Oh my lord, that is a foot gun waiting to go off. &nbsp;Assuming 2k<BR=
>
&gt; connections, and somehow a fair number of them went active with big<BR=
>
&gt; sorts, you'd be able to exhaust all physical memory &nbsp;with about 8=
 to<BR>
&gt; 16 connections. &nbsp;Lower work_mem now. To something like 1 to 4 Meg=
. &nbsp;Do<BR>
&gt; not pass go. &nbsp;If some oddball query really needs a lot of work_me=
m,<BR>
&gt; and benchmarks show something larger work_mem helps, consider raising<=
BR>
&gt; the work_mem setting for that one query to something under 1G (way<BR>
&gt; under 1G) That makes it noticeably faster. &nbsp;Don't allocate more t=
han a<BR>
&gt; test shows you helps.<BR>
<BR>
Thanks a lot Scott. That's what I thought in the beginning but was very dou=
btful since the documentation is a bit odd regarding this point and several=
 bloggers talk about increasing this value up to 250MB. I really think that=
 separating regular non pooled distributed applications and pooled applicat=
ion servers makes a lot of difference in this point.<BR>
<BR>
&gt; Vacuum does not block transactions. &nbsp;unless you're dropping table=
s or something.<BR>
<BR>
I'll try to separate things and check if the DELETE queries have something =
related here.<BR>
<BR>
(...)<BR>
&gt; what you think they mean. &nbsp;Post the first 20 or so lines from top=
 to<BR>
&gt; show us.<BR>
<BR>
Unfortunately I can't do it. The data there is very sensitive (it's a publi=
c company here in Brazil) and the server is operated only by selected perso=
nal. I just ask for information and give written recomendations. Anyway, I'=
m going to pay some more attention in this topic.<BR>
<BR>
This is a very interesting implementation of PostgreSQL (3 large databases,=
 heavy load, things growing all the time) and I'll let you all know what ha=
ppened when tuning it. I'll feedback you after lowering work_mem and changi=
ng related settings.<BR>
<BR>
Thanks<BR>
Flavio<BR>
</SPAN></FONT><FONT FACE=3D"Calibri, Verdana, Helvetica, Arial"><SPAN STYLE=
=3D'font-size:11pt'><BR>
</SPAN></FONT></BLOCKQUOTE>
</BODY>
</HTML>


--_000_C5D406052F56scottrichrelevancecom_--



From scott.marlowe@gmail.com Wed Mar  4 21:49:29 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id AC61663350E
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Mar 2009 17:49:39 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 24271-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  4 Mar 2009 17:49:32 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-gx0-f176.google.com (mail-gx0-f176.google.com
	[209.85.217.176])
	by mail.postgresql.org (Postfix) with ESMTP id 9AA4E6326B5
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Mar 2009 17:49:31 -0400 (AST)
Received: by gxk24 with SMTP id 24so8117167gxk.19
	for <pgsql-performance@postgresql.org>;
	Wed, 04 Mar 2009 13:49:29 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=/q4yabmb7Q2OJUKgl7JnuIVABbK8Oo4FYmyJ1KAczFM=;
	b=MliutTtRY+hQ155gYMlFKsPv4RLUBGoROIcZqtTOG9SGD9VBGogXTNL48urUmzsv+a
	+9TGTCP1DoQ+fN4odVC3FS8gOfVC3M0nLgZ7Y30KSDWU7vNnCE5Ar0OlSbyh9Dns+Yw/
	+rJ3aTtFFVBYHpFkgCsxlrPgMttcimEbe5Zt4=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=CpVcJ9reUuqPyqKBsq+92zmVbXGZkNh7VR9HjumXz8yLunZVVSyr7CZwaMIslZFQNt
	YQZJjW9MtA01EqqzkC9EQ69NYuE/MzeIVkn2DXW728dCJOPo5cwBqfUXWsQOEsUVX/CH
	1CJ5R+1Kon5B85/V0YudtAh40QM3L3WVJJXP4=
MIME-Version: 1.0
Received: by 10.151.150.13 with SMTP id c13mr911845ybo.63.1236203369695; Wed,
	04 Mar 2009 13:49:29 -0800 (PST)
In-Reply-To: <C5D40605.2F56%scott@richrelevance.com>
References: <13904207.99291236170787203.JavaMail.root@mail.4linux.com.br>
	<C5D40605.2F56%scott@richrelevance.com>
Date: Wed, 4 Mar 2009 14:49:29 -0700
Message-ID: <dcc563d10903041349x36e4d082wddc6ffdac89505ab@mail.gmail.com>
Subject: Re: work_mem in high transaction rate database
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Scott Carey <scott@richrelevance.com>
Cc: Flavio Henrique Araque Gurgel <flavio@4linux.com.br>,
	pgsql-performance <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200903/45
X-Sequence-Number: 32967

On Wed, Mar 4, 2009 at 11:18 AM, Scott Carey <scott@richrelevance.com> wrot=
e:
> You may have decreased performance in your batch jobs with the lower
> work_mem setting.

That would be why I recommended benchmarking queries that need more
memory and setting work_mem for those queries alone.

> Additionally, the fact that you haven=92t had swap storm issues so far me=
ans
> that although there is certain risk of an issue, its probably a lot lower
> than what has been talked about here so far.

No, it means you and the OP are guessing at what's a good number
without any actual proof of it.  Guessing it not a particularly good
method for setting work_mem, especially on a server with 2000+
connections.

> Without a change in client behavior (new queries or large change in data)=
 a
> change in load alone is very unlikely to cause a problem.

That is demonstrably incorrect.  If the average number of live queries
out of the 2000 connections is currently 10, and an increase in load
makes it 500, there is a very REAL chance of running the server out of
memory.

>=A0So take your time
> to do it right.

I believe I made mention of benchmarking queries above and in my first
post.  But doing it right does NOT mean setting work_mem to 2G then
whittling it down as your server crashes under load.

> =A0I disagree with the knee-jerk =93change it now!=94 response.
> =A0The very fact you have gotten this far means it is not as risky as the=
 bare
> settings indicate.

Sorry, but I disagree back at you, and it's not a knee jerk reaction,
it's a reaction honed from years of watching supposedly stable
postgresql servers crash and burn under slashdot effect type loads.

> Definitely plan on improving and testing out work_mem variants soon, but =
a
> hasty change to a small value might cause your batch jobs to take much
> longer =97 what is your risk if they take too long or don=92t complete in=
 time?
> =A0That risk is yours to assess =97 if its not much of a risk, then by al=
l means
> lower work_mem soon. =A0But if it is business critical for those batch jo=
bs to
> complete within some time frame, be careful.

Sorry, but that's backwards.  Unless the batch jobs are the only
important thing on this server, running it with work_mem=3D2G is asking
for trouble under any real kind of load.  It's sacrificing stability
for some unknown and quite probably minimal performance improvement.

It seems a lot of your post is based on either hoping for the best, or
assuming access patterns won't change much over time.  Do you test at
higher and higher parallel loads until failure occurs and then figure
out how to limit that type of failure?  I do, because I can't afford
to have my db servers crash and burn midday under peak load.  And you
never know when some app is gonna start suddenly spewing things like
unconstrained joins due to some bug, and if you've got work_mem set to
1G your server IS gonna have problems.