Option shared_buffers in PostgreSQL

From: Javier Reyes <c(dot)javier(dot)reyes(dot)e(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Option shared_buffers in PostgreSQL
Date: 2011-05-01 11:20:15
Message-ID: BANLkTikfpYSRHMpEQ6CqCk2Qz5_9OUBe1g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Lou Picciano 2011-05-01 14:11:06 Re: Option shared_buffers in PostgreSQL
Previous Message Anibal David Acosta 2011-04-30 04:13:22 Postgres TimeZone