Friday, June 30, 2006

Writing NDBAPI programs - connecting to MySQL Cluster

In this first little lesson we will learn how to connect an NDBAPI program to connect to MySQL Cluster. Start a X node cluster with atleast two or three [API] or [MYSQLD] slots so that you can connect a mysql server and the example program.

Before running these examples, please read the documentation on the NDBAPI classes and methods that are used!

The first example program (listed below), ndbapi_connect.cpp illustrates how to connect to MySQL Cluster.

> cat ndbapi_connect.cpp

#include
#include

#include


using namespace std;

int main()
{
ndb_init();
/**
* define a connect string to the management server
*/
char * connectstring = "localhost";

/**
* Create a Ndb_cluster_connection object using the connectstring
*/

Ndb_cluster_connection * conn = new Ndb_cluster_connection(connectstring);


/**
* Connect to the management server
* try 12 times, wait 5 seconds between each retry,
* and be verbose (1), if connection attempt failes
*/

if(conn->connect(12, 5, 1) != 0)
{
cout << "Unable to connect to management server." << face="courier new"> return -1;
}

/**
* Join the cluster
* wait for 30 seconds for first node to be alive, and 0 seconds
* for the rest.
*/

if (conn->wait_until_ready(30,0) <0)
{
cout << "Cluster nodes not ready in 30 seconds." << endl;
return -1;
}

cout << "Congratulations, you have connected to MySQL Cluster!" << endl;
cout << "run 'ndb_mgm -e \"show\" to see that your app is connected! " << endl << "Press any key to exit" << endl;

char data;
read (0, &data, 1);

return 0;
}


To build this I do:
g++ -c -I/home/johan/mysql/include/ -I/home/johan/mysql/include/ndb -I/home/johan/mysql/include/ndb/ndbapi ndbapi_connect.cpp

and to link:
g++ ndbapi_connect.o -L/home/johan/mysql/lib -lndbclient -lmysys -lmystrings -lpthread -o ndbapi_connect

and to run:
./ndbapi_connect

It should print out:

Congratulations, you have connected to MySQL Cluster!
run 'ndb_mgm -e "show" to see that your app is connected!
Press any key to exit

Congratulations, you have now written your first NDBAPI example.
Next time we will look how to implement a simple query, SELECT b,c FROM t1 WHERE a=1 , using the NDBAPI.

Writing NDBAPI programs - getting our environment

The first thing we have to do, before starting with any examples, is to setup our environment.

The linux kernel I am using is ( I am using Ubunto 6.06):
Linux stingray 2.6.15-25-386 #1 PREEMPT Wed Jun 14 11:25:49 UTC 2006 i686 GNU/Linux

The compiler I am using is ( g++ -v):
...
gcc version 4.0.3 (Ubuntu 4.0.3-1ubuntu5)

Another good thing to have is e.g. the NDBAPI documentation.
For this I will use MySQL 5.0.22, but the steps are the same for 5.1.X
  1. Download the source distribution of MySQL 5.0.22
  2. tar xvfz mysql-5.0.22.tar.gz
  3. cd mysql-5.0.22
  4. ./configure --with-ndbcluster --with-ndb-docs
  5. cd ndb/docs (in 5.1.X it is 'storage/ndb/docs')
  6. make
Now you have html documentation of the ndbapi, if you have got all the necessary things (like doxygen) installed to generate the documentation.
You will also get the documentation for the management api (mgmapi), but it will be covered later here.

Finally, I download the binary distribution of MySQL 5.0.22 and:
  1. cd /home/johan
  2. tar xvfz mysql-max-5.0.22-linux-i686-glibc23.tar.gz
  3. ln -s mysql-max-5.0.22-linux-i686-glibc23 mysql
Include files are located in:
mysql/include, mysql/include/ndb and mysql/include/ndb/ndbapi

Libraries are located in:
mysql/lib

Alright, I think we are good to go and continue with some examples. First of all how to connect an application to MySQL Cluster.

Sunday, June 25, 2006

Faster JOINs in MySQL Cluster 5.1 using Replication

Many of you have experienced performance problems with JOINs in MySQL Cluster. However, in MySQL 5.1, it is possible to address this using MySQL replication and create a read slave where the JOINs are performed.

It is possible to set a MySQL Cluster looking like this (very schematic):
To read more on how to setup the replication link beetween the Master and the Slave, see http://dev.mysql.com/doc/refman/5.1/en/replication.html .

The trick here is to make sure that the tables created using the NDBCluster engine are created on the slave MySQL server as MyISAM or in another storage engine more performant resolving JOINs.

This way you can now issue complex JOINs on the slave instead of on the cluster and the replication ensures that the changes you make on the NDBCluster tables will be replicated to the slave.


Unfortunately, it is not yet (will it ever be?) possible to let the Master MySQL server replicate its binlog to itself (into another database) in order to avoid the extra Slave MySQL server.

Writing NDBAPI programs

The most powerful and efficient way to access data stored in the NDB storage engine is to use the NDBAPI, a C++ API which gives you direct access to data stored in MySQL Cluster.

Instead of having to go through the MySQL server to access data, you can access the data directly from your C++ application. Why would you like to do this? There are some reasons:
  1. Data access with minimal overhead
  2. Flexibility - posssible to hand-optimize queries. You control exactly when you want to send data to NDB Cluster.
  3. Performance - Shorter response times, because of fewer network hops (no MySQL server as a proxy) and (1,2) above.
Typically, application developers that want soft realtime characteristics on data access use the NDBAPI, and use SQL (going through the MySQL server) for OAM type applications.

In this blog, I will try to show you how to start programming your own NDBAPI applications and I will illustrate this with simple examples. It will require that you have knowledge about C/C++.