Dear Kaj, Giuseppe, Jay, Lenz, Colin,
MySQL™ Community Team,

This is my Report for the MySQL™ 5.1 Use Case Competition.

Before start reporting on my use of the new 5.1 Features, just a note.

I was quite sure on early August 2008 that including similar posts in my Report would be allowed. But during an email conversation, Giuseppe told me that similar Reports won’t qualify for the competition because the Community Team was looking for Reports on ‘Real Word’ usage of MySQL™ 5.1 in production systems rather than a description of software specific features though implemented on MySQL™ 5.1 features.

BTW, I decided to go on with my series of articles as I wrote in this post (I published it only today, before it was just a draft).

Nevertheless, few weeks ago, after reading this post by Lenz I changed my mind once again about the real stuff a UC Report should contain and today I think that my Report could be admitted (if that has been admitted).

So here’s my report ;-)

http://www.honeysoftware.it/sleto/blog/ucc/sleto-MySQL-5.1-Use-Case-Reports.pdf

Username and password has been sent to the Community email address.

Enjoy!

[HoneyMonitor is a GUI for MySQL™. Server Administration, Database and Database Objects Administration, Code Development, Server Monitoring, Performance Monitoring, Performance Auditing. Read more on http://www.honeysoftware.com/]

Contents

• Introduction
• Log File Group & UndoFiles
• TableSpaces & DataFiles
• Cluster Disk Objects Metadata
• Creating Cluster Disk Tables
• Dropping Cluster Disk Data Objects
• The Cluster Monitor
• Cluster Disk Data Limitations
• Cluster Disk Data Quiz
• Other Resources

1. Introduction

Cluster Disk Data is a new feature of MySQL™ 5.1. Beginning with MySQL™ 5.1.6, in fact, it is possible to store the non-indexed columns of NDBCLUSTER tables on disk, rather than in RAM  this allowing MySQL™ Cluster to scale upward with fewer RAM requirements than previously.

This is a short tutorial on the features implemented in HoneyMonitor for the use and administration of Cluster Disk Data Objects . It could be useful for Users who are new to the product and want to get start with it or to whom want to learn the basis of Cluster Disk Data in MySQL™ 5.1.

For more information about Cluster Disk Data, please visit http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-disk-data.html, while, if you want to learn more on HoneyMonitor, please visit its Project Home Page at http://www.honeysoftware.com/honeymonitor.

For this tutorial we will suppose you have a MySQL™ Cluster up and running and we will connect to the Linux machine, from HoneyMonitor, through a SSH Tunneling connection.

If you are new to MySQL™ Cluster and want to set up a testbed (all nodes on localhost), you can use this very useful configuration tool (created by Johan Andersson) or, if you want to do a more complicated (but nice) exercise, you can set up the Cluster in 4 Solaris™ Zones (a tutorial can be found here).

For our Tutorial, we will follow the following steps:

• Create a LogFile Group
• Add one more UndoFile to the LogFile Group
• Create a TableSpace that uses the LogFile Group just created
• Add one more DataFile to the TableSpace
• Create a Cluster Disk Table

You can skip steps no. 2 and 4 as they are just to practise.

2. Log File Group & UndoFiles

The LogFile Group Editor (Fig. 1) allows you create / edit / drop a LogFile Group.

Fig. 1: Creating a LogFile Group

It implements the following Data Definitions Statements:

1) [http://dev.mysql.com/doc/refman/5.1/en/create-logfile-group.html]

CREATE LOGFILE GROUP logfile_group
ADD UNDOFILE ‘undo_file’
[INITIAL_SIZE [=] initial_size]
[UNDO_BUFFER_SIZE [=] undo_buffer_size]
[REDO_BUFFER_SIZE [=] redo_buffer_size]
[NODEGROUP [=] nodegroup_id]
[WAIT]
[COMMENT [=] comment_text]
ENGINE [=] engine_name

2) [http://dev.mysql.com/doc/refman/5.1/en/alter-logfile-group.html]

ALTER LOGFILE GROUP logfile_group
ADD UNDOFILE ‘file_name’
[INITIAL_SIZE [=] size]
[WAIT]
ENGINE [=] engine_name

3) [http://dev.mysql.com/doc/refman/5.1/en/drop-logfile-group.html]

DROP LOGFILE GROUP logfile_group
ENGINE [=] engine_name

As you can see from Fig. 1, some options are disabled as they are parsed but not yet implemented by the  MySQL™ Server.

An UndoFile is required for the LogFile Group creation. In our example we will use an UndoFile named ‘undofile1′.

After the creation  of the LogFile Group the Tabs ‘UndoFiles’ and SQL will be enabled (Fig. 2, 3, 4).

Fig. 2: LogFile Group Editor - Advanced Tab

Fig. 3: LogFile Group Editor - UndoFiles Tab

Fig. 4: LogFile Group Editor - SQL Tab

In the UndoFiles Tab you can see UndoFiles’ details or you can create one more UndoFile, using the Add UndoFile Dialog (Fig. 5).

Fig. 5: Add UndoFile Dialog

The SQL Tab contains the SQL statements to be executed for re-creating the LogFile Group (and all its UndoFiles!).

Note: you can’t delete UndoFiles.

Note 2: beginning with MySQL™ 5.1.8, you can have only one LogFile Group per Cluster at any given time (see Bug#16386).

Let’s now create a TableSpace that uses the LogFile Group just created.

3. TableSpaces & DataFiles

The TableSpace Editor (Fig. 6) allows you create / edit / drop TableSpaces.

Fig. 6: Creating a TableSpace

It implements the following Data Definitions Statements:

1) [http://dev.mysql.com/doc/refman/5.1/en/create-tablespace.html]

CREATE TABLESPACE tablespace_name
ADD DATAFILE ‘file_name’
USE LOGFILE GROUP logfile_group
[EXTENT_SIZE [=] extent_size]
[INITIAL_SIZE [=] initial_size]
[AUTOEXTEND_SIZE [=] autoextend_size]
[MAX_SIZE [=] max_size]
[NODEGROUP [=] nodegroup_id]
[WAIT]
[COMMENT [=] comment_text]
ENGINE [=] engine_name

2) [http://dev.mysql.com/doc/refman/5.1/en/alter-tablespace.html]

ALTER TABLESPACE tablespace_name
{ADD|DROP} DATAFILE ‘file_name’
[INITIAL_SIZE [=] size]
[WAIT]
ENGINE [=] engine_name

3) [http://dev.mysql.com/doc/refman/5.1/en/drop-tablespace.html]

DROP TABLESPACE tablespace_name
ENGINE [=] engine_name

Note: The Editor can be used to create both NDBCLUSTER and FALCON TableSpaces. When creating FALCON TableSpaces, the ‘LogFile Group’ option isn’t visible.

As you can see from Fig. 6, some options are disabled as they are parsed but not yet implemented by the  MySQL™ Server.

A DataFile is required for the TableSpace creation. In our example we will use a DataFile named ‘datafile-11′.

After the creation  of the TableSpace the Tabs ‘DataFiles’ and SQL will be enabled (Fig. 7, 8, 9).

Fig. 7: TableSpace Editor - Advanced Tab

Fig. 8: TableSpace Editor - DataFiles Tab

Fig. 9: TableSpace Editor - SQL Tab

In the DataFiles Tab you can see DataFiles’ details or you can create one more DataFile, using the Add DataFile Dialog (Fig. 10). You can also drop a DataFile.

Fig. 10: Add DataFile Dialog

The SQL Tab contains the SQL statements to be executed for re-creating the TableSpace (and all its DataFiles!).

4. Cluster Disk Objects Metadata

The source for Cluster Disk Object Metadata in MySQL™ 5.1 is the  information_schema.files table. I’ll try to post a more detailed article on Disk Object Metadata later on.

In the HoneyMonitor’s Metadata Catalog there is a visualization dedicated to Cluster Disk Objects (Fig. 11).

Fig. 11: Metadata Catalog - Cluster Disk Objects

That visualization is very useful to know, with just a look, the Cluster Disk Objects’  hierarchy. You can use popup menus to create / edit / drop the objects.

5. Creating Cluster Disk Tables

Now that our TableSpace has been created, we can create a Cluster Disk Table. We can use the command line client

CREATE TABLE my_disk_data_table (
….
)
TABLESPACE tablespace1 STORAGE DISK
ENGINE = NDBCLUSTER;

or the Create Table Wizard (Fig. 12).

Fig. 12: Creating a Cluster Disk Table

6. Dropping Cluster Disk Data Objects

Please, note that there are particular rules for dropping Cluster Disk Data Objects. You have to follow a logical order. For more information, please refer to http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-disk-data-objects.html.

7. The Cluster Monitor

The Cluster Monitor (Fig. 13) allows you to monitor the status of your Cluster.

Fig. 13: The Cluster Monitor

At this stage of development it’s a simple (but useful) Window which shows information about the NDB Engine Status (at a glance or category by category) and the status, dynamic and system variables of the Server related to Cluster.

We plan to add new features for Cluster Management and Monitoring in HoneyMonitor, so stay tuned!

8. Cluster Disk Data Limitations

Disk Data Objects are subject to the following maximums:

1. Maximum number of TableSpaces: 2^32 (4294967296)
2. Maximum number of DataFiles per TableSpace: 2^16 (65535)
3. The theoretical maximum number of extents per TableSpace data file is 2^16 (65525); however, for practical purposes, the recommended maximum number of extents per DataFile is 2^8 (32768).
4. Maximum DataFile size: The theoretical limit is 64G; however, in MySQL™ 5.1, the practical upper limit is 32G. This is equivalent to 32768 extents of 1M each.The minimum and maximum possible sizes of extents for TableSpace data files are 32K and 2G, respectively.

For known limitations of MySQL Cluster, please refer to http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations.html.

9. Cluster Disk Data Quiz

Create a LogFile Group, some TableSpaces and some Cluster Disk Tables. Then choose a TableSpace and write a query to find the list of the tables which are using that TableSpace.

10. Other Resources

It’s now time to close this article. If you want to learn more on HoneyMonitor please visit the Project Home Page, at http://www.honeysoftware.com/honeymonitor, or check out one of the following resources:

• Getting Started Guide
• Video Tutorials
• User’s Guide

Enjoy your MySQL™ Cluster!

Today (August 1st 2008) I started write a series of articles to be referenced in my MySQL™ 5.1 Use Case Report that I will submit later this month to the Community Team for the MySQL™ 5.1 Use Case Competition.

I guess UC Reports should contain real world use of MySQL™ 5.1. So if I am not mistaken you can submit a Report on partitioning, for instance, if you are using v. 5.1 because you need partitioning and you can include in your Report the reason you needed partitioning and a description of your development and deployment environment.

Now, I am not using Partitioning (or Events or Plugins) because I *need* partitioning in my production system for a specific reason. I am a developer so I developed software on those features ;-). So I will submit some posts about new features of the version 5.1 and let others decide if my stuff can be qualified for the competition or not.

The posts already on-line are:

1. Using Federated Server in MySQL™ 5.1
2. Using Scheduled Events in MySQL™ 5.1

Next posts will be:

1. Using Plugins in MySQL™ 5.1
2. Using Partitioning in MySQL™ 5.1
3. Using Table Logging in MySQL™ 5.1
4. Using Cluster Disk Data in MySQL™ 5.1

Contents are subject to change.