Sept. 3, 2011, 9:26 a.m.
IT

Optimising PostgreSQL application Part II

Review Part I here. The best I could muster was 21 minutes in a co-located setup. To make the comparison fair, I am going to express it as a percentage of the pre-optimisation application / PostgreSQL DB on production versus my development machine.

  Production App Production DB Development Co-located
CPU 2 x Xeon 5140 2.33GHz (#4) 1 x Xeon 5140 2.33GHz (#2) Core i7 2600 3.4GHz (#4)
RAM 4GiB 4GiB 12GiB
HDD Enterprise SAN Enterprise SAN OCZ Vertex 3 240GB SSD
NIC Broadcom Gigabit BCM5708S Broadcom Gigabit BCM5708S Intel Gigabit 82579V
PostgreSQL - fsync = off fsync = on, synchronous_commit = off
Application Unoptimised, single threaded - Optimised, multithreaded

The performance increase between the Production App + Production DB server combo vs. my co-located development machine?

  Production Split Development Co-located
Duration (min) 564 12

That is a performance increase of 98%, or 47 times faster. The three big factors?

  1. Fast dedicated SSD on proper SATA3 controller vs. Enterprise SAN sharing workload with other clients.
  2. Multithreaded engine taking advantage of multi core systems vs. single threaded causing other cores to idle.
  3. Internal application caching to reduce number of database reads vs. hitting the database for every request.

My work is done here. 9.4 hours optimised down to 12 minutes. Impressive.