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.