How to Affect Database Performance and Identify Bottlenecks to Scalability

Contributed by Jason Davis

Recently, EDB significantly optimized locking mechanisms that improved scalability and performance in PostgreSQL 9.5. EDB further enhanced the locking subsystems to deliver even greater performance results with an improvement that was released with EDB Postgres Advanced Server 9.5. (Note: This technology also has been committed into the open source PostgreSQL database, and will be made available with PostgreSQL 9.6.)

The graph below shows transactions per second (tps) of a testing system while under increasing client concurrency for each of the last five EDB Postgres Advanced Server releases. The tests were performed on a high-performance 8 socket / 64 core / 128 thread machine.

Write Workloads Where Data Fits In Shared Buffers

pgbench –M prepared

(Read/Write workload)

Median of (3) 30-minute runs, scale_factor=300 (~4.5 gb workload), max_connections = 200, shared_buffers = 8gb

8 CPU / 64 Core / 128 Threads, 500 GB RAM

This is a read/write workload with a scale factor of 300, which implies that the database fits within shared buffers. You can see in this case that EDB Postgres Advanced Server 9.5 scales to a much higher concurrency count than previous releases.

How did we achieve this?

We focused on lock contention. When a system like PostgreSQL is under high concurrency, the processes it undertakes to enforce ACID properties strains the system by requiring locks to ensure data integrity.   

We targeted specific locking subsystems to alleviate that strain and enabled the database to take advantage of newer hardware and better scale to fully utilize system resources. These subsystems included shared buffer eviction locks and ProcArrayLocks.

To achieve the performance results shown in the chart, we reduced contention in the ProcArrayLock by finishing the transactions in batches. The end effect is more efficient writes at high client counts, and again, we see the result of that effort with the huge boost in scalability for this workload which fits within shared buffers.

Write Workloads Where Data Is Larger Than Shared Buffers

pgbench –M prepared

(Read/Write workload)

Median of (3) 30-minute runs, scale_factor=1000 (~15 gb workload), max_connections = 200, shared_buffers = 8gb

8 CPU / 64 Core / 128 Threads, 500 GB RAM

Above is an example of a workload that is larger than shared buffers. Although we still see constant improvement in the annual release cycle, it is less pronounced. Expect to see more from EDB in the area of scalability and performance over the next 12 months, as we continue to focus on this important characteristic of the system.

One of EDB’s core goals is to develop enhancements for PostgreSQL that ensure global enterprises can deploy the database for business-critical workloads. For more information about EDB Postgres Advanced Server or to understand how new system and hardware configurations (i) affect database performance and (ii) identify bottlenecks to scalability, contact us.

Jason Davis is Senior Director, Product Management, at EnterpriseDB.