Skip to content

HOWTO: Tunning PostgreSQL with pgbench results compare part 2: custom postgresql.conf

October 23, 2012

Scenario 2: Custom configuration and network testing

# postgresql.conf

max_connections = 4096
shared_buffers = 2048MB
wal_level = archive
synchronous_commit = off
checkpoint_segments = 3
checkpoint_timeout = 5min
archive_mode = on
archive_command = ‘cp %p /db/pgsql/archive/%f’

# sysctl.conf

kernel.sem = 250        32000   32      12288

All Tests here are from networked host on 1Gbit/s Links

Test 1: 64 clients, 8 threads, 60 seconds, SELECTS ONLY

/usr/pgsql-9.1/bin/pgbench -S -c 64 -j 8 -T 60 -h ip_address -U postgres postgres

number of transactions actually processed: 4407965

tps = 73403.688974 (including connections establishing)
tps = 73425.055707 (excluding connections establishing)

Test 2: Same test 64 clients, 8 threads 60 seconds , but with   ‘ -C   establish new connection for each transaction ‘ , SELECTS ONLY

/usr/pgsql-9.1/bin/pgbench -S -C -c 64 -j 8 -T 60 -h ip_address -U postgres postgres

number of transactions actually processed: 63000
tps = 2253.207441 (including connections establishing)
tps = 203664.094869 (excluding connections establishing)

Test 3: 1000 clients, 8 threads , 60 seconds , All Simple Query Test

/usr/pgsql-9.1/bin/pgbench -c 1000 -j 8 -T 60 -h ip_address -U postgres postgres

number of transactions actually processed: 178512
tps = 2941.034250 (including connections establishing)
tps = 3017.231850 (excluding connections establishing)

Test4: 1000, clients , 8 threads, 60 seconds , but with  ‘-C establish new connection for each transaction’ ,  All Simple Query Test

/usr/pgsql-9.1/bin/pgbench -C -c 1000 -j 8 -T 60 -h ip_address -U postgres postgres

number of transactions actually processed: 24291
tps = 393.461066 (including connections establishing)
tps = 5117.819835 (excluding connections establishing)

Test 5: 1000, clients , 8 threads, 60 seconds , but with  ‘-C establish new connection for each transaction’ , SELECTS ONLY

/usr/pgsql-9.1/bin/pgbench -S -c 1000 -T 60 -h 192.168.200.65 -U postgres postgres

number of transactions actually processed: 2032893
tps = 33513.847239 (including connections establishing)
tps = 34515.646139 (excluding connections establishing)

Test 6: 100 clients, 8 threads, 60 seconds, no new connections, All Simple Query Test

/usr/pgsql-9.1/bin/pgbench -c 100 -T 60 -h 192.168.200.65 -U postgres postgres

number of transactions actually processed: 417973
tps = 6941.591948 (including connections establishing)
tps = 6962.824727 (excluding connections establishing)

 

Advertisements
Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: