1

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;

enter image description here

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?

closed as primarily opinion-based by Philᵀᴹ, mustaccio, Mr.Brownstone, LowlyDBA, Max Vernon Nov 15 '18 at 21:05

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

2

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.

1

Indicators of something needed:

  • If you are more than 2 versions behind the latest, it is time to upgrade. (When you get too many versions behind, the upgrading becomes more difficult.) For MySQL, you should be on 5.6, 5.7, or 8.0.
  • As software grows, and the data grows, and the expectations of the 'users' grow, you may find that there is demand for better performance. Has that happened? If it has happened, then the typical "solution" involves tweaking the application, not the hardware, not the OS, and rarely the software version.
  • In my experience, the slowlog has the best information on not only whether to do 'something', but clues on where to turn your attention.
  • Watch disk usage. But you need a graph spanning weeks or months. With such, you can predict when you will run out of disk space. Add disk space? Purge old date? Compress? Etc.; there are multiple possible actions.

Bogus indicators:

  • For hardware, the only substantive change in the last two decades has been the disk shift from HDDs to SSDs. But even this is not a "necessary" change. That is, if you are experiencing performance problems, then there is little need to upgrade.
  • Each version of hardware, OS, MySQL, etc, brings a flurry of "better" things -- a little faster, better handling of some obscure cases, etc. But, in general, each "improvement" is needed by less than 1% of systems. So, without identifying a specific need that is specifically satisfied by an upgrade, the upgrade is probably a waste of effort.
  • The graphs you presented tell me that your system is nowhere near to saturating the system.
  • 1% CPU? That sounds like the background noise caused by the OS and monitoring tasks. Is anything running? Ditto for IOPs.
  • Read latency < 1ms? A human can't notice less than a few dozen milliseconds.

Not the answer you're looking for? Browse other questions tagged or ask your own question.