First I will have to apologize for talking out of my ***.
On to the software side, I’m not sure what your reasoning is for running Ubuntu as a host OS instead of something like Proxmox (which I highly recommend, it uses Debian as a base) or a cloud hypervisor on top of Ubuntu, like OpenNebula, but I won’t really question your choices, just pointing out some options.
Now, for the system. I’m not really into server hardware, but what I can tell from AMD’s own webpage is that all EPYC Rome models are running up to 3.2 - 3.4 GHz (and that is boosting), so not much of a noticeable difference in clock speeds. Intel might be better if you can find fewer cores and higher clock speeds, but it seems that they don’t have much of an offering either, the fastest CPUs I can find are some 10 core 20 thread 3.7 GHz (turbo) Xeons, so I can’t say for sure. Architecturally, AMD seems to hold the advantage with Rome and pricing should be better.
NVME SSDs can help with iops, but you will be giving away hot-swap (well, I think there are some concepts / early adopter NVME hotswap out there, but let’s be serious). Just pointing it out… IDK, it looks like you won’t be using more than 10 drives, so I can’t recommend insane configurations, like Striped Raid Z3 with sata ssds, but even with raid 10 with 4x sata ssds, you will have a lot of iops (compared to the 900 your currently have) - probably in excess of 30k iops.
Then, a few questions come, like, how many vms are you planning to run? What vm specs? Will you be running them on the same ssds that you are running mysql or do you plan to have separate location for them? Will there be other drives inside this new server or will you run the vms from a NAS / SAN? There are a lot of variables.
Another thing that I don’t know if you checked is Amazon’s documentation about vms running on ssds:
“The following instances support instance store volumes that use solid state drives (SSD) to deliver high random I/O performance: C3, G2, I2, M3, R3, and X1”
“The following instances offer non-volatile memory express (NVMe) SSD instance store volumes: C5d, I3, I3en, F1, M5ad, M5d,
p3dn.24xlarge , R5ad, R5d, and z1d”
Are you even using all those core in aws? Try running a vm with fewer cores on an ssd instance and check performance.
Long term pricing might not be very beneficial and you would still be bottlenecked by the Internet connection between you and Amazon, but if you are not filling that mysql db strictly from your intranet, Amazon’s internet connection might be more beneficial (I think you have a 10G pipe between vms and at least 1G to the internet, I don’t remember).
If you are buying a server from Dell EMC or HPE, then you don’t need to study the chassis…