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.