Date: Sun, 1 May 2011 14:11:06 +0000 (UTC)
From: Lou Picciano <loupicciano@comcast.net>
To: Javier Reyes <c.javier.reyes.e@gmail.com>
Cc: pgsql-admin@postgresql.org
Message-ID: 
 <1388090922.738519.1304259066269.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net>
In-Reply-To: 
 <881491270.738428.1304258927478.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net>
Subject: Re: Option shared_buffers in PostgreSQL
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_738518_336253797.1304259066269"

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

Javier, 


There has been much discussion of this. 


Generally - and though it's a bit counter-intuitive - wholesale increase in PostgreSQL's shared buffer won't give you the outcomes you might expect. In short, once there's enough, there's enough. Workmem is another option which behaves along these lines. Reason for this is that PostgreSQL makes _very_ good use of its surrounding memory environment; IE, after a certain point, it's better to focus on tweaking your OS and operating environment. 


Now, there will probably be some 'optimal' shared memory and workmem settings, given the specifics of your query and execution environment; here's where mileage will vary. But you shouldn't expect a direct and/or linear increase in performance with doubling/quadrupling of shared memory. 


You'll get answers here from folks well above my paygrade, but I think that's a fair rough outline. (!!) 


Lou Picciano 


----- Original Message ----- 
From: "Javier Reyes" <c.javier.reyes.e@gmail.com> 
To: pgsql-admin@postgresql.org 
Sent: Sunday, May 1, 2011 7:20:15 AM 
Subject: [ADMIN] Option shared_buffers in PostgreSQL 

Hello. 

I have a server with 4GB of RAM and PostgreSQL 9.0.3 on Centos 5. I'm using pgbench and pgbench-tools to measure performance, using two pgbench-tools queries: select and tpc-b. 

With default settings of postgresql.conf and select query, I get the following results: 

Scale: 1, 10, 100, 1000. 
Transactions per second: 10000, 8800, 7500, 100. 

(the number of records of the table is scale*100000) 

I've only increased the option shared_buffer to 256MB (previously had 32 MB) and I get the following results: 

Scale: 1, 10, 100, 1000. 
Transactions per second: 10000, 8000, 3200, 30 

I don't understand why when the scale is 100 or more in the second benchmark, the performance is so low compared to the first test. The only thing I have done was increase the memory. 

I've thrown every test twice and the results were similar. In an earlier test, the memory configuration was: 

shared_buffers = 512 MB 
maintenance_work_mem = 8MB 
effective_cache_size = 1GB 
work_mem = 2MB 

And the results were similar, even slightly worse. For that reason, I did tests changing options one by one, to know what was the cause of poor performance. And "the winner" is shared_buffers... 

Thanks. 

------=_Part_738518_336253797.1304259066269
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: Verdana; font-size: 12pt; color: #000000'>Javier,=
<div><br></div><div>There has been much discussion of this.&nbsp;</div><div=
><br></div><div>Generally - and though it's a bit counter-intuitive - whole=
sale increase in PostgreSQL's shared buffer won't give you the outcomes you=
 might expect. In short, once there's enough, there's enough. Workmem is an=
other option which behaves along these lines. Reason for this is that Postg=
reSQL makes _very_ good use of its surrounding memory environment; IE, afte=
r a certain point, it's better to focus on tweaking your OS and operating e=
nvironment.</div><div><br></div><div>Now, there will probably be some 'opti=
mal' shared memory and workmem settings, given the specifics of your query =
and execution environment; here's where mileage will vary. But you shouldn'=
t expect a direct and/or linear increase in performance with doubling/quadr=
upling of shared memory.</div><div><div><br></div><div>You'll get answers h=
ere from folks well above my paygrade, but I think that's a fair rough outl=
ine. (!!)</div><div><br></div><div>Lou Picciano</div><div><br></div><div><b=
r>----- Original Message -----<br>From: "Javier Reyes" &lt;c.javier.reyes.e=
@gmail.com&gt;<br>To: pgsql-admin@postgresql.org<br>Sent: Sunday, May 1, 20=
11 7:20:15 AM<br>Subject: [ADMIN] Option shared_buffers in PostgreSQL<br><b=
r>Hello.<br><br>I have a server with 4GB of RAM and PostgreSQL 9.0.3 on Cen=
tos 5. I'm using pgbench and pgbench-tools to measure performance, using tw=
o pgbench-tools queries: select and tpc-b.<br><br>With default settings of =
postgresql.conf and select query, I get the following results:<br>
<br>Scale: 1, 10, 100, 1000.<br>Transactions per second: 10000, 8800, 7500,=
 100.<br><br>(the number of records of the table is scale*100000)<br><br>I'=
ve only increased the option shared_buffer to 256MB (previously had 32 MB) =
and I get the following results:<br>
<br>Scale: 1, 10, 100, 1000.<br>Transactions per second: 10000, 8000, 3200,=
 30<br><br>I don't understand why when the scale is 100 or more in the seco=
nd benchmark, the performance is so low compared to the first test. The onl=
y thing I have done was increase the memory.<br>
<br>I've thrown every test twice and the results were similar. In an earlie=
r test, the memory configuration was: <br><br>shared_buffers =3D 512 MB <br=
>maintenance_work_mem =3D 8MB<br>effective_cache_size =3D 1GB<br>work_mem =
=3D 2MB <br>
<br>And the results were similar, even slightly worse. For that reason, I d=
id tests changing options one by one, to know what was the cause of poor pe=
rformance. And "the winner" is shared_buffers...<br><br>Thanks.<br>
</div></div></div></body></html>
------=_Part_738518_336253797.1304259066269--