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.
