Mysql Cluster Guide

Permalink: http://osc.co.cr/mysql-cluster-guide/

What is mysql cluster?

Web definitions
  • MySQL Cluster is a technology which provides shared-nothing clustering capabilities for the MySQL database management system. It was first included in the production release of MySQL 4.1 in November 2004. ... en.wikipedia.org/wiki/MySQL_Cluster
 

[caption id="attachment_2192" align="aligncenter" width="300"]mysql cluster flow mysql cluster flow[/caption]

This guide is based in that you already know about mysql cluster.. at least about how it works.. so i will give you some tips about mysql cluster... very usefull tips..

1.) How to convert an existent schema to mysql cluster

For example how to convert innodb schema to mysql cluster schema , the schema of mysql cluster its called NBCLUSTER

Importing schema is so simple like change InnoDB engine to NDBCLUSTER engine.
cat schema.james.sql | sed 's/InnoDB/NDBCLUSTER/gi' > ndb_schema.james.sql #migration of DDL is so simple like change engine of tables to NDBCLUSTER eng.
cat schema.jamesjara.sql | sed 's/InnoDB/NDBCLUSTER/gi' > ndb_schema.jamesjara.sql

mysql -u root -e 'create database james'
mysql -u root -e 'create database jamesjara'
mysql -u root james < ndb_schema.james.sql
mysql -u root jamesjara < ndb_schema.jamesjara.sql

2) Scaling across multiple machines with mysql cluster

First , I recommend to read this links to understand data distribution at 100%
The data and indexes must fit into memory. Each cluster has node groups. Each node group holds a fragment of the data, and each node group has a number of replicas. So a cluster with 2 node groups with 4 replicas each has the data split in half, with 4 machines redundantly storing one half the data, and another 4 redundantly storing the other half. Therefore, the data doesn’t have to entirely fit into RAM, but it does have to fit in (RAM of 1 node)*(# node groups).

(from the blog)Tables are 'horizontally fragmented' into table fragments each containing a disjoint subset of the rows of the table. The union of rows in all table fragments is the set of rows in the table. Rows are always identified by their primary key. Tables with no primary key are given a hidden primary key by MySQLD.

3) How to get the Database status of a mysql cluster ecosystem

DATABASE STATUS
  • TABLES MEMORY : SHOW TABLE STATUS LIKE '%'; --IN MYSQLD
  • NODES MEMORY : ndb_mgm -e'ALL REPORT MEMORYUSAGE' # in mgm
To get the EXACTLY status of the mysql cluster tables we use:
SELECT TABLE_NAME,ROW_FORMAT,TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH,MAX_DATA_LENGTH, INDEX_LENGTH,DATA_FREE, AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = DATABASE();
Also With the comando ALL REPORT MEMORYUSAGE, we can check how is the data spreaded.

4) Benchmarking in mysql cluster

To execute perfoming test or other kind of test is simple like:

10 threads and each thread loops 10000 times. 1000 Rows in table visitor:
Command: bencher -s/var/lib/mysql/mysql.sock -t10 -l10000 -d visitor -q "select * from visitor"

Or more comples:

10 threads and each thread loops 10000 times. 1000 Rows in table visitor:
Command: bencher -s/var/lib/mysql/mysql.sock -t10 -l1000 -d visitor -q "Select c.* from visitor_a c join visitor a on c.count_request = a.count_request join visitor_accesso b on c.count_requested = b.request_dura_secs where a.count_reest between 0 and 15000 and b.http between 777 and 7777"

Well this its all.. This are only 5 topics, but there is more.. in my next post i will talk about :

  • auto-sharding , rebalance, internal programns of mysql cluster, nice & usefull commands and others..
thanks for the read,

@JamesJara

 

 

 

 

 

 

 

 

 

0 pensamientos:

Post a Comment

feedback!