Notes from MySQL Cluster

Here is the quick notes from MySQL Cluster by Stewart Smith session that I attended today in the MySQL conference 2008.

• What it is
  • Clustering of in memory databases in a shared-nothing system
  • Designed for HA, 99.9% Uptime (not really) and sub-second failover
  • Supports Hot (online) consistent backup along with compression
  • No locks are used during the backup as NDB uses a global counters
• Think as virtual file system
• Redundancy
  • NoOfReplicas (1,2,3,4), For production use 1 or 2 but don’t use any other as it may have bugs (2 is preferred and well tested mode)
  • 1 means no redundancy (a node fails and cluster fails)
  • 2 means two copies
• High performance (due to parallelism)
• In memory only
  • Both data and index needs to fit in memory
  • 5.1 optionally supports to have non-index stuff to disk
  • Rows will have in-memory part and disk part
• Durability
  • Checkpoint to disk (frequency is configurable, default 2 secs?)
  • Rate is limited to avoid IO
  • Cluster will restore from last known Global Check Point (GCP)
  • Any comitted transactions that are not part of checkpoint will be lost
• Node Types
  1. Data Nodes (ndbd)
     • Grouped into nodegroups
     • NoOfReplicas is number of nodes in nodegroup
     • Up2 48 in one cluster
     • Interact using regular SQL or C++ NDBAPI or using other langauge binders
     • For each transaction, one is used in Transaction Coordinator and load balanced around the cluster
     • Node failure, application has to retry the transaction
     • All MySQL servers connected to server have the same view of the database
     • Replication between data nodes is synchrounous as opposed to standard asynchrounous MySQL replication, uses two-phase commit
     • ndbd is single threaded (thats why it is not CPU bound), multithreading coming soon
     • Compressed backups, checkpoint might use other CPUs
     • Multiple nodes for SMP
     • Every table should have a Primary key, else 64-bit int will be internally used (same as InnoDB)
     • Uses hashkey(PK) to find the right partition
  2. Management Server (ndb_mgmd)
     • Interact with management client (ndb_mgm) or C management API (mgmapi)
     • distributes configuration to other nodes
     • Backup is started by management server
     • Can have more than one for redundancy
     • Stopping management server will not break the existing cluster
     • It has to be running when nodes join the cluster
     • Backing up of config.ini is very much recommended
     • Issues any commands to nodes (start, stop, restart)
  3. SQL Node (mysqld)
     • MySQL Server
     • Many API/SQL nodes are needed to load storage nodes
     • dependent on application and types of queries
     • Is multi-threaded, takes advantage of SMP
• Collection of nodes constitute Cluster
• Requirements
  • Node is a process not a physical computer
  • Atleast 3 physical machines needed for HA
    • Avoid split brain problem (network down, node down etc)
    • In case of split brain problem, nodes will be shutdown forecefully and all comitted transacations will be lost and will be restored only to last known checkpoint.
    • With only two, its hard to know if it has relly failed or network connection has failed
    • You probably need more than 3 machines though
  • ndbd is single threaded, and future version might be multi-threaded
  • SQL node is multi-threaded (ofcourse)
  • Management server is not CPU intensive (almost 0 CPU)
  • Data Nodes need lots of memory, Disk I/O can be calculated and generally not CPU bound (as its single threaded)
  • Memory needed for Index and Data can be calculated using a tool provided (ndb_size.pl)
• Setup
  • Configure the nodes
  • Management server needs to be started first (ndbm_mgmd -f config.ini)
  • Data Nodes
    • on each storage node ndbd -c IP
    • Can have more than one on the same physical box
  • SQL Nodes
    • Make sure NDBCLUSTER option is enabled (command line or config)
    • Make sure the connect string is specified (command line or config)
    • start the server
  • Monitor (using ndb_mgb -c localhost:9350 -e ‘show’)
• Using the Cluster
  • Tables can be stored in cluster with ENGINE=NDBCLUSTER
  • Supports views, stored procedur, triggers etc
  • Permissions needs to exists in each SQL Node (mysql.user*)
• Limitations:
  • prior to 5.1; everything in memory (both index and data)
  • 5.1, non-index can be to disk
  • Foreignkey constraints are not yet supported
  • maximum of 255 nodes can belong to a single MySQL Cluster with 48 of being data nodes
  • No dynamic expansion and needs to be restarted to introduce new nodes in the cluster
  • ndbd is single threaded