OSSCube MySQL Cluster Tutorial By Sonali At Osspac 09

Notes:	Hide Notes

Slide 1: www.osscube.com MySQL Cluster Tutorial Pre s e n te d b y – So n a li M o c h a in Slide 2: www.osscube.com Wh o Am I ? MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 3: www.osscube.com Architecture and Organization Slide 4: www.osscube.com What is a Cluster? MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 5: www.osscube.com Single MySQL Server Architecture SQL Re q u e s t M ySQL Clie n t Re s p o n s e M ySQL Se rv e r Slide 6: www.osscube.com MySQL Cluster Architecture NDB API Ca ll SQL Re q u e s t M ySQL Clie n t Re s p o n s e Pa rtitio n in g Re p lic a tio n Mssage e Re s p o n s e SQL No d e (M ySQL s e rv e r) Da ta No d e s Slide 7: www.osscube.com Co m p o n e n ts o f M ySQL Clus te r MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 8: www.osscube.com Various Nodes API No d e Da ta No d e Ap p lic a tio n M n a g e m e n t No d e a MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 9: www.osscube.com NdbCluster Engine NdbCluster Engine NdbCluster Engine (Data Nodes) MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 10: www.osscube.com Org a n iza tio n MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 11: www.osscube.com Partition MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 12: www.osscube.com Partitioning Horizontal Partitioning Vertical Partitioning MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 13: www.osscube.com Fragments MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 14: www.osscube.com Node Groups MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 15: www.osscube.com Split Brain I have full responsibility of the cluster Servers can not see each other I have full responsibility of the cluster MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 16: www.osscube.com Arbitration NG 1 Network Split Firs t No d e to a s k w ill c o n tin u e w h ile th e o th e r w ill b e s h u t do w n MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 17: www.osscube.com More Data Nodes NG 1 N G 2 Network Split/ Shutdown MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 18: www.osscube.com Arbitrator Issues MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 19: www.osscube.com Arbitrator Issues MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 20: www.osscube.com Normal Operation m I a ive Al MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Ia Al m ive Ia Am liv e Ia Al m ive Slide 21: www.osscube.com Missed Heartbeats m I a ive Al I am Alive MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Ia Al m ive Slide 22: www.osscube.com Synchronous MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 23: www.osscube.com Phase One: CommitRequest Commit Message Commit Message Response Message Response Message MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 24: www.osscube.com Phase Two: Successful Commit Transaction Successful Transaction Successful Commit Transaction Commit Transaction MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 25: Phase Two: Failure (Abort Commit) www.osscube.com Transaction Failed Transaction Successful Rollback Transaction Rollback Transaction MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 26: www.osscube.com INSTALL MySQL CLUSTER Slide 27: www.osscube.com 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 • • • • • Slide 28: www.osscube.com Management Node MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 29: www.osscube.com Data Nodes • my.cnf# example additions to my.cnf for MySQL Cluster • [ndbd] connectstring=ndb_mgmd.MySQL.com MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 30: www.osscube.com Data Node Configurations Options MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 31: www.osscube.com 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 MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube :1186 Slide 32: www.osscube.com Config.ini Parameters Ge n e ra l Pa ra m e te rs –I d – Ho s tn a m e File a n d Dire c to ry Lo c a tio n Pa ra m e te r – Ba c k u p Da ta Dir – Da ta Dir – File Sys te m Pa th MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 33: www.osscube.com Slide 34: www.osscube.com •Tra n s a c tio n Ha n d lin g Pa ra m e te rs •Tra n s a c tio n I a c tiv e Tim e o u t n •Tra n s a c tio n De a d lo c k De te c tio n Tim e o ut •M m o ry U a g e Pa ra m e te rs e s •Lo c k Pa g e s I M in M m o ry na e •U tility Pro g ra m s •n d b _s ize .p l Slide 35: www.osscube.com Monitoring the Startup Phases MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube. http://osscube.com/technology/mysql Slide 36: www.osscube.com MySQL Cluster Storage Engine --NDB MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 37: www.osscube.com NDB Sto ra g e En g in e MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 38: www.osscube.com NDB’s Storage Method • In-Memory S torage • Dis k-B as ed S torag e – Ne w in M ySQL 5 .1 – U d o Lo g g in g n – Fixe d -Wid th Co lu m n s MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 39: www.osscube.com Tablespace Da ta file Tablespace Da ta file Da ta file Da ta file Log File group n Re d o lo gU d o Lo g Slide 40: www.osscube.com Indexes MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 41: www.osscube.com Creating Indexes • • Ea c h ta b le in th e c lu s te r a lw a ys h a s a h a s h in d e x fo r a Prim a ry K y e Ord e re d in d e xe s c a n b e c re a te d w ith ALTER TABLE ADD I NDEX.... Country Table: Unique Constraint on Name and Ordered Index on Continent Code BRA CHE CHN ESP FIN GOR IND MOR SKO Name Continent Brazil South America Switzerland Europe China Asia Spain Europe Finland Europe United KingdomEurope India Asia Morocco Africa South Korea Asia Data Node Data Node MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 42: www.osscube.com First Node/First Fragment • • Hid d e n fie ld /ta b le c re a te d th a t w ill h a n d le th e m a n a g e m e n t o f th e U iq u e I d e x o n Na m e n n Ord e re d I d e x (T-Tre e ) c re a te d fo r Co n tin e n t fie ld n Code_Hash 1351 1785 1943 2031 2345 Code CHE CHN FIN GOR SKO Name Switzerland China Finland United Kingdom South Korea Continent Europe Asia Europe Europe Asia Hidden Field Ordered Index Name_Hash 17832 24556 27432 28900 31234 Name Morocco Brazil Spain United Kingdom China Code MOR BRA ESP GOR CHN Hidden Table MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 43: www.osscube.com Second Node/Second Fragment • • Sa m e a s Firs t No d e /Fra g m e n t s e tu p Co n ta in s th e re m a in in g d a ta Code Hash 1231 1853 2145 2197 Code BRA ESP IND MOR Name Brazil Spain India Morocco Continent South America Europe Asia Africa Hidden Field Ordered Index Name_Hash 17832 24556 27432 28974 Name Finland South Korea Switzerland India Code FIN SKO CHE IND Hidden Table MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 44: www.osscube.com Unique Key Lookup Primary Key Lookup may UniqueKey Lookup network hop require another PK values Function() Hash Look up row Da ta n o d e Look up row Da ta n o d e MySQL Server MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 45: www.osscube.com Parallel Ordered Index Scan TTre e I de n x Da ta n o d e rows MySQL Server Da ta n o d e TTre e I de n x MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 46: www.osscube.com Parallel full table scan Da ta n o d e rows MySQL Server Da ta n o d e MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 47: www.osscube.com Parallel full table scan ( WHERE condition processed in SQL node) SQL Query Da ta n o d e Fe w Ro w s Mn a rows y Ro w s MySQL Server Where Conditi on Da ta n o d e MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 48: www.osscube.com Parallel full table scan ( WHERE condition processed in data node) SQL Query Fe w Ro w s Where Conditi Fe wrows s on Ro w Da ta n o d e MySQL Server Where Conditi on Da ta n o d e MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 49: www.osscube.com Storage of Indexes MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 50: www.osscube.com Characteristics of NDB storage Engine MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 51: www.osscube.com Durability MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 52: www.osscube.com Global Check Point • A GCP o c c u rs e v e ry fe w s e c o n d s , w h e n tra n s a c tio n s fo r a ll n o d e s a re s yn c h ro n ize d a n d th e REDO lo g is flu s h e d to d is k MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 53: www.osscube.com Local Check Point MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 54: www.osscube.com Memory - RAM • M m o ry Allo c a tio n e Total Memory = N(da ta nodes )* Local Memory / NoOfReplica s -- or -Loca l Memory = Tota l Memory * NoOfReplica s / N(data nodes ) Sizin g u p th e M m o ry e Binary(15) 1 Byte Padded • – Fixe d s ize in m e m o ry Binary(14) – Va ria b le s ize d in m e m o ry – Fixe d s ize o n d is k MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Binary(14) 2 Bytes Padded 2 Bytes Padded Slide 55: www.osscube.com Memory Pages Table A IndexMemory Memory Page Table A (up to 8Kb) Table B DataMemory Memory Page Table A (up to 32Kb) IndexMemory Memory Page Table B (up to 8Kb) MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube DataMemory Memory Page Table B (up to 32Kb) Slide 56: www.osscube.com MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 57: www.osscube.com HANDLING DATA IN MySQL CLUSTER Slide 58: Disk Data Objects www.osscube.com Table Space Table Space Data File Data File Data File Log File Group (Only One per Server) Data File Data File Undo File Undo File Disk Data Table MySQL Cluster Tutorial, OSSPAC 09 Disk Data Disk Data Singapore, © OSSCube Table Table Slide 59: www.osscube.com Creating the Log File Group • Cre a te Lo g File Gro u p CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE 2M ENGINE NDB; • Ad d in g U d o File s to Lo g File Gro u p n ALTER LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_2.dat' INITIAL_SIZE 12M ENGINE NDB; MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 60: www.osscube.com Verifying UNDO Files are Created •I NFORM ON_SCHEM Ta b le s ATI A 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 Slide 61: www.osscube.com Creating the Tablespace • Cre a te a Ta b le s p a c e CREATE TABLESPACE ts_1 ADD DATAFILE 'data_1.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 32M ENGINE NDB; ALTER TABLESPACE ts_1 ADD DATAFILE 'data_2.dat' INITIAL_SIZE 48M ENGINE NDB; 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 • Ad d a n Ad d itio n a l Da ta File • Ve rifyin g Da ta File s a re Cre a te d Slide 62: www.osscube.com Create a Disk Data Table • No n -I d e xe d Co lu m n s Sto re d o n Dis k in Ta b le s p a c e n 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; • Alte r Exis tin g No n -Clu s te r Ta b le s ALTER TABLE city TABLESPACE ts1 STORAGE DISK ENGINE=NDB; • Vie w Ta b le Sta tu s SHOW TABLE STATUS LIKE 'city'\G MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 63: www.osscube.com Monitoring Free Space MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 64: www.osscube.com Dropping Cluster Tables MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 65: www.osscube.com MySQL CLUSTER MANAGEMENT Slide 66: MySQL Cluster’s Native Backup Tool • Ba c k u p Da ta – .ctl – .log – .data Backup-1.2.ctl Backup-1.2.log Backup-1.0.2.Data Backup-1.1.2.Data www.osscube.com Data Node Data Node • Ab o rt Ba c k u p Data Data ndb_mgm> ABORT BACKUP 6 Node Node Abort of backup 6 ordered Node 2: Backup 10 started from 1 has been aborted. Error: 1321 Backup-1.3.ctl Backup-1.3.log Backup-1.0.3.Data Backup-1.1.3.Data MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 67: www.osscube.com MySQLdump MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 68: www.osscube.com Restoring Backups • Re c o v e rin g M ySQL Clu s te r's Na tiv e Ba c k u p File s (1 /3 ) – Ve rify M n a g e m e n t No d e is Ru n n in g a shell> ndb_mgm -e "SHOW" – Sta rt a n d Em p ty th e Da ta No d e s • Sta rt e a c h d a ta n o d e w ith th e --initial o p tio n . MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 69: www.osscube.com Restoring Backups MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 70: www.osscube.com Restoring Backups MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 71: www.osscube.com Restoring MySQLdump Files MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 72: www.osscube.com Optimization Slide 73: www.osscube.com EXPLAIN MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 74: www.osscube.com Full Table Scan • With o u t Co n d itio n Pu s h d o w n • With Co n d itio n Pu s h d o w n MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 75: www.osscube.com Parallel full table scan ( WHERE condition processed in SQL node) SQL Query Few Rows Where Condition Data node Many rows Rows MySQL Server Data node MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 76: www.osscube.com Parallel full table scan ( WHERE condition processed in data node) SQL Query Few Rows Where Condition Data node Few Rows rows MySQL Server Where Data Condition node MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 77: www.osscube.com Index Statistics MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 78: www.osscube.com MySQL Cluster Query Cache MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 79: www.osscube.com Data Size/Usage Issues MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 80: www.osscube.com Query Design MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 81: www.osscube.com MySQL Cluster Replication Slide 82: www.osscube.com MySQL Replication MySQL Server Slave Application W MySQL Server Master Relaylog IO thread SQL thread Binlog MySQL Server Slave Relaylog Binlog Slide 83: www.osscube.com MySQLd Update MySQLd Data node Cluster Data node Data node Data node Update Update Update Ndb API Ndb API Slide 84: www.osscube.com NDB Injector Thread •A th re a d in s id e M ySQL s e rv e r •Re s p o n s ib le fo r in je c tin g ro w s in b in lo g a n d p ro d u c e s “Sin g le c o n n ic a l b in lo g fo r c lu s te r” • not just one MySQL Server •It contains everything written on all ndbApi program including MySQLd connected to cluster Slide 85: www.osscube.com MySQL Cluster Replication MySQLd(slave) MySQLd(Master) Replication Binlog NdbClsuter Handler Relaylog NdbClsuter Handler IO thread Binlog Data node Cluster Data node Data node Data node Apply thread Data node Cluster Data node Data node Data node Slide 86: www.osscube.com Th a n k yo u fo r yo u r tim e a n d a tte n tio n www.osscube.com For more information, please feel free to drop in a line to sonali@osscube.com or visit http://www.osscube.com MySQL Cluster Tutorial, OSSPAC 09 Singapore, © OSSCube Slide 87: www.osscube.com QnA