0

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!!!

1

"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.

  • i get your point, but am not refering to scaling vertically infinitely..i think the vertical scale am mentioning is for many scenarios where a single db server can surfice...i mean imagine a wordpress website with like 200 million pageviews a month just mostly blog content and couple images per blog posts, not video hosting...many people will think of having clusters of servers, but do you think the vertical scale am mentioning can be enough for such scenario...also there will be caching as well too...the server spec i mentioned..you think that will handle such traffic? – uberrebu Jan 3 at 11:12
  • @uberrebu - 200M PV/mo might be the limit of available hardware today. That's about 160/sec. How many queries are needed for the typical PV? How big can a burst be? You might need a machine that can handle 5K qps. Yes, that should be feasible by Vertical scaling. While you are at it, fix the inefficiency in postmeta: mysql.rjweb.org/doc.php/… – Rick James Jan 3 at 15:37
  • queries will be basic queries to read the blog post..there will be nginx page caching and redis object caching...so there wont be too much stress on the database server...what spec of a single machine do you think will handle the 200 M PV/month? what are your recommended specs? – uberrebu Jan 3 at 16:40
  • @uberrebu - Calculate the bandwidth needed. How many bytes in a page; assume 500 pages/sec; ... Now does your Ethernet card, your modem, your IPS, etc, have that much bandwidth. That may be the gating issue. – Rick James Jan 3 at 16:52
  • each pageview is average 1.25MB..there will be cloudflare CDN free version for light static caching as well...i just want to know single database server can handle this..i will work on bandwidth issue if that comes up to be bottleneck – uberrebu Jan 3 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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