Postgres Performance on AWS EBS

AWS EBS is network-attached storage … in other words, S L O W, compared to local SSD for Postgres database use.

I’ve been seeing average disk latency of 0.55 – 0.80 milliseconds per block, and IOPS and bandwidth are throttled by both the instance and the volume.

For m4.2xlarge, only 10,000 IOPS with 100 Mbps are available, regardless of how many or beefy your attached EBS volumes are – not impressive for SSD at all:

Figure 1: m4.2xlarge throttling IO from 4G EBS gp2 volume with 10,000 IOPS and 250 Mbps

Figure 2: 4G EBS gp2 unencrypted volume showing minimum read latency of 0.55 ms

In the above case, one thing you can do is to switch from m4.2xlarge to m5.2xlarge, which is cheaper and has double the IO performance.

But if you’re stuck using Postgres with EBS for large databases (bigger than RAM), there are workarounds related to the fact that shared_buffers will store the index in RAM:

  1. carefully configure shared_buffers to be as large as possible, and max_connections as small as possible
  2. run EXPLAIN to see if indexes are used (no SEQ SCAN) and pg_stat_statements extension to identify slow or frequent queries
  3. use covering indexes to read data from the index cache
  4. rewrite queries to do index scans from RAM instead of table scans across the network from EBS (ie. HAVING => INTERSECT and EXCEPT, WHERE-splitting, etc.)
  5. remove ORDER BY if clause not indexed and your app doesn’t need sorting
  6. use Redis to cache repeated queries
  7. use io1 instead of gp2 volumes, but do your own benchmarks and latency measurements as they vary with both types
  8. use local instance, “ephemeral” SSD volumes and replication/WAL copy for HA.

It would be nice if Postgres had a setting to indicate network-attached storage as a hint to the optimizer.

Percona has some advice for tuning operating system parameters.

Amazon EBS Volume Types
The most useful Postgres extension: pg_stat_statements
Amazon Postgres RDS pg_stat_statements not loaded
PostgreSQL Workload Analyzer

Keywords: cloud, architecture, Postgresql

This entry was posted in Linux, Postgresql, Tech. Bookmark the permalink.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.