Store
Community Documentation

v3 Knowledgebase

MySql Cluster Intro

MYSQL Clusters

This article will provide a basic understanding of clusters and how to deploy them with phpfox.
You would deploy clusters for 2 reasons 1.) Performance & 2.) Redundancy & Availability.

If your looking to deploy clustering to improve site performance you should start, with simpler things first like,

1.) Turning on and using phpfox memcached support. (see http://www.phpfox.com/kb/article/533/introduction-to-cache/ )

2.) Installing & configuring PHP-APC will also help. (see http://www.phpfox.com/kb/article/533/introduction-to-cache/ )

Both are simple to install, configure and will provide speed improvements, and may delay the need for MySql Clustering.

3) If your MySql and Web Server are on the same host, move the web server to its own host.

4) Finally look at off loading some of your content serving to a CDN.

5) Whats your hosts internet connection like? Many hosting companies throttle your connection speed.

Still slower than you want? Then your sites probably ready for clustering.

TYPES OF CLUSTERS

MySQL Replication.

Replication enables data from Master MySql server to be replicated to slave MySql server(s). In this configuration phpfox would write data to the master and read data from the slaves. This works quite well, and is probably the most deployed, solution for web applications, that have out grown a single MySql sever solutions.

It the easiest of the cluster solutions to implement. A minimum of 2 servers is required. The one you already have plus one extra. You can scale this out and down as required.

Phpfox supports this out of the box.

To configure phpfox to use this do the following. (this assume that you have setup your MySQL Replication Cluster) see:

http://www.clusterdb.com/mysql-cluster/get-mysql-replication-up-and-running-in-5-minutes/

http://www.phpfox.com/kb/article/436/how-to-get-mysql-replication-up-and-running/

Once you have set up your cluster running, follow the steps below.

First, open the file:

include/setting/server.sett.php

Look for:

$_CONF['db']['slave_servers'] = array();

In this example we will be adding 2 slave servers. In order to do this we include 2 arrays so it looks like:

$_CONF['db']['slave_servers'] = array( array( '192.168.0.193', 'root', 'phpfox', false, false ), array( '192.168.0.195', 'root', 'phpfox',false, false ) );

You will notice each array looks like:

array( '192.168.0.193', 'root', 'phpfox', false, false )

The first param is the server host name.
The 2nd param is the MySQL user name for that server.
The 3rd param is the MySQL password for that server.
The 4th and 5h params can be both set to "false".

Depending on your setup this array can look different.

Next, look for:

$_CONF['db']['slave'] = false;

Replace that with:

$_CONF['db']['slave'] = true;

That’s it. With this configuration all the MySql writes will go to the master whilst the reads to the slaves. (phpfox uses round robin, e.g does not take, server health, load or availability into account)

For a good tutorial on basic Mysql replication see:

http://www.severalnines.com/resources/clustercontrol%E2%84%A2-mysql-replication-tutorial#what

This can also be configure so that a backup master will be available in the event of the primary failing see:

http://www.severalnines.com/resources/clustercontrol%E2%84%A2-mysql-replication-tutorial

MySql with Galera Replication.

In this mode MySql is configured with multi-master replication. That is phpfox can write and read to any server in the cluster.

To use this configuration with phpfox you would need a load balancer to sit between phpfox and the MySql Cluster.

To run your MySql cluster in this configuration you will need at least 3 servers (1 that you currently have plus 2 others) + load balancing solution (you can read the tutorial to find out why)

This configuration provides great read scale and pretty good write scale.

For this to work with minimal issues it recommended that you have good network speed between the MySql servers and that the servers have the same specifications. e.g cpu, memory & disk space. You can scale this out and down as required.

For a good tutorial on MySql Galera replication see:

http://www.severalnines.com/clustercontrol-mysql-galera-tutorial#intro

http://www.severalnines.com/resources/clustercontrol-mysql-haproxy-load-balancing-tutorial


Finally the Ultimate option is Full MySql Cluster

If you need this you have some serious traffic to your site ☺ In this configuration the cluster is split into server, data & management nodes. Data is auto sharded. e.g all users whos user name start with a have data saved to node a all users whos user name start with b have data saved to data node b etc

Like the Galera cluster you will need a load balancing solution and the servers should be of the same specification with good network speed connecting them. Now the bad news. You need 5 servers to set this up and you cant easily scale it down. (its to do with the fact that the data is shared across data nodes) No problems scaling out though See:

http://www.mysql.com/products/cluster/ If your considering this, you are supporting a serious number of users and traffic. You probably don’t need my article ☺. This is the type of arrangement that the big boys use eg facebook etc.

So which one to use:

Well depends on quite a few things. So I am not going to cover it in this article. All have pros and cons and to a large extent, depend on your sites traffic, your budget etc Read the above tutorials to fully understand them all.

The good news is phpfox will work with any of them. So you can scale out as your needs grow.