How do you know when it's time to upgrade a RDBMS/SQL database infrastructure? What metrics to follow? In my specific case, I have just deployed my side project to AWS and using AWS MySQL Tier 2 Micro instance, which is within AWS' Free Tier;

https://aws.amazon.com/ec2/instance-types/t2/

It has: 20 GB storage, 1 GB memory, 1 vCPUs, 6 CPU Credits

So now I have a bunch of metrics I can follow in AWS;



As a developer, I know the basics of using SQL databases, but I know very little as a DBA (Database Administrator) and hosting them, but I want to learn the basics.

So my question is not a AWS specific question (its relevant for Google Cloud Platform and Microsoft Azure as well), but its a more more broad question;


As a DBA, how do you know when its time to upgrade a RDBMS infrastructure?
What key metrics should you follow and why in order to know when should try and upgrade your infrastructure?
What are the basics any new DBA should look at?
Unfortunately this is not a simple matter. There is much black art and experience involved because you are dealing with a few different but strongly coupled matters. First you are trying to decide if your infrastructure needs updating to handle the application1, if the application needs changes to optimise it instead2, some mix of the two3, or if you need to re-architect both in-step4.

1: throwing hardware at the problem
2: better indexing, fixing anti-patterns to improve use of those indexes, and so on
3: when there are optimisation gains to make in the application, but hardware upgrades are still needed to achieve the desired performance
4: switching to a wide scaling model may require changes to both the infrastructure and the code as part of the same grand (re)design

If you have little or no control over the application, you beleive it has been optimised as far as is possible on its own, or you desire to leave it alone as much as possible, then infrastructure changes are all you can work with, in which case there are slightly more hard and fast rules: if you are hitting disk/network a lot for read activity then consider upgrading the amount of RAM, if you are blocking on disk/network IO for write activity then a better performing storage layer may be needed, if your CPU resource is always busy but other resources less so then that is a likely upgrade target though take care to analyse if the load is mostly concurrent or not (if you have small numbers of long-running CPU-busy tasks then faster cores are useful, if you have high concurrency them more cores is probably going to be more helpful though make sure you have enough memory that they won't be starved by IO bottlenecks reading the data they need to process).

Of course even with no control to change the rest of the application you might be able to help it out with index changes.

As a general rule you are not wanting to monitor specific metrics for absolute values, but instead watching key ones for changes over time. These metrics will be a mix of application throughput at the front end (response times generally or for specific actions), throughput server-side (transactions per period, average transaction length, ...) and infrastructure related (CPU utilisation, IO busy/wait times, memory use). Make sure you know your baseline metrics, so you can more immediately see where a bottleneck develops over time due to data growth, over time due to concurrency growth, or suddenly due to a new glut of users or a change in the application.