the osscube mysql high availability tutorial
Upload: osscube-llc-a-global-open-source-enterprise-for-open-source-solutions
Post on 19-May-2015
4.727 views
DESCRIPTION
The OSSCube MySQL High Availability Tutorial presentation given by Rakesh Kumar and Sonali Minocha at OSI Days 2010TRANSCRIPT
Architecture and OrganizationArchitecture and Organization
What is a Cluster?
MySQL Cluster Tutorial, © OSSCube
Single MySQL Server Architecture
MySQL ClientMySQL Client MySQL Server
SQL Request
Response
MySQL Cluster Tutorial, © OSSCube
MySQL Cluster Architecture
MySQL ClientMySQL ClientSQL Request
Response
SQL Node (MySQL server)Data Nodes
Response
NDB API Call
PartitioningReplicationMessage
MySQL Cluster Tutorial, © OSSCube
Various Nodes
Application
API Node Data Node
Management Node
MySQL Cluster Tutorial, © OSSCube
(Data Nodes)
NdbClusterEngine
NdbClusterEngine
NdbClusterEngine
MySQL Cluster Tutorial,© OSSCube
Partitioning
HorizontalPartitioning
VerticalPartitioning
MySQL Cluster Tutorial © OSSCube
Split Brain
Servers can not see each other
I have full responsibility of the cluster
I have full responsibility of the cluster
MySQL Cluster Tutorial,© OSSCube
Arbitration
First Node to ask will continue while the other will be shut down
Network Split
NG 1
MySQL Cluster Tutorial, © OSSCube
More Data Nodes
NG 1
NG 2
Network Split/ Shutdown
MySQL Cluster Tutorial, © OSSCube
Normal Operation
I am
Aliv
e
I am
Aliv
eI am
Alive
I am Aliv
e
MySQL Cluster Tutorial,© OSSCube
Missed Heartbeats
I am
Aliv
e
I am
Alive
I am
A
live
MySQL Cluster Tutorial, © OSSCube
Phase One: Commit-Request
Commit
Message
Commit
Message
Response
Message
Response
Message
MySQL Cluster Tutorial, © OSSCube
Phase Two: Successful Commit
Transaction
Successful
Transaction
Successful
Commit
Transaction
Commit
Transaction
MySQL Cluster Tutorial, © OSSCube
Phase Two: Failure (Abort Commit)
Transaction
Successful
Transaction
Failed
Rollback
Transaction
Rollback
Transaction
MySQL Cluster Tutorial, © OSSCube
INSTALL MySQL CLUSTERINSTALL 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= 2DataDir= /var/lib/MySQL-cluster
• [NDB_MGMD]Hostname= ndb_mgmd.MySQL.comDataDir= /var/lib/MySQL-cluster
• [NDBD]HostName= ndbd_2.MySQL.com
• [NDBD]HostName=ndbd_3.MySQL.com
• [MySQLD]• [MySQLD]
HostName= MySQLd_5.MySQL.comMySQL Cluster Tutorial, © OSSCube
Data Nodes
• my.cnf# example additions to my.cnf for MySQL Cluster
• [ndbd]connect-string=ndb_mgmd.MySQL.com
MySQL Cluster Tutorial, © OSSCube
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, © OSSCube
Config.ini ParametersGeneral Parameters
– Id– Hostname
File and Directory Location Parameter– BackupDataDir– DataDir– FileSystemPath
MySQL Cluster Tutorial, © OSSCube
MySQL Cluster Tutorial, © OSSCube
•Transaction Handling Parameters•TransactionInactiveTimeout•TransactionDeadlockDetectionTimeout
•Memory Usage Parameters•LockPagesInMainMemory
•Utility Programs•ndb_size.pl
MySQL Cluster Tutorial, © OSSCube
MySQL Cluster Storage Engine --MySQL Cluster Storage Engine --
NDBNDB
NDB’s Storage Method
• In-Memory Storage
• Disk-Based Storage– New in MySQL 5.1– Undo Logging– Fixed-Width Columns
MySQL Cluster Tutorial, © OSSCube
Tablespace Tablespace
Log File group
DatafileDatafile Datafile Datafile
Redo log Undo Log
MySQL Cluster Tutorial, © OSSCube
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 ....
Code Name ContinentBRA Brazil South AmericaCHE Switzerland EuropeCHN China AsiaESP Spain EuropeFIN Finland EuropeGOR United KingdomEuropeIND India AsiaMOR Morocco AfricaSKO South Korea Asia
Data Node Data Node
Country Table: Unique Constraint on Name and Ordered Index on Continent
MySQL Cluster Tutorial, © OSSCube
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
HiddenField
Name_Hash Name Code17832 Morocco MOR24556 Brazil BRA27432 Spain ESP28900 United Kingdom GOR31234 China CHN
HiddenTable
Code_Hash Code Name Continent1351 CHE Switzerland Europe1785 CHN China Asia1943 FIN Finland Europe2031 GOR United Kingdom Europe2345 SKO South Korea Asia
Code_Hash Code Name Continent1351 CHE Switzerland Europe1785 CHN China Asia1943 FIN Finland Europe2031 GOR United Kingdom Europe2345 SKO South Korea Asia
OrderedIndex
MySQL Cluster Tutorial, © OSSCube
Second Node/Second Fragment• Same as First Node/Fragment setup• Contains the remaining data
Code Hash Code Name Continent1231 BRA Brazil South America1853 ESP Spain Europe2145 IND India Asia2197 MOR Morocco Africa
Code Hash Code Name Continent1231 BRA Brazil South America1853 ESP Spain Europe2145 IND India Asia2197 MOR Morocco Africa
HiddenField
Name_Hash Name Code17832 Finland FIN24556 South Korea SKO27432 Switzerland CHE28974 India IND
HiddenTable
OrderedIndex
MySQL Cluster Tutorial, © OSSCube
Primary Key Lookup
PK values Function()Hash Data nodeData node
Look up row
Data nodeData node
MySQL Server
Unique Key Lookup
Look up row
Unique Key Lookup may require another network hop
MySQL Cluster Tutorial, © OSSCube
Parallel Ordered Index Scan
Data nodeData node
Data nodeData nodeMySQL Server
rows
T- Tree Index
T- Tree Index
T- Tree Index
T- Tree Index
MySQL Cluster Tutorial, © OSSCube
Parallel full table scan
Data nodeData node
Data nodeData nodeMySQL Server
rows
MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube
Parallel full table scan ( WHERE condition processed in SQL
node)
Data nodeData node
Data nodeData node
MySQL Server
rows
SQL Query
Many RowsMany RowsFew RowsFew Rows
Where Condition
MySQL Cluster Tutorial, © OSSCube
Parallel full table scan ( WHERE condition
processed in data node)
Data nodeData node
Data nodeData node
MySQL Server
rows
SQL Query
Few RowsFew RowsFew RowsFew Rows
Where Condition
Where Condition
MySQL Cluster Tutorial, © OSSCube
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, © OSSCube
Memory - RAM
• Memory AllocationTotal 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
Binary(15)1 Byte
Padded
Binary(14)2 BytesPadded
Binary(14)2 BytesPadded
MySQL Cluster Tutorial, © OSSCube
Memory Pages Table BTable BTable A
DataMemoryMemory Page
Table A(up to 32Kb)
IndexMemoryMemory PageTable A
(up to 8Kb)
IndexMemoryMemory Page
Table B(up to 8Kb)
DataMemoryMemory Page
Table B(up to 32Kb)
MySQL Cluster Tutorial, © OSSCube
MySQL Cluster Tutorial, © OSSCube
HANDLING DATA IN MySQL HANDLING DATA IN MySQL
CLUSTERCLUSTER
Disk Data Objects
Log File Group
(Only One per Server)
Table Space
UndoFile
UndoFile
DataFile
DataFile
DataFile
Table Space
DataFile
DataFile
Disk Data Table
Disk Data Table
Disk Data Table
MySQL Cluster Tutorial, © OSSCube
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 GroupALTER LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_2.dat' INITIAL_SIZE 12M ENGINE NDB;
MySQL Cluster Tutorial, © OSSCube
Verifying UNDO Files are Created
• INFORMATION_SCHEMA TablesSELECT 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, © OSSCube
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 FileALTER TABLESPACE ts_1 ADD DATAFILE 'data_2.dat' INITIAL_SIZE 48M ENGINE NDB;
• Verifying Data Files are CreatedSELECT FILE_NAME, LOGFILE_GROUP_NAME, EXTRAFROM INFORMATION_SCHEMA.FILESWHERE TABLESPACE_NAME = 'ts_1' AND FILE_TYPE = 'DATAFILE';
MySQL Cluster Tutorial, © OSSCube
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 TablesALTER TABLE city TABLESPACE ts1 STORAGE DISK ENGINE=NDB;
• View Table StatusSHOW TABLE STATUS LIKE 'city'\G
MySQL Cluster Tutorial, © OSSCube
Monitoring Free Space
MySQL Cluster Tutorial, © OSSCube
Dropping Cluster Tables
MySQL Cluster Tutorial, © OSSCube
MySQL CLUSTER MySQL CLUSTER
MANAGEMENTMANAGEMENT
MySQL Cluster’s Native Backup Tool
• Backup Data– .ctl– .log– .data
• Abort Backupndb_mgm> ABORT BACKUP 6Abort of backup 6 orderedNode 2: Backup 10 started from 1 has been aborted. Error: 1321
Data Node
Data Node
Data Node
Data Node
Backup-1.2.ctlBackup-1.2.logBackup-1.0.2.DataBackup-1.1.2.Data
Backup-1.3.ctlBackup-1.3.logBackup-1.0.3.DataBackup-1.1.3.Data
MySQL Cluster Tutorial, © OSSCube
MySQLdump
MySQL Cluster Tutorial, © OSSCube
Restoring Backups• Recovering MySQL Cluster's Native Backup Files (1/3)
– Verify Management Node is Runningshell> ndb_mgm -e "SHOW"
– Start and Empty the Data Nodes• Start each data node with the --initial option.
MySQL Cluster Tutorial, © OSSCube
Restoring Backups
MySQL Cluster Tutorial, © OSSCube
Restoring Backups
MySQL Cluster Tutorial, © OSSCube
Restoring MySQLdump Files
MySQL Cluster Tutorial, © OSSCube
OptimizationOptimization
EXPLAIN
MySQL Cluster Tutorial, © OSSCube
Full Table Scan
• Without Condition Pushdown• With Condition Pushdown
MySQL Cluster Tutorial, © OSSCube
Parallel full table scan ( WHERE condition
processed in SQL node)
Data node
Data nodeMySQL Server
rows
SQL Query
Many RowsFew Rows
Where Condition
MySQL Cluster Tutorial, © OSSCube
Parallel full table scan ( WHERE condition processed in data
node)
Data node
Data nodeMySQL Server
rows
SQL Query
Few Rows
Where Condition
Where Condition
Few Rows
MySQL Cluster Tutorial, © OSSCube
Index Statistics
MySQL Cluster Tutorial, © OSSCube
MySQL Cluster Query Cache
MySQL Cluster Tutorial, © OSSCube
Data Size/Usage Issues
MySQL Cluster Tutorial, © OSSCube
Query Design
MySQL Cluster Tutorial, © OSSCube
MySQL Cluster ReplicationMySQL Cluster Replication
MySQL ServerMaster
MySQL ServerSlave
MySQL ServerSlave
IO threadSQL thread
Binlog
Relaylog
Relaylog Binlog
Application W
MySQL Replication
MySQL Cluster Tutorial, © OSSCube
Cluster
Data node Data node
Data node Data node
mysqld mysqld
Update
Update
Ndb API Ndb API
Update
Update
MySQL Cluster Tutorial, © OSSCube
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
MySQL Cluster Tutorial, © OSSCube
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
MySQL Cluster Tutorial, © OSSCube
Q n A
Thank you for your time and attention
For more information, please feel free to drop in a line to [email protected] or visit http://www.osscube.com
Or call us at 1-888-9OSSCube (Toll Free for USA)1-919-791-5472 (From outside USA)
MySQL Cluster Tutorial, © OSSCube