5 ms·

I've recently been spending a fair amount of time trying to improve query performance on RDS. This includes reviewing and optimizing particularly nasty queries,

I've recently been spending a fair amount of time trying to improve query performance on RDS. This includes reviewing and optimizing particularly nasty queries, tuning PG configuration (min_wal_size, random_page_cost, work_mem, etc). I am using a db.t3.xlarge with general purpose SSD (gp2) for a web server that sees moderate writes and a lot of reads. I know there's no real way to know other than through testing, but I'm not clear on which instance type best serves our needs — I think it may very well be the case that the t3 family isn't fit for our purposes. I'm also unclear on whether we ought to switch to provisioned IOPS SSD. Does anyone have any general pointers here? I know the question is pretty open-ended, but would be great if anyone has general advice from personal experience?

notac 4y ago
I'd recommend hopping off of t3 asap if you're searching for performance gains - performance can be extremely variable (by design). M class will even you out. General storage IOPS is governed by your provisioned storage size. You can again get much more consistent performance by using provisioned IOPS. Feel free to email me if you want to chat through things specific to your env - email is in my about:
- xwowsersx 4y ago
  Thank you so much, will definitely take you up on the offer.
- brazzledazzle 4y ago
  Provisioned IOPS is much more expensive though, so make sure you really need it. If you use general IOPS you can monitor your burst balance. You can always start with general and then move to provisioned when you need it too.
  - xwowsersx 4y ago
    Thanks. If I'm reading this https://ibb.co/bNGmrCB https://ibb.co/bNGmrCB correctly, it seems like we have plenty burst balance. Does this seem to indicate that provisioned IOPS is unlikely to help us here?
    - brazzledazzle 4y ago
      Yeah you're looking good though I would recommend adding a CloudWatch alert to make sure it doesn't sneak up on you. IIRC in general provisioned IOPS can help with other performance attributes like throughput so I would look at the differences documented by AWS and then take a look at all of the relevant metrics to be certain.
      - xwowsersx 4y ago
        Oh interesting, thanks. So PIOPS may help even if we're not getting low on balance. Will dig into the docs some more. Thanks!
- aeyes 4y ago
  I would advise that you try to fit the working set into memory before spending on provisioned IOPS. Reading a lot of data from network storage constantly should be avoided as much as possible, having more IOPS doesn't necessarily improve read latency.
Nextgrid 4y ago
It's hard to say without metrics; what does your CPU load look like? In general, unless your CPU is often maxing out, changing the CPU is unlikely to help, so you're left with either memory or IO. Unused memory on Linux will be automatically used to cache IO operations, and you can also tweak PG itself to use more memory during queries (search for "work_mem", though there are others). If your workload is read-heavy, just giving it more memory so that the majority of your dataset is always in the kernel IO cache will give you an immediate performance boost, without even having to tweak PG's config (though that might help even further). This won't transfer to writes - those still require an actual, uncached IO operation to complete (unless you want to put your data at risk, in which case there are parameters that can be used to override that). For write-heavy workloads, you will need to upgrade IO; there's no way around the "provisioned IOPS" disks.
- xwowsersx 4y ago
  Thanks very much for the reply. CPU is not often maxing out. Here's a graph of max CPU utilization from the last week https://ibb.co/tzw5p3L https://ibb.co/tzw5p3L
  - Nextgrid 4y ago
    You've got some spikes that could signify some large or unoptimized queries, but otherwise yes, the CPU doesn't look that hot. I suggest upgrading to an instance type which gives you 32GB or more of memory. You'll get a bigger CPU along with it as well, but don't make the CPU your priority, it's not your main bottleneck at the moment.
    - xwowsersx 4y ago
      Makes sense, thank you. Sounds like M class is the way to go as other commenter suggested. Also, yes. There are many awful queries that I'm aware of and working to correct.
      - CodesInChaos 4y ago
paulryanrogers 4y ago
General storage IOPS scales with disk size, roughly and to a point. It's often cheaper and faster to increase the instance storage than move to EBS, prioritized or not. Of course if you need to recover quickly in a disaster you'll want a hot standby or replica. Still may be cheaper than PIOPs. (Especially if you need HA anyway.)
- xwowsersx 4y ago
  Thanks for the reply. > It's often cheaper and faster to increase the instance storage than move to EBS, prioritized or not. You're saying it may well be cheaper to increase storage in order to get more IOPS than moving to an EBS-optimized instance type? Regarding HA, not relevant for at this point (assuming I understood you correctly). We've only got a single primary and one replica, the latter being used primarily for analytics.

notac 4y ago

I'd recommend hopping off of t3 asap if you're searching for performance gains - performance can be extremely variable (by design). M class will even you out. General storage IOPS is governed by your provisioned storage size. You can again get much more consistent performance by using provisioned IOPS. Feel free to email me if you want to chat through things specific to your env - email is in my about:

xwowsersx 4y ago
Thank you so much, will definitely take you up on the offer.
brazzledazzle 4y ago
Provisioned IOPS is much more expensive though, so make sure you really need it. If you use general IOPS you can monitor your burst balance. You can always start with general and then move to provisioned when you need it too.
- xwowsersx 4y ago
  Thanks. If I'm reading this https://ibb.co/bNGmrCB https://ibb.co/bNGmrCB correctly, it seems like we have plenty burst balance. Does this seem to indicate that provisioned IOPS is unlikely to help us here?
  - brazzledazzle 4y ago
    Yeah you're looking good though I would recommend adding a CloudWatch alert to make sure it doesn't sneak up on you. IIRC in general provisioned IOPS can help with other performance attributes like throughput so I would look at the differences documented by AWS and then take a look at all of the relevant metrics to be certain.
    - xwowsersx 4y ago
      Oh interesting, thanks. So PIOPS may help even if we're not getting low on balance. Will dig into the docs some more. Thanks!
aeyes 4y ago
I would advise that you try to fit the working set into memory before spending on provisioned IOPS. Reading a lot of data from network storage constantly should be avoided as much as possible, having more IOPS doesn't necessarily improve read latency.

Nextgrid 4y ago

It's hard to say without metrics; what does your CPU load look like? In general, unless your CPU is often maxing out, changing the CPU is unlikely to help, so you're left with either memory or IO. Unused memory on Linux will be automatically used to cache IO operations, and you can also tweak PG itself to use more memory during queries (search for "work_mem", though there are others). If your workload is read-heavy, just giving it more memory so that the majority of your dataset is always in the kernel IO cache will give you an immediate performance boost, without even having to tweak PG's config (though that might help even further). This won't transfer to writes - those still require an actual, uncached IO operation to complete (unless you want to put your data at risk, in which case there are parameters that can be used to override that). For write-heavy workloads, you will need to upgrade IO; there's no way around the "provisioned IOPS" disks.

xwowsersx 4y ago
Thanks very much for the reply. CPU is not often maxing out. Here's a graph of max CPU utilization from the last week https://ibb.co/tzw5p3L https://ibb.co/tzw5p3L
- Nextgrid 4y ago
  You've got some spikes that could signify some large or unoptimized queries, but otherwise yes, the CPU doesn't look that hot. I suggest upgrading to an instance type which gives you 32GB or more of memory. You'll get a bigger CPU along with it as well, but don't make the CPU your priority, it's not your main bottleneck at the moment.
  - xwowsersx 4y ago
    Makes sense, thank you. Sounds like M class is the way to go as other commenter suggested. Also, yes. There are many awful queries that I'm aware of and working to correct.
    - CodesInChaos 4y ago

paulryanrogers 4y ago

General storage IOPS scales with disk size, roughly and to a point. It's often cheaper and faster to increase the instance storage than move to EBS, prioritized or not. Of course if you need to recover quickly in a disaster you'll want a hot standby or replica. Still may be cheaper than PIOPs. (Especially if you need HA anyway.)

xwowsersx 4y ago
Thanks for the reply. > It's often cheaper and faster to increase the instance storage than move to EBS, prioritized or not. You're saying it may well be cheaper to increase storage in order to get more IOPS than moving to an EBS-optimized instance type? Regarding HA, not relevant for at this point (assuming I understood you correctly). We've only got a single primary and one replica, the latter being used primarily for analytics.