PostgreSQL Benchmarks: Optimizing Database Performance with Tuning

PostgreSQL Benchmarks: Optimizing Database Performance with Tuning

 

EDB Postgres Advanced Server (EPAS) and PostgreSQL ship with a set of default configurations designed to perform well on conservatively sized hardware. There’s plenty of room for improvement by customizing the settings and configuration for your specific workload and hardware environment. You might be surprised by how much of a performance boost you can get by just tuning a few database parameter configurations. 

In a recent blog published by my colleague, Vik Fearing, “PostgreSQL TPROC-C Benchmarks: PostgreSQL 12 vs. PostgreSQL 13 Performance,” EIGHT times as many transactions per minute were achieved on both 12.4 and 13.0 PostgreSQL when it was properly tuned vs. not tuned (i.e. default). This work was using HammerDB, a TPC-C like benchmark. 
 

Test results on 12.4

It’s important to note that the significant performance gain holds steady as the number of virtual users increases.

 

Test results on 13

The following table lists the tuneable database parameters and their respective settings Vik used in his benchmark testing. All other settings remained at the default values. The testing was done on an Amazon m5.metal instance running Red Hat Enterprise Linux release 8.2 with 96 vCPUs and 393216 MiB memory. The data directory was on eight disks configured in RAID 10 and the size of the data directory was 2000 GiB. It is worth noting that tuning effects vary on different workloads and hardware, and the values chosen in this case were specific to the hardware being used for the benchmark. What Vik performed was considered a ‘starter’ tuning configuration (i.e. one that we would suggest that users use as a starting point for their own deployments) for the specific server and workload that he chose. Further fine-tuning the settings could potentially yield even higher performance gains. 

autovacuum_max_workers

10

autovacuum_vacuum_cost_limit

3000

checkpoint_completion_target

0.9

checkpoint_timeout

'15min'

cpu_tuple_cost

0.03

effective_cache_size

'350GB'

lc_messages

'C'

listen_addresses

'*'

log_filename

'postgresql.log'

logging_collector

on

maintenance_work_mem

'2GB'

max_connections

1700

max_wal_size

'300GB'

pg_stat_statements.save

off

pg_stat_statements.track

all

random_page_cost

1.1

shared_buffers

'100GB'

shared_preload_libraries

'pg_stat_statements'

unix_socket_directories

'/tmp'

wal_buffers

'1GB'

work_mem

'128MB'

 

For additional information about the test server specifications, test methods, and results, please check out Vik’s blog

Vik’s benchmark tests undoubtedly illustrate the impact that performance tuning can have on database server performance for a real-life OLTP workload. Sometimes a small change in a setting may have a big impact. However, tuning EDB Postgres Advanced Server (EPAS) and PostgreSQL can be challenging. Performance tuning requires a significant amount of expertise and practice to achieve maximum performance. To make it easier for EPAS and PostgreSQL users, EDB has published two platform-specific tuning guides this past summer - “Configuring and Tuning PostgreSQL and EDB Postgres Advanced Server” for both Linux and Windows users. 

The guides address recommendations for some of the most important parameter settings for:

  • Operating System Optimization
  • Configuration & Authentication
  • Resource Usage
  • Query Tuning
  • Reporting and Logging
  • Autovacuum
  • Client Connection Defaults

These recommendations are a great starting point, and will help you tune your database to achieve peak performance. to learn more, you can download the complete guides here!   
 

Hope Jiang

Hope Jiang is VP, Strategic Customer Projects at EDB. She works with the Sales, Field Organization, and Product Development to accelerate sales growth, shorten sales cycles, and create new business opportunities. Hope has been with EDB for close to 15 years, started as a Project Manager, ...