MySQL 5.1 Cluster Testing

Hi,

It has been a while since I last posted. We have been busy testing MySQL Cluster, MySQL Replication and MySQL Cluster Replication. Good news is the serious bugs are getting harder to find. This is allowing us to spend some time automating more testing like configuration and up/down grade testing for example.

The documentation guy for cluster, Jonathan "Jon" Stephens, has been doing great work to expand the Cluster on-line manual. If you have not been out there in a while to review you should have a look.

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html

I feel very positive about this version of the cluster as it has had more hands on testing then ever before. The testing includes: Functional, Negative, Load/Stress, Performance, Recovery, Network Failure, etc...

The new Disk Data will allow customer with huge database to start taking advantage of the reliability and scalability that the cluster offers.

In addition the Cluster Replication opens a whole new use for cluster. This feature adds a few more 9's to the 99.999 up time for services and allows for many different strategies in database design to take place.

One example would be to replicate to another cluster so that data mining and report generation can be done with out putting a load on the main system.

In addition, you could replicate to another site so that if something goes wrong at the main site the secondary site could take over minimizing the companies down time.

I would love to here from the users out there in the world that are using the cluster and have ideas of test they would like to see done, and or want to share how they are using it so we can incorporate that into our testing.

I will attempt to post more often and keep you apprised of our efforts and share any tips or trick I run across that might run across.

Cheers!

/ Jeb

Jonathan Miller
Austin, Texas USA
Senior Lead Quality Assurance Developer
MySQL AB www.mysql.com

Jumpstart your cluster!
http://www.mysql.com/consulting/packaged/cluster.html

Get training on clusters
http://www.mysql.com/training/courses/mysql_cluster.html

All-in-one Enterprise-grade Database, Support and Services
http://www.mysql.com/network/

MySQL Cluster can be too much typing

Testing the MySQL Cluster can mean a lot of typing. Not being the best typist and getting tired of typing the same thing over and over again I created a short cut to make life better.

I have a file called shortcut.sh. Inside my .bash_profile for each of my test systems, I have added this file to be run when I login.

.bash_profile example

. /home/ndb/jmiller/shortcut.sh

Then all the command and connection strings I need are just few key strokes away.

shortcut.sh

alias ndbd='/home/ndb/jmiller/builds/libexec/ndbd'

alias ndb_mgmd='/home/ndb/jmiller/builds/libexec/ndb_mgmd'

alias ndb_mgm='/home/ndb/jmiller/builds/bin/ndb_mgm'

alias mysql='/home/ndb/jmiller/builds/bin/mysql'

alias mysql_server='/etc/init.d/mysql.server'

alias ndb_restore='/home/ndb/jmiller/builds/bin/ndb_restore'

alias mysqldump='/home/ndb/jmiller/builds/bin/mysqldump'

alias installdb='/home/ndb/jmiller/builds/bin/mysql_install_db'

export NDB_CONNECTSTRING=n10:14000

alias mysqladmin='/home/ndb/jmiller/builds/bin/mysqladmin'

dbvar="/space/var"

ndbrun="/space/run"

jmiller="/home/ndb/jmiller"

Before doing this, to start a cluster I would have to do the following:

(short example)

Either cd to

$> /home/ndb/jmiller/builds/libexec/

or

type

$> /home/ndb/jmiller/builds/libexec/ndb_mgmd -f /path/to/config/file

and then

$>/home/ndb/jmiller/builds/libexec/ndbd -c n10:14000 --initial

as you can see this would get to be tiring.

By adding the shortcut, I can just do

$>ndb_mgmd -f /path/to/config/file

$> ndbd --initial

Note that since I exported my NDB_CONNECTSTRING I do not need to include the -c option when starting the ndbd. In addition, I can now use ndb_mgm (mgt client) any where on the host and I do not need to include the connect string.

example:

$>ndb_mgm -e show

$>ndb_mgm -e "all status"

$>ndb_mgm -e shutdown

In addition I created in the file above some shortcuts to directories that I often need to go to.

Example:

cd /space/var

Or

cd $db

It only saved 7 key strokes, but in a day of typing that can really add up.

In short, we are supposed to work smarter, not harder. Hope this helps for those using MySQL Cluster

MySQL Cluster Replication

MySQL QA

In the 5.1 release MySQL Cluster will support replication to another MySQL installation. The replication is handled using Row Bases Replication instead of Statement Based Replication.

I have been testing it for about a year now and it has gotten really hard for me to break at this point, but I am still trying.

So what is so cool about Cluster Replication? Good question!

One answer:

Cluster Replication really gives a company five 9's (99.999%) in up time. You can have a Master Cluster in the main site supporting the business and have another Slave Cluster in a total different site that can be used as a backup for times there are issues with main site. In addition, the Slave Cluster can also be used for reporting or data mining to take that traffic off of the main Cluster.

How hard is it to setup? Another great question.

Answer: Easy

So to set it up, you would create two set of Clusters configured the way that you wanted them. You configure the Master MySQLD to create a bin log and to connect to the Master Cluster. You configure the Slave MySQLD to read from that Master MySQLD and to connect to the Slave Cluster. That is basically it. Of course you have to grant replication permissions, but I am sure you knew that. Here is are some examples:
******************************************************

Master MySQLD my.cnf:

[mysqld]
server-id=1
log-bin = /space/var/master1
log = /space/var/master1.log
log-error = /space/var/master1.err
socket = /tmp/mysql.sock
port = 3306
pid-file = /space/var/hostname.pid
datadir = /space/var/
language = /usr/mysql/english/
ndbcluster # Use NDB engine
ndb-connectstring=master01:1234 # location of MGM node ndb-connectstring=host:port

Slave Cluster MySQLD my.cnf:
[mysqld]
server-id=8
relay-log=slave-relay-bin
replicate-ignore-db=mysql
master-user=rep
master-connect-retry=1
master-host=master01
master-password=test
master-port=3306
skip-slave-start
datadir=/space/var/
log-warnings
log-error=/space/var/slave.err
ndbcluster
ndb-connectstring=slave01:1234 # location of MGM node
******************************************************

Hey, in your example the slave has an ID of 8, why? You noticed.

Answer:
To really get the most out of the Cluster you need a lot feeding it. You can have more then one MySQLD process feeding the Master Cluster and producing bin logs for that matter.

Well if you have more then one MySQLD feeding the Master Cluster then how does replication work?

Answer: The injector thread.

The Master Cluster is responsible for updating the Bin Log, not the MySQLD process. This is done through the Cluster Bin Log Injector Thread.

Therefore you can have many MySQLD processes feeding the Master Cluster, but have the Slave only reading from one of them and not miss a beat (or data action).

One other questions is why do you have skip-slave-start in you Slave my.cnf? You saw that too, hmmm.

Answer:
Sometimes your Master Cluster will have been up a long time and you are wanting to bring a slave on-line, but you really don't want the slave in catch-up mode for days. So you can start the the MySQLD on the slave telling it to not start replication yet, restore a backup from the Master, run a couple of command, then start the slave and you will catch-up quickly and be in sync.

To read more about this, checkout:

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication.html

If you have ideas for testing, or have different scenarios you would like to see tested, please send them to me. Always looking to improve.

Cheers!

MySQL CLuster Disk Data QA

MySQL QA

Now that we have Disk Data for MySQL Cluster you can have the best of both worlds. You can use memory tables for those needing quick access and updates and Disk Data Files for those that will be too large to keep in memory all the time.

One issue with this first release is not having the ability to spread the data and undo files accross the disk sub system using the config.ini file. This can be a important issue for performance. You really don't want to have everything on one disk. Here I list a work around for moving undo and/or data files off to different drives to help in disk performance that I used during performance testing.

1) Bring up the cluster to include all data nodes
2) Each data node will create its own File System ((e.g. ndb_#_fs) where # == the DN ID)).
3) Under the data node FS create the symbolic links pointing to the other drives:

Examples Single Data Node:
[ndb_2_fs]$ ls
D1 D10 D11 D2 D8 D9 LCP
[ndb_2_fs]$ ln -s /data0/log1/ dn_logs
[ndb_2_fs]$ ln -s /data1/data/ dn_data

We now have 2 symbolic links

12 Jan 31 20:31 dn_data -> /data1/data/
12 Jan 31 20:31 dn_logs -> /data0/log1/

Note: This needs to be done for each and every data node before going to next step:

4) Now do the create statements
CREATE LOGFILE GROUP lg1
ADD UNDOFILE './dn_logs/undofile.dat'
INITIAL_SIZE 150M
UNDO_BUFFER_SIZE = 1M
ENGINE=NDB;

CREATE TABLESPACE ts1
ADD DATAFILE './dn_data/datafile.dat'
USE LOGFILE GROUP TPCB_LOG
INITIAL_SIZE 5M
ENGINE=NDB;

5) Doing the same for multi data nodes running on one host.

Give each data node its own path
[ndbd]
Id: 2
HostName: host13
FileSystemPath: /data0

[ndbd]
Id: 3
HostName: host13
FileSystemPath: /data1

DN ID 2 will create it file system on /data0 and DN ID 3 will create its file system on /data1

we then repeat the steps in steps 2 & 3 placing the files in the desired location using symbolic links, and the step 4 for the actual create.

You can then make sure the above worked correctly:

[dn_data]$ cd /data1/data/
[data]$ ls -l
total 2099304
-rw-rw-r-- 1 user group 2147581952 Jan 28 19:58 datafile.dat


Best wishes,
/JBM

MySQL 5.1

MySQL QA

Testing 5.1 MySQL CLuster is moving along. We now have more automated test cases, have been using TPC-B and DBT2 in testing, conducted load and HA testing.

The great news is that many issues are now found early before the customer base has to deal with them.

This relase will be packed full of great stuff for Cluster including the first release of Disk Data and Replication for MySQL Cluster.

The MySQL User Conf is just a few weeks away where much of this will be shown. Hope to see you there.