Monday, June 28, 2010

MySQL Sandbox embraces Python and meets Cluster

If you have tried Quick start guides: MySQL cluster in 10 minutes, you may have realized that it is really quick and easy.
However, it leaves some typing to be done.
Users of MySQL Sandbox have a horror of repetitive typing, and this got me thinking. "Could I integrate MySQL Sandbox and Cluster?"
The answer was: "Sure."
But then I started thinking of all the minor and major changes that I wanted to do to the Sandbox and have delayed for too long. What I need, is a radical refactoring.
And then I remembered that it has been almost two years since I learned a new programming language and that perhaps I could expand my horizons and the Sandbox architecture at once.
Thus, thanks to an irresistible offer from O'reilly about ebooks, last week I bought both Learning Python, fourth edition and Programming Python, Third edition.
During the week end I produced my first tool: a Python script that installs and starts a small cluster, following the instructions given in the MySQL Cluster quick start guides. The script unpacks the cluster tarball, installs a server sandbox from it, then starts the cluster nodes and the MySQL server, and then it monitors the cluster until all the nodes are connected before finally testing the server.
Here is a sample run:

$ make_cluster.py ~/Downloads/mysql-cluster-gpl-7.1.4b-osx10.6-x86_64.tar.gz

++ tar -xzf ~/Downloads/mysql-cluster-gpl-7.1.4b-osx10.6-x86_64.tar.gz

++ low_level_make_sandbox --basedir=$HOME/python/mysql-cluster-gpl-7.1.4b-osx10.6-x86_64 --sandbox_directory=mcluster --install_version=5.1 --sandbox_port=5144 --no_ver_after_name --no_run --force --my_clause=log-error=msandbox.err --my_clause=ndbcluster 
    The MySQL Sandbox,  version 3.0.12
    (C) 2006-2010 Giuseppe Maxia
installing with the following parameters:
upper_directory                = $HOME/sandboxes
sandbox_directory              = mcluster
sandbox_port                   = 5144
check_port                     = 
no_check_port                  = 
datadir_from                   = script
install_version                = 5.1
basedir                        = $HOME/python/mysql-cluster-gpl-7.1.4b-osx10.6-x86_64
tmpdir                         = 
my_file                        = 
operating_system_user          = gmax
db_user                        = msandbox
db_password                    = msandbox
my_clause                      = log-error=msandbox.err ; ndbcluster
prompt_prefix                  = mysql
prompt_body                    =  [\h] {\u} (\d) > 
force                          = 1
no_ver_after_name              = 1
verbose                        = 
load_grants                    = 1
no_load_grants                 = 
no_run                         = 1
no_show                        = 
loading grants
.. sandbox server started
stopping server
Your sandbox server was installed in $HOME/sandboxes/mcluster

++ mysql-cluster-gpl-7.1.4b-osx10.6-x86_64/bin/ndb_mgmd -f $HOME/sandboxes/mcluster/my_cluster/conf/config.ini --initial --configdir=$HOME/sandboxes/mcluster/my_cluster/conf/
2010-06-28 21:29:57 [MgmtSrvr] INFO     -- NDB Cluster Management Server. mysql-5.1.44 ndb-7.1.4b
2010-06-28 21:29:57 [MgmtSrvr] INFO     -- Reading cluster configuration from '$HOME/sandboxes/mcluster/my_cluster/conf/config.ini'

++ mysql-cluster-gpl-7.1.4b-osx10.6-x86_64/bin/ndbd -c localhost:1186
2010-06-28 21:29:57 [ndbd] INFO     -- Configuration fetched from 'localhost:1186', generation: 1

++ mysql-cluster-gpl-7.1.4b-osx10.6-x86_64/bin/ndbd -c localhost:1186
2010-06-28 21:29:57 [ndbd] INFO     -- Configuration fetched from 'localhost:1186', generation: 1

++ mysql-cluster-gpl-7.1.4b-osx10.6-x86_64/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 (not connected, accepting connect from localhost)
id=4 (not connected, accepting connect from localhost)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @localhost  (mysql-5.1.44 ndb-7.1.4)

[mysqld(API)] 1 node(s)
id=50 (not connected, accepting connect from any host)


++ $HOME/sandboxes/mcluster/clear

++ $HOME/sandboxes/mcluster/start
... sandbox server started
Please wait. Giving the cluster time to catch up
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0, Master)
id=4 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @localhost  (mysql-5.1.44 ndb-7.1.4)

[mysqld(API)] 1 node(s)
id=50 (not connected, accepting connect from any host)


It may take up to 2 minutes to initialize ... ( 0 )
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0, Master)
id=4 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @localhost  (mysql-5.1.44 ndb-7.1.4)

[mysqld(API)] 1 node(s)
id=50 (not connected, accepting connect from any host)


It may take up to 2 minutes to initialize ... ( 5 )
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0, Master)
id=4 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @localhost  (mysql-5.1.44 ndb-7.1.4)

[mysqld(API)] 1 node(s)
id=50 (not connected, accepting connect from any host)

[...]

It may take up to 2 minutes to initialize ... ( 100 )
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0, Master)
id=4 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4)

[mysqld(API)] 1 node(s)
id=50 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4)


++ $HOME/sandboxes/mcluster/use -vvv -e "create table test.t1(i int not null primary key)engine=ndb"
--------------
create table test.t1(i int not null primary key)engine=ndb
--------------
Query OK, 0 rows affected (0.45 sec)

++ $HOME/sandboxes/mcluster/use -vvv -e "show create table test.t1\G"
--------------
show create table test.t1
--------------

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

to shut down the cluster, type:
$HOME/python/mysql-cluster-gpl-7.1.4b-osx10.6-x86_64/bin/ndb_mgm -e shutdown
It works! Ans this is a good start to make me feel confident with Python, which I will use to develop MySQL Sandbox version 4. This cluster snippet will probably be made from scratch once the new architecture is in place. For now, it was enough to get the feeling of the language.

6 comments:

Justin Swanhart said...

Will you still be able to install the python version from CPAN? I really like how easy it is to install mysqlsandbox from CPAN.

Shlomi Noach said...

I do not know that there's any way to install python modules from CPAN.

Giuseppe:
Pythons scripts are winners, from my experience. However, the python versions installed out there make for a lot of inconsistency. Python 2.3 to 2.6 has many changes in syntax and libraries, so make sure you know about these and support them.
With Python 3 some libraries and syntax become obsolete, so this is another headache to take care of. Currently there is no package management that I know of that introduces Python 3 by default.

Giuseppe Maxia said...

@Swany,
As a seasoned Perl coder, I am sure I can find a way of installing Python modules from CPAN (by encapsulating them into a Perl package), but I am not sure it would be ethical. So I will not even try.
The Python equivalent to CPAN should be easy_install. I will start exploring it as soon as I have something worth installing.

@Shlomi,
Thanks for the heads up. I am aware of the scenario and I have made my choices.
I plan to use Python 2.6 as a minimal requirement, using as many as the 3.0 ready features available in 2.6, to ease the path for the upgrade.
Keeping compatibility with old versions of Python is beyond my goals.

Shlomi Noach said...

@Giuseppe,
To make sure you realize no yum or up2date or apt-get will install a python2.6!
This means anyone using Linux will be unable to use your code unless she downloads python2.6 separately (source + compile, and perhaps there's RPMs, I don;t know).
Actually, many yum/up2date (CentOS/RedHat 4) still work with python 2.3.
I believe targeting python 2.3 to be the right choice.

Anonymous said...

Can I know the main reasons why you prefer using python in place of perl and so building from scratch MySQL Sandbox 4 ?

Giuseppe Maxia said...

@LesDatabases,
The main reason is probably better OOP support. Another reason is that I would like to find more contributors, and I know more Python than Perl coders among database experts. Probably it's a subjective reason, but that's it.
Besides, I'd really like to get another language under my belt.