Re: Load overshooting ONE server

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.

-- 
"Anyone who believes that the laws of physics are mere social 
conventions is invited to try transgressing those conventions from the 
windows of my apartment. (I live on the twenty-first floor.) "

Alan Sokal