Wednesday, February 03, 2010

MySQL Cluster Configurator v3

Version 3.0 of the severalnines/Configurator has been released:
  • Improved scripts (a lot of cleanup), prefixing the script output with the hostname:
    E.g,:
    Cluster: Cluster Start
    Cluster: STARTING MANAGEMENT SERVERS
    ps-ndb01: Starting management server (nodeid=1)
    ps-ndb01: Copying ../config/config.ini to /etc/mysql
    ps-ndb01: Started management server (nodeid=1, pid=28253)
    ...

  • Reduced number of scripts
    start-cluster-initial.sh --> start-cluster.sh --initial
    rolling-restart-initial.sh --> rollling-restart.sh --initial
    start-ndbd-<host>-<id>-initial.sh --> start-ndbd-<host>-<id>.sh --initial
  • Better error handling during rolling restarts
  • Scripts are checking if Node Failure handling is ready before starting nodes
  • start-ndbd-<host>-<id>.sh - new parameters [--initial] [--nowait] [--ignore]
    --initial - initial node start (clear out local ndb_fs
    --nowait - script will exit as soon as data node is starting
    --ignore - ignore waiting for Node Failure handling to complete
  • Supports MySQL Cluster 7.1.1
Here is an example of how to try out 7.1.1 using the Configurator

1. Generate a Configuration (make sure you select "MySQL Cluster 7.1.1")
  • Currently 7.1.1 is only in source format so you need to have ncurses-devel/gcc/g++/make installed


2. Unpack the tarball you get and start the installation!
You are recommended to set up shared ssh keys between the "Frontend" (where you run the scripts) to the other nodes in the cluster in order to avoid typing passwords all the time.
tar xvfz mysqlcluster-71.tar.gz
cd mysqlcluster-71
cd cluster/scripts/install/
./shared-ssh-keys.sh ###WARNING - This script is experimental, let me know if it works!
./download-and-compile.sh
./install-cluster.sh
./bootstrap.sh
cd ..
./start-cluster.sh --initial
Voila!

MySQL Cluster 7.1.1 (beta) - what's in there

The main new features in MySQL Cluster 7.1.1 (beta) is the following:
  • ndbinfo (aka ndb$info) - Finally!! System tables for MySQL Cluster describing live usage of a lot of resources (RedoBuffer, Redo Log space, counters etc etc). Looks promising - makes Cluster a lot more transparent. See examples below.
  • Cluster/J - for more information and to learn more about it - register to a webinar and meet the architect and developer of Cluster/J!
Severalnines/Configurator supports 7.1.1 - to try it out!

NDBINFO - examples

Ever wondered how much for the RedoBuffer you are actually using?
mysql>  SELECT * FROM ndbinfo.logbuffers;
+---------+----------+--ç------+----------+----------+--------+
| 3 | REDO | 0 | 1 | 33554432 | 229376 |
| 3 | REDO | 0 | 2 | 33554432 | 229376 |
| 3 | REDO | 0 | 3 | 33554432 | 98304 |
| 3 | REDO | 0 | 4 | 33554432 | 229376 |
| 4 | REDO | 0 | 1 | 33554432 | 262144 |
| 4 | REDO | 0 | 2 | 33554432 | 65536 |
| 4 | REDO | 0 | 3 | 33554432 | 98304 |
| 4 | REDO | 0 | 4 | 33554432 | 262144 |
+---------+----------+--------+----------+----------+--------+
And how about the Redo log space?
mysql>  SELECT * FROM ndbinfo.logspaces;
+---------+----------+--------+----------+------------+-----------+
| node_id | log_type | log_id | log_part | total | used |
+---------+----------+--------+----------+------------+-----------+
| 3 | REDO | 0 | 0 | 3221225472 | 200278016 |
| 3 | REDO | 0 | 0 | 3221225472 | 201326592 |
| 3 | REDO | 0 | 0 | 3221225472 | 202375168 |
| 3 | REDO | 0 | 0 | 3221225472 | 202375168 |
| 4 | REDO | 0 | 0 | 3221225472 | 201326592 |
| 4 | REDO | 0 | 0 | 3221225472 | 201326592 |
| 4 | REDO | 0 | 0 | 3221225472 | 202375168 |
| 4 | REDO | 0 | 0 | 3221225472 | 202375168 |
+---------+----------+--------+----------+------------+-----------+
8 rows in set (0.00 sec)
Statistics ...
mysql> SELECT * FROM ndbinfo.counters;
+---------+------------+----------------+------------+--------------+-----------+
| node_id | block_name | block_instance | counter_id | counter_name | val |
+---------+------------+----------------+------------+--------------+-----------+
| 3 | DBLQH | 1 | 10 | OPERATIONS | 1747971 |
| 3 | DBLQH | 2 | 10 | OPERATIONS | 1748870 |
| 3 | DBLQH | 3 | 10 | OPERATIONS | 1749056 |
| 3 | DBLQH | 4 | 10 | OPERATIONS | 1777925 |
| 4 | DBLQH | 1 | 10 | OPERATIONS | 1747976 |
| 4 | DBLQH | 2 | 10 | OPERATIONS | 1749008 |
| 4 | DBLQH | 3 | 10 | OPERATIONS | 1749033 |
| 4 | DBLQH | 4 | 10 | OPERATIONS | 1777928 |
| 3 | DBTC | 0 | 1 | ATTRINFO | 470827014 |
| 3 | DBTC | 0 | 2 | TRANSACTIONS | 245150 |
| 3 | DBTC | 0 | 3 | COMMITS | 245137 |
| 3 | DBTC | 0 | 4 | READS | 25 |
| 3 | DBTC | 0 | 5 | SIMPLE_READS | 0 |
| 3 | DBTC | 0 | 6 | WRITES | 3512472 |
| 3 | DBTC | 0 | 7 | ABORTS | 13 |
| 3 | DBTC | 0 | 8 | TABLE_SCANS | 29 |
| 3 | DBTC | 0 | 9 | RANGE_SCANS | 0 |
| 4 | DBTC | 0 | 1 | ATTRINFO | 472683577 |
| 4 | DBTC | 0 | 2 | TRANSACTIONS | 218857 |
| 4 | DBTC | 0 | 3 | COMMITS | 218834 |
| 4 | DBTC | 0 | 4 | READS | 10 |
| 4 | DBTC | 0 | 5 | SIMPLE_READS | 1 |
| 4 | DBTC | 0 | 6 | WRITES | 3501562 |
| 4 | DBTC | 0 | 7 | ABORTS | 22 |
| 4 | DBTC | 0 | 8 | TABLE_SCANS | 3 |
| 4 | DBTC | 0 | 9 | RANGE_SCANS | 0 |
+---------+------------+----------------+------------+--------------+-----------+
26 rows in set (0.12 sec)
And there is a lot more!
mysql> show tables;
+-------------------+
| Tables_in_ndbinfo |
+-------------------+
| blocks |
| config_params |
| counters |
| logbuffers |
| logspaces |
| memoryusage |
| nodes |
| pools |
| resources |
| transporters |
+-------------------+
10 rows in set (0.01 sec)

Monday, February 01, 2010

Cluster Performance Tuning Webinar - EMEA

The 2nd of February, 0900GMT/1000CET I will have a webinar on Cluster Performance Tuning for people located in EMEA timezone.

For more information and registration:

http://mysql.com/news-and-events/web-seminars/display-480.html


In addition to what is mentioned in the Agenda I will also present numbers on typical operations, how to design requests, explain how the optimizer works with MySQL Cluster etc etc.

See you there!

-johan