Skip site navigation (1) Skip section navigation (2)

dbt2 performance regresses from 9.1.6 to 9.2.1

From: Dong Ye <yed(at)vmware(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: dbt2 performance regresses from 9.1.6 to 9.2.1
Date: 2012-10-31 22:48:44
Message-ID: 03E840D17E263A48A5766AD576E0423A03DF0A266B@exch-mbx-111.vmware.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi there,

I work for VMware with our Postgres performance team. We recently came across a dbt2 performance regression from 9.1.6 to 9.2.1. We have done some profiling and don't see anything obvious. Would like to get some suggestions from the community where to investigate further.

The average notpm is 61384.24 with 9.1.6 and 57381.43 with 9.2.1.
Plotting notps over time shows that the slowdown of 9.2.1 is evident across the entire run period.
Since we also observed sustained 80+% CPU utilization during both runs, we suspected this is a CPU bottleneck issue.
So we run oprofile hoping that the profiles may suggest one is using CPU less productively than the other; but nothing jumped out to that explanation.
The profiling results are posted on http://pgsql.privatepaste.com/3fa3ae0627 (9.1.6 run) and http://pgsql.privatepaste.com/930bb51374 (9.2.1 run).


Specs:

HP ML360 G6:
2x Xeon E5520 (4-core/processor, Hyperthreading disabled)
12GB DRAM
HP P410i RAID controller (256MB battery-backed cache)
- One 10k-rpm SAS: to mount /
- One SSD: to mount pgdata and wal

SUSE Linux Enterprise Server 11 SP1 64-bit (kernel version: 2.6.32.59-0.7-default)

postgresql.conf:
max_connections = 100			
shared_buffers = 5600MB 
temp_buffers = 8193kB 
work_mem = 4096kB 
maintenance_work_mem = 400MB 
wal_buffers = -1 
checkpoint_segments = 300
logging_collector = on		        
datestyle = 'iso, mdy'
lc_messages = 'C'			
lc_monetary = 'C'			
lc_numeric = 'C'			
lc_time = 'C'				
default_text_search_config = 'pg_catalog.english'
log_destination = 'csvlog'
log_directory = 'pg_log'
log_filename = 'postgresql-%a'
log_rotation_age = 1440
log_truncate_on_rotation = on

dbt2-0.40 was used:
40 warehouse
20 db connections 
use no thinktime
use prepared statement
buffer warmup before measurement run (warmup is done through disabling sequential scan and count(*) all tables and indexes).
measurement run lasts 20 minutes

We used postgresql91-9.1.6 and postgresql92-9.2.1 openSUSE builds:
http://download.opensuse.org/repositories/server:/database:/postgresql/openSUSE_12.1/x86_64/


Thanks,
Dong



pgsql-performance by date

Next:From: Dong YeDate: 2012-10-31 22:51:39
Subject: dbt2 performance regresses from 9.1.6 to 9.2.1
Previous:From: Merlin MoncureDate: 2012-10-31 18:39:53
Subject: Re: How to keep queries low latency as concurrency increases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group