is vertical scaling good idea for a mysql database server?

Posted on

Question :

I operate a mariadb/mysql database server and i do not want to get into the problems with managing a multi-cluster database server
i have access to huge CPU and RAM and STORAGE when needed

so my question to the experienced and expert db admins is that is vertical scaling good enough? yes downtime is ok as long as data is protected
I rather deal with downtime of some system failure or issue that will only happen very rarely…it is better than the headache with managing a multi-cluster database to me

so what are the bottlenecks i will run into running a database up to 512GB memory and like 128vCPU? and like 2TB of SSD storage?

how is this worse than lets say running like a 8 cluster of mariadb/mysql database with each 64GB memory, 16vCPU and 256GB SSD storage?

yes i know about iops but i mean with lots of redis caching i think one can reduce stress on that part as well

pretty much there is more and more powerful CPU and servers that one can pack lots of stuffs in a single machine..not like years ago where that was not possible or at least easy..now one can pack several Terabytes, TBs(not a typo) of memory in a single machine

please send your advice and tips
thanks!!!

Answer :

“You can’t throw hardware at a performance problem.” Or, rather, you can’t do that twice.

  • CPU speed has not changed significantly in 20 years.
  • The number of cores increases, but InnoDB has always hit a brick wall before using all cores.
  • SSDs were a quantum improvement over HDDs, but nothing is on the horizon for the next leap.
  • Bigger RAM (hence buffer_pool) gets into “diminishing returns”.
  • Ethernet makes a 10x “speedup” every so often.
  • It would take years to fill a Petabyte disk; don’t you want to read the data before that?
  • etc.

One trick is to virtualize a big hunk of iron by having multiple VMs, each running an instance of MySQL. This does “horizontal” scaling on a single “vertically” scaled server. This at least helps with the brick wall I mentioned. But then you have a big, costly, single-point-of-failure.

My usual answer is “let’s see if we can program smarter”.

A recurring example is Data Warehousing. DBAs come to this (and other) forums bemoaning that their ‘report’ (a big SELECT with a big GROUP BY) is taking too long. I point out the beauty of a Summary table — 10x speedup, 10x smaller disk footprint. Poof, problem gone. No hardware upgrade could have gotten 10x.

Replication provides nearly infinite scaling, nearly linear, of reads.

Sharding (horizontal scaling) provides nearly infinite scaling, nearly linearly, but it does not work for all applications.

The best I see in Vertical is adding enough RAM to turn an I/O-bound query into a CPU-bound query running in the buffer_pool. I regularly see a 10x improvement for that. But it is a one-time improvement. That is, adding even more RAM won’t help any more.

So, to your title question, I say a resounding No.
OK, Vertical may help, but be aware of its limits.

Leave a Reply

Your email address will not be published. Required fields are marked *