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?

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

1 Answer 1

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

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 ?
    – Luan Huynh
    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.
    – Kassandry
    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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.