Feb 15, 2010
Sam Bowling

Here at SingleHop we deal with a lot of sites that are heavy in MySQL database usage and over time the dedicated servers end up with so much traffic that their MySQL service gets overloaded. There are many options we here at SingleHop utilize to expand their MySQL service and I’d like to go over the most common ones with you.
The first solution most customers will do is upgrade the processor, ram and hard drives on their dedicated server. There are many processors you can upgrade to which provide more speed and an upgrade from sata hard drives to SAS hard drives provides a significant increase in IO. The problem with these upgrades is that bleeding edge processors can be expensive and SAS hard drives are way more expensive than SATA.
The easiest way we have found at SingleHop to expand capability of a site is to use slave replication. Slave replication is where you have your old dedicated server which is deemed a master. All updates, inserts and deletes are replicated to the slave dedicated server so you have a 1:1 copy. You can then perform all selects on the slave dedicated server which reduces the amount of table locks you incur from traffic on your site due to select and database modification queries.
There are other methods like switching to a clustering storage engine but they don’t have the functionality that the two main storage engines have. If after you add a slave dedicated server you’re still having performance issues you should look into hiring a database architect to go over your database schema and work on improving the layout so it is as optimized as possible.

Comments

    I typically start with a server analysis using syssat. This way you can pinpoint what needs upgrading. MySQL is a RAM hungry beast, so try to give it as much as you can. I’ve seen entry level xeon servers with SATA disks and large amounts of RAM (16GB) handle loads very well.

    Also, never forget to tune your queries. Using the slow query log and EXPLAIN can help you find and fix bad queries. I’ve seen overloaded servers go to near idle by simply fixing a few queries. While this requires a lot of expertise, it can give you many-times the return as replication.

    Not all applications can easily be switched to send read-only statements to the slave, so before deploying replication, be sure your application can effectively utilize your new slave server.

    Posted by jeffatrackaid on February 16, 2010 Reply

Leave a Comment