Advice on MySQL Setup for 2 Machines with Load Balancing
We now have 2 brand new dedicated boxes with load balancing for our site (our web-hosting company suggested it after some minor DDoS attacks were pegging our CPU).
Our site has a custom portal that we designed (PHP and each page queries the database for portal placement) and we currently use "Simple Machines Forum" both talking to MySQL.
My question deals with how I should setup MySQL for maximum performance and compatibility with our existing forum software. There were several issues I noticed.
We could use replication:
"spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves"
But would our forum software be able to read from multiple servers but only write to one?
We could use clustering:
"While many standard MySQL schemas and applications can work using MySQL Cluster, it is also true that unmodified applications and database schemas may be slightly incompatible or have suboptimal performance when run using MySQL Cluster (see Section 17.1.5, "Known Limitations of MySQL Cluster"). Most of these issues can be overcome, but this also means that you are very unlikely to be able to switch an existing application datastore-that currently uses, for example, MyISAM or InnoDB-to use the NDB storage engine without allowing for the possibility of changes in schemas, queries, and applications."
Again, would this be compatible with out existing infastructure?
We could use rsync (the recommendation given to us initially when we purchased the servers):
"Works great for daily file backups but not suitable for a MySQL database. If you're rsyncing while your database is live, you'll never reach a "safe" point where the database DNS record can be switched over (causing all future requests to land on the new machine) with a guarantee that all data up to that point has been transferred successfully.
Depending on your load, you will experience some degree of data loss."
"One thing to keep an eye out for is rsync failing because a file has been updated after it was added to the list of files to be transfered and before it was actually transfered. This is a problem when the database is accessed while the rsync job is running, generally only with very large tables (millions of records)."
Lastly, we could install MySQL on only one of the machines and install Apache/PHP on both with failover to the database machine if connections exceeded a certain limit (who knows that that limit might be) that way the database server would have it's own box unless the box was needed as a failover.
As an update to my original post, I could set up MySQL MASTER-MASTER replication but:
"The most common problem with replication is primary key collision. Primary key collision involves two MySQL servers creating table rows containing different data, but the same primary key. When this happens replication stops. With replication stopped, the difference between the data on the servers grows. At some point the weirdness gets noticed. Then begins the painful process of recovery, of trying to weave masses of conflicting data into a whole."
Also, I heard I can set up one system to use even numbered keys and another system to use odd numbered keys - but that's if the forum software ALWAYS and ONLY uses MySQL to generate primary keys (and I'm not certain it does).
"It doesn't take much for replication to go out of sync. A simple network interruption to one server can effectively halt two-way replication if data gets written during the interruption"
Also, I hear that clustering isn't going to be of any benefit until a certain minimum number of servers are used. Since we have only 2 servers, it doesn't sound like this would provide much benefit.
It's not foolproof as there may be issues where certain functions will try a read-back and the slave hasn't updated yet.
You gave a lot of great information and even summed up what I thought about the other options.
Master-Slave replication might very well be the option for me. I was thinking, however, that allowing the MySQL server to run on only one of the boxes by itself and then use the other server for Apache/PHP might be the best for performance (I could even failover to the other server with Apache/PHP on it as well if the load hits a threshold to balance connections). It seems like it would eliminate the network back traffic and the overhead in synchronization - meaning it might be better for CPU usage.
But what you are recommending is pretty cool. It would be easy to hijack that function in Simple Machines Forum and even put a variable for the $read_server_location (or whatever I want to call it) in the main config file. It's good because it not only spreads out the load more evenly, but it also keeps an up to the second backup of your database on another server. The drawback here is that almost every update to Simple Machines Forum replaces that main Subs.php file. That means I would have to manually update the code ever time there's an upgrade - though I would know basically what to do each time.