Diamond Notes

So today I really started testing MySQL Cluster. I have been using it for around six to eight weeks. However, we are very soon rolling out an application that will use a cluster in the back so I needed to understand what was really going on.

I think I have said before that the MySQL cluster product is very different than the “standard” MySQL server. It is important that if you are evaluating the MySQL Cluster for your use that you understand that. Take time to understand what is going on. Don’t be easily frustrated. And don’t expect it to be some silver bullet that will solve your database problems.

Some of these test are rather simplistic. However, one of the things I have found is that the MySQL Clustering product is moving very very fast. And much of the documentation is dated. I have the MySQL Clustering book and it is very well written, but it was written either before MySQL 5.1 was released or just shortly after. They do not cover on-disk tables or any of the other new features of 5.1. So, even though I thought I knew what the outcome of most of these test would be, it was worthwhile to perform them.

The one place that I have found that the information is consistently up to date is at MySQL AB’s website. That shouldn’t be a surprise, but the documentation on 5.1 should be required reading . This overall lack of documentation is one of the big reasons that I am taking the time to write about my clustering experience.

On to the tests!!

I have twelve tests currently planned. There is always the possibility that I might add some more over the next few days. Eight of the test I performed today. I will write about the first five and then do another posting or two over the next week with the other tests.

One note about the configuration. I have two servers set up as a cluster. Each server runs a SQL node and data node. One server also runs the management node. Not an ideal setup, but the best I could do. I began testing running 5.1.20 and one of the test is a rolling upgrade to 5.1.22 (rc).

Test one: shut of power on one server.

This simple test required that I shut of the power to the server NOT running the management node. Then, I verified that the cluster was still up and operational. Finally, I booted the server, started the data node and then the SQL node. Afterwards I verified once again that the cluster was up and operating as expected.

Everything went fine with cluster availability throughout and no loss of data. The reason I didn’t bring down the server with the management node is that I know that this would have caused an arbitration issue and the cluster would have been shut down. If the management node is on located on a third server then either of the two sql/data node servers should be able to be shut down.

Test two: shut of power to both servers.

This is just simulating a complete power failure. After both servers were shut down I booted them back up and then restarted the cluster in the order that MySQL specifies: management node, data nodes and last the SQL nodes (making sure each one starts before moving on to the next).

This worked exactly as expected with a complete recovery. Even so, this was a fairly simplistic exercise with no data being read from or written to the database at the time the power was shut off. I would like to test both that and what happens when a node doesn’t really come up properly because of corruption.

Test three: kill management node.

According to everything I have read and in my (limited) experience, you can bring the management node down and the cluster does not stop. This is because for normal operation the management node is not needed. You really only use the management node to tell the nodes their configuration on startup and to stop either nodes or the entire cluster. There are other functions, but none that impact the ongoing operation of the cluster.

This test worked as expected. I brought the management node down and verified that the cluster was still up and running. I created a database and verified that it showed up in the other SQL node.

Test four: killing ndbd daemon.

In this test I executed a ‘kill -9′ command against the process id of one of the ndbd daemons. I should have been able to kill either one of the data nodes and have the cluster stay operational.

Everything worked as advertised. I killed one data node and the cluster stayed up. I verified through the opposite servers SQL node and then restarted the data node.

Test five: Stopping (and restarting) a SQL node.

I know, this is simplistic stuff. But, it’s good just to verify this. We will have a load balancer in front of our SQL nodes so that to the application there is just one IP address for however many SQL nodes we have. Any node should be able to go down without affecting operation.

This worked of course. I shut down one SQL node and verified through the other SQL node that the cluster was up and worked as expected.

The remainder of the tests I have planned  cover the following:

• running ndbd –initial on one node
• running ndbd –initial on both data nodes at start-up
• making configuration changes
• rolling upgrade from 5.1.20 to 5.1.22
• convert ndbcluster - memory - table to disk-based table
• add secondary management node
• backups and restores
  

Hope this has been useful.  I will write about the other tests as soon as possible.