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"

------=_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--