3 ms·
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 y
by 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 agoThanks 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 agoYou'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 agoMakes 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 agoI'd look into Memory Optimized (R*) instances.
- xwowsersx 4y agoThank you. I think this makes a lot more sense actually. I can go to db.r6g.xlarge and double the memory to 32gb from the t3.xlarge I'm currently on for an additional ~$117/month vs getting to 32gb with the db.m5.2xlarge for $308 more per month. Also, looks like X2g among the memory optimized is the lowest price per GiB of RAM (for MySQL, MariaDB, and PostgreSQL). Thoughts on the X2g? The db.x2g.large, for example, doubles memory AND network performance (Gbps) vs current DB for less than $30 more per month. Does drop vCPU down to 2 from 4, which might not matter given where CPU utilization seems to spike to (~50% at peak) https://ibb.co/WsxN6D3 https://ibb.co/WsxN6D3