Results 1 to 9 of 9
  1. #1

    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.

    www_simplemachines_org

    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"

    dev_mysql_com/doc/refman/5.0/en/replication.html

    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."

    dev_mysql_com/doc/refman/5.0/en/mysql-cluster.html

    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."

    www_softwareprojects_com/resources/programming/t-how-to-move-copy-a-live-mysql-database-and-what-1257.html

    "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)."

    www_webhostingtalk_com/showthread.php?t=862692

    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.

    Any suggestions or advice would be appreciated.

  2. #2
    Join Date
    Dec 2010
    Posts
    64
    Last edited by neverknowhk; 02-24-2011 at 12:17 AM.

  3. #3
    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"

    www_neocodesoftware_com/replication/

    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.
    Last edited by Billie; 02-24-2011 at 01:00 AM.

  4. #4
    I must ask, before taking these painful routes you are wanting advice on, have you first tried optimizing Mysql on one server?
    BackupTeddy - Easy Linux Backups
    Easiest automated backups for webmasters with VPS's or Servers

  5. #5
    Yes, I have. I have also setup caching for both MySQL and PHP.

    Also, the steps outlined here:

    www_simplemachines_org/community/index.php?topic=293441.0

  6. #6
    Join Date
    Jan 2011
    Location
    Canada
    Posts
    934
    Don't do the rsync. I can't even believe someone suggested that. MySQL clustering is for the brave. Master-master is for the foolish.

    Honestly, master-slave is really the most robust and fault-tolerant of the bunch. As per the read/write issues, this is the function you'll need to adjust to redirect reads to slaves.

    http://support.simplemachines.org/fu...unction;id=206

    It's not foolproof as there may be issues where certain functions will try a read-back and the slave hasn't updated yet.

  7. #7
    tchen, great advice. That was extremely helpful. Thank you!

  8. #8
    Quote Originally Posted by tchen View Post
    Don't do the rsync. I can't even believe someone suggested that. MySQL clustering is for the brave. Master-master is for the foolish.

    Honestly, master-slave is really the most robust and fault-tolerant of the bunch. As per the read/write issues, this is the function you'll need to adjust to redirect reads to slaves.

    support_simplemachines_org/function_db/index.php?action=view_function;id=206

    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.

    Lots to think about. That was extremely helpful.

  9. #9
    Join Date
    Jan 2011
    Location
    Canada
    Posts
    934
    I totally missed the vertical scale-up option ( i.e. get a bigger mysql box ) Generally the easiest route to go. But whatever you decide, best of luck in getting it working.

Similar Threads

  1. Load Balancing and Uptime Setup
    By gocard in forum Hosting Security and Technology
    Replies: 11
    Last Post: 09-27-2008, 12:28 AM
  2. MySQL Load Balancing (Shared Server) - Possible?
    By infamous91 in forum Hosting Security and Technology
    Replies: 2
    Last Post: 09-26-2007, 09:57 AM
  3. Setup Load balancing
    By whdev in forum Managed Hosting and Services
    Replies: 4
    Last Post: 11-06-2006, 06:50 PM
  4. Load balancer setup for identical machines
    By Igotit in forum Colocation and Data Centers
    Replies: 7
    Last Post: 07-06-2006, 12:47 PM
  5. Question About Load Balancing And MySql
    By Jason Benn in forum Dedicated Server
    Replies: 4
    Last Post: 08-05-2005, 03:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •