Re: Load overshooting ONE server

In article <nqocvv$6br$1_at_news.albasani.net>,  The Natural Philosopher <tnp_at_invalid.invalid> wrote:

> On 07/09/16 02:55, Michael Vilain wrote:
> > In article <nqlsrg$2u2$1_at_news.albasani.net>,
> > The Natural Philosopher <tnp_at_invalid.invalid> wrote:
> >
> >> On 06/09/16 07:24, bit-naughty_at_hotmail.com wrote:
> >>> If you have a website whose main job is to put in and retrieve from a
> >>> MySQL DB, and the load of the site overshoots 1 server, what do you
> >>> do, exactly? If you add *webservers* for the site, and load blance
> >>> between them, that doesn't solve the problem, because the load on the
> >>> MySQL server will remain the same. Do you have to add *another MySQL
> >>> sserver*, and then copy the entire database over from the existing
> >>> server to the new one, and load balance between THOSE 2? How is this
> >>> done? And then, how do you do it so that an INSERT into the db
> >>> INSERTs into BOTH those servers? And if more servers are added later,
> >>> then those, and so on....?
> >>>
> >>>
> >>>
> >>> Thanks.
> >>>
> >>
> >> 1/. Increase the power of the server. More CPU, More RAM, SSD if its I/O
> >> bound.
> >>
> >> 2/. Rewrite the code so the load is off the server and into the
> >> application(s).
> >>
> >> 3/. Use a proper database like Oracle, and pay for it.
> >
> > or 4/. "Sharding" which is very tricky. It's a form of clustering.
> >
> > I don't know much about it but if you think about the distribution of
> > your "hotest" query and you split that in half, one half to one machine
> > and one half to the other, then a third machine to manage "which machine
> > gets the query", that's kinda what sharding is.
> >
> > If you have $$$$ to throw at the problem, but not much database smarts,
> > you can hire someone to figure out where to go next. If need be, they
> > can help you setup a cluster.
> >
> > Or you can figure out where the bottleneck is (memory, CPU, or IO) and
> > increase the server's resources until the next bottleneck happens.
> >
> > You might get lucky. It might be a poorly designed table layout where
> > all you need is an index.
> >
> > Or you may truly need a bigger server. Here's where "more cores" may
> > not buy you much if you have a single-threaded transaction that you
> > can't chop up.
> >
> > In any case, be prepared to spend time and money on solving this problem.
> >
> I do know that adding judicious indices will net you a 10x speed
> increase or more in certain cases, and that any complex query is often 5
> times as fast when done in stages using logic in the application and
> simple queries than in MySQl.

Which demonstrates the statement I learned over 30 years ago in a system performance and capacity planning class for VMS.

[Quoted] "Unless a system is poorly tuned at the start, usually you'll only get at best 10% increase by spending time tuning a system. However, if an application is poorly designed, you can get massive increases in performance and throughput by tuning that application."

Which still seems to apply, even today. Tune the application first, then throw hardware at it. If it doesn't scale well, go back and rethink the design so that it will scale.

-- 
DeeDee, don't press that button!  DeeDee!  NO!  Dee...
[I filter all Goggle Groups posts, so any reply may be automatically ignored]