Re: Load overshooting ONE server
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Wed, 7 Sep 2016 08:50:32 -0400
Message-ID: <nqp2em$mjj$1_at_jstuckle.eternal-september.org>
>> 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.
>>
Date: Wed, 7 Sep 2016 08:50:32 -0400
Message-ID: <nqp2em$mjj$1_at_jstuckle.eternal-september.org>
On 9/7/2016 2:44 AM, The Natural Philosopher 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. > > >
"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."
That's true in a poorly implemented database on a poorly tuned MySQL. Fix the design and tune MySQL and it is faster than multiple requests from an application.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Wed Sep 07 2016 - 14:50:32 CEST