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