June 17, 2013 By Severalnines Magento is an open-source e-commerce platform built on Zend PHP and MySQL. It is widely adopted by online retailers with some 150,000 sites known to use it. Single server setups are easy to set up, but if your

June 17, 2013

By Severalnines

Magento is an open-source e-commerce platform built on Zend PHP and MySQL. It is widely adopted by online retailers with some 150,000 sites known to use it. Single server setups are easy to set up, but if your store is a huge success, then you probably need to think about clustering your environment with multiple servers. Clustering is done at the web, database and file-system level, as all web nodes need access to catalog images.?

?

This post is similar to our previous posts on?scaling Drupal?and?WordPress performance, and focuses on how to scale Magento on multiple servers. The software used is Magento version 1.7.0.2 , nginx, HAProxy, MySQL Galera Cluster and OCFS2 (Oracle Cluster File System) with a shared storage using Ubuntu 12.04.2 LTS (Precise) 64bit.

Our setup consists of 6 nodes or servers:

• NODE1: web server + database server
• NODE2: web server + database server
• NODE3: web server + database server
• LB1: load balancer (master) + keepalived
• LB2: load balancer (backup) + keepalived
• ST1: shared storage + ClusterControl

?

We will be using OCFS2, a shared disk file system to serve the web files across our web servers. Each of these web servers will have a nginx web server colocated with a MySQL Galera Cluster instance. We will be using 2 other nodes for load balancing.

Our major steps would be:

1. Prepare 6 instances
2. Deploy MySQL Galera Cluster onto NODE1, NODE2 and NODE3 from ST1
3. Configure iSCSI target on ST1
4. Configure OCFS2 and mount the shared disk onto NODE1, NODE2 and NODE3
5. Configure nginx on NODE1, NODE2 and NODE3
6. Configure Keepalived and HAProxy for web and database load balancing with auto failover
7. Install Magento and connect it to the Web/DB cluster via the load balancer

?

Prepare Hosts

?

Add following hosts definition in?/etc/hosts:

192.168.197.150	mymagento.com www.mymagento.com mysql.mymagento.com <span><em>#virtual IP</em></span>
192.168.197.151	NODE1 web1 db1
192.168.197.152	NODE2 web2 db2
192.168.197.153	NODE3 web3 db3
192.168.197.161	LB1 
192.168.197.162	LB2
192.168.197.171	ST1 clustercontrol

?

Turn off sudo with password:

<span>$ </span><span><strong>sudo</strong></span> visudo

?

And append following line:

<span><strong>%</strong></span><span><strong>sudo</strong></span> <span>ALL</span>=<span><strong>(</strong></span>ALL:ALL<span><strong>)</strong></span> NOPASSWD: ALL

?

Deploy MySQL Galera Cluster

?

** The deployment of the database cluster will be done from ST1

?

1. To set up MySQL Galera Cluster, go to the?Galera Configurator?to generate a deployment package. In the wizard, we used the following values when configuring our database cluster:

• Vendor: Codership (based on MySQL 5.5)?
• Infrastructure: none/on-premises
• Operating System: Ubuntu 12.04
• Number of Galera Servers: 3+1
• OS user: ubuntu
• ClusterControl Server: 192.168.197.171
• Database Servers: 192.168.197.151 192.168.197.152 192.168.197.153

At the end of the wizard, a deployment package will be generated and emailed to you.

?

2. Download the deployment package and run?deploy.sh:

$ <span><strong>wget</strong></span> http:<span><strong>//</strong></span>www.severalnines.com<span><strong>/</strong></span>galera-configurator<span><strong>/</strong></span>tmp<span><strong>/</strong></span>a3l3pnv560sforpeb29lruds94<span><strong>/</strong></span>s9s-galera-codership-2.4.0.tar.gz
$ <span><strong>tar</strong></span> xvfz s9s-galera-codership-2.4.0.tar.gz
$ <span><strong>cd</strong></span> s9s-galera-codership-2.4.0<span><strong>/</strong></span>mysql<span><strong>/</strong></span>scripts<span><strong>/</strong></span><span><strong>install</strong></span>
$ <span><strong>bash</strong></span> .<span><strong>/</strong></span>deploy.sh <span>2</span><span><strong>>&</strong></span><span>1</span> <span><strong>|</strong></span> <span><strong>tee</strong></span> cc.log

?

3. The deployment takes about 15 minutes, and once it is completed, note your API key. Use it to register the cluster with the ClusterControl UI by going to?http://192.168.197.171/cmonapi?. You will now see your MySQL Galera Cluster in the UI.?

?

Configure iSCSI

?

1. The storage server (ST1) needs to export a disk through iSCSI so it can be mounted on all three web servers (NODE1, NODE2 and NODE3). iSCSI basically tells your kernel you have a SCSI disk, and it transports that access over IP. The “server” is called the “target” and the “client” that uses that iSCSI device is the “initiator”.

Install iSCSI target in ST1:

<span>$ </span><span><strong>sudo</strong></span> <span><strong>apt-get install</strong></span> <span>-y</span> iscsitarget iscsitarget-dkms

?

2. Enable iscsitarget:

<span>$ </span><span><strong>sudo</strong></span> <span><strong>sed</strong></span> <span>-i</span> <span>"s|false|true|g"</span> <span><strong>/</strong></span>etc<span><strong>/</strong></span>default<span><strong>/</strong></span>iscsitarget

?

3. It is preferred to have separate disk for this file system clustering purpose. So we are going to use another disk mounted in ST1 (/dev/sdb) to be shared among web server nodes. Define this in iSCSI target configuration file:

<span>$ </span><span><strong>vim</strong></span> <span><strong>/</strong></span>etc<span><strong>/</strong></span>iet<span><strong>/</strong></span>ietd.conf

?

And add following lines:

Target iqn.2013-06.ST1:ocfs2
        Lun <span>0</span> <span>Path</span>=<span><strong>/</strong></span>dev<span><strong>/</strong></span>sdb,<span>Type</span>=fileio
        Alias iscsi_ocfs2

?

4. Add NODE1, NODE2 and NODE3 by specifying the network into iSCSI allow list:

<span>$ </span><span><strong>vim</strong></span> <span><strong>/</strong></span>etc<span><strong>/</strong></span>iet<span><strong>/</strong></span>initiators.allow

?

And append following line:

ALL 192.168.197.0<span><strong>/</strong></span><span>24</span>

?

5. Start iSCSI target service:

<span>$ </span><span><strong>sudo</strong></span> service iscsitarget start

?

** The following steps should be performed on NODE1, NODE2 and NODE3

?

6. Install iSCSI initiator on respective hosts:

<span>$ </span><span><strong>sudo</strong></span> <span><strong>apt-get install</strong></span> <span>-y</span> open-iscsi

?

7. Set the iSCSI initiator to automatically start and restart the iSCSI initiator service to apply changes:

$ <span><strong>sudo</strong></span> <span><strong>sed</strong></span> <span>-i</span> <span>"s|^node.startup.*|node.startup = automatic|g"</span> <span><strong>/</strong></span>etc<span><strong>/</strong></span>iscsi<span><strong>/</strong></span>iscsid.conf
$ <span><strong>sudo</strong></span> service open-iscsi restart

?

8. Discover iSCSI targets that we have setup earlier:

$ <span><strong>sudo</strong></span> iscsiadm <span>-m</span> discovery <span>-t</span> sendtargets <span>-p</span> ST1
192.168.197.171:<span>3260</span>,<span>1</span> iqn.2013-06.ST1:ocfs2

?

9. If you see some result as above, means we can see and able to connect to the iSCSI target. We just need to do another restart to access the iSCSI target:

<span>$ </span><span><strong>sudo</strong></span> service open-iscsi restart

?

10. Make sure you can see the new hard disk (/dev/sdb) listed under?/dev?directory:

<span>$ </span><span><strong>ls</strong></span> <span>-1</span> <span><strong>/</strong></span>dev<span><strong>/</strong></span>sd<span><strong>*</strong></span>

?

Configure OCFS2

?

** The following steps should be performed on NODE1 unless specified.

?

1. OCFS2 allows for file system to be mounted more than one place. Install OCFS2 tools in NODE1, NODE2 and NODE3:

<span>$ </span><span><strong>sudo</strong></span> <span><strong>apt-get install</strong></span> <span>-y</span> ocfs2-tools

?

2. Create disk partition table for hard disk drive?/dev/sdb:

<span>$ </span><span><strong>sudo</strong></span> cfdisk <span><strong>/</strong></span>dev<span><strong>/</strong></span>sdb

?

Create a partition by using following sequences in the wizard: New > Primary > accept Size > Write > yes

?

3. Creates an OCFS2 file system on?/dev/sdb1:

<span>$ </span><span><strong>sudo</strong></span> mkfs.ocfs2 <span>-b</span> 4K <span>-C</span> 128K <span>-L</span> <span>"Magento_Cluster"</span> <span><strong>/</strong></span>dev<span><strong>/</strong></span>sdb1

?

4. Create cluster configuration file and define the node and cluster directives:

<span><em># /etc/ocfs2/cluster.conf</em></span>
cluster:
        node_count = <span>3</span>
        name = ocfs2
node:
        ip_port = <span>7777</span>
        ip_address = 192.168.197.151
        number = <span>1</span>
        name = NODE1
        cluster = ocfs2
node:
        ip_port = <span>7777</span>
        ip_address = 192.168.197.152
        number = <span>2</span>
        name = NODE2
        cluster = ocfs2
node:
        ip_port = <span>7777</span>
        ip_address = 192.168.197.153
        number = <span>3</span>
        name = NODE3
        cluster = ocfs2

*Notes: The attributes under the node or cluster clause need to be after a tab.

?

** The following steps should be performed on NODE1, NODE2 and NODE3 unless specified.

?

5. Create the same configuration file (/etc/ocfs2/cluster.conf) in NODE2 and NODE3. This file should be the same on all nodes in the cluster, and changes to this file must be propagated to the other nodes in the cluster.

?

6. Enable o2cb driver to load the driver on boot on all nodes:

<span>$ </span><span><strong>sudo</strong></span> <span><strong>sed</strong></span> <span>-i</span> <span>"s|false|true|g"</span> <span><strong>/</strong></span>etc<span><strong>/</strong></span>default<span><strong>/</strong></span>o2cb

?

7. Restart iSCSI initiator to update the newly created disk partition:

<span>$ </span><span><strong>sudo</strong></span> service open-iscsi restart

?

8. Restart o2cb service to apply the changes in?/etc/ocfs2/cluster.conf:

<span>$ </span><span><strong>sudo</strong></span> service o2cb restart

?

9. Create the web files directory under?/var/www:

<span>$ </span><span><strong>sudo</strong></span> <span><strong>mkdir</strong></span> <span>-p</span> <span><strong>/</strong></span>var<span><strong>/</strong></span>www<span><strong>/</strong></span>magento

?

10. Get the block ID for the?/dev/sdb1. UUID is recommended in fstab if you use iSCSI device:

$ <span><strong>sudo</strong></span> blkid <span><strong>/</strong></span>dev<span><strong>/</strong></span>sdb1 <span><strong>|</strong></span> <span><strong>awk</strong></span> <span><strong>{</strong></span><span>'print $3'</span><span><strong>}</strong></span>
<span>UUID</span>=<span>"82b1d98c-30e7-4ade-ab9b-590f857797fd"</span>

?

11. Add following line into?/etc/fstab:

<span>UUID</span>=82b1d98c-30e7-4ade-ab9b-590f857797fd		<span><strong>/</strong></span>var<span><strong>/</strong></span>www<span><strong>/</strong></span>magento     ocfs2   defaults,_netdev        <span>0</span> <span>0</span>

?

12. Mount the filesystem:

<span>$ </span><span><strong>sudo</strong></span> <span><strong>mount</strong></span> <span>-a</span>

?

13. In NODE1, uncompress and copy Magento web files into?/var/www/magento?and setup directory permission:

$ <span><strong>tar</strong></span> <span>-xzf</span> magento-1.7.0.2.tar.gz
$ <span><strong>sudo</strong></span> <span><strong>cp</strong></span> <span>-Rf</span> magento<span><strong>/*</strong></span> <span><strong>/</strong></span>var<span><strong>/</strong></span>www<span><strong>/</strong></span>magento
$ <span><strong>sudo</strong></span> <span><strong>chown</strong></span> <span>-R</span> www-data.www-data <span><strong>/</strong></span>var<span><strong>/</strong></span>www<span><strong>/</strong></span>magento
$ <span><strong>sudo</strong></span> <span><strong>chmod</strong></span> <span>777</span> <span><strong>/</strong></span>var<span><strong>/</strong></span>www<span><strong>/</strong></span>magento<span><strong>/</strong></span>app<span><strong>/</strong></span>etc
$ <span><strong>sudo</strong></span> <span><strong>chmod</strong></span> <span>777</span> <span>-Rf</span> <span><strong>/</strong></span>var<span><strong>/</strong></span>www<span><strong>/</strong></span>magento<span><strong>/</strong></span>var
$ <span><strong>sudo</strong></span> <span><strong>chmod</strong></span> <span>777</span> <span>-Rf</span> <span><strong>/</strong></span>var<span><strong>/</strong></span>www<span><strong>/</strong></span>magento<span><strong>/</strong></span>media

?

Configure nginx and PHP-FPM

?

** The following steps should be performed on NODE1, NODE2 and NODE3.

?

1. We will use nginx as the web server for Magento. Install nginx and all required PHP modules:

<span>$ </span><span><strong>sudo</strong></span> <span><strong>apt-get install</strong></span> nginx php5-common php5-cli php5-fpm php5-mysql php5-mcrypt php5-gd php5-curl php-soap

?

2. Open nginx virtual host configuration file at?/etc/nginx/sites-available/default?and add following lines:

<span><em># /etc/nginx/sites-available/magento</em></span>
server <span><strong>{</strong></span>
    server_name mymagento.com www.mymagento.com;
    root <span><strong>/</strong></span>var<span><strong>/</strong></span>www<span><strong>/</strong></span>magento;
?
    location <span><strong>/</strong></span> <span><strong>{</strong></span>
        index index.html index.php;
        try_files <span>$uri</span> <span>$uri</span><span><strong>/</strong></span> <span><strong>@</strong></span>handler;
        expires 30d;
    <span><strong>}</strong></span>
?
    location <span><strong>/</strong></span>app<span><strong>/</strong></span>                <span><strong>{</strong></span> deny all; <span><strong>}</strong></span>
    location <span><strong>/</strong></span>includes<span><strong>/</strong></span>           <span><strong>{</strong></span> deny all; <span><strong>}</strong></span>
    location <span><strong>/</strong></span>lib<span><strong>/</strong></span>                <span><strong>{</strong></span> deny all; <span><strong>}</strong></span>
    location <span><strong>/</strong></span>media<span><strong>/</strong></span>downloadable<span><strong>/</strong></span> <span><strong>{</strong></span> deny all; <span><strong>}</strong></span>
    location <span><strong>/</strong></span>pkginfo<span><strong>/</strong></span>            <span><strong>{</strong></span> deny all; <span><strong>}</strong></span>
    location <span><strong>/</strong></span>report<span><strong>/</strong></span>config.xml   <span><strong>{</strong></span> deny all; <span><strong>}</strong></span>
    location <span><strong>/</strong></span>var<span><strong>/</strong></span>                <span><strong>{</strong></span> deny all; <span><strong>}</strong></span>
?
    location ~ <span><strong>/</strong></span>\. <span><strong>{</strong></span>
        deny all;
        access_log off;
        log_not_found off;
    <span><strong>}</strong></span>
?
    location <span><strong>@</strong></span>handler <span><strong>{</strong></span>
        rewrite <span><strong>/</strong></span> <span><strong>/</strong></span>index.php;
    <span><strong>}</strong></span>
?
    location ~ .php<span><strong>/</strong></span> <span><strong>{</strong></span>
        rewrite ^<span><strong>(</strong></span>.<span><strong>*</strong></span>.php<span><strong>)</strong></span><span><strong>/</strong></span> <span>$1</span> <span><strong>last</strong></span>;
    <span><strong>}</strong></span>
?
    location ~ .php$ <span><strong>{</strong></span>
        <span><strong>if</strong></span> <span><strong>(</strong></span><span><strong>!</strong></span>-e <span>$request_filename</span><span><strong>)</strong></span> <span><strong>{</strong></span> rewrite <span><strong>/</strong></span> <span><strong>/</strong></span>index.php <span><strong>last</strong></span>; <span><strong>}</strong></span>
        expires        off;
        fastcgi_pass   127.0.0.1:<span>9000</span>;
        fastcgi_param  SCRIPT_FILENAME  <span>$document_root</span><span>$fastcgi_script_name</span>;
        fastcgi_param  MAGE_RUN_CODE default;
        fastcgi_param  MAGE_RUN_TYPE store;
        include        fastcgi_params;
    <span><strong>}</strong></span>
<span><strong>}</strong></span>

?

3. Create a symbolic link from sites-available directory to enable the magento virtual host:

$ <span><strong>cd</strong></span> <span><strong>/</strong></span>etc<span><strong>/</strong></span>nginx<span><strong>/</strong></span>sites-enabled
$ <span><strong>sudo</strong></span> <span><strong>ln</strong></span> <span>-s</span> <span><strong>/</strong></span>etc<span><strong>/</strong></span>nginx<span><strong>/</strong></span>sites-available<span><strong>/</strong></span>magento magento

?

4. Restart nginx and PHP:

$ <span><strong>sudo</strong></span> service php5-fpm restart
$ <span><strong>sudo</strong></span> service nginx restart

?

Load Balancer and Failover

?

Instead of using HAProxy for doing SQL load balancing, we will be using some of the suggestions based on?this article?and just have the Magento instances connect to their local MySQL Server using localhost, with following criteria:

• Magento in each node will connect to MySQL database using localhost and bypassing HAProxy.
• Load balancing on database layer is only for mysql client/console. HAProxy will be used to balance HTTP.
• Keepalived will be used to hold the virtual IP: 192.168.197.150 on load balancers LB1 and LB2

  In case you plan to place the MySQL Servers on separate servers, then the Magento instances should connect to the database cluster via the HAProxy.

  ?

  ** The following steps should be performed on ST1

  ?

  1. We have created scripts to install HAProxy and Keepalived, these can be obtained from our?Git repository.

  Install git and clone the repo:

  $ <span><strong>apt-get install</strong></span> <span>-y</span> <span><strong>git</strong></span>
  $ <span><strong>git clone</strong></span> https:<span><strong>//</strong></span>github.com<span><strong>/</strong></span>severalnines<span><strong>/</strong></span>s9s-admin.git

  ?

  2. Make sure LB1 and LB2 are accessible using passwordless SSH. Copy the SSH keys to LB1 and LB2:

  $ ssh-copy-id <span>-i</span> ~<span><strong>/</strong></span>.ssh<span><strong>/</strong></span>id_rsa 192.168.197.161
  $ ssh-copy-id <span>-i</span> ~<span><strong>/</strong></span>.ssh<span><strong>/</strong></span>id_rsa 192.168.197.162

  ?

  3. Install HAProxy on both nodes:

  $ <span><strong>cd</strong></span> s9s-admin<span><strong>/</strong></span>cluster<span><strong>/</strong></span>
  $ <span><strong>sudo</strong></span> .<span><strong>/</strong></span>s9s_haproxy <span>--install</span> <span>-i</span> <span>1</span> <span>-h</span> 192.168.197.161
  $ <span><strong>sudo</strong></span> .<span><strong>/</strong></span>s9s_haproxy <span>--install</span> <span>-i</span> <span>1</span> <span>-h</span> 192.168.197.162

  ?

  4. Install Keepalived on LB1 (master) and LB2 (backup) with 192.168.197.150 as virtual IP:

  <span>$ </span><span><strong>sudo</strong></span> .<span><strong>/</strong></span>s9s_haproxy <span>--install-keepalived</span> <span>-i</span> <span>1</span> <span>-x</span> 192.168.197.161 <span>-y</span> 192.168.197.162 <span>-v</span> 192.168.197.150

  ?

  ** The following steps should be performed on LB1 and LB2

  ?

  5. By default, the script will configure the MySQL reverse proxy service to listen on port 33306. We will need to add a few more lines to tell HAproxy to load balance our web server farm as well. Add following line in?/etc/haproxy/haproxy.cfg:

  frontend http-in
      <span><strong>bind</strong></span> <span><strong>*</strong></span>:<span>80</span>
      default_backend web_farm
  ?
  backend web_farm
      server NODE1 192.168.197.151:<span>80</span> maxconn <span>32</span>
      server NODE2 192.168.197.152:<span>80</span> maxconn <span>32</span>
      server NODE3 192.168.197.153:<span>80</span> maxconn <span>32</span>

  ?

  6. Restart HAProxy service:

  $ <span><strong>sudo</strong></span> <span><strong>killall</strong></span> haproxy
  $ <span><strong>sudo</strong></span> <span><strong>/</strong></span>usr<span><strong>/</strong></span>sbin<span><strong>/</strong></span>haproxy <span>-f</span> <span><strong>/</strong></span>etc<span><strong>/</strong></span>haproxy<span><strong>/</strong></span>haproxy.cfg <span>-p</span> <span><strong>/</strong></span>var<span><strong>/</strong></span>run<span><strong>/</strong></span>haproxy.pid <span>-st</span> <span><strong>`</strong></span><span><strong>cat</strong></span> <span><strong>/</strong></span>var<span><strong>/</strong></span>run<span><strong>/</strong></span>haproxy.pid<span><strong>`</strong></span>

  ?

  Install Magento

  ?

  1. Now that we have a load-balanced setup that is ready to support Magento, we will now create our Magento database. From the ClusterControl UI, go to Manage > Schema and Users > Create Database to create the database:

  

  ?

  2. Create the database user under Privileges tab:

  

  ?

  3. Assign the correct privileges for magento_user on database magento_site:

  

  At the moment, we assume you have pointed mymagento.com and www.mymagento.com to the virtual IP, 192.168.197.150.?

  ?

  4. Open web browser and go to mymagento.com. You should see an installation page similar to screenshot below:

  

  ?

  * Take note that we are using localhost in the host value, session data will be saved in database. It will allow users to use the same session regardless of which web server they are connected to.

  ?

  Notes

  ?

  ** Updated on 9th Dec 2013 **

  By default Magento will setup a MyISAM table specifically for FULLTEXT indexing called?catalogsearch_fulltext. MyISAM tables are supported within MySQL Galera Cluster, however, MyISAM has only basic support, primarily because the storage engine is non-transactional and so Galera cannot guarantee the data will remain consistent within the cluster.

  Codership has released MySQL-wsrep 5.6 supports with Galera 3.0 which currently in beta release at the time of this update. You could either use the MySQL-wsrep 5.6 which supports InnoDB FTS or convert all non-Galera friendly tables to use InnoDB with primary keys. Alternatively, you can use external search engine (such as Solr or Sphinx) for FTS capabilities.

  If you choose the latter option, you need to convert some of the tables to work well with Galera by executing following queries on one of the DB node:

  mysql<span>></span> <span><strong>ALTER</strong></span> <span><strong>TABLE</strong></span> magento.api2_acl_user <span><strong>ADD</strong></span> id <span><strong>INT</strong></span> <span><strong>UNSIGNED</strong></span> <span><strong>AUTO_INCREMENT</strong></span> <span><strong>PRIMARY KEY</strong></span> <span><strong>FIRST</strong></span><span>;</span>
  mysql<span>></span> <span><strong>ALTER</strong></span> <span><strong>TABLE</strong></span> magento.api_session <span><strong>ADD</strong></span> id <span><strong>INT</strong></span> <span><strong>UNSIGNED</strong></span> <span><strong>AUTO_INCREMENT</strong></span> <span><strong>PRIMARY KEY</strong></span> <span><strong>FIRST</strong></span><span>;</span>
  mysql<span>></span> <span><strong>ALTER</strong></span> <span><strong>TABLE</strong></span> magento.weee_discount <span><strong>ADD</strong></span> id <span><strong>INT</strong></span> <span><strong>UNSIGNED</strong></span> <span><strong>AUTO_INCREMENT</strong></span> <span><strong>PRIMARY KEY</strong></span> <span><strong>FIRST</strong></span><span>;</span>
  mysql<span>></span> <span><strong>ALTER</strong></span> <span><strong>TABLE</strong></span> magento.widget_instance_page_layout <span><strong>ADD</strong></span> id <span><strong>INT</strong></span> <span><strong>UNSIGNED</strong></span> <span><strong>AUTO_INCREMENT</strong></span> <span><strong>PRIMARY KEY</strong></span> <span><strong>FIRST</strong></span><span>;</span>
  mysql<span>></span> <span><strong>ALTER</strong></span> <span><strong>TABLE</strong></span> magento.xmlconnect_config_data <span><strong>ADD</strong></span> id <span><strong>INT</strong></span> <span><strong>UNSIGNED</strong></span> <span><strong>AUTO_INCREMENT</strong></span> <span><strong>PRIMARY KEY</strong></span> <span><strong>FIRST</strong></span><span>;</span>
  mysql<span>></span> <span><strong>ALTER</strong></span> <span><strong>TABLE</strong></span> magento.oauth_nonce <span><strong>ADD</strong></span> id <span><strong>INT</strong></span> <span><strong>UNSIGNED</strong></span> <span><strong>AUTO_INCREMENT</strong></span> <span><strong>PRIMARY KEY</strong></span> <span><strong>FIRST</strong></span><span>,</span> <span><strong>ENGINE</strong></span><span>=</span><span>'InnoDB'</span><span>;</span>
  mysql<span>></span> <span><strong>ALTER</strong></span> <span><strong>TABLE</strong></span> magento.catalogsearch_fulltext <span><strong>DROP</strong></span> <span><strong>INDEX</strong></span> FTI_CATALOGSEARCH_FULLTEXT_DATA_INDEX<span>;</span>
  mysql<span>></span> <span><strong>ALTER</strong></span> <span><strong>TABLE</strong></span> magento.catalogsearch_fulltext <span><strong>ENGINE</strong></span><span>=</span><span>'InnoDB'</span><span>;</span>

  ?

  Verify The Architecture

  ?

  1. Check the HAproxy statistics by logging into the HAProxy admin page at LB1 host port 9600. The default username/password is admin/admin. You should see some bytes in and out on the?web_farm?ands9s_33306_production?divs:

  

  ?

  2. Check and observe the traffic on your database cluster from the ClusterControl overview page athttps://192.168.197.171/clustercontrol?:

  

  ?

  There are many improvements that could be made to this setup. For example, you could provide redundancy to the shared storage server by installing DRBD. You can also add a Varnish Cache in the load balancing servers to provide better caching on your static contents and reduce the load on the web servers/database servers.