Skip to content

HOWTO: Tunning PostgreSQL with pgbench results compare part 1: default postgresql.conf

October 23, 2012

Hardware:

CPU: Intel(R) Xeon(R) CPU E3-1240 V2 @ 3.40GHz stepping 09
RAM: 32GB
RAID: LSI MR9260-4i RAID1
SSD: 2x OCZ-AGILITY4  RAID1

Software:

OS: CentOS release 6.3 (Final) Linux hostname 3.6.2-1.el6.elrepo.x86_64 #1 SMP Fri Oct 12 23:52:09 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
PGSQL: postgresql91-server.x86_64

Scenario1 : Default PostgreSQL configuration after default yum install postgresql91-server

pgbench -i -s 100  # DataBase Size = 1.5GB

Test 1:  16 connections , 8 threads, 60 seconds

/usr/pgsql-9.1/bin/pgbench -c 16 -j 8 -T 60 -U postgres postgres # 16 connections, 8 threads, 10 transactions per run
/usr/pgsql-9.1/bin/pgbench -S -c 16 -j 8 -T 60 -U postgres postgres # 16 connections, 8 threads, 10 transactions per run FOR SELECTS ONLY

Results All Simple Queries:

number of transactions actually processed: 173673
tps = 2890.913376 (including connections establishing)
tps = 2891.111222 (excluding connections establishing)

Results Selects only:

number of transactions actually processed: 2976620
tps = 49608.018292 (including connections establishing)
tps = 49611.007733 (excluding connections establishing)

Test 2: 16 connections , 16 threads , 60 seconds

/usr/pgsql-9.1/bin/pgbench -c 16 -j 16 -T 60 -U postgres postgres # 16 connections, 16 threads, 10 transactions per run
/usr/pgsql-9.1/bin/pgbench -S -c 16 -j 16 -T 60 -U postgres postgres # 16 connections, 16 threads, 10 transactions per run FOR SELECTS ONLY

Results All Simple Queries:

number of transactions actually processed: 173036
tps = 2881.395208 (including connections establishing)
tps = 2881.564468 (excluding connections establishing)

Results Selects only:

number of transactions actually processed: 2914892
tps = 48579.233107 (including connections establishing)
tps = 48582.789806 (excluding connections establishing)

Test 3: 96 connections 8 threads 60 seconds

/usr/pgsql-9.1/bin/pgbench -c 96 -j 8 -T 60 -U postgres postgres # 96 connections, 8 threads, 10 transactions per run
/usr/pgsql-9.1/bin/pgbench -S -c 96 -j 8 -T 60 -U postgres postgres # 96 connections, 8 threads, 10 transactions per run FOR SELECTS ONLY

Results All Simple Queries:

number of transactions actually processed: 221469
tps = 3678.510088 (including connections establishing)
tps = 3679.781476 (excluding connections establishing)

Results Selects only:

number of transactions actually processed: 2615356
tps = 43576.835648 (including connections establishing)
tps = 43593.165712 (excluding connections establishing)

Test 4: 64 connections, 64 threads, 60 seconds

/usr/pgsql-9.1/bin/pgbench -c 64 -j 64 -T 60 -U postgres postgres # 64 connections,64 threads, 10 transactions per run
/usr/pgsql-9.1/bin/pgbench -S -c 64 -j  64-T 60 -U postgres postgres # 64 connections, 64 threads, 10 transactions per run FOR SELECTS ONLY

Results All Simple Queries:

number of transactions actually processed: 229090
tps = 3817.336968 (including connections establishing)
tps = 3818.574797 (excluding connections establishing)

Results Selects only:

number of transactions actually processed: 2091601
tps = 34852.859632 (including connections establishing)
tps = 34858.043647 (excluding connections establishing)

Test5: 64 connections, 32 threads, 60 seconds

Results All Simple Queries:

number of transactions actually processed: 230494
tps = 3840.492481 (including connections establishing)
tps = 3841.568127 (excluding connections establishing)

Results Selects only:

number of transactions actually processed: 2085782
tps = 34756.171886 (including connections establishing)
tps = 34763.978026 (excluding connections establishing)

Advertisements

From → databases, Linux

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: