Tuning postgresql.conf when benchmark Description:

We build a new database server Enterprisedb v9.3 (PostgreSQL commercial), so we use JMX to benchmark it with 1000 users. Here is our detailed information:

Operating System: Centos 6 x64
RAM: 8 GB
CPU: 2 CPU, Intel(R) Xeon (R) CPU @ 2.50 GHz 
User quantity: 1000 


Query to benchmark: 

select count(distinct(a.id)) 
from table_a a 
join table_b on a.id = b.fk_id  (*)

Running (*) before benchmark : 100 ms -&gt; 200 ms
Running (*) during benchmark : 3000 ms -&gt; 180000 ms (increase 30 -&gt; 900 times)
CPU is used by benchmark: ~ 95 %
RAM is used by benchmark: ~ 3.5 GB


postgresql.conf file:

max_connections = 2000
shared_buffers = 5000MB 
temp_buffers = 32MB 
work_mem = 5MB  
maintenance_work_mem = 1MB
effective_cache_size = 1000MB
checkpoint_segments = 128
edb_enable_icache = on
enable_indexscan = on
enable_mergejoin = on
enable_nestloop = on
edb_dynatune = 90
edb_dynatune_profile = oltp


Questions: 


If resources do not change, how we can improve our configuration? (can we edit parameters in postgresql.conf?)
Can we use pgbench to benchmark with query (*) above?
Yes, you can run those queries in pgbench. Look for the parts talking about custom scripts:


http://www.postgresql.org/docs/9.4/static/pgbench.html


The max_connections, and shared_buffers seem really high for the server you have. 25% of RAM for shared_buffers would be more reasonable, and getting a connection pooler like pgbouncer to handle lots of connections would be much more productive. 


You might find this useful as well:

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Hope that helps. =)