MySQL Cluster uses the new
NDB Cluster storage engine to enable running
several MySQL servers in a cluster.
NDB Cluster storage engine is available in BitKeeper from MySQL
release 4.1.2, and in binary releases from MySQL-Max 4.1.3.
Currently supported operating systems are Linux, Mac OS X, and Solaris.
We are working to make
NDB Cluster run on all operating systems
supported by MySQL, including Windows.
This chapter represents work in progress. Other documents describing MySQL Cluster can be found at http://www.mysql.com/cluster/.
You may also wish to subscribe to the MySQL Cluster mailing list. See http://lists.mysql.com/. You may also find the MySQL forums at http://forums.mysql.com/to be useful.
MySQL Cluster is a new technology to enable clustering of in-memory
databases in a share-nothing system. The share-nothing architecture allows
the system to work with very inexpensive hardware, and without any specific
requirements on hardware or software. It also does not have any single
point of failure because each component has its own memory and disk.
MySQL Cluster integrates the standard MySQL server with an
in-memory clustered storage engine called
NDB. In our
documentation, the term
NDB refers to the
part of the setup that is specific to the storage engine, whereas
MySQL Cluster refers to the combination
of MySQL and the new storage engine.
A MySQL Cluster consists of a set of computers, each running a number of processes including MySQL servers, storage nodes for NDB Cluster, management servers and (possibly) specialized data access programs. All these programs work together to form MySQL Cluster. When data is stored in the NDB Cluster storage engine, the tables are stored in the storage nodes for NDB Cluster. Such tables are directly accessible from all other MySQL servers in the cluster. Thus, in a payroll application storing data in a cluster, if one application updates the salary of an employee, all other MySQL servers that query this data can see the change immediately.
The data stored in the storage nodes for MySQL Cluster can be mirrored; the cluster can handle failures of individual storage nodes with no other impact than that a number of transactions are aborted due to losing the transaction state. Since transactional applications are expected to handle transaction failure, this should not be a source of problems.
By bringing MySQL Cluster to the Open Source world, MySQL makes clustered data management with high availability, high performance, and scalability available to all who need it.
NDB is an in-memory storage engine offering high-availability
and data-persistence features.
NDB storage engine can be configured with a range of failover and
load-balancing options, but it is easiest to start with the storage
engine at the cluster level. MySQL Cluster's
NDB storage engine
contains a complete set of data, dependent only on other data within
the cluster itself.
We will now describe how to set up a MySQL Cluster consisting of an
NDB storage engine and some MySQL servers.
The cluster portion of MySQL Cluster is currently configured independently
of the MySQL servers. In a MySQL Cluster, each part of the cluster
is considered to be a
Note: A node is in many contexts a computer, but for MySQL Cluster it is a process. There can be any number of nodes on a single computer.
Each node has a type, and there can be multiple nodes of each type in a MySQL Cluster. In a minimal MySQL Cluster configuration, there will be at least three nodes:
MGM) node: The role of this type of node is to manage the other nodes within the MySQL Cluster, such as providing configuration data, starting and stopping nodes, running backup, and so forth. Because this node type manages the configuration of the other nodes, a node of this type should be started first, before any other node. With a running cluster, the MGM node does not necessarily have to be running all the time. A MGM node is started with the command
ndb_mgmd; for this reason NDB_MGMD is provided as an alias for MGM when configuring the cluster.
DB) node: This is the type of node that manages and stores the database itself. There are as many DB nodes as you have replicas times the number of fragments. For example, with two replicas, each having two fragments, you will need four DB nodes. It is not necessary to have more than one replica, so a minimal MySQL Cluster may contain just one DB node. A DB node is started with the command
ndbd, and so NDBD is provided as an alias for DB when configuring the cluster.
API) node: This is the client node that will access the cluster. In the case of MySQL Cluster, a client node is a traditional MySQL server that uses the
NDB Clusterstorage engine, enabling access to clustered tables. Basically, the MySQL server acts as a client of the NDB cluster. Applications using the NDB API directly are also considered API nodes. Since a MySQL server is typically started with the command
mysqld_safe, MYSQLD is provided as an alias for API when configuring the cluster.
Cluster processes are also referred to as cluster nodes. Configuration of the cluster involves configuring each individual node in the cluster and setting up individual communication links between nodes. MySQL Cluster is currently designed with the intention that storage nodes are homogenous in terms of processor power, memory space, and communication bandwidth. In addition, in order to provide a single point of configuration, all configuration data for the cluster as a whole is located in one configuration file.
The management server manages the cluster configuration file and the cluster log. Each node in the cluster retrieves the configuration data from the management server, and so requires a way to determine where the management server resides. When interesting events occur in the storage nodes, the nodes transfer information about these events to the management server, which then writes the information to the cluster log.
In addition, there can be any number of clients to the cluster. These are of two types.
A MySQL server that is part of a MySQL Cluster differs in only one
respect from a normal (non-clustered) MySQL server, employing the
NDBCLUSTER) storage engine. This engine is also referred to
NDB, and the two forms of the name are synonomous.
In order to avoid unnecessary resources allocation, the server is
configured by default with the
NDB storage engine disabled. To
NDB, you will need to modify the server's `my.cnf'
Since the MySQL server is a part of the cluster, it will also need to
know how to access an MGM node in order to obtain the cluster
configuration data. The default behavior is to look for the MGM node on
localhost. However, should you need to specify its location
elsewhere, this is can be done in `my.cnf' or on the MySQL server
command line. Before the
NDB storage engine can be used, at
least one MGM node must be operational, as well as any desired DB
NDB, the Cluster storage engine, is available in binary
distributions beginning with MySQL-Max 4.1.3 for Linux, Mac OS X, and
Solaris. It is not yet supported on Windows, but we intend to make it
available for win32 platforms in the near future.
If you choose to build from a source tarball or the MySQL 4.1 BitKeeper
tree, be sure to use the
--with-ndbcluster option when running
configure. You can instead use the
BUILD/compile-pentium-max build script. Note that this script
includes OpenSSL, so you must have or obtain OpenSSL to build
successfully; otherwise you will need to modify
compile-pentium-max to exclude this requirement. Of course, you
can also just follow the standard instructions for compiling your own
binaries, then perform the usual tests and installation procedure.
See section 2.8.3 Installing from the Development Source Tree.
In the next few sections, we assume that you are already familiar with installing MySQL, and here we cover only the differences between configuring MySQL Cluster and configuring MySQL without clustering. (See section 2 Installing MySQL if you require more information about the latter.)
You will find Cluster configuration easiest if you have already have all MGM and DB nodes running first; this is likely to be the most time-consuming part of the configuration. Editing the `my.cnf' file is fairly straightforward, and this section will cover only any differences from configuring MySQL without clustering.
In order to familiarise you with the basics, we will describe the simplest possible configuration for a functional MySQL Cluster. After this, you should be able to design your desired setup from the information provided in the other relevant sections of this chapter.
First, you need to create a configuration directory, for example
`/var/lib/mysql-cluster', by executing the following command as
shell> mkdir /var/lib/mysql-cluster
In this directory, create a file named `config.ini' with the
following information, substituting appropriate values for
DataDir as necessary for your system.
# file "config.ini" - showing minimal setup consisting of 1 DB node, # 1 management server, and 3 MySQL servers. # The empty default sections are not required, and are shown only for # the sake of completeness. # Storage nodes are required to provide a hostname but MySQL Servers # are not. # If you don't know the hostname for your machine, use localhost. # The DataDir parameter also has a default value, but it is recommended to # set it explicitly. # NDBD, MYSQLD, and NDB_MGMD are aliases for DB, API, and MGM respectively # [NDBD DEFAULT] NoOfReplicas= 1 [MYSQLD DEFAULT] [NDB_MGMD DEFAULT] [TCP DEFAULT] [NDB_MGMD] HostName= myhost.example.com [NDBD] HostName= myhost.example.com DataDir= /var/lib/mysql-cluster [MYSQLD] [MYSQLD] [MYSQLD]
You can now start the management server as follows:
shell> cd /var/lib/mysql-cluster shell> ndb_mgmd
Then start a single DB node by running
ndbd. When starting
ndbd for a given DB node for the very first time, you should
shell> ndbd --initial
ndbd starts, you will generally not want to use
This is because the
--initial option will delete all existing
data and log files (as well as all table metadata) for this storage
node and create new ones.
ndbd will look for the management server at
localhost at port 1186. (Prior to MySQL 4.1.8, the default port
Note: If you have installed MySQL from a binary tarball, you
will need to specify the path of the
servers explicitly. (Normally, these will be found in
Finally, go to the MySQL data directory (usually `/var/lib/mysql'
or `/usr/local/mysql/data'), and make sure that the `my.cnf'
file contains the option necessary to enable the
You can now start the MySQL server as usual:
shell> mysqld_safe --user=mysql &
Wait a moment to make sure the MySQL server is running properly. If you
see the notice
mysql ended, check the server's `.err' file
to find out what went wrong.
If all has gone well so far, you now can start using the cluster:
shell> mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.7 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SHOW ENGINES; +------------+---------+------------------------------------------------------------+ | Engine | Support | Comment | +------------+---------+------------------------------------------------------------+ ... | NDBCLUSTER | DEFAULT | Clustered, fault-tolerant, memory-based tables | | NDB | YES | Alias for NDBCLUSTER | ... mysql> USE test; Database changed mysql> CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER; Query OK, 0 rows affected (0.09 sec) mysql> SHOW CREATE TABLE ctest \G *************************** 1. row *************************** Table: ctest Create Table: CREATE TABLE `ctest` ( `i` int(11) default NULL ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
To check that your nodes were set up properly, start the management client as shown:
You can then use the
SHOW command from within the management
client in order to obtain a report on the cluster's status:
NDB> SHOW Cluster Configuration --------------------- [ndbd(NDB)] 1 node(s) id=2 @127.0.0.1 (Version: 3.5.3, Nodegroup: 0, Master) [ndb_mgmd(MGM)] 1 node(s) id=1 @127.0.0.1 (Version: 3.5.3) [mysqld(API)] 3 node(s) id=3 @127.0.0.1 (Version: 3.5.3) id=4 (not connected, accepting connect from any host) id=5 (not connected, accepting connect from any host)
At this point, you have successfully set up a working MySQL Cluster.
You can now store data in the cluster by using any table created with
ENGINE=NDBCLUSTER or its alias
Configuring MySQL Cluster requires working with two files:
We are continuously making improvements in Cluster configuration and attempting to simplify this process. While we strive to maintain backwards compatibility, there may be times when introduce an incompatible change. In such cases we will try to let Cluster users know in advance if a change is not backwards compatible. If you find such a change which we have not documented, please use our Bugs Database to report it.
In order to support MySQL Cluster, you will need to update `my.cnf' as shown in the example below.
From version 4.1.8 some simplifications in `my.cnf' were made,
including new sections for the
ndbcluster executables. However,
these should not be confused with those occurring in `config.ini'
files. As always, you may specify these parameters when invoking those
executables from the command line.
# my.cnf # example additions to my.cnf for MySQL Cluster # (valid from 4.1.8) # enable ndbcluster storage engine, and provide connectstring for # management server host (default port is 1186) [mysqld] ndbcluster ndb-connectstring=ndb_mgmd.mysql.com # provide connectstring for management server host (default port: 1186) [ndbd] connect-string=ndb_mgmd.mysql.com # provide connectstring for management server host (default port: 1186) [ndb_mgm] connect-string=ndb_mgmd.mysql.com # provide location of cluster configuration file [ndb_mgmd] config-file=/etc/config.ini
(For more information on connectstrings, see
section 188.8.131.52 The MySQL Cluster
# my.cnf # example additions to my.cnf for MySQL Cluster # (will work on all versions) # enable ndbcluster storage engine, and provide connectstring for management # server host to the default port 2200 [mysqld] ndbcluster ndb-connectstring=ndb_mgmd.mysql.com:2200
Also starting with MySQL 4.1.8, the `my.cnf' file supports a
[mysql_cluster] section for settings to be read by and
affecting all executables in the cluster:
# cluster-specific settings [mysql_cluster] ndb-connectstring=ndb_mgmd.mysql.com:2200
Currently the configuration file is in INI format, and is named
`config.ini' by default. It is read by
startup and it can be placed anywhere. Its location and name are
specified by using
--config-file=[<path>]<filename> on the
command line with
ndb_mgmd. If the configuration file is not
ndb_mgmd will by default try to read a
`config.ini' located in the current working directory.
Default values are defined for most parameters, and can also be
specified in `config.ini'. To create a default value section,
simply add the word
DEFAULT to the section name. For example, DB
nodes are configured using
[DB] sections. If all DB nodes use the
same data memory size, and this is not the same as the default size,
[DB DEFAULT] section containing a
line to specify the default data memory size for all DB nodes.
The INI format consists of sections preceded by section headings (surrounded by square brackets), followed by the appropriate parameter names and values. One deviation from the standard format is that the parameter name and value can be separated by a colon (`:') as well as the equals sign (`='); another is that sections are not uniquely identified by name. Instead, unique entries (such as two different nodes of the same type) are identified by a unique ID.
At a minimum, the configuration file must define the computers and nodes involved in the cluster and on which computers these nodes are located. An example of a simple configuration file for a cluster consisting of one management server, two storage nodes and two MySQL servers is shown below:
# file "config.ini" - 2 DB nodes and 2 mysqld # This file is placed in the startup directory of ndb_mgmd, # i.e., the management server. # The first MySQL Server can be started from any host and the second # can only be started at the host mysqld_5.mysql.com # NDBD, MYSQLD, and NDB_MGMD are aliases for DB, API, and MGM respectively # [NDBD DEFAULT] NoOfReplicas= 2 DataDir= /var/lib/mysql-cluster [NDB_MGMD] Hostname= ndb_mgmd.mysql.com DataDir= /var/lib/mysql-cluster [NDBD] HostName= ndbd_2.mysql.com [NDBD] HostName= ndbd_3.mysql.com [MYSQLD] [MYSQLD] HostName= mysqld_5.mysql.com
There are six different sections in this configuration file:
[COMPUTER]: Defines the computers in the cluster.
[DB|NDBD]: Defines the cluster's storage nodes.
[API|MYSQLD]: Defines the cluster's MySQL server nodes.
[MGM|NDB_MGMD]: Defines the management server node in the cluster.
[TCP]: Defines TCP/IP connections between nodes in the cluster, with TCP/IP being the default connection protocol.
[SHM]: Defines shared-memory connections between nodes. This type of connection is available only in binaries that have been built with
Note that each node has its own section in the `config.ini'. For
instance, since this cluster has two storage nodes, the configuration
file contains two sections defining these nodes. (In the example above,
these sections are labelled with
[NDBD], but either or both of
them could have been labelled with
One can define
DEFAULT values for each section. As of MySQL
4.1.5, all parameter names are case insensitive.
With the exception of the MySQL Cluster management server
ndb_mgmd), each node making up a MySQL Cluster requires a
connectstring which points to the management server's location.
This is used in establishing a connection to the management server as
well as in performing other tasks depending on the node's role in the
cluster. The syntax for a
connectstring is as follows:
<connectstring> := [<nodeid-specification>,]<host-specification>[,<host-specification>] <nodeid-specification> := nodeid=<id> <host-specification> := <host>[:<port>] <id> is an integer larger than 1 identifying a node in config.ini <port> is an integer referring to a regular unix port <host> is a string which is a valid Internet host address
example 1 (long): "nodeid=2,myhost1:1100,myhost2:1100,192.168.0.3:1200" example 2 (short): "myhost1"
All nodes will use
localhost:1186 as the default
connectstring value if none is provided. If
omitted from the
connectstring, the default port is 1186.
(Note: Prior to MySQL 4.1.8, the default port was 2200.) This
port should always be available on the network, since it has been
assigned by IANA for this purpose (see
http://www.iana.org/assignments/port-numbers for details).
By listing multiple
<host-specification> values, it is possible
to designate several redundant management servers. A cluster node will
attempt to contact succesive management servers on each host in the
order specified, until a successful connection has been established.
There are a number of different ways to specify the connectstring:
connectstringto be used by all nodes in the cluster by placing it in a
[mysql-cluster]section in the management server's `my.cnf' file.
NDB_CONNECTSTRINGenvironment variable to contain the connectstring.
connectstringfor each executable into a text file named `Ndb.cfg' and place this file in the executable's startup directory.
The recommended method for specifying the
connectstring is to
set it on the command line or `my.cnf' fiel for each executable.
[COMPUTER] section has no real significance other than serving as
a way to avoid the need of defining host names for each node in the system.
All parameters mentioned here are required.
[MGM] section (or its alias
[NDB_MGMD]) is used to
configure the behavior of the management server. Either the
HostName parameter must be present.
All other parameters can be omitted and if so will assume their
CONSOLEoutputs the log to
SYSLOGsends the log to a
syslogfacility, possible values being one of
local7. (Note: Not every facility is necessarily supported by every operating system.)
FILEpipes the cluster log ouput to a regular file on the same machine. The following values can be specified:
filename: The name of the logfile.
maxsize: The maximum size to which the file can grow before logging rolls over to a new file. When this occurs, the old logfile is renamed by appending `.x' to the filename, where
xis the next number not yet used with this name.
maxfiles: The maximum number of logfiles.
FILE:filename=cluster.log,maxsize=1000000,maxfiles=6It is possible to specify multiple log destinations as shown here, using a semicolon-delimited string:
CONSOLE;SYSLOG:facility=local0;FILE:filename=/var/log/mgmdThe default value for the
<id>is the ID of the node.
0: The node will never be used as an arbitrator.
1: The node has high priority; that is, it will be preferred as an arbitrator over low-priority nodes.
2: Indicates a low-priority node which be used as an arbtrator only if a node with a higher priority is not available for that purpose.
ArbitrationRankto 1 (the default) and that of all API or server nodes to 0.
[MGM]LogDestinationas discussed previously in this section.)
[DB] section (or its alias
[NDBD]) is used to configure
the behavior of the storage nodes. There are many parameters specified that
controls the buffer sizes, pool sizes, timeout parameters and so forth. The
only mandatory parameter is either
HostName and the parameter
NoOfReplicas which need to be
defined in the
[DB DEFAULT] section. Most parameters should be set in
[DB DEFAULT] section. Only parameters explicitly stated as
possible to have local values are allowed to be changed in the
ExecuteOnComputer needs to be
defined in the local
Id value (that is, the identification of the storage node)
can now be allocated when the node is started. It is still possible to
assign a node ID in the configuration file.
For each parameter it is possible to use k, M, or G as a suffix to indicate units of 1024, 1024*1024, or 1024*1024*1024. For example, 100k means 102400. Parameters and values are currently case sensitive.
[DB DEFAULT]section because it is a global parameter. It defines the number of replicas for each table stored in the cluster. This parameter also specifies the size of node groups. A node group is a set of nodes that all store the same information. Node groups are formed implicitly. The first node group is formed by the storage nodes with the lowest node identities. And the next by the next lowest node identities. As an example presume we have 4 storage nodes and
NoOfReplicasis set to 2. The four storage nodes have node IDs 2, 3, 4 and 5. Then the first node group will be formed by node 2 and node 3. The second node group will be formed by node 4 and node 5. It is important to configure the cluster in such a manner such that nodes in the same node groups are not placed on the same computer. This would cause a single HW failure to cause a cluster crash. If no node identities are provided then the order of the storage nodes will be the determining factor for the node group. The actual node group assigned will be printed by the
SHOWcommand in the management client. There is no default value and the maximum number is 4.
DataDir. The directory must be created before starting the
ndbdprocess. If you use the recommended directory hierarchy, you will use a directory `/var/lib/mysql-cluster'. Under this directory a directory `ndb_2_fs' will be created (if node ID was 2) which will be the file system for that node.
FileSystemPath/`BACKUP' will be choosen.
IndexMemory are the parameters that specify
the size of memory segments used to store the actual records and their
indexes. It is important to understand how
IndexMemory are used to understand how to set these parameters.
For most uses, they need to be updated to reflect the usage of the
DataMemorywill be allocated in memory so it is important that the machine contains enough memory to handle the
DataMemoryis used to store two things. It stores the actual records. Each record is currently of fixed size. So
VARCHARcolumns are stored as fixed size columns. There is an overhead on each record of 16 bytes normally. Additionally each record is stored in a 32KB page with 128 byte page overhead. There will also be a small amount of waste for each page because records are only stored in one page. The maximum record size for the columns currently is 8052 bytes. The
DataMemoryis also used to store ordered indexes. Ordered indexes uses about 10 bytes per record. Each record in the table is always represented in the ordered index. The
DataMemoryconsists of 32KB pages. These pages are allocated to partitions of the tables. Each table is normally partitioned with the same number of partitions as there are storage nodes in the cluster. Thus for each node there are the same number of partitions (=fragments) as the
NoOfReplicasis set to. Once a page has been allocated to a partition it is currently not possible to bring it back to the pool of free pages. The method to restore pages to the pool is by deleting the table. Performing a node recovery also will compress the partition because all records are inserted into an empty partition from another live node. Another important aspect is that the
DataMemoryalso contains UNDO information for records. For each update of a record a copy record is allocated in the
DataMemory. Also each copy record will also have an instance in the ordered indexes of the table. Unique hash indexes are updated only when the unique index columns are updated and in that case a new entry in the index table is inserted and at commit the old entry is deleted. Thus it is necessary also to allocate memory to be able to handle the largest transactions which are performed in the cluster. Performing large transactions has no advantage in MySQL Cluster other than the consistency of using transactions which is the whole idea of transactions. It is not faster and consumes large amounts of memory. The default
DataMemorysize is 80MB. The minimum size is 1MB. There is no maximum size, but in reality the maximum size has to be adapted so that the process doesn't start swapping when using the maximum size of the memory.
IndexMemoryis the parameter that controls the amount of storage used for hash indexes in MySQL Cluster. Hash indexes are always used for primary key indexes, unique indexes, and unique constraints. Actually when defining a primary key and a unique index there will be two indexes created in MySQL Cluster. One index is a hash index which is used for all tuple accesses and also for lock handling. It is also used to ensure unique constraints. The size of the hash index is 25 bytes plus the size of the primary key. For primary keys larger than 32 bytes another 8 bytes is added for some internal references. Thus for a table defined as
CREATE TABLE example ( a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, PRIMARY KEY(a), UNIQUE(b) ) ENGINE=NDBCLUSTER;We will have 12 bytes overhead (having no nullable columns saves 4 bytes of overhead) plus 12 bytes of data per record. In addition we will have two ordered indexes on a and b consuming about 10 bytes each per record. We will also have a primary key hash index in the base table with roughly 29 bytes per record. The unique constraint is implemented by a separate table with b as primary key and a as a column. This table will consume another 29 bytes of index memory per record in the table and also 12 bytes of overhead plus 8 bytes of data in the record part. Thus for one million records, we will need 58MB of index memory to handle the hash indexes for the primary key and the unique constraint. For the
DataMemorypart we will need 64MB of memory to handle the records of the base table and the unique index table plus the two ordered index tables. The conclusion is that hash indexes takes up a fair amount of memory space but in return they provide very fast access to the data. They are also used in MySQL Cluster to handle uniqueness constraints. Currently the only partitioning algorithm is hashing and the ordered indexes are local to each node and can thus not be used to handle uniqueness constraints in the general case. An important point for both
DataMemoryis that the total database size is the sum of all
IndexMemoryin each node group. Each node group is used to store replicated information, so if there are four nodes with 2 replicas there will be two node groups and thus the total
DataMemoryavailable is 2*
DataMemoryin each of the nodes. Another important point is about changes of
IndexMemory. First of all, it is highly recommended to have the same amount of
IndexMemoryin all nodes. Since data is distributed evenly over all nodes in the cluster the size available is no better than the smallest sized node in the cluster times the number of node groups.
IndexMemorycan be changed, but it is dangerous to decrease them because that can easily lead to a node that will not be able to restart or even a cluster not being able to restart since there is not enough memory space for the tables needed to restore into the starting node. Increasing them should be quite okay, but it is recommended that such upgrades are performed in the same manner as a software upgrade where first the configuration file is updated, then the management server is restarted and then one storage node at a time is restarted by command. More
IndexMemoryis not used due to updates but inserts are inserted immediately and deletes are not deleted until the transaction is committed. The default
IndexMemorysize is 18MB. The minimum size is 1MB.
The next three parameters are important because they affect the number of
parallel transactions and the sizes of transactions that can be handled by
MaxNoOfConcurrentTransactions sets the number of
parallel transactions possible in a node and
MaxNoOfConcurrentOperations sets the number of records that can be
in update phase or locked simultaneously.
Both of these parameters and particularly
MaxNoOfConcurrentOperations are likely targets for users setting
specific values and not using the default value. The default value is set
for systems using small transactions and to ensure not using too much
memory in the default case.
MaxNoOfConcurrentOperationswill always be used to calculate the number of operation records in the transaction coordinator part of the node. It is also important to have an idea of the memory requirements for those operation records. In MySQL 4.1.5, operation records consume about 1KB per record. This figure will shrink in future 5.x versions.
MaxNoOfConcurrentOperationswhich fits systems with many simultaneous, not very large transactions. If the configuration needs to handle one very large transaction at a time and there are many nodes then it is a good idea to configure this separately.
The next set of parameters are used for temporary storage in the midst of executing a part of a query in the cluster. All of these records will have been released when the query part is completed and is waiting for the commit or rollback.
Most of the defaults for these parameters will be okay for most users. Some high-end users might want to increase those to enable more parallelism in the system and some low-end users might want to decrease them to save memory.
MaxNoOfConcurrentOperations. The default value of this parameter is 8192. Only in rare cases of extremely high parallelism using unique hash indexes should this parameter be necessary to increase. To decrease could be performed for memory savings if the DBA is certain that such high parallelism is not occurring in the cluster.
MaxNoOfFiredTriggersis 4000. Normally this value should be sufficient for most systems. In some cases it could be decreased if the DBA feels certain the parallelism in the cluster is not so high. This record is used when an operation is performed that affects a unique hash index. Updating a column that is part of a unique hash index or inserting/deleting a record in a table with unique hash indexes will fire an insert or delete in the index table. This record is used to represent this index table operation while its waiting for the original operation that fired it to complete. Thus it is short lived but can still need a fair amount of records in its pool for temporary situations with many parallel write operations on a base table containing a set of unique hash indexes.
ZATTRBUF_FILESIZEin Dbtc.hpp. A similar buffer for key info exists which contains 4000*16 bytes, 62.5KB of buffer space. The parameter in this case is
Dbtcis the module for handling the transaction coordination. Similar parameters exist in the
Dblqhmodule taking care of the reads and updates where the data is located. In `Dblqh.hpp' with
ZATTRINBUF_FILESIZEset to 10000*128 bytes (1250KB) and
ZDATABUF_FILE_SIZE, set to 10000*16 bytes (roughly 156KB) of buffer space. No known instances of that any of those compile time limits haven't been big enough has been reported so far or discovered by any of our extensive test suites. The default size of the
MaxNoOfConcurrentScansis 256. The maximum value is 500. This parameter will always specify the number of scans possible in the transaction coordinator. If the number of local scan records is not provided it is calculated as the product of
MaxNoOfConcurrentScansand the number of storage nodes in the system.
ScanBatchSizedefined in the API nodes.
Out of log file space temporarily. This condition will prevail until a checkpoint has completed and the log tail can be moved forward.
The next set of parameters defines the pool sizes for metadata objects. It is necessary to define the maximum number of attributes, tables, indexes, and trigger objects used by indexes, events and replication between clusters.
BLOBdata type an extra table is used to store most of the
BLOBdata. These tables also must be taken into account when defining the number of tables. The default value of this parameter is 128. The minimum is 8 and the maximum is 1600. Each table object consumes around 20KB in each node.
USING HASHoption in the unique index definition. The default value is 64. Each index will consume around 15KB per node.
MaxNoOfUniqueHashIndexesinstead. This parameter is only used by unique hash indexes. There needs to be one record in this pool for each unique hash index defined in the cluster. The default value of this parameter is 128.
There is a set of boolean parameters affecting the behavior of storage nodes. Boolean parameters can be specified to true by setting it to Y or 1 and to false by setting it to N or 0.
Diskless, in this case even the tables doesn't exist anymore after a crash. Enabling this feature can be done by either setting it to Y or 1. When this feature is enabled, backups will be performed but will not be stored because there is no "disk". In future releases it is likely to make the backup diskless a separate configurable parameter. The default is that this feature is not enabled.
There are quite a few parameters specifying timeouts and time intervals between various actions in the storage nodes. Most of the timeouts are specified in milliseconds with a few exceptions which will be mentioned below.
StartPartialTimeoutbut is still in a possibly partitioned state one waits until also this timeout has passed. The default timeout is 60000 milliseconds (60 seconds).
NoOfDiskPagesToDiskAfterRestartACC. This parameter handles the limitation of writes from the
DataMemory. So this parameter specifies how quickly local checkpoints will be executed. This parameter is important in connection with
IndexMemory. The default value is 40 (3.2MB of data pages per second).
NoOfDiskPagesToDiskAfterRestartTUPbut limits the speed of writing index pages from
IndexMemory. The default value of this parameter is 20 (1.6MB per second).
NoOfDiskPagesToDiskAfterRestartACC, only it does it for local checkpoints executed in the node as part of a local checkpoint when the node is restarting. As part of all node restarts a local checkpoint is always performed. Since during a node restart it is possible to use a higher speed of writing to disk because fewer activities are performed in the node due to the restart phase. This parameter handles the
DataMemorypart. The default value is 40 (3.2MB per second).
IndexMemorypart of local checkpoint. The default value is 20 (1.6MB per second).
A number of new configuration parameters were introduced in MySQL 4.1.5. These correspond to values that previously were compile time parameters. The main reason for this is to enable the advanced user to have more control of the size of the process and adjust various buffer sizes according to his needs.
All of these buffers are used as front-ends to the file system when
writing log records of various kinds to disk. If the node runs with
Diskless then these parameters can most definitely be set to their
minimum values because all disk writes are faked as okay by the file system
abstraction layer in the
NDB storage engine.
NDBstorage engine uses a recovery scheme based on a consistent checkpoint together with an operational REDO log. In order to produce a consistent checkpoint without blocking the entire system for writes, UNDO logging is done while performing the local checkpoint. The UNDO logging is only activated on one fragment of one table at a time. This optimization is possible because tables are entirely stored in main memory. This buffer is used for the updates on the primary key hash index. Inserts and deletes rearrange the hash index and the
NDBstorage engine writes UNDO log records that map all physical changes to an index page such that they can be undone at a system restart. It also logs all active insert operations at the start of a local checkpoint for the fragment. Reads and updates only set lock bits and update a header in the hash index entry. These changes are handled by the page write algorithm to ensure that these operations need no UNDO logging. This buffer is 2MB by default. The minimum value is 1MB. For most applications this is good enough. Applications doing extremely heavy inserts and deletes together with large transactions using large primary keys might need to extend this buffer. If this buffer is too small, the
NDBstorage engine issues the internal error code 677 which will be translated into "Index UNDO buffers overloaded".
UndoIndexBufferbut is used for the data part. This buffer is used during local checkpoint of a fragment and inserts, deletes, and updates use the buffer. Since these UNDO log entries tend to be bigger and more things are logged, the buffer is also bigger by default. It is set to 16MB by default. For some applications this might be too conservative and they might want to decrease this size, the minimum size is 1MB. It should be rare that applications need to increase this buffer size. If there is a need for this it is a good idea to check if the disks can actually handle the load that the update activity in the database causes. If they cannot then no size of this buffer will be big enough. If this buffer is too small and gets congested, the
NDBstorage engine issues the internal error code 891 which will be translated to "Data UNDO buffers overloaded".
NDBstorage engine issues the internal error code 1221 which will be translated into "REDO log buffers overloaded".
For cluster management, it is important to be able to control the amount of log messages sent to stdout for various event types. The possible events will be listed in this manual soon. There are 16 levels possible from level 0 to level 15. Setting event reporting to level 15 means receiving all event reports of that category and setting it to 0 means getting no event reports in that category.
The reason why most defaults are set to 0 and thus not causing any output to stdout is that the same message is sent to the cluster log in the management server. Only the startup message is by default generated to stdout.
A similar set of levels can be set in management client to define what levels to record in the cluster log.
There is a set of parameters defining memory buffers that are set aside for online backup execution.
BackupWriteSizeparameter. When sending data to the disk, the backup can continue filling this buffer until it runs out of buffer space. When running out of buffer space, it will simply stop the scan and wait until some disk writes return and thus free up memory buffers to use for further scanning. The default value is 2MB.
BackupDataBufferSizeexcept that when this part runs out of buffer space, it causes the backup to fail due to lack of backup buffers. Thus the size of this buffer must be big enough to handle the load caused by write activities during the backup execution. The default parameter should be big enough. Actually it is more likely that a backup failure is caused by a disk not able to write as quickly as it should. If the disk subsystem is not dimensioned for the write load caused by the applications this will create a cluster which will have great difficulties to perform the desired actions. It is important to dimension the nodes in such a manner that the processors becomes the bottleneck rather than the disks or the network connections. The default value is 2MB.
BackupLogBufferSize. The default value is 4MB.
[API] section (with its alias
[MYSQLD]) defines the
behavior of the MySQL server. No parameter is mandatory. If no computer or
host name is provided, then any host can use this API node.
TCP/IP is the default transport mechanism for establishing connections in MySQL Cluster. It is actually not necessary to define any connection because there will be a one connection setup between each of the storage nodes, between each storage node, and all MySQL server nodes and between each storage node and the management server.
It is only necessary to define a connection if it is necessary to change
the default values of the connection. In that case it is necessary to
define at least
NodeId2 and the parameters to
It is also possible to change the default values by setting the parameters
[TCP DEFAULT] section.
[TCP DEFAULT]section normally. This parameter should no longer be used. Use the parameter ServerPort on storage nodes instead.
Shared memory segments are currently supported only for special builds of
MySQL Cluster using the
implementation will most likely change. When defining shared memory as the
connection method it is necessary to define at least
ShmKey. All other parameters have default values
that will work out fine in most cases.
SCI Transporters as connection between nodes in the MySQL Cluster is only
supported for special builds of MySQL Cluster using the
--with-ndb-sci=/your/path/to/SCI. The path should point
to a directory that contains at least a lib and a include directory where
SISCI libraries and header files are provided.
It is strongly recommended to only use SCI Transporters for communication between ndbd processes. Also using SCI Transporters will mean that the ndbd process will never sleep so use SCI Transporters only for machines with at least 2 CPU's which are dedicated for use by ndbd process(es). There should be at least 1 CPU per ndbd process in this case and in addition at least one more is needed to also handle OS activities.
There are four processes that are important to know about when using MySQL Cluster. We will cover how to work with those processes, which options to use when starting and so forth.
mysqld is the traditional MySQL server process. To be used with
MySQL Cluster it needs to be built with support for the NDB Cluster storage
engine. If the
mysqld binary has been built in such a manner, the
NDB Cluster storage engine is still disabled by default.
To enable the NDB Cluster storage engine there are two ways. Either use
--ndbcluster as a startup option when starting
insert a line with
ndbcluster in the
[mysqld] section of your
An easy way to verify that your server runs with support for the
NDB Cluster storage engine is to issue the command
SHOW ENGINES from a
You should see
YES for the row listing
If you see
NO, you are not running a
mysqld that is compiled
NDB Cluster support enabled. If you see
DISABLED, then you
need to enable it in the `my.cnf' configuration file.
The MySQL server needs to know how to get the configuration of the cluster. To access this configuration, it needs to know three things:
The node ID can be skipped from MySQL 4.1.5 on, because a node ID can be dynamically allocated.
ndb-connectstring is used to specify the
connectstring either when starting
mysqld or in `my.cnf'.
See also section 184.108.40.206 The MySQL Cluster
connectstring for more info on connectstrings.
shell> mysqld --ndb-connectstring=ndb_mgmd.mysql.com:1186
ndb_mgmd.mysql.com is the host where the management server resides,
and it is listening to port 1186.
With this setup the MySQL server will be a full citizen of MySQL Cluster and will be fully aware of all storage nodes in the cluster and their status. It will setup connection to all storage nodes and will be able to use any storage node as a transaction coordinator and to access their data for reading and updating.
ndbd, the Storage Engine Node Process
ndbd is the process that is used to handle all the data in the
tables using the NDB Cluster storage engine. This is the process that
contains all the logic of distributed transaction handling, node recovery,
checkpointing to disk, online backup, and lots of other functionality.
In a cluster there is a set of
ndbd processes cooperating in
handling the data. These processes can execute on the same computer or on
different computers, in a completely configurable manner.
Before MySQL 4.1.5,
ndbd process should start from a separate
directory. The reason for this was that
ndbd generates a set of log
files in its starting directory.
In MySQL 4.1.5, this was changed such that the files are placed in the
directory specified by
DataDir in the configuration file. Thus
ndbd can be started from anywhere.
These log files are (the 2 is the node ID):
ndbdprocess has encountered and a smaller error string and reference to a trace file for this crash. An entry could like this:
Date/Time: Saturday 31 January 2004 - 00:20:01 Type of error: error Message: Internal program error (failed ndbrequire) Fault ID: 2341 Problem data: DbtupFixAlloc.cpp Object of reference: DBTUP (Line: 173) ProgramName: NDB Kernel ProcessID: 14909 TraceFile: ndb_2_trace.log.2 ***EOM***
MySQL Cluster Troubleshooting. There can be a configurable number of those trace files in the directory before old files are overwritten. 1 in this context is simply the number of the trace file.
ndbdprocess. 2 in this context is the node ID. (this file only applies if
ndbdis started in dameon mode which is default from 4.1.5, was `node2.out' in version 4.1.3)
ndbdwhere it is possible to trace all incoming, outgoing and internal messages with their data in the
It is recommended to not use a directory mounted through NFS because in some environments that can cause problems with the lock on the pid-file remaining even after the process has stopped.
Also when starting the
ndbd process it may be necessary to specify
the hostname of the management server and the port it is listening to,
optionally one may specify node ID the process is to use,
see section 220.127.116.11 The MySQL Cluster
shell> ndbd --connect-string="nodeid=2;host=ndb_mgmd.mysql.com:1186"
ndbd starts it will actually start two processes. The starting
process is called the "angel" and its only job is to discover when the
execution process has completed, and then restart the
ndbd process if
configured to do so. Thus if one attempts to kill
ndbd through the
kill command in Unix, it is necessary to kill both processes. A
more proper way to handle the stopping of
ndbd processes is to use
the management client and stop the process from there.
The execution process uses one thread for all activities in reading,
writing, and scanning data and all other activities. This thread is designed
with asynchronous programming so it can easily handle thousands of
concurrent activites. In addition there is a watch-dog thread supervising
the execution thread to ensure it doesn't stop in an eternal loop or other
problem. There is a pool of threads handling file I/O. Each thread can
handle one open file. In addition threads can be used for connection
activities of the transporters in the
ndbd process. Thus in a system
that performs a large number of activities including update activities the
ndbd process will consume up to about 2 CPUs if allowed to. Thus in
a large SMP box with many CPUs it is recommended to use several
ndbd processes which are configured to be part of different node
ndb_mgmd, the Management Server Process
The management server is the process that reads the configuration file of the cluster and distributes this information to all nodes in the cluster requesting it. It also maintains the log of cluster activities. Management clients can connect to the management server and use commands to check status of the cluster in various aspects.
As of MySQL 4.1.5, it is no longer necessary to specify a connectstring when starting the management server. However, if you are using several management servers, a connectstring should be provided and each node in the cluster should specify its nodeid explicitly.
The following files are created or used by
ndb_mgmd in its starting
ndb_mgmd. From MySQL 4.1.5, the log and PID files will
be placed in the
DataDir specified in the configuration file:
MySQL Cluster Configuration.
ndb_mgm, the Management Client Process
The final important process to know about is the management client. This process is not needed to run the cluster. Its value lies in its ability to check status of the cluster, start backups, and perform other management activities. It does so by providing access to a set of commands.
Actually the management client is using a C API that is used to access the management server so for advanced users it is also possible to program dedicated management processes which can do similar things as the management client can do.
When starting the management client, it is necessary to state the hostname and port of the management server as in the example below. The default is localhost as host and port number 1186 (was 2200 prior to version 4.1.8).
shell> ndb_mgm localhost 1186
All MySQL Cluster executables (except
mysqld) takes the
following options as of 4.1.8. If you're running an earlier version
please read carefully, as we have made changes in some of these
switches in order to make them consistent between the different
executables and with
mysqld. (For example:
-v in earlier versions.) Note also that you can use the
-? option to see what is supported in your version.
-?, --usage, --help
ndbdprocess. The version number is the MySQL Cluster version number. It is important because at startup the MySQL Cluster processes verifies that the versions of the processes in the cluster can co-exist in the cluster. It is also important for online software upgrade of MySQL Cluster (see section
Software Upgrade of MySQL Cluster).
-c connect_string (not
ndb_mgmd), --connect-string connect_string
ndb_mgmddoes not take the -c option until 5.0, as it currently specifies the config file). Available with
shell> ndbd --connect-string="nodeid=2;host=ndb_mgmd.mysql.com:1186"
NDB Clusterstorage engine the default disabling of support for the
NDBstorage engine can be overruled by using this option. Using the
NDB Clusterstorage engine is necessary for using MySQL Cluster.
NDB Clusterstorage engine. This is disabled by default for binaries where it is included. So this option only applies if the server was configured to use the
NDB Clusterstorage engine.
NDBstorage engine, it is possible to point out the management server that distributes the cluster configuration by setting the connect string option.
For common options see section 16.4.5 Command Options for MySQL Cluster Processes.
ndbdto execute as a daemon process. From MySQL 4.1.5 on, this is the default behavior.
ndbdnot to start as a daemon process. Useful when
ndbdis debugged and one wants printouts on the screen.
ndbdto perform an initial start. An initial start will erase any files created by earlier
ndbdinstances for recovery. It will also recreate recovery log files which on some operating systems can take a substantial amount of time. An initial start is only to be used at the very first start of the
ndbdprocess. It removes all files from the file system and creates all REDO log files. When performing a software upgrade which has changed the file contents on any files it is also necessary to use this option when restarting the node with a new software version of
ndbd. Finally it could also be used as a final resort if for some reason the node restart or system restart doesn't work. In this case be aware that destroying the contents of the file system means that this node can no longer be used to restore data. This option does not affect any backup files created. The previous possibility to use
-ifor this option was removed to ensure that this option is not used by mistake.
ndbdnot to automatically start.
ndbdwill connect to the management server and get the configuration and initialise communication objects. It will not start the execution engine until requested to do so by the management server. The management server can request by command issued by the management client.
For common options see section 16.4.5 Command Options for MySQL Cluster Processes.
-f filename (from 4.1.8), --config-file=filename, -c filename (obsolete from 5.0)
ndb_mgmdto start as a daemon process. This is the default behavior.
For common options see section 16.4.5 Command Options for MySQL Cluster Processes.
localhostand the default port is 1186 (was 2200 prior to version 4.1.8).
Managing a MySQL Cluster involves a number of activities. The first activity is to configure and startup MySQL Cluster. This is covered by the sections section 16.3 MySQL Cluster Configuration and section 16.4 Process Management in MySQL Cluster. This section covers how to manage a running MySQL Cluster.
There are essentially two ways of actively managing a running MySQL Cluster.
The first is by commands entered into the management client where status of
cluster can be checked, log levels changed, backups started and stopped and
nodes can be stopped and started. The second method is to study the output
in the cluster log. The cluster log is directed towards the
`ndb_2_cluster.log' in the
DataDir directory of the management
server. The cluster log contains event reports generated from the
ndbd processes in the cluster. It is also possible to send the
cluster log entries to a Unix system log.
In addition to the central configuration file, the cluster may also be controlled through a command line interface. The command line interface is available through a separate management client process. This is the main administrative interface to a running cluster.
The management client has the following basic commands.
<id> denotes either a database node id (e.g. 21) or the
ALL that indicates that the command should be applied
to all database nodes in the cluster.
<id>or all database nodes.
<id>or all database nodes.
<id> RESTART [-N] [-I]
<id>or all database nodes.
ENTER SINGLE USER MODE <id>
<id>is allowed to access the database system.
EXIT SINGLE USER MODE
Commands for the event logs are given in the next section and commands for backup and restore are given in a separate section on these topics.
MySQL Cluster has two event logs, the cluster log and the node log.
Note: The cluster log is the recommended log. The node log is only intended to be used during application development or for debugging application code.
Each reportable event has the following properties:
The two logs (the cluster log and the node log) can be filtered on these properties.
The following management commands are related to the cluster log:
<id> CLUSTERLOG <category>=<threshold>
CLUSTERLOG FILTER <severity>
The following table describes the default setting (for all database nodes) of the cluster log category threshold. If an event has a priority with a value lower than or equal to the priority threshold, then it is reported in the cluster log.
Note that the events are reported per database node and that the thresholds can be set differently on different nodes.
|Category||Default threshold (All database nodes)|
The threshold is used to filter events within each category.
For example: a
STARTUP event with a priority of 3 is never sent
unless the threshold for
STARTUP is changed to 3 or lower.
Only events with priority 3 or lower are sent if the threshold is 3.
The event severities are (corresponds to UNIX syslog levels):
|1||ALERT||A condition that should be corrected immediately, such as a corrupted system database|
|2||CRITICAL||Critical conditions, such as device errors or out of resources|
|3||ERROR||Conditions that should be corrected, such as configuration errors|
|4||WARNING||Conditions that are not error conditions but might require handling|
|6||DEBUG||Messages used during development of NDB Cluster|
LOG_NOTICE are not used/mapped.
The event severities can be turned on or off. If the severity is on then all events with priority less than or equal to the category thresholds are logged. If the severity is off then no events belonging to the severity are logged.
All reportable events are listed below.
|DB nodes connected||CONNECTION||8||INFO|
|DB nodes disconnected||CONNECTION||8||INFO|
|Communication closed||CONNECTION||8||INFO||API & DB nodes connection closed|
|Communication opened||CONNECTION||8||INFO||API & DB nodes connection opened|
|Global checkpoint started||CHECKPOINT||9||INFO||Start of a GCP, i.e., REDO log is written to disk|
|Global checkpoint completed||CHECKPOINT||10||INFO||GCP finished|
|Local checkpoint started||CHECKPOINT||7||INFO||Start of local check pointing, i.e., data is written to disk. LCP Id and GCI Ids (keep and oldest restorable)|
|Local checkpoint completed||CHECKPOINT||8||INFO||LCP finished|
|LCP stopped in calc keep GCI||CHECKPOINT||0||ALERT||LCP stopped!|
|Local checkpoint fragment completed||CHECKPOINT||11||INFO||A LCP on a fragment has been completed|
|Report undo log blocked||CHECKPOINT||7||INFO||Reports undo logging blocked due buffer near to overflow|
|DB node start phases initiated||STARTUP||1||INFO||NDB Cluster starting|
|DB node all start phases completed||STARTUP||1||INFO||NDB Cluster started|
|Internal start signal received STTORRY||STARTUP||15||INFO||Internal start signal to blocks received after restart finished|
|DB node start phase X completed||STARTUP||4||INFO||A start phase has completed|
|Node has been successfully included into the cluster||STARTUP||3||INFO||President node, own node and dynamic id is shown|
|Node has been refused to be included into the cluster||STARTUP||8||INFO|
|DB node neighbours||STARTUP||8||INFO||Show left and right DB nodes neighbours|
|DB node shutdown initiated||STARTUP||1||INFO|
|DB node shutdown aborted||STARTUP||1||INFO|
|New REDO log started||STARTUP||10||INFO||GCI keep X, newest restorable GCI Y|
|New log started||STARTUP||10||INFO||Log part X, start MB Y, stop MB Z|
|Undo records executed||STARTUP||15||INFO|
|Completed copying of dictionary information||NODERESTART||8||INFO|
|Completed copying distribution information||NODERESTART||8||INFO|
|Starting to copy fragments||NODERESTART||8||INFO|
|Completed copying a fragment||NODERESTART||10||INFO|
|Completed copying all fragments||NODERESTART||8||INFO|
|Node failure phase completed||NODERESTART||8||ALERT||Reports node failure phases|
|Node has failed, node state was X||NODERESTART||8||ALERT||Reports that a node has failed|
|Report whether an arbitrator is found or not||NODERESTART||6||INFO||7 different cases|
|- President restarts arbitration thread [state=X]|
|- Prepare arbitrator node X [ticket=Y]|
|- Receive arbitrator node X [ticket=Y]|
|- Started arbitrator node X [ticket=Y]|
|- Lost arbitrator node X - process failure [state=Y]|
|- Lost arbitrator node X - process exit [state=Y]|
|- Lost arbitrator node X <error msg>[state=Y]|
|Report arbitrator results||NODERESTART||2||ALERT||8 different results|
|- Arbitration check lost - less than 1/2 nodes left|
|- Arbitration check won - node group majority|
|- Arbitration check lost - missing node group|
|- Network partitioning - arbitration required|
|- Arbitration won - positive reply from node X|
|- Arbitration lost - negative reply from node X|
|- Network partitioning - no arbitrator available|
|- Network partitioning - no arbitrator configured|
|GCP take over started||NODERESTART||7||INFO|
|GCP take over completed||NODERESTART||7||INFO|
|LCP take over started||NODERESTART||7||INFO|
|LCP take completed (state = X)||NODERESTART||7||INFO|
|Report transaction statistics||STATISTICS||8||INFO||# of: transactions, commits, reads, simple reads, writes, concurrent operations, attribute info, aborts|
|Report operations||STATISTICS||8||INFO||# of operations|
|Report table create||STATISTICS||7||INFO|
|Report job scheduling statistics||STATISTICS||9||INFO||Mean Internal job scheduling statistics|
|Sent # of bytes||STATISTICS||9||INFO||Mean # of bytes sent to node X|
|Received # of bytes||STATISTICS||9||INFO||Mean # of bytes received from node X|
|Memory usage||STATISTICS||5||INFO||Data and Index memory usage (80%, 90% and 100%)|
|Missed heartbeats||ERROR||8||WARNING||Node X missed heartbeat # Y|
|Dead due to missed heartbeat||ERROR||8||ALERT||Node X declared dead due to missed heartbeat|
|General warning events||ERROR||2||WARNING|
|Sent heartbeat||INFO||12||INFO||Heartbeat sent to node X|
|Create log bytes||INFO||11||INFO||Log part, log file, MB|
|General info events||INFO||2||INFO|
An event report has the following format in the logs:
<date & time in GMT> [<any string>] <event severity> -- <log message> 09:19:30 2003-04-24 [NDB] INFO -- Node 4 Start phase 4 completed
Single user mode allows the database administrator to restrict access to the database system to only one application (API node). When entering single user mode all connections to all APIs will be gracefully closed and no transactions are allowed to be started. All running transactions are aborted.
When the cluster has entered single user mode (use the all status command to see when the state has entered the single user mode), only the allowed API node is granted access to the database.
ENTER SINGLE USER MODE 5
After executing this command and after cluster has entered the single user mode, the API node with node id 5 becomes the single user of the cluster.
The node specified in the command above must be a MySQL Server node. Any attempt to specify any other type of node will be rejected.
Note: if the node with id 5 is running when executing
ENTER SINGLE USER MODE 5, all transactions running on node 5
will be aborted, the connection is closed, and the server must be
EXIT SINGLE USER MODE alters the state of the
cluster DB nodes from ``single user mode'' to ``started''.
MySQL Servers waiting for a connection, i.e. for the cluster to become
ready, are now allowed to connect. The MySQL Server denoted as the
single user continues to run, if it is connected, during and after the
EXIT SINGLE USER MODE
Best practice in case of node failures when running in single user mode is to:
Or restart database nodes prior to entering single user mode.
This section describes how to create a backup and later restore the backup to a database.
A backup is a snapshot of the database at a given time. The backup contains three main parts:
Each of these parts is saved on all nodes participating in a backup.
During backup each node saves these three parts to disk into three files:
Above <BackupId> is an identifier for the backup and <NodeId> is the node id of the node creating the file.
Before starting make sure that the cluster is properly configured for backups.
Using the management server to abort a backup:
ABORT BACKUP <BACKUPID>. The number <BackupId> is the identifier of the backup that is included in the response of the management server when the backup is started, i.e. in the message ``Backup <BackupId> started''. The identifier is also saved in the cluster log (cluster.log).
Note that if there is not any backup with id <BackupId> running when it is aborted, the management server will not reply anything. However there will be a line in the cluster.log mentioning that an ``invalid'' abort command has been filed.
The restore program is implemented as seperate command line utility. It reads the files created from the backup and inserts the stored information into the database. The restore program has to be executed once for each set of backup files, i.e. as many times as there were database nodes running when the backup we created.
The first time you run the restore program you also need to restore
the meta data, i.e. create tables. The restore program acts as an
API to the cluster and therefore needs a free connection to connect
to. This can be verified with the
ndb_mgm command SHOW.
-c <connectstring> may
be used to locate the MGM node (see section 18.104.22.168 The MySQL Cluster
info on connectstrings).
The backup files must be present in
the directory given as an argument to the restore program. The backup
can be restored to a database with a different configuration than it was
created from. For example, consider if a backup (with id 12) created
in a cluster with two database nodes (with node id 2 and node id 3)
that should be restored to a cluster with four nodes. The restore
program then has to be executed two times (one for each database node
in the cluster where the backup was taken) as described in the box
Note: for rapid restore, the data may be restored in parallell (provided that there are enough free API connections available). Note however that the data files must always be applied before the logs.
Note: the cluster should have an empty database when starting to restore a backup.
There are four configuration parameters for backup:
If an error code is returned when issuing a backup request, then check that there is enough memory allocated for the backup (i.e. the configuration parameters). Also check that there is enough space on the hard drive partition of the backup target.
Already before design of NDB Cluster started in 1996 it was evident that one of the major problems of building parallel databases is the communication between the nodes in the network. Thus from the very beginning NDB Cluster was designed with a transporter concept to allow for different transporters.
At the moment the code base includes 4 different transporters where 3 of them are currently working. Most users today uses TCP/IP over Ethernet since this exists in all machines. This is also by far the most well-tested transporter in MySQL Cluster.
Within MySQL we are working hard to ensure that communication with the ndbd process is made in as large chunks as possible since this will benefit all communication medias since all means of transportation benefits from sending large messages over small messages.
For users which desire top performance it is however also possible to use cluster interconnects to increase performance even further. There are two ways to achieve this, either a transporter can be designed to handle this case, or one can use socket implementations that bypass the TCP/IP stack to a small or large extent.
We have made some experiments with both those variants using SCI technology developed by Dolphin (www.dolphinics.no).
In this section we will show how one can use a cluster configured for normal TCP/IP communication to instead use SCI Sockets. Prerequisites for doing this is that the machines to communicate needs to be equipped with SCI cards. This documentation is based on the SCI Socket version 2.3.0 as of 1 october 2004.
To use SCI Sockets one can use any version of MySQL Cluster. The tests were performed on an early 4.1.6 version. No special builds are needed since it uses normal socket calls which is the normal configuration set-up for MySQL Cluster. SCI Sockets are only supported on Linux 2.4 and 2.6 kernels at the moment. SCI Transporters works on more OS's although only Linux 2.4 have been verified.
There are essentially four things needed to enable SCI Sockets. First it is necessary to build the SCI Socket libraries. Second the SCI Socket kernel libraries need to be installed. Third one or two configuration files needs to be installed. At last the SCI Socket kernel library needs to be enabled either for the entire machine or for the shell where the MySQL Cluster processes are started from. This process needs to be repeated for each machine in cluster which will use SCI Sockets to communicate.
Two packages need to be retrieved to get SCI Sockets working. The first package builds the libraries which SCI Sockets are built upon and the second is the actual SCI Socket libraries. Currently the distribution is only in source code format.
The latest versions of these packages is currently found at. Check
for latest versions.
The next step is to unpack those directories, SCI Sockets is unpacked below the DIS code. Then the code base is compiled. The example below shows the commands used in Linux/x86 to perform this.
shell> tar xzf DIS_GPL_2_5_0_SEP_10_2004.tar.gz shell> cd DIS_GPL_2_5_0_SEP_10_2004/src/ shell> tar xzf ../../SCI_SOCKET_2_3_0_OKT_01_2004.tar.gz shell> cd ../adm/bin/Linux_pkgs shell> ./make_PSB_66_release
If the build is made on an Opteron box and is to use the 64 bit extensions then use make_PSB_66_X86_64_release instead, if the build is made on an Itanium box then use make_PSB_66_IA64_release instead. The X86-64 variant should work for Intel EM64T architectures but no known tests of this exists yet.
After building the code base it has been put into a zipped tar filed DIS and OS and date. It is now time to install the package in the proper place. In this example we will place the installation in /opt/DIS. These actions will most likely require you to log in as root-user.
shell> cp DIS_Linux_2.4.20-8_181004.tar.gz /opt/ shell> cd /opt shell> tar xzf DIS_Linux_2.4.20-8_181004.tar.gz shell> mv DIS_Linux_2.4.20-8_181004 DIS
Now that all the libraries and binaries are in their proper place we need to ensure that SCI cards gets proper node identities within the SCI address space. Since SCI is a networking gear it is necessary to decide on the network structure at first.
There are three types of network structures, the first is a simple one-dimensional ring, the second uses SCI switch(es) with one ring per switch port and finally there are 2D/3D torus. Each has its standard of providing node ids.
A simple ring uses simply node ids displaced by 4.
4, 8, 12, ....
The next possibility uses switch(es). The SCI switch has 8 ports. On each port it is possible to place a ring. It is here necessary to ensure that the rings on the switch uses different node id spaces. So the first port uses node ids below 64 and the next 64 node ids are allocated for the next port and so forth.
4,8, 12, ... , 60 Ring on first port 68, 72, .... , 124 Ring on second port 132, 136, ..., 188 Ring on third port .. 452, 456, ..., 508 Ring on the eight port
2D/3D torus network structures takes into account where each node is in each dimension, increment by 4 for each node in the first dimension, by 64 in the second dimension and by 1024 in the third dimension. Please look in the Dolphin for more thorough documentation on this.
In our testing we have used switches. Most of the really big cluster installations uses 2D/3D torus. The extra feature which switches provide is that with dual SCI cards and dual switches we can easily build a redundant network where failover times on the SCI network is around 100 microseconds. This feature is supported by the SCI transporter and is currently also developed for the SCI Socket implementation.
Failover for 2D/3D torus is also possible but requires sending out new routing indexes to all nodes. Even this will complete in around 100 milliseconds and should be ok for most high-availability cases.
By placing the NDB nodes in proper places in the switched architecture it is possible to use 2 switches to build a structure where 16 computers can be interconnected and no single failure can hamper more than one computer. With 32 computers and 2 switches it is possible to configure the cluster in such a manner that no single failure can hamper more than two nodes and in this case it is also known which pair will be hit. Thus by placing those two in separate NDB node groups it is possible to build a safe MySQL Cluster installation. We won't go into details in how this is done, since it is likely to be only of interest for users wanting to go real deep into this.
To set the node id of an SCI card use the following command still being
/opt/DIS/sbin directory. -c 1 refers to the number of the SCI
card, where 1 is this number if only 1 card is in the machine. In this case
use adapter 0 always (set by -a 0). 68 is the node id set in this example.
shell> ./sciconfig -c 1 -a 0 -n 68
In case you have several SCI cards in your machine the only safe to discover which card has which slot is by issuing the following command
shell> ./sciconfig -c 1 -gsn
This will give the serial number which can be found at the back of the SCI card and on the card itself. Do this then for -c 2 and onwards as many cards there are in the machine. This will identify which cards uses which id. Then set node ids for all cards.
Now we have installed the necessary libraries and binaries. We have also set the SCI node ids. The next step is to set the mapping from hostnames (or IP addresses) to SCI node ids.
The configuration file for SCI Sockets is to be placed in the file
/etc/sci/scisock.conf. This file contains a mapping from hostnames
(or IP addresses) to SCI node ids. The SCI node id will map the hostname
to communicate through the proper SCI card. Below is a very simple such
#host #nodeId alpha 8 beta 12 192.168.10.20 16
It is also possible to limit this configuration to only apply for a subset of
the ports of these hostnames. To do this another configuration is used which
is placed in
#-key -type -values EnablePortsByDefault yes EnablePort tcp 2200 DisablePort tcp 2201 EnablePortRange tcp 2202 2219 DisablePortRange tcp 2220 2231
Now we ready to install the drivers. We need to first install the low-level drivers and then the SCI Socket driver.
shell> cd DIS/sbin/ shell> ./drv-install add PSB66 shell> ./scisocket-install add
If desirable one can now check the installation by invoking a script which checks that all nodes in the SCI Socket config files are accessible.
shell> cd /opt/DIS/sbin/ shell> ./status.sh
If you discover an error and need to change the SCI Socket config files then it is necessary to use a program ksocketconfig to change the configuration.
shell> cd /opt/DIS/util shell> ./ksocketconfig -f
To check that SCI Sockets are actually used you can use a test program
latency_bench which needs to have a server component and clients can
connect to the server to test the latency, whether SCI is enabled is very clear
from the latency you get. Before you use those programs you also need to set
the LD_PRELOAD variable in the same manner as shown below.
To set up a server use the command
shell> cd /opt/DIS/bin/socket shell> ./latency_bench -server
To run a client use the following command
shell> cd /opt/DIS/bin/socket shell> ./latency_bench -client hostname_of_server
Now the SCI Socket configuration is completed. MySQL Cluster is now ready to use both SCI Sockets and the SCI transporter documented in section 22.214.171.124 MySQL Cluster SCI Transport Connections.
The next step is to start-up MySQL Cluster. To enable usage of SCI Sockets it is necessary to set the environment variable LD_PRELOAD before starting the ndbd, mysqld and ndb_mgmd processes to use SCI Sockets. The LD_PRELOAD variable should point to the kernel library for SCI Sockets.
So as an example to start up ndbd in a bash-shell use the following commands.
bash-shell> export LD_PRELOAD=/opt/DIS/lib/libkscisock.so bash-shell> ndbd
From a tcsh environment the same thing would be accomplished with the following commands.
tcsh-shell> setenv LD_PRELOAD=/opt/DIS/lib/libkscisock.so tcsh-shell> ndbd
Noteworthy here is that MySQL Cluster can only use the kernel variant of SCI Sockets.
The ndbd process has a number of simple constructs which are used to access the data in MySQL Cluster. We made a very simple benchmark to check the performance of each such statement and the effect various interconnects have on their performance.
There are four access methods:
Primary key access
Unique key access
Full table scan
Range scan using ordered index
To check the base performance of these access methods we developed a set of benchmarks. One such benchmark, testReadPerf issues, simple primary and unique key access, batched primary and unique key accesses. The benchmark also measures the set-up cost of range scans by issuing scans returning a single record and finally there is a variant which uses a range scan to fetch a batch of records.
In this manner we can test the cost of issuing single key access and single record scan accesses and measure the impact of the communication media implementation of these base access methods.
We executed those base benchmark both using a normal transporter using TCP/IP sockets and a similar set-up using SCI sockets. The figures reported below is for small accesses of 20 records per of data per access. The difference between serial and batched goes down by a factor of 3-4 when using 2 kB records instead. SCI Sockets were not tested with 2 kB record2 kB records. Tests were performed on a 2-node cluster with 2 dual CPU machines equipped with AMD MP1900+ processors.
Access type: TCP/IP sockets SCI Socket Serial pk access: 400 microseconds 160 microseconds Batched pk access: 28 microseconds 22 microseconds Serial uk access: 500 microseconds 250 microseconds Batched uk access: 70 microseconds 36 microseconds Indexed eq-bound: 1250 microseconds 750 microseconds Index range: 24 microseconds 12 microseconds
We did also another set of tests to check the performance of SCI Sockets compared to using the SCI transporter and all compared to the TCP/IP transporter. All these tests used primary key accesses either serially, multi-threaded and multi-threaded and batched simultaneously.
More or less all of these tests showed that SCI sockets were about 100% faster compared to TCP/IP. The SCI transporter was faster in most cases compared to SCI sockets. One notable case however with many threads in the test program showed that the SCI transporter behaved really bad if used in the mysqld process.
Thus our conclusion overall is that for most benchmarks SCI sockets improves performance with around 100% compared to TCP/IP except in rare cases when communication performance is not an issue such as when scan filters make up most of processing time or when very large batches of primary key accesses are achieved. In that case the CPU processing in the ndbd processes becomes a fairly large part of the cost.
Using the SCI transporter instead of SCI Sockets is only of interest in communicating between ndbd processes. Using the SCI transporter is also only of interest if a CPU can be dedicated for the ndbd process since the SCI transporter ensures that the ndbd will never go to sleep. It is also important to ensure that the ndbd process priority is set in such a way that the process doesn't lose in priority due to running for a long time (as can be done by locking processes to CPU's in Linux 2.6). If this is a possible configuration then ndbd process will benefit by 10-70% compared to using SCI sockets (the larger figures when performing updates and probably also on parallel scan activities).
There are several other implementations of optimised socket variants for clusters reported in various papers. These include optimised socket variants for Myrinet, Gigabit Ethernet, Infiniband and the VIA interface. We have only tested MySQL Cluster so far with SCI sockets and we also include documentation above on how to set-up SCI sockets using ordinary TCP/IP configuration for MySQL Cluster.
Below is a list of known limitations with release 4.1 when comparing to the storage engines MyISAM and InnoDB. Currently there are no plans to address these in coming releases of 4.1 (but well in 5.0 or later releases). At http://bugs.mysql.com, category cluster, you fill find known bugs which are intended to be fixed in upcoming releases of 4.1 (if marked 4.1). This list is intended to be complete with respect to the above, please report discrepancies at http://bugs.mysql.com, category cluster. If this discrepancy will not be fixed in 4.1 it will be added to this list.
MaxNoOfConcurrentOperations(bulk load, truncate table and alter table are handled specially by running several transactions, and thus does not have this limitation)
FORCE INDEXto work around non optimal query plans.
USING HASH) cannot be used for accessing table if NULL is given as part of the key.
mysqlclients that may be accessing the cluster.
Go to the first, previous, next, last section, table of contents.