OSSCube MySQL Cluster Tutorial By Sonali

MySQL Cluster Tutorial : 

MySQL Cluster Tutorial Presented by – Sonali Minocha

Who Am I? : 

Who Am I? MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Slide 3: 

Architecture and Organization

What is a Cluster? : 

What is a Cluster? MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Single MySQL Server Architecture : 

Single MySQL Server Architecture MySQL Client MySQL Server SQL Request Response

MySQL Cluster Architecture : 

MySQL Cluster Architecture MySQL Client SQL Request Response SQL Node (MySQL server) Data Nodes Response NDB API Call Partitioning Replication Message

Components of MySQL Cluster : 

Components of MySQL Cluster MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Various Nodes : 

Various Nodes Application API Node Data Node Management Node MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Slide 9: 

(Data Nodes) MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Organization : 

Organization MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Partition : 

Partition MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Partitioning : 

Partitioning MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Fragments : 

Fragments MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Node Groups : 

Node Groups MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Split Brain : 

Split Brain A B Servers can not see each other I have full responsibility of the cluster I have full responsibility of the cluster MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Arbitration : 

Arbitration 2 3 First Node to ask will continue while the other will be shut down Network Split NG 1 MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

More Data Nodes : 

More Data Nodes NG 1 Network Split/ Shutdown MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Arbitrator Issues : 

Arbitrator Issues MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Arbitrator Issues : 

Arbitrator Issues MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Normal Operation : 

Normal Operation I am Alive I am Alive I am Alive I am Alive MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Missed Heartbeats : 

Missed Heartbeats I am Alive I am Alive I am Alive MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Synchronous : 

Synchronous MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Phase One: Commit-Request : 

Phase One: Commit-Request MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Phase Two: Successful Commit : 

Phase Two: Successful Commit MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Phase Two: Failure (Abort Commit) : 

Phase Two: Failure (Abort Commit) MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Slide 26: 

INSTALL MySQL CLUSTER

Initiate MySQL Cluster : 

Initiate MySQL Cluster # file "config.ini" - 2 data nodes and 2 SQL nodes# This file is placed in the startup directory of ndb_mgmd (the# management server) # The first MySQL Server can be started from any host. The second# can be started only on the host MySQLd_5.MySQL.com [NDBD DEFAULT] NoOfReplicas= 2 DataDir= /var/lib/MySQL-cluster [NDB_MGMD] Hostname= ndb_mgmd.MySQL.com DataDir= /var/lib/MySQL-cluster [NDBD] HostName= ndbd_2.MySQL.com [NDBD] HostName=ndbd_3.MySQL.com [MySQLD] [MySQLD] HostName= MySQLd_5.MySQL.com MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Management Node : 

Management Node MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Data Nodes : 

Data Nodes my.cnf# example additions to my.cnf for MySQL Cluster [ndbd] connect-string=ndb_mgmd.MySQL.com MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Data Node Configurations Options : 

Data Node Configurations Options MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

SQL Nodes : 

SQL Nodes my.cnf # example additions to my.cnf for MySQL Cluster# (will work on all versions) # enable ndbcluster storage engine, and provide connectstring for management # server host to the default port 1186 [MySQLd] Ndbcluster ndbconnectstring=ndb_mgmd.MySQL.com:1186 MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Config.ini Parameters : 

Config.ini Parameters General Parameters Id Hostname File and Directory Location Parameter BackupDataDir DataDir FileSystemPath MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Slide 34: 

Transaction Handling Parameters TransactionInactiveTimeout TransactionDeadlockDetectionTimeout Memory Usage Parameters LockPagesInMainMemory Utility Programs ndb_size.pl

Monitoring the Startup Phases : 

Monitoring the Startup Phases MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube. http://osscube.com/technology/mysql

Slide 36: 

MySQL Cluster Storage Engine --NDB MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

NDB Storage Engine : 

NDB Storage Engine MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

NDB’s Storage Method : 

NDB’s Storage Method In-Memory Storage Disk-Based Storage New in MySQL 5.1 Undo Logging Fixed-Width Columns MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Indexes : 

Indexes MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Creating Indexes : 

Creating Indexes Each table in the cluster always has a hash index for a Primary Key Ordered indexes can be created with ALTER TABLE ADD INDEX .... MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

First Node/First Fragment : 

First Node/First Fragment Hidden field/table created that will handle the management of the Unique Index on Name Ordered Index (T-Tree) created for Continent field MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Second Node/Second Fragment : 

Second Node/Second Fragment Same as First Node/Fragment setup Contains the remaining data MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Primary Key Lookup : 

Primary Key Lookup PK values Function() Hash Data node Look up row Data node MySQL Server Unique Key Lookup Look up row Unique Key Lookup may require another network hop MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Parallel Ordered Index Scan : 

Parallel Ordered Index Scan Data node Data node MySQL Server rows T- Tree Index T- Tree Index MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Parallel full table scan : 

Parallel full table scan Data node Data node MySQL Server rows MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Parallel full table scan ( WHERE condition processed in SQL node) : 

Parallel full table scan ( WHERE condition processed in SQL node) Data node Data node MySQL Server rows SQL Query Many Rows Few Rows Where Condition MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Parallel full table scan ( WHERE condition processed in data node) : 

Parallel full table scan ( WHERE condition processed in data node) Data node Data node MySQL Server rows SQL Query Few Rows Few Rows Where Condition Where Condition MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Storage of Indexes : 

Storage of Indexes MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Characteristics of NDB storage Engine : 

Characteristics of NDB storage Engine MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Durability : 

Durability MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Global Check Point : 

Global Check Point A GCP occurs every few seconds, when transactions for all nodes are synchronized and the REDO log is flushed to disk MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Local Check Point : 

Local Check Point MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Memory - RAM : 

Memory - RAM Memory Allocation Total Memory = N(data nodes)* Local Memory / NoOfReplicas -- or -- Local Memory = Total Memory * NoOfReplicas / N(data nodes) Sizing up theMemory Fixed size in memory Variable sized in memory Fixed size on disk MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Memory Pages : 

Memory Pages MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Slide 56: 

MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Slide 57: 

HANDLING DATA IN MySQL CLUSTER

Disk Data Objects : 

Disk Data Objects MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Creating the Log File Group : 

Creating the Log File Group Create Log File Group CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE 2M ENGINE NDB; Adding Undo Files to Log File Group ALTER LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_2.dat' INITIAL_SIZE 12M ENGINE NDB; MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Verifying UNDO Files are Created : 

Verifying UNDO Files are Created INFORMATION_SCHEMA Tables SELECT LOGFILE_GROUP_NAME, LOGFILE_GROUP_NUMBER, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME = 'undo_1.dat'; SELECT LOGFILE_GROUP_NAME, LOGFILE_GROUP_NUMBER, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME = 'undo_2.dat'; MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Creating the Tablespace : 

Creating the Tablespace Create a Tablespace CREATE TABLESPACE ts_1 ADD DATAFILE 'data_1.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 32M ENGINE NDB; Add an Additional Data File ALTER TABLESPACE ts_1 ADD DATAFILE 'data_2.dat' INITIAL_SIZE 48M ENGINE NDB; Verifying Data Files are Created SELECT FILE_NAME, LOGFILE_GROUP_NAME, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME = 'ts_1' AND FILE_TYPE = 'DATAFILE'; MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Create a Disk Data Table : 

Create a Disk Data Table Non-Indexed Columns Stored on Disk in Tablespace CREATE TABLE dt_1 ( memberId INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, lName VARCHAR(50) NOT NULL, fName VARCHAR(50) NOT NULL, dob DATE NOT NULL, joined DATE NOT NULL, INDEX(lName, fName) ) TABLESPACE ts_1 STORAGE DISK ENGINE NDB; Alter Existing Non-Cluster Tables ALTER TABLE city TABLESPACE ts1 STORAGE DISK ENGINE=NDB; View Table Status SHOW TABLE STATUS LIKE 'city'\G MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Monitoring Free Space : 

Monitoring Free Space MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Dropping Cluster Tables : 

Dropping Cluster Tables MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Slide 65: 

MySQL CLUSTER MANAGEMENT

MySQL Cluster’s Native Backup Tool : 

MySQL Cluster’s Native Backup Tool Backup Data .ctl .log .data Abort Backup ndb_mgm> ABORT BACKUP 6 Abort of backup 6 ordered Node 2: Backup 10 started from 1 has been aborted. Error: 1321 MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

MySQLdump : 

MySQLdump MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Restoring Backups : 

Restoring Backups Recovering MySQL Cluster's Native Backup Files (1/3) Verify Management Node is Running shell> ndb_mgm -e "SHOW" Start and Empty the Data Nodes Start each data node with the --initial option. MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Restoring Backups : 

Restoring Backups MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Restoring Backups : 

Restoring Backups MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Restoring MySQLdump Files : 

Restoring MySQLdump Files MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Slide 72: 

Optimization

EXPLAIN : 

EXPLAIN MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Full Table Scan : 

Full Table Scan Without Condition Pushdown With Condition Pushdown MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Parallel full table scan ( WHERE condition processed in SQL node) : 

Parallel full table scan ( WHERE condition processed in SQL node) Data node Data node MySQL Server rows SQL Query Many Rows Few Rows Where Condition MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Parallel full table scan ( WHERE condition processed in data node) : 

Parallel full table scan ( WHERE condition processed in data node) Data node Data node MySQL Server rows SQL Query Few Rows Where Condition Where Condition Few Rows MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Index Statistics : 

Index Statistics MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

MySQL Cluster Query Cache : 

MySQL Cluster Query Cache MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Data Size/Usage Issues : 

Data Size/Usage Issues MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Query Design : 

Query Design MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube

Slide 81: 

MySQL Cluster Replication

Slide 82: 

MySQL Server Master MySQL Server Slave MySQL Server Slave IO thread SQL thread Binlog Relaylog Relaylog Binlog Application W MySQL Replication

Slide 83: 

Cluster Data node Data node Data node Data node MySQLd MySQLd Update Update Ndb API Ndb API Update Update

NDB Injector Thread : 

NDB Injector Thread A thread inside MySQL server Responsible for injecting rows in binlog and produces “Single connical binlog for cluster” not just one MySQL Server It contains everything written on all ndbApi program including MySQLd connected to cluster

Slide 85: 

NdbClsuter Handler IO thread Binlog Relaylog Binlog MySQLd(Master) MySQL Cluster Replication Cluster Data node Data node Data node Data node Replication MySQLd(slave) NdbClsuter Handler Apply thread Cluster Data node Data node Data node Data node

Slide 86: 

Thank you for your time and attention MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube For more information, please feel free to drop in a line to sonali@osscube.com or visit http://www.osscube.com www.osscube.com

Slide 87: 

Q n A