October 29, 2006

MySQL Releases version 5.0.27

Posted in MySQL CLustering at 2:36 am by captsulu32

You may ave noticed The Documentation has not been updated to reflect the version that they are distributing. Curently Version 5.0.27 is now up for download but the changes have not been placed on there website so I can only talk about what changes have been done in verison 5.0.26 which was released on Oct 3, 2006. Just some of the changes I found that are important this go round:

The parser rejected queries that selected from a table twice using a UNION within a subquery. The parser now supports arbitrary subquery, join, and parenthesis operations within EXISTS subqueries. A limitation still exists for scalar subqueries: If the subquery contains UNION, the first SELECT of the UNION cannot be within parentheses. For example, SELECT (SELECT a FROM t1 UNION SELECT b FROM t2) will work, but SELECT ((SELECT a FROM t1) UNION (SELECT b FROM t2)) will not.

EXPLAIN sometimes returned an incorrect select_type for a SELECT from a view, compared to the select_type for the equivalent SELECT from the base table.

Since the current implementation of LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER is very limited, these statements are deprecated in versions 4.1 of MySQL and above. We will introduce a more advanced technique (called “online backup”) in a future version. That technique will have the additional advantage of working with more storage engines.

You may hav noticed they actually gave up on making this feature work correctly in the 5.0 series in favor of mysqlhotcopy.

For MySQL 5.1 and earlier, the recommended alternative solution to using LOAD DATA FROM MASTER or LOAD TABLE FROM MASTERis using mysqldump or mysqlhotcopy. The latter requires Perl and two Perl modules (DBI and DBD:mysql) and works for MyISAM and ARCHIVE tables only. With mysqldump, you can create SQL dumps on the master and pipe (or copy) these to a mysql client on the slave. This has the advantage of working for all storage engines, but can be quite slow, since it works using SELECT.

This statement takes a snapshot of the master and copies it to the slave. It updates the values of MASTER_LOG_FILE and MASTER_LOG_POS so that the slave starts replicating from the correct position. Any table and database exclusion rules specified with the --replicate-*-do-* and --replicate-*-ignore-* options are honored. --replicate-rewrite-db is not taken into account because a user could use this option to set up a non-unique mapping such as --replicate-rewrite-db="db1->db3" and --replicate-rewrite-db="db2->db3", which would confuse the slave when loading tables from the master.

Permalink Leave a Comment

October 22, 2006

MySQL Clustering backing up your Data

Posted in MySQL CLustering at 6:07 pm by captsulu32

MySQL engineers where quite on top of things when they thought about backup and restore of data onto a cluster. When you start a backup the command is issued from the management node. The command can be done in one of two ways.

First, your can issue the start backup command from within the ndb_mgm program by simply issuing the command:

start backup

This usually this takes time based on the size of the database. What the management node does is talk to the storage nodes and asks which one has the master at that time.  Once that is determined it slices the database into the number of storage nodes.  For example 4 storage nodes 4 slices are created and storage is on each of the nodes. If one of the nodes is not responding at the time of the backup the nodes that would normally be carrying a duplicate of each other will handle 2 of the slices. This is determined by the config.ini file.

Numberofreplica=2

If you use the default location that mysql recommends you will see that a new directory is made each time that the start backup command is issued. I used /var/lib/mysql-cluster in my config.ini file so when issuing this command you can change to this directory and see that it creates the following:

/var/lib/mysql-cluster/BACKUP/BACKUP-##/

##-replacing with the incremented number of the backup. Each time that you initially start the cluster this number is reset to 1.  For example if this is the 12th time you have backed up the cluster you will find three files at this location on each of the storage nodes. Remembering that each storage node will save a slice of the entire database each time that you create a backup.  The files are located at

 /var/lib/mysql-cluster/BACKUP/BACKUP-12/  

The three files that you will find are named in a similar fashion. One for data, one for pointers and the third for a counter so when you restore the data it knows which slice of the database you are reloading.

The second way to start the backup is from the command line. This command can be placed in the /etc/cron.daily/ directory on a redhat machine to get the database to automatically back itself up each night. Usually files in this directory start around 4am each day.

ndb_mgm -e “start backup”

This allows you to backup the database directly from the command prompt. One the master has completed telling all of the storage nodes to store there slice of the database on your hard-drives it will return to the manager node an all complete signal and return the stats of backing up your database.

Permalink Leave a Comment

October 9, 2006

How to create a MySQL Cluster with 6 machines

Posted in MySQL CLustering at 12:48 am by captsulu32

Installing Mysql Cluster can me a little tricky if you don’t have the right resources.

How many machines are you going to be running.
The optimal configuration for MySQL-Cluster is

4 storage node machines.
1 Manager machine.
1 Api Machine (Of course web-server can double as an API)

Storage nodes should by nature have at least 1 Gig of memory in them you have to remember that MySQL-cluster ndbcluster table structure keeps all the data in memory. So hard-drive space really is not much of a concern. (However, Mysql is working on Version 5.1 which is supposed to be able to keep text and memo fields on hard-drive and keep all the indexes in memory to keep speed to the utmost.) If you are going to be using this cluster in a production environment you should make sure you have the right hardware that will support your application.
I’ll walk through what I did as a test cluster before I could convince upper management to rely on a open source database cluster in a production environment. I took 5 workstation computers nothing too expensive they will all MSI motherboards which had Microsoft windows on them and 256 megs of Ram.  They were sitting around the office not being used as workstation since my business is travel we have our slow times and busy times of the year. This was one of those slow times.

Anyway, 6 machines. Yes I thought I could get away with 4 in the begining. But after reading the MySQL clustering whitepaper and a couple of magazine articles on the topic I ran around the office and got 2 more machines.

Installed FC5, on them the i386 version you can download the ISO images from http://download.fedora.redhat.com  and installed them on the machines with a level 3 type OS install (this means without Xwindows or any graphical programs). I would recommend installing the Developer tools you need to have the gcc compiler on the machine. Then once that is installed. Do a yum update on the machine. Be patient this will take a while there are 1058 updates the last time I looked. Redhat is working very hard on the selinux security therefore its had a lot of attention and lots of updates.

Then in a browser go to the mysql website copy the links to wget the needed files. With FC5 the i386 RHRel 5 edition will work fine. I used the rpm’s that are already compiled. On each of the storage nodes and the manager install

rpm -Uvh MySQL-ndb-tools-version.rpm

rpm -Uvh MySQL-ndb-extras-version.rpm

rpm -Uvh MySQL-ndb-storage-version.rpm

rpm -Uvh MySQL-ndb-management-version.rpm

What I did once I downloaded them is to scp copy from one machine to the rest don’t mix match the versions they won’t work. I also installed the management on all the storage nodes so that a storage node could double as a manager in case of an emergency.

MySQL-cluster uses what is called a heart beat. You go too long without a heart beat you can loose all your data because it’s all stored in memory.

Then on the manager node I created a config.ini file and stored in the /var/lib/mysql-cluster/ directory as config.ini

[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=786M
IndexMemory=128M
MaxNoOfAttributes=4000
MaxNoOfOrderedIndexes=125
MaxNoOftables=150
TimeBetweenWatchDogCheck= 90000

[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# Managment Server
[NDB_MGMD]
# the IP of THIS SERVER
HostName=192.168.1.11
LogDestination=FILE:filename=cluster.log,maxsize=1000000,maxfiles=6
# Storage Engines
[NDBD]
# the IP of the FIRST SERVER (STORAGE NODE)
HostName=192.168.1.12
DataDir= /var/lib/mysql-cluster
[NDBD]
# the IP of the SECOND SERVER (STORAGE NODE)
HostName=192.168.1.13
DataDir=/var/lib/mysql-cluster
[NDBD]
# the IP of the Third SERVER (STORAGE NODE)
HostName=192.168.1.14
DataDir=/var/lib/mysql-cluster
[NDBD]
# the IP of the Forth SERVER (STORAGE NODE)
HostName=192.168.1.15
DataDir=/var/lib/mysql-cluster

# 2 MySQL Clients
# I personally leave this blank to allow rapid changes of the mysql clients;
# you can enter the hostnames of the above two servers here. I suggest you dont.
[MYSQLD] 

[MYSQLD]

[MYSQLD]

#end of file

Then run the manager daemon

ndb_mgmd -f /var/lib/mysql-cluster/config.ini

then

ndb_mgm

Once you have run the manager and are at a ndb_mgm> prompt type in the command show. I use putty.exe on my windows desktop machine on my desk and ssh into my servers so I am have to bring up all of the nodes and manager in separate screens(windows) I know I should be using a Linux desktop but I just can’t bring myself to make that plunge just yet.  

Now on each of the storage nodes you need to create a my.cnf file and put it in the /etc directory. Create the my.cnf file making sure the ip address is that of the manager for each storage node.

#Options for mysqld process:
[MYSQLD]
ndbcluster
ndb-connectstring=192.168.1.11

#Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=192.168.1.11

#end of file

Then you need to make sure you create the directory if it’s not already there

/var/lib/mysql-cluster

chown mysql:mysql /var/lib/mysql-cluster

Now your ready to start each of the storage nodes. Login to each node and run the

ndbd –initial

since I use putty on the manager node in the ndb_mgm program type show you should now see that each node is in starting mode. As you bring them up.  If you are doing the same type of config The cluster will not start until you run the ndbd –initial on each of the storage nodes.  Once that is done it will take a few minutes (It’s creating a blank database with the size that is specified in the config.ini file. If you need to change this you will have to rerun the ndbd –initial on each of the storage nodes to create the new sizes. Don’t forget to restart the ndb_mgmd as well if you change the config.ini after initial startup. Otherwise running ndbd –initial will only re-create the same size database that if is memory)

Now you should have a screen that looks like this in your manager

Cluster Configuration
———————
[ndbd(NDB)]     4 node(s)
id=2    @192.168.1.12  (Version: 5.0.24, Nodegroup: 0, Master)
id=3    @192.168.1.13  (Version: 5.0.24, Nodegroup: 0)
id=4    @192.168.1.14  (Version: 5.0.24, Nodegroup: 1)
id=5    @192.168.1.15  (Version: 5.0.24, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.1.11 (Version: 5.0.24)

[mysqld(API)]   6 node(s)
id=6     (not connected, accepting connect from any host)
id=7     (not connected, accepting connect from any host)
id=8 (not connected, accepting connect from any host)
id=9 (not connected, accepting connect from any host)
id=10 (not connected, accepting connect from any host)
id=11 (not connected, accepting connect from any host)
Now your ready to start interfacing with your new cluster to interface you must do so through an API node. API nodes are the trickier ones to load. The my.cnf files should be the same as a storage node as I did above. 

From the Mysql download website make sure that you use the same version as the rpms you had for the ndb part of your cluster

rpm -Uvh MySQL-client-verison.rpm

rpm -Uvh MySQL-server-version.rpm

rpm -Uvh MySQL-Max-version.rpm  (absolutely a must have)

rpm -Uvh MySQL-shared-version.rpm

If while installing any of these rpm’s you find that it’s requesting additional files to be installed to support the rpm packages I would recommend that you yum install them. One example that I remember is I had to yum install perl-DBI on all the machines.

Make sure that you restart the mysql daemon once you get the my.cnf file in place. Oh, and something that FC5 won’t tell you is selinux comes turned on. I would not do this on an outside IP address do it behind a firewall and simply turn off the selinux you can do this at a prompt type:

setenforce 0

But make sure you have a firewall in place otherwise you will have to dig into selinux and tell it to allow port 1156 communication and iptables that this port is needed.

Once all that is done you should be able to see something like this in you manager section when you do a show command.

ndb_mgm> show
Cluster Configuration
———————
[ndbd(NDB)]     4 node(s)
id=2    @192.168.1.12  (Version: 5.0.24, Nodegroup: 0, Master)
id=3    @192.168.1.13  (Version: 5.0.24, Nodegroup: 0)
id=4    @192.168.1.14  (Version: 5.0.24, Nodegroup: 1)
id=5    @192.168.1.15  (Version: 5.0.24, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.1.11  (Version: 5.0.24)

[mysqld(API)]   6 node(s)
id=6    @192.168.1.7  (Version: 5.0.24)
id=7    @192.168.1.110  (Version: 5.0.24)
id=8 (not connected, accepting connect from any host)
id=9 (not connected, accepting connect from any host)
id=10 (not connected, accepting connect from any host)
id=11 (not connected, accepting connect from any host)

ndb_mgm>
Oh, mine looks this way because I have 2 api’s you need to also make sure you are doing regular scheduled backups RAM can fail from power loss or static electricity can destroy memory over time.  I made a simple script and put it in the /etc/cron.daily sub-directory called clusterbackup. Inside the script I have the following command.

ndb_mgm -e “START BACKUP”

This will create a backup set of files on each of the storage nodes in your /var/lib/mysql-cluster/ directory each time one is done it creates a new sub-directory under BACKUP. Example would be…

/var/lib/mysql-cluster/BACKUP/BACKUP-15

if you have a catastrophic failure you can restore from backup. With the ndb_restore command. Remember that storage node 1 is numbered starting with 2 as in the management program. So your restore would look like this.

ndb_restore -c 192.168.1.11 -b 15 -n 2 -m -r /var/lib/mysql-cluster/BACKUP/BACKUP-15

-m is used only on the first node your recovering on

-b stands for the backup number your restoring

-n is the node that you are restoring the files from.

-c is the management node ip address

Notice in all of the configs that IP addresses are used instead of DNS names. This is because the amount of communication that is needed between the machines is heavy at times and if the heart beat can’t get through you could have a catastrophic failure on your hands.  But as a network engineer knows plan for the worst and hope for the best.

Failures do happen but for the most part I must admit the cluster is pretty stable. I have been running one for about 9 months now and have only seen 15 to 20 failures and most of the time it was electrical or NIC cards on cheap motherboards that caused issues. But I have for the lsat three months had 100% database uptime despite the failures. And to top it off I live in one of the roughest places to have a network when we get tornado’s, hurricanes and spring time weather that leads to hours without power but luckily our building has a generator for those issues. If your not so lucky you should at the very least have battery backup that will give you enough time to run a manual backup.  You can login at any time to the manager and do a snap shot backup of the database.

Permalink 1 Comment

October 8, 2006

MySQL i386 version verses x86_64 on a supermicro motherboard

Posted in MySQL CLustering at 5:10 pm by captsulu32

Recently we purchased several new machines. Ok, motherboard, chassis form super-micro. Specifically the X7DBE+ motherboard version 2.01 and 4U chassis with 4 Gigs of DDR II 667MHz memory which was buffered. We tried loading the Test release of Fedora Core 6 (5.92) which was released on Sept12, 2006. Well that didn’t go very well the GRUB loader can’t handle memory over 4 gigs and gives you an Error 32:. Which by the way has been reported to Redhat on Bugzilla.  Apparently I was not the only one with this issue. Hopeful now that they have delayed the release of FC6 they will have time to fix this issue before it gets released.

Then I tried FC5 x86_64 bit version. Well that didn’t recognize the e1000 Intel Giga Bit Ethernet ports that come on the motherboard. So after much scratching of the head we added a PCI ethernet NIC card to the motherboard and proceeded to load FC5. Everything loaded fine on the motherboard However, when we loaded the MYSQL cluster onto the machines. The response time was worst than if I was running i386 on 1 Gig old workstations which is what we put together to get a test cluster working to make sure that we could do what we wanted in a production environment.

I got so mad that I called Redhat and asked for Pre-Sales Engineering. They gave me a 30 day free eval of Redhat Enterprise AS edition update 4.  I proceeded to load it on to my first API server in the cluster and everything worked but I was still getting  a very slow response time from the MySQL cluster. Oh, and by the way it recognized all my onboard Nic cards and processor and memory without any issues.

So as I was just about to throw in the towel and go back to the i386 version of software on everything I though, “ok I will continue to load the rest of the cluster with the 30 day eval version of the software and see what happens”.

My setup is 4 storage nodes and 1 manager and 2 API servers. As I finished loading the 4th storage node and took the manager down to start reloading it something wonderful happened. The cluster started responding very fast. So I finished loading up the manager with the same version of the OS.  Now I am going to have to figure out how to get support for all these machines and stay in budget for my department. Ouch. Redhat on there website wants 2500 per server for 24/7 support. I hope I can figure out something.

Permalink Leave a Comment