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 , we noticed a 10% performance degradation, which we then reported to the community .
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 .
We proposed the fix to the wider PostgreSQL community and the ensuing discussion led to a refined resolution which was implemented in a patch . This patch has been back-ported to the latest PostgreSQL 9.2.3 release and is included in the latest vFabric Postgres release .
Performance of the PostgreSQL 9.2.3 Patch
To be sure of our results, VMware conducted a performance test of the patch . 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 126.96.36.199-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
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.