I am not a big fan of AWS. It is a closed platform. It is designed to be the Apple of the Cloud to the Eve of Postgres users. That said, customers drive business and some of our customers use AWS, even if begrudgingly. Because of these factors we are getting very good at getting PostgreSQL to perform on AWS/EBS, albeit with some disclosures:
- That high IO latency is an acceptable business requirement.
- That you are willing to spend a lot of money to get performance you can get for less money using bare metal: rented or not. Note: This is a cloud issue not an AWS issue.
Using the following base configuration (see adjustments for each configuration after the graphic):
port = 5432 max_connections = 500 ssl = true shared_buffers = 4GB temp_buffers = 8MB work_mem = 47MB maintenance_work_mem = 512MB wal_level = hot_standby synchronous_commit = on commit_delay = 0 commit_siblings = 5 checkpoint_segments = 30 checkpoint_timeout = 10min checkpoint_completion_target = 0.9 random_page_cost = 1.0 effective_cache_size = 26GB
Each test was run using pgbench against 9.1 except for configuration 9 which was 9.3:
pgbench -F 100 -s 100 postgres -c 500 -j10 -t1000 -p5433
Here are some of our latest findings:
The AWS configuration is:
16 Cores
30G of memory (free -h reports 29G)
(2) PIOPS volumes at 2000 IOPS a piece.
The PIOPS volumes are not in A RAID and are mounted separately.
The PIOPS volumes are formatted with xfs and default options
The PIOPS volumes were warmed.
- Configuration 1:
$PGDATA and pg_xlog on the same partition
synchronous_commit = on
- Configuration 2:
$PGDATA and pg_xlog on the same partition
synchronous_commit = off
- Configuration 3:
$PGDATA and pg_xlog on the same partition
synchronous_commit = off
commit_delay = 100000
commit_siblings = 50
- Configuration 4:
$PGDATA and pg_xlog on the same partition
synchronous_commit = off
commit_delay = 100000
commit_siblings = 500
- Configuration 5:
$PGDATA and pg_xlog on different partitions
synchronous_commit = off
commit_delay = 100000
commit_siblings = 500
- Configuration 6:
$PGDATA and pg_xlog on different partitions
synchronous_commit = on
commit_delay = 100000
commit_siblings = 500
- Configuration 7:
$PGDATA and pg_xlog on different partitions
synchronous_commit = on
commit_delay = 0
commit_siblings = 5
- Configuration 8:
$PGDATA and pg_xlog on different partitions
synchronous_commit = on
checkpoint_segments = 300
checkpoint_timeout = 60min
- Configuration 9:
$PGDATA and pg_xlog on different partitions
PostgreSQL 9.3
synchronous_commit = on
checkpoint_segments = 300
checkpoint_timeout = 60min