Re: Load overshooting ONE server

From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Tue, 6 Sep 2016 08:58:13 -0400
Message-ID: <nqmeh2$b62$1_at_jstuckle.eternal-september.org>


On 9/6/2016 2:24 AM, bit-naughty_at_hotmail.com wrote:
[Quoted] > 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.
>

It depends a lot on the setup and why you are overloading the database.

To start with, you need to find out where your performance problem is. I've seen people blame the RDBMS when it's the database design that's causing problems. Poor normalization or insufficient/incorrect (or even too many) indexes can kill database performance. So can not trimming tables of old data which isn't used any more (move this to historical tables in the event it may be needed at some time in the future).

There are a number of tuning parameters you can use with MySQL. The default parameters are good for general purpose access, but not all conditions. Check those and see if they help. You may need to add more memory, as TNP suggested. But first you should ensure you're using what you have.

You can also move the database to another host, and repeat the process. When it's on another host, you need to add network performance to the equation. Ensure your network is not overloaded. It definitely would help to have a separate NIC to connect to the database so the NIC doesn't have to share the bandwidth. And ensure you're NICs and cabling support 1G.

If you're still overloaded, you can go to a master-slave(s) database setup where the master automatically replicates changes to the slave(s).  This is a bit harder to set up, but it works well. Updates should be done through the master, and you can read from the slave(s). The only problem here is there can be a slight delay between when the master gets updated and the slave(s) get updated.

As a last resort, you can go to a commercial database. Oracle, SQL Server and DB2 are all good ones (I personally prefer DB2). But if you do that, you'll have to rewrite all of your database access on your site. A long process, especially for a large site, but possibly necessary.

The bottom line is, there are very few websites with the load necessary to overload MySQL. You should easily be able to run hundreds of hits per second, depending on the site's needs. More often it is simply bad design and/or tuning that causes problems.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================
Received on Tue Sep 06 2016 - 14:58:13 CEST

Original text of this message