Wiki Home | Recent changes | View source | Page history Printable version | Disclaimers | Privacy policy

Not logged in Log in | Help

MySQL Cluster

From Cloud Computing Wiki - Kaavo

Contents 1 Overview 2 What does it do? 3 What deployment actions are included in the System Definition? 4 What run-time actions/events are defined in the System Definition? 5 Configuration for your setup 6 What run-time Events are defined in the System Definition? 7 Connection String 8 Monitoring Note

Overview

IMOD, the MySQL Cluster System Definition templates for 32bit and 64bit OS for EC2 are provided as best practice examples to help users. Users are expected to customize the provided solutions for their own needs. Please also refer to the article on MySQL cluster fault tolerance to understand how the cluster works and how to properly configure it for your needs. The information assumes that the users have basic understanding of deploying and starting systems in IMOD. Please refer to the Quick Start Guide Quick Start Guide to learn the basics of deploying and starting a system.

• In this template we use Mysql Cluster 7.0. Some of the key feature of MySQL Cluster 7.0 are follows

• • ACID Compliant, Transactional Database
  • In-Memory Index and Data
  • Disk-Based Data
  • Distributed, Shared Nothing Architecture
  • Synchronous Data Replication
  • Automatic Sub-Second Failover & Self-Healing
  • Variable Sized Records
  • User-Defined Partitioning
  • NDBINFO
  • On-Line Back-Up
  • SQL
  • NDB API (C++)
  • MySQL Cluster Manager
  • On-Line Add Node

• Following are the differences b/w Mysql Cluster For 32 Bit and Mysql Cluster For 64 Bit System Definition Templates.

                               32 bit template                     |   64 bit template
Instance Type : (m1.small)                                         | (m1.large)
Ami           : 32 bit image (ami-5647a33f)                        | 64 bit image (ami-2a5fba43)

• install-mysql-manager and install-mysql-ndbd actions are different in the templates as they install corresponding RPMs for 32bit and 64 bit solution

Required RPM : MySQL-Cluster-gpl-client-7.0.9-0.rhel5.i386.rpm     | MySQL-Cluster-gpl-client-7.0.9-0.rhel4.x86_64.rpm
               MySQL-Cluster-gpl-server-7.0.9-0.rhel5.i386.rpm     | MySQL-Cluster-gpl-server-7.0.9-0.rhel4.x86_64.rpm
               MySQL-Cluster-gpl-storage-7.0.9-0.rhel5.i386.rpm    | MySQL-Cluster-gpl-storage-7.0.9-0.rhel5.x86_64.rpm
               MySQL-Cluster-gpl-tools-7.0.9-0.rhel5.i386.rpm      | MySQL-Cluster-gpl-management-7.0.9-0.rhel4.x86_64.rpm
               MySQL-Cluster-gpl-management-7.0.9-0.rhel5.i386.rpm | MySQL-Cluster-gpl-tools-7.0.9-0.rhel4.x86_64.rpm          

Please also check when to use MySQL Cluster vs. when to use Master/Slave. Cluster was introduced by MySQL couple of years back, for scalability it is the preferred solution. One limitation is that it currently doesn't support foreign key constraints, this limitation may not be an issue if your application uses an O/R mapping layer e.g. Hibernate, EJB, ActiveRecord, etc. for enforcing the constraints.

What does it do?

With a single click bring online fully functional Mysql cluster with 3 servers, 1 Manager server group having role manager, and 2 NDBD node group having role dual-ndb-api.

What deployment actions are included in the System Definition?

Default deployment has two server roles and 3 servers. One server for running the Manager node process (server role MySQL Manager), and two servers for NDBD node and API node processes (server role dual-node). If you are running a multi-tier system you can run the Manager node process on your app or web tier. You shouldn't run the manager node on the same server as NDBD node as it can cause the entire cluster to fail in case the manager process dies. If manager process is running on separate server then killing the manager process doesn't impact cluster runtime performance. Manager node process is used for backups and schema changes. API node is used for performing queries, and NDBD node is maintaining data. For an HA setup you need at least two NDBD nodes and two API nodes . You can change the number of nodes per your deployment needs in the system definition. Please refer to MySQL documentation to learn about MySQL Cluster.

• During our testing we found that for a two server cluster with one manager setup using 4 API nodes with 2 NDBD nodes gives the optimal query performance and therefore we used this setup in our best practice system definition templates for MySQL Cluster. Following are the performance results, comparing 2 api nodes for each NDBD node with the 4 api for each NDBD node configuration using 32bit machine.

2 API Node processes per NDBD Node Process                  |  4 API Node Processes per NDBD Node Process
MySQL number of slow queries 01 Mar 2010 23:10:47 21        |  MySQL number of slow queries 25 Feb 2010 09:38:44 0
MySQL number of threads 01 Mar 2010 23:10:48 11             |  MySQL number of threads 25 Feb 2010 09:38:45 23
MySQL queries per second 01 Mar 2010 23:10:45 339.84 qps    |  MySQL queries per second 25 Feb 2010 09:38:43 414.51 qps

What run-time actions/events are defined in the System Definition?

• install-mysql-ndbd
• install-mysql-manager
• create-manager-config
• manager-kick-start
• create-ndbd-config
• ndb-api-start
• SetupS3cfg
• backup-database
• restore-database-from-s3files

Configuration for your setup

You will need to provide the values of the parameters for the following actions for your setup

• SetupS3cfg

<parameter name="access_key" type="literal" value="put_your_s3_access_key"/>  
<parameter name="secret_key" type="literal" value="put_your_s3_secret_key"/>

• backup-database

<parameter name="BUCKET" type="literal" value="put_your_bucket_name"/>
<parameter name="DATABASE_NAME" type="literal" value="put_your_database_name"/>

• restore-database-from-s3files

<parameter name="BUCKET" type="literal" value="put_your_bucket_name"/>
<parameter name="DATABASE_NAME" type="literal" value="put_your_database_name"/>
<parameter name="BACKUP_FILE" type="literal" value="put_your_backup_filename"/>

What run-time Events are defined in the System Definition?

• backup-database
• restore-database-from-s3files
• mysql-manager-overload
• mysql-manager-recovered
• mysql-data-server-overload
• mysql-data-server-recorved

Note : Before scaleup or recovery event please make sure to backup your database using backup-database action. You can use scheduler to automatically take scheduled backups at specified intervals. The backups are for disaster recovery in case all the NDBD nodes with redundant data are lost. For example in this setup, data wouldn't be lost in case one of the two dual-node server dies for any reason.

Connection String

For connection string if you use jdbc connection, use the following format. You will need to add comma separated URLs for each dual-node (servers running ndbd and api nodes), since this is a cluster setup all nodes can perform read & write operations.

<connection-url>jdbc:mysql://private_dns_of_ndbd1,private_dns_of_ndbd2:3306/database_name</connection-url>

Monitoring Note

Apart from the common monitoring metrics (CPU, DISK , Memory, Bandwidth), IMOD can monitor Mysql QPS and Number of Slow queries on the mysql API node. We have tested this functionality on  :

• Amazon EC2
  • Fedora 8 and higher

• RackSpace
  • Fedora 10, 11, and 13
  • CentOS 5.3 and higher

View source | Discuss this page | Page history | What links here | Related changes Wiki Home | About Cloud Computing Wiki - Kaavo | Recent changes |   This page was last modified on 9 February 2012, at 16:03. Content is available under Copyright 2012 Kaavo,All rights reserved..