0

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 -> 200 ms
Running (*) during benchmark : 3000 ms -> 180000 ms (increase 30 -> 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:

  1. If resources do not change, how we can improve our configuration? (can we edit parameters in postgresql.conf?)

  2. Can we use pgbench to benchmark with query (*) above?

Improve this question
2
  • We use pgbouncer for pooling and performance is better. This topic is difficult to discuss . Commented Aug 6, 2015 at 15:56
  • Q1: it's hard to say. Q2: the answer is "can" but only pgbench database. Commented Sep 12, 2015 at 16:42

1 Answer 1

Reset to default
1
  1. 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

  1. 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. =)

Improve this answer
2
  • Thanks. As I know, pgbench just benchmark on its tables (pgbench_accounts, pgbench_branches, pgbench_history, and pgbench_tellers), so how can I use pgbench for my tables ? Commented Aug 5, 2015 at 3:15
  • You can use the -f flag on pgbench, as stated in the documentation. If you want to get fancier, there are a bunch of other options you can use. But you can probably just get away with a begin/commit and that query, in a file. -f filename --file=filename Read transaction script from filename. See below for details. -N, -S, and -f are mutually exclusive. Commented Aug 5, 2015 at 3:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.