Home > Blogs > VMware vFabric Blog


Behind the Scenes: Patching PostgreSQL for Performance–vFabric Team Contributes to Open Source

After PostgreSQL 9.2 was released, users that relied on PostgreSQL for scale, may have noticed a performance hit. In fact, the PostgreSQL community alongside the VMware vFabric Postgres team, was able to prove that the new version demonstrated a 10% performance hit over version 9.1. As part of the VMware Postgres team, we wanted to fix this problem for our own distribution, but as mentioned in previous posts, we also wanted to contribute our fixes back to the common core.  This post provides additional detail on how this problem was identified and how we worked with the open source PostgreSQL community to restore performance.

Background on the Performance Issue in PostgreSQL 9.2

Last year, during routine regression testing of vFabric Postgres, we found that PostgreSQL 9.2, the latest major release of PostgreSQL, demonstrated a significant performance regression from version 9.1. Using DBT-2, an open-source and fair-use implementation of TPC-C benchmark [1], we noticed a 10% performance degradation, which we then reported to the community [2].

To troubleshoot the problem we used git bisect to find the type of commit that caused the performance problem and cross-examined the statistical profiles using oprofile. As it turns out, the regression was caused by a commit that changed the way memory was allocated when SPI queries were executed. The commit was intended to reduce the number of allocations for queries using a cached plan at the cost of more logistics work. However, according to the DBT-2 test, we could see that this tradeoff was unfavorable for dynamic queries. So to fix it, we would need reintroduce the original tradeoff on its intended queries using conditions [3].

We proposed the fix to the wider PostgreSQL community and the ensuing discussion led to a refined resolution which was implemented in a patch [4]. This patch has been back-ported to the latest PostgreSQL 9.2.3 release and is included in the latest vFabric Postgres release [5].

Performance of the PostgreSQL 9.2.3 Patch

To be sure of our results, VMware conducted a performance test of the patch [6]. The configuration details and results follow:

System Under Test (SUT)

Model: HP ML350 G6
CPU: Two quad-core Intel Xeon E5520 @ 2.27GHz, hyper-threading disabled
Memory: 12GB DDR3 @ 1333MHz
Storage: HP P410i RAID controller (256MB battery-backed cache) creating:
- One virtual drive out of one 10k-rpm SAS disk to hold OS
- One virtual drive out of one Intel 520 SSD to hold database
OS: SUSE Linux Enterprise Server 11 SP1 64-bit (kernel version 2.6.32.59-0.7-default)

The DBT-2 traffic driver was running on a separate machine (Dell Precision 390 with dual-core Intel Core2 E6700 @ 2.66 GHz and 8GB DDR2 @ 667MHz) connected to the SUT by a GbE crossover cable.

Key postgresql.conf settings

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

Key DBT-2 and experiment settings

dbt2-0.40
40 warehouses
40 db connections
zero think time
no prepared statement
shared buffer warmed up before measurement run
measurement run lasting 20 minutes
oprofile (one sample per 1 million CPU cycles) run in background

Detailed Results—Performance with the Patch

The following chart shows the performance readings (throughput, average response time, and 90th-percentile response time of all component transaction types of DBT-2) under the three concerned PostgreSQL bits (9.1.7, 9.2.2, and 9.2.2 patched). The performance regression before applying the patch is evident across all the transaction types with respect to both throughput and response time metrics; and so is the subsequent full recovery after applying the patch. 

References:

[1] http://sourceforge.net/apps/mediawiki/osdldbt/index.php?title=Main_Page#dbt2
[2] http://archives.postgresql.org/pgsql-performance/2012-11/msg00029.php
[3] http://archives.postgresql.org/pgsql-hackers/2012-12/msg01601.php
[4] http://archives.postgresql.org/pgsql-hackers/2013-01/msg00016.php
[5] https://www.vmware.com/support/vfabric-postgres/doc/vfabric-postgres-92-release-notes.html
[6] http://archives.postgresql.org/pgsql-hackers/2013-01/msg00159.php

This entry was posted in Postgres and tagged , , on by .
Stacey Schneider

About Stacey Schneider

Stacey Schneider has over 15 years of working with technology, with a focus on working with sales and marketing automation as well as internationalization. Schneider has held roles in services, engineering, products and was the former head of marketing and community for Hyperic before it was acquired by SpringSource and VMware. She is now working as a product marketing manager across the vFabric products at VMware, including supporting Hyperic. Prior to Hyperic, Schneider held various positions at CRM software pioneer Siebel Systems, including Group Director of Technology Product Marketing, a role for which her contributions awarded her a patent. Schneider received her BS in Economics with a focus in International Business from the Pennsylvania State University.

2 thoughts on “Behind the Scenes: Patching PostgreSQL for Performance–vFabric Team Contributes to Open Source

  1. Pingback: Scaling for the Information Explosion: Master-Slave Cluster with vFabric Postgres 9.2 on vSphere | VMware vFabric Blog - VMware Blogs

  2. Pingback: How-To: Build a Geographic Database with PostGIS and vPostgres | VMware vFabric Blog - VMware Blogs

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>