AWS Aurora postgres benchmarking with pgbench notes

Photo by Veri Ivanova on Unsplash
  • Client
    Instance vCPU*| Mem (GiB)| Storage Dedicated EBS Bandwidth (Mbps) | Network Performance
    c4.xlarge | 4 | 7.5 | EBS-Only |750 | High (700–900 MBit/s)
  • Aurora
    db.r6g.4xlarge
    vCPU 16 RAM 128 GB
    Up to 10 Gbps
  • Choose a client which is compute optimized and has enough cpu power.
    TX(T4, T2 etc) instances have variable cpu performance and might be throttled when the client generates high load.
  • Observe cloudwatch dashboards and see if the network performance of either the client or server is reaching its peak.
  • Observe the cpu usage on both the client and db server.
    At RDS, observe DBLoad, DBLoadCPU and DBLoadNonCPU.
    Check this.
  • In cloudwatch most of the metrics report average values.
    Change them to maximum to see if there are any spikes or saturation.
    For example cpu utilization max can throw light on sudden spikes because of which your tps(throughput) is not increasing.
  • If your client machine’s cpu util is not getting saturated but when your database performance saturates, then you can assume multiple client machines are redundant for any further testing.
    Ex: If increasing either the number of connections or threads is not fetching you more tps without crossing 10ms latency with the client cpu util well near normal levels, indicates db saturation.
  • pgbench has an option -d.Don’t mistake it for database name as in psql cli.
    Here it refers to debug output.
  • For the final tests, don’t turn on -r or -d flags as they reduce the tps.-d especially reduces the tps considerably.
  • pgbench -h xyz.db.com -i -s 1
    If you are using a scale factor of 1, the tps is very low.Compare this to a scale factor of 10.You will observe the difference.
  • First use fixed number of threads(-j) less than or equal to the cpu cores.
  • Now increase the number of connections(-c) and observe the tps and latency.
  • Initially the tps might increase and at the point at when latency increasing but tps is same for increase in connections, increase number threads to a max of (cores + 1)
  • Now if you use your own custom scripts the tps might vary depending on the queries.
  • You can also observe select performance using the -S flag.
  • You can pass -r flag to observe the individual query latencies either for default or custom scripts.

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Entering a Program Shouldn’t Be This Frustrating

Flutter in Mobile App Development — Pros & Cons for App Owners

Learn iPhone development in Savannah. For free.

How to deploy a Laravel + MongoDB project on Digitalocean

Systems Design Study Guide

AWS Lambda — Sending Slack notification

Information: The hidden key to software engineering success

Data Collection 2— Web scraping

example html code | web scraping beautiful soup

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
none

none

More from Medium

Using AWS Config to assist in PCI and HIPAA compliance audits

Configure Session Manager access for federated users using SAML session tags — AWS SSO edition

post fix install and configure with AWS SES

Static website hosting with AWS S3 + Route 53 linking it with DNS