個人用ツール

Pandora:Docuemntation ja:MySQL Cluster

提供: Pandora FMS Wiki JP

移動先: 案内, 検索

Pandora FMS ドキュメント一覧に戻る

目次

MySQL クラスタを使った Pandora FMS の HA

概要

MySQL Cluster allows the database clustering in a non sharing scenario. This reduces the number of single points of failure as it's possible to use inexpensive hardware with few requirements while still having redundancy of hardware.

MySQL クラスタでは、非共有 の状態でデータベースをクラスタリングすることができます。これにより、若干のハードウエアの冗長構成を持った安価なハードウエアにて、単一障害点を減らすことができます。

MySQL Cluster mixes the MySQL database server with an in memory clustered storage engine called NDB. In our documentation when we talk about NDB we talk about the storage engine, meanwhile when we talk about MySQL Cluster we talk about the combination of the database server technology and the NDB storage engine. A MySQL Cluster is a set of servers each one running several processes including MySQL servers, data nodes for the NDB storage engine, management severs, and (probably) specific programs to access the data.

MySQL クラスタは、MySQL データベースサーバに、NDB と呼ばれる memory clustered storage engine を組み込んだものです。このドキュメントでは、NDB に関して述べる場合はストレージエンジンに関してであり、MySQL クラスタに関して述べる場合は、データベースサーバと NDB ストレージエンジンの組み合わせを意味します。MySQL クラスタは、MySQL サーバを動かすサーバ、NDB ストレージエンジンのためのデータノード、管理サーバ、そして、データにアクセスするための特別なプログラムの組み合わせです。

All data stored in a MySQL Cluster can be replicated so it can handle the failure of a single node without any more impact than a few transactions aborted as their status was lost with the node. As transactional applications are supposed to handle transaction errors this shouldn't be a problem.

MySQL クラスタに保存される全データは複製可能で、ノードがダウンしたといった若干のトランザクションが中断される以上の影響がない単一ノードの障害に対応できます。トランザクションエラーを想定したアプリケーションでは、これは問題にはなりません。

Pandora FMS ドキュメントにおけるクラスタに関する用語

Data Node
データノード
This kind of node stores the cluster data. There are as much data nodes as replicas times the number of fragments (at least). For example, with tow replicas, each with two fragments, four data nodes are needed. There is no need of having more than one replica. A data node is started with the command ndbd (or ndbmtd if the multithreaded version is started).
この種類のノードは、クラスタデータを保存します。(少なくとも)フラグメントの数分データノードがあります。たとえば、2つの複製で、それぞれに 2つのフラグメントがあれば、4つのデータノードが必要です。1つより多い複製を持つ必要はありません。データノードは、ndbd コマンド (もしくは、マルチスレッドバージョンの場合は ndbmtd) で起動します。
SQL Node (or API Node)
SQL ノード (または API ノード)
This is the node that access the data stored in the cluster. For MySQL Cluster this is a traditional MySQL server using NDB Cluster engine. A SQL node is started by the command mysqld with the option ndbcluster added in the my.cnf configuration file.
これは、クラスタ内で保存されているデータにアクセスするノードです。MySQL クラスタにとって、これは、NDB クラスタエンジンを使った MySQL サーバです。SQL ノードは、my.cnf 設定ファイル内で ndbcluster オプションを設定した mysqld コマンドで起動します。
Manager or MGM
マネージャ または MGM
This is the cluster administration node. The role of this node is to manage all the other nodes in the cluster, allowing tasks like give configuration parameters, start and stop nodes, create backups, and in general all the management tasks of the cluster. As this is the node that manages the cluster configuration one of this kind of nodes should be started the first one, before any other one. The management node is started with the command ndb_mgmd.
これは、クラスタの管理ノードです。このノードの権限は、クラスタ内のすべてのノードを管理することで、パラメータ設定、ノードの起動・停止、バックアップ取得など、クラスタの全ての管理タスクを実行します。クラスタを管理するノードのため、この種類のノードは他のノードよりも先に、最初に起動します。管理ノードは、ndb_mgmd コマンドで起動します。

Pandora FMS で利用するクラスタアーキテクチャ

The sample architecture used in this documentation has two servers that will run data nodes, and SQL nodes, also it has two management servers used to manage the cluster.

このドキュメントで利用しているサンプルのアーキテクチャでは、データノードと SQL ノードを動かす 2つのサーバと、クラスタを管理するのに利用する 2台の管理サーバがあります。

The sample architecture has Pandoradb1 and Pandoradb2 as data and SQL nodes, Pandoradbhis and Pandora2 as managers, and finally Pandora1, Pandor2 and Pandora3 running pandora servers and pandora consoles.

サンプルのアーキテクチャでは、データおよび SQL ノードとして Pandoradb1 と Pandoradb2 があり、管理ノードとして Pandoradbhis および Pandora2、さらに Pandora1、Padora2、Pandora3 で pandora サーバと pandora コンソールが動いています。

There is also some assumptions in this architecture:

また、このアーキテクチャでは以下を仮定します。

  • There is a load balancer in the front-end, balancing the tentacle and SNMP traffic to the three Pandora FMS servers with a RR (RoundRobin) type of algorithm.
  • フロントエンドにロードバランサがあり、tentacle および SNMP の通信を 3台の Pandora FMS サーバへラウンドロビンでバランシングしています。
  • There is a load balancer in the back-end to balance the queries done by the pandora servers and pandora consoles to the SQL nodes.
  • バックエンドには、pandora サーバと pandora コンソールから SQL ノードへのクエリをバランシングするロードバランサがあります。

Those load balancers are external to pandora and can be either software or hardware. To use a software load balancer there is documentation in Pandora FMS about how to setup a keepalievd.

これらのロードバランサは pandora の外部にあるもので、ソフトウエアまたはハードウエアのいずれかです。ソフトウエアロードバランサを利用については、Pandora FMS ドキュメントの keepalived の設定方法に記載しています。

The purpose of the database cluster is to share the workload of the database when monitoring a high number of machines and parameters. For the cluster to work properly it's very important that the load balancer is well designed and works properly.

データベースクラスタの目的は、多くのマシンや項目をモニタリングした時のデータベースの負荷を分散することです。クラスタが正しく動作するには、ロードバランサが適正に設計され正しく動作することがとても重要です。

The database cluster characteristics are the following:

データベースクラスタの特徴は次の通りです。

  • Works on memory, dumping to disk logs of the transactions.
  • メモリ上で動作し、トランザクションのログをディスクに書出す。
  • Needs a manager to operate the recovery process.
  • リカバリ処理のために管理ノードが必要。
  • Needs fast disks and fast network.
  • 高速なディスクと高速なネットワークが必要。
  • It has strict memory requirements.
  • メモリの制限がある。
  • It has to store all the database in memory to work fast.
  • 高速動作のために、メモリ上にデータベースを持つ。

To improve the performance of the cluster, more RAM can be added. In this example it's supposed that the requirement of RAM is 16 GiB for each server involved in the database.

クラスタのパフォーマンスを改善するためには、多くのメモリが必要です。ここでの例では、それぞれのサーバでデータベースに 16GB のメモリが必要であると仮定します。

インストールと設定

The documentation is based on a SUSE installation where the installation of MySQL Cluster implies the rpms with the MySQL cluster software, in this case the rpms are the following files:

このドキュメントは SUSE をベースにしており、そこに MySQL クラスタソフトを rpm にてインストールします。この場合、rpm ファイルは次の通りです。

  • MySQL-Cluster-gpl-client-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-extra-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-management-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-server-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-shared-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-storage-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-test-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-tools-7.0.6-0.sles10.x86_64.rpmlibmysqlclient16

SQL ノードとデータノードの設定

In each data node or SQL node we should modify the /etc/my.cnf configuration file, that besides the current MySQL configuration should also contain some extra parameters of the cluster configuration. Next these parameters are described, and also the values we should give to them (the complete final configuration is at the end of this annex). The cluster configuration parameters in the my.cnf file are applied two two sections: mysqld and mysql_cluster.In the mysqld section the following parameters should be added:

それぞれのデータノードまたは SQL ノードでは、/etc/my.cnf の設定ファイルを編集します。この MySQL 設定ファイルには、クラスタ設定の拡張パラメータも含んでいます。以下にこれらのパラメータおよび、それらに設定する値について説明します。(最終的な全体の設定はこのドキュメントの最後を参照してください) my.cnf ファイル内のクラスタ設定パラメータは、mysqld と mysql_cluster の 2つのセクションに分かれています。mysqld セクションでは、次のパラメータを記述します。

  • ndbcluster: order to the mysql motor that it have to star the NDB motor for databases in cluster.
  • ndbcluster: クラスタ内のデータベースに NDB エンジンを使う設定です。
  • ndb-connectstring="10.1.1.215:1186;10.1.1.216:1186":contains the connection string to the /node/s of management. It is a string of characters with th host format: port,host:port.
  • ndb-connectstring="10.1.1.215:1186;10.1.1.216:1186": 管理ノードの接続設定です。ポート番号,ホスト:ポート番号 というフォーマットです。
  • ndb-cluster-connection-pool=10: connexion number in the connexion reserve, the cluster config.ini file should also define at least one MySQL node or an API node) for each connection
  • ndb-cluster-connection-pool=10: 接続プール数です。クラスタの config.ini ファイルでは、少なくとも 1台の MySQL ノードまたは API ノードを定義する必要があります。
  • ndb-force-send=1: force the buffers to be sent inmediately without waiting for other threads.
  • ndb-force-send=1: 他のスレッドを待たずに、すぐにバッファを送信する設定です。
  • ndb-use-exact-count=0:deactivate the NDB forced to count the registers while the consulting SELECT COUNT (*) planning to make the queries quicker.
  • ndb-use-exact-count=0: SELECT COUNT (*) のクエリを高速化するために、NDB のレジスタカウントを無効にします。
  • ndb-autoincrement-prefetch-sz=256:determines the possibility of leaving blanks in an self incremented column. With a value of 1 the blanks, higher values speed the insertions, but reduce the possibilities that the consecutives numbers would be used in group insertions.
  • ndb-autoincrement-prefetch-sz=256: 自動インクリメントカラムにおけるギャップを定義します。値が1の場合はギャップは最小です。大きい値にするとインサートが高速化しますが、連続した複数データのインサートは遅くなります。

In the mysql_cluster section, the following parameters should be added: mysql_cluster セクションでは、次のパラメータを設定します。

  • ndb-connectstring="10.1.1.230:1186;10.1.1.220:1186": has the connection string to it/ the management node/s. It consist of a string of characters with the host format:port,host:port.
  • ndb-connectstring="10.1.1.230:1186;10.1.1.220:1186": 管理ノードへ接続するための設定です。ポート番号,ホスト:ポート番号 というフォーマットになっています。

Here we can see an extract of the file:

以下に実際のファイルを示します。

[mysqld]
# Run NDB storage engine
ndbcluster
# Location of management servers
ndb-connectstring="10.1.1.215:1186;10.1.1.216:1186"   
# Number of connections in the connection pool, the config.ini file of the
# cluster have to define also [API] nodes at least for each connection.
ndb-cluster-connection-pool=10
# Forces sending of buffers to NDB  immediately, without waiting
# for other threads. Defaults to ON. 
ndb-force-send=1
# Forces NDB to use a count of records during SELECT COUNT(*) query planning 
# to speed up this type of query. The default value is ON. For faster queries
# overall, disable this feature by setting the value of ndb_use_exact_count
# to OFF. 
ndb-use-exact-count=0

# Determines the probability of gaps in an autoincremented column. 
# Set it to 1 to minimize this. Setting it to a high value for 
# optimization — makes inserts faster, but decreases the likelihood
# that consecutive autoincrement numbers will be used in a batch
# of inserts. Default value: 32. Minimum value: 1. 
ndb-autoincrement-prefetch-sz=256

# Options for ndbd process:
[mysql_cluster]
# Location of management servers (list of host:port separated by ;)
ndb-connectstring="10.1.1.230:1186;10.1.1.220:1186" 

The final version of this file is on Annex 1

このファイルの最終バージョンは、補足1に示します。

管理設定

First we should create the directory where the information of the cluster (/var/lib/mysql-cluster/) will be kept and in this directory will be created the cluster configuration file from which we are going to give a summary with the most relevant parameters:

最初に、クラスタの情報を置くディレクトリ (/var/lib/mysql-cluster/) を作成する必要があります。このディレクトリには、クラスタの設定ファイルが作成されます。最も適切なパラメータ設定を以下に示します。

# MySQL Cluster Configuration file 
# By Pablo de la Concepción Sanz <pablo.concepcion@artica.es>
# This file must be present on ALL the management nodes
# in the directory /var/lib/mysql-cluster/

##########################################################
# MANAGEMENT NODES                                       #
# This nodes are the ones running the management console #
##########################################################

# Common configuration for all management nodes:

[ndb_mgmd default]

ArbitrationRank=1
# Directory for management node log files
datadir=/var/lib/mysql-cluster  

[ndb_mgmd]
id=1
# Hostname or IP address of management node
hostname=<hostname_nodo_de_gestion_1>        

[ndb_mgmd]
id=2
# Hostname or IP address of management node
hostname=<hostname_nodo_de_gestion_2> 

.
.
.

The final version of this file is at the end of this document.

このファイルの最終版は、このドキュメントの最後にあります。

The config.ini file is divided in the following options:

config.ini ファイルは、次のオプションに分割できます。

  • [ndb_mgmd default]: common configuration for all the management nodes.
  • [ndb_mgmd default]: 全管理ノード共通の設定
  • [ndb_mgmd]:individual configuration of each management node.
  • [ndb_mgmd]: 個々の管理ノードの個別設定
  • [ndbd default]: common configuration of the data nodes.
  • [ndbd default]: データノードの共通設定
  • [ndbd]: Configuración individual de cada nodo de datos
  • [ndbd]: データノードの個別設定
  • [mysqld default]: common configuration of all API or SQL nodes
  • [mysqld default]: 全 API または SQL ノードの共通設定
  • [mysqld]: individual configuration of each API or SQL node
  • [mysqld]: それぞれの API または SQL ノードの個別設定
  • [tcp default]: Connection buffers configuration
  • [tcp default]: 接続バッファ設定

管理ノードの共通設定パラメータ

Arbitration Rank:

調停順位:

This parameter is useful to define which node will be the arbitrator (the management nodes and SQL nodes can arbitrate, it is recommended that there would be the management nodes will be the ones that have high priority), could have values from 0 to 2:

このパラメータは、どのノードが調停ノード (管理ノードおよび SQL ノードが調停ノードになれます) になるかを定義するのに便利です。値は 0 から 2 で、管理ノードの優先順位を高くする必要があります。

  • 0: The node will be never be used as arbitrator
  • 0: 調停ノードにはなりません。
  • 1: The node will have high priority, it will have priority over the nodes of low priority
  • 1: ノードの優先順位が高くなります。優先順位が低いノードよりも上の優先順位となります。
  • 2: The node will have low priority and will only used as arbitratos if there are not other nodes of higher priority availables
  • 2: ノードの優先順位が低くなります。高い優先順位のノードが他に無い場合にのみ調停ノードになります。

Datadir: Directory where are kept the logs of the management node

Datadir: 管理ノードのログを保存するディレクトリです。

2つの管理ノードにおける個別設定パラメータ

There should be a section [ndb_mgmd] for each management node.

[ndb_mgmd] セクションにて、それぞれの管理ノード設定をします。

id: node identificator. It should be the only one in all the configuration file.

id: ノードの ID です。すべての設定ファイル内で唯一の値です。

Hostname:host name or IP adress of the management node

Hostname: 管理ノードのホスト名または IP アドレスです。

ストレージノードの共通設定パラメータ

NoOfReplicas: Redundancy, number of replies for each table kept in the cluster. This parameter also specifies the size of the node groups. A group of nodes is a set of nodes that keeps all the same information. It is recommended to stablish the number of replies to 2 that allow to have high availability.

NoOfReplicas: クラスタ内にテーブルの複製をいくつもつかの冗長化設定です。このパラメータはまた、ノードグループのサイズも定義します。ノードのグループは、同じ情報を保持するノードの集合です。冗長性を持たせるためには、2を設定することをお勧めします。

Datadir: Directory where are kept the files related with the data node (logs, trace files,error files, files with the pid)

Datadir: データノードに関するファイル(ログ、トレースファイル、エラーファイル、pidファイル)を置くディレクトリです。

DataMemory: This parameter fix the space (in bytes) that is available to keep registers of the database, all the space that is shown is reserved in memory, so it is extremely important that there should be enough physical memory to reserve without the necesity of using the exchange memory.

DataMemory: このパラメータは、データベースを保持するメモリ領域の大きさ(バイト)を指定します。すべての領域は確保済みのメモリとして表示されます。スワップなしに確保できるだけの十分な物理メモリがあることが重要です。

IndexMemory: This parameter monitors the storage quantity used by hash index in MySQL Cluster. The hash index are always used by index with primary key, unique index and unique restrictions.

IndexMemory: このパラメータは、MySQL クラスタのハッシュインデックスに使うストレージの量です。ハッシュインデックスは、プライマリキーのインデックス、一意のインデックス、および一意の制限付きインデックスで利用されます。

StringMemory: This parameter shows how much memory is reserved for strings of characters ( such as tht names of the tables), a value between 0 and 100 is taken as a percentage of the maximum value( that changes according to a big number of factors) while a value higher to 100 is interpreted as the number of bytes. (25% should be enough).

StringMemory: このパラメータは、文字列(テーブル名など)のためにどれだけのメモリを確保するかを指定します。最大値(要素に応じて変化します)を 0 と 100 の間のパーセンテージで指定します。100より大きい値はバイト数として解釈されます。(25%で十分です)

MaxNoOfConcurrentTransactions:This parameter shows the maximum number of transactions in a node. It should be the same for all data nodes. This is due to that if a node fails, the older node of the ones that are left, start again to create all the transactions of the fallen node (change the value of this parameter implies a complete stop of the cluster).

MaxNoOfConcurrentTransactions: このパラメータは、ノードの最大トランザクション数を示します。すべてのデータノードで同じです。あるノードで障害が発生した場合、残りのノードで障害ノードのトランザクションを再開する必要があるためです。(このパラメータの変更にはクラスタの停止が必要です)

MaxNoOfConcurrentOperations: Shows the maximum number of registers that could be simultaneously in updating phase or bloqued

MaxNoOfConcurrentOperations: 同時に更新またはロックできる最大レコード数です。

MaxNoOfLocalOperations: It is recommended to stablish this parameter with a value of the 110% of MaxNoOfConcurrentOperations.

MaxNoOfLocalOperations: MaxNoOfConcurrentOperations の 110% に設定することをお勧めします。

MaxNoOfConcurrentIndexOperations: This parameter has a default value of 8192 and only in cases of extremely high parallelism that use unique hash index, it should be necessary to increase its value. It is posible to reduce its value if the Database administrator considers that there is not much parallelism and with it saving some memory.

MaxNoOfConcurrentIndexOperations: このパラメータのデフォルト値は 8192 です。並列処理が多い場合それぞれでハッシュインデックスが使われるため、この値を大きくする必要があります。並列処理が多くなければ、メモリの節約で値を減らすこともできます。

MaxNoOfFiredTriggers: This parameter has by default a value of 4000, and it should be enough in the majority of cases. Some times it would be even posible to reduce its value if the Database administrator considers that there is not much parallelism.

MaxNoOfFiredTriggers: このパラメータのデフォルト値は 4000 で、ほとんどの場合はこれで十分です。並列処理があまり無い場合は減らすこともできます。

TransactionBufferMemory: This temporal memory storage is used while the update of the index tables and of reading of unique index for keeping the key and the column in this operations, and usually, we should not modify the 1M default value.

TransactionBufferMemory: この一時メモリ領域は、テーブルのインデックスを更新したり、キーとカラムを保持したインデックスを読む時に使われます。通常は、デフォルトの 1M から変更する必要はありません。

MaxNoOfConcurrentScans: This parameter shows the maximum number of parallel scanning that the cluster could do, that could be able to support so many scans as the selected ones for this parameter in each node.

MaxNoOfConcurrentScans: このパラメータは、クラスタがそれぞれのノードで並列に検索する最大値を示します。

MaxNoOfLocalScans: This parameter shows the number of registers scanned locally if several scans are not made completely in parallel. If it is not specified, it is calculated as the product of MaxNoOfConcurrentScans by the number of data nodes.

MaxNoOfLocalScans: このパラメータは、複数の検索が並列処理できなかったときに、ローカルで検索する数を示します。指定されていない場合は、MaxNoOfConcurrentScans とデータノードの数をかけた値になります。

BatchSizePerLocalScan:Shows the number of bloqued registers that are used to deal with concurrent scanning operations

BatchSizePerLocalScan: 同時検索操作時のレコードのロック数を示します。

LongMessagesBuffer: This parameter determines the size of a temporary internal storage for the information exchange between nodes.

LongMessagesBuffer: このパラメータはノード間で情報交換するための一時的な内部ストレージのサイズを定義します。

NoOfFragmentLogFiles: This parameter shows how many redo log blocks will be generated and together with FragmentLogFileSize allows to determine the total size of the redo log.

NoOfFragmentLogFiles: このパラメータは生成される redo ログ数を示します。redo ログのサイズは、FragmentLogFileSize で定義できます。

FragmentLogFileSize: Size of the redo log extracts makes a redo log and it is the size with wich is reserved the space of redo log. A bigger size of the 16M of FragmentLogSize allows a bigger performance when there is much writting. In this case it is very recommended to increase the value of this parameter.

FragmentLogFileSize: redo ログのサイズを示します。また redo ログの領域として予約されたサイズでもあります。FragmentLogSize を 16M 以上にすると、大量に書かれる場合はパフォーマンスが向上します。このような場合、このパラメータの値を大きくすることをお勧めします。

InitFragmentLogFiles: this parameter can have two values: SPARSE or FULL

InitFragmentLogFiles: このパラメータには、SPARSE または FULL を指定できます。

  • SPARSE: this is the default value. The log fragments are created in a separated way.
  • SPARSE: デフォルト値です。分割ログファイルがまばらに作成されます。
  • FULL: forces to that all the bytes of the log fragments are written in disk.
  • FULL: 分割ログファイルの全てをディスクに書きます。

MaxNoOfOpenfiles: This parameter limits the number of threads for the file opening. Any situation that requires to change this parameter could be reported as a bug.

MaxNoOfOpenfiles: このパラメータは、ファイルを開くためのスレッド数を制限します。このパラメータの変更が必要な状況になった場合は、バグとして報告してください。

InitialNoOfOpenFiles: Initial number of threads for the file opening.

InitialNoOfOpenFiles: ファイルを開く初期スレッド数です。

MaxNoOfSavedMessages: Maximum number of trace files that are kept before to start overwriting the old ones.

MaxNoOfSavedMessages: 旧トレースファイルを上書きせずに保持するトレースファイルの最大数です。

MaxNoOfAttributes: Defines the maximum number of features that could be defined in the cluster. Each feature takes up about 200 bytes of storage for each node due to that all the metadata are replied in the servers.

MaxNoOfAttributes: クラスタに定義できるアトリビュートの最大数の設定です。サーバで全てのメタデータに応答するには、それぞれのノードでそれぞれのアトリビュートは最大 200バイト程度必要です。

MaxNoOfTables: Defines the total maximum of objects (table, unique hash index and ordered index) in the cluster.

MaxNoOfTables: クラスタ内のトータルの最大オブジェクト数(テーブル、ユニークなハッシュインデックス、orderedインデックス)の設定です。

MaxNoOfOrderedIndexes: For each ordered index in this cluster, an object is reserved that describes what is indexed and its storage segments. By default, each defined index defined an ordered index too. Each unique index and primary key has an ordered index and a hash index.

MaxNoOfOrderedIndexes: クラスタにおけるそれぞれのorderedインデックスのために、何がインデックス化されストレージセグメントが何かを説明するオブジェクトが予約されています。デフォルトでは、それぞれの定義済のインデックスは、orderedインデックスも定義します。それぞれのユニークインデックスおよびプライマリキーは、orderedインデックスおよびハッシュインデックスを持ちます。

MaxNoOfTriggers: Defines the maximum number of triggers in the cluster.

MaxNoOfTriggers: クラスタにおけるトリガの最大値の設定です。

LockPagesOnMainMemory: Lock the data node processes in the memory avoiding that they become swap. The possible values of the parameter are:

LockPagesOnMainMemory: スワップを避けるためにメモリ内でデータノードプロセスをロックします。設定可能な値は次の通りです。

  • 0: Disables the lockout (default value).
  • 0: ロック無効 (デフォルト)
  • 1: does the lockout after reserving the process memory.
  • 1: プロセスメモリを予約した後にロック
  • 2: does the lockout before reserving the process memory.
  • 2: プロセスメモリを予約する前にロック

StopOnError: Shows if the data node processes ends after an error or if they are restarted automatically.

StopOnError: エラー時にデータノードのプロセスを停止または自動的に再起動する設定です。

Diskless: Force to all the cluster to work without disk, in memory. This way the online backups are deactivated and it is not possible to start the cluster partially.

Diskless: すべてのクラスタをディスクを使わずに強制的にメモリ上で動かします。この場合、オンラインバックアップは無効になり、クラスタを部分的に開始することはできません。

ODirect: Activating this parameter we use O_DIRECT writing in local checkpoints and redo logs, reducing the CPU load. It is recommended to activate it for systems on a Linux with a kernel 2.6 or higher.

ODirect: このパラメータを有効にするとチェックポイントおよび redoログの書き込みに O_DIRECT オプションを利用します。これにより CPU の負荷が下がります。Linux kernel 2.6 またはそれ以上では有効にすることをお勧めします。

CompressedBackup: When it is activated (1), it does a compression similar to gzip -fast saving up to 50% space in the backup files.

CompressedBackup: 有効(1)にした場合、gzip -fast に似た圧縮を行いバックアップファイルサイズを最大 50% 削減します。

CompressedLCP: when it is activated (1), it does a compression similar to gzip -fast saving up to 50% space in the Checkpoint files.

CompressedLCP: 有効(1)にした場合、gzip -fast に似た圧縮を行いチェックポイントファイルのサイズを最大 50% 削減します。

TimeBetweenWatchDogCheck: Number of miliseconds of the WatchDog checking interval (thread that checks that the main thread is not lockout) if after 3 checks the main thread is in the same state in the watchdog will end the main thread.

TimeBetweenWatchDogCheck: ウォッチドッグのチェック(メインスレッドはロックされません)間隔をミリ秒で指定します。メインスレッドが 3回同じ状態の場合は、メインスレッドを終了します。

TimeBeweenWatchDogCheckInitial:Has the same function that TimeBetweenWachdogCheck, but this value is applied in the initial phase of the cluster start, when the memory reserve is done.

TimeBeweenWatchDogCheckInitial: TimeBetweenWachdogCheck と同じ機能がありますが、メモリの予約が実施され、クラスタ起動時の初期フェーズに値が適用されます。

StartPartialTimeout: Shows how long you have to wait from the cluster launching process is started until all the data node will be up. This parameter is ignored if it is a cluster starting. Its function is that the cluster would not be half launched.

StartPartialTimeout: クラスタがプロセスを起動してから全てのデータノードが起動するのにどれくらい待つかを示します。クラスタ起動時はこのパラメータは無視されます。この機能は、クラスタが中途半端な状態で起動しないようにするものです。

StartPartitionedTimeout: If the cluster is ready to start without waiting Start PartialTimeout,but it is in a partitioned state, the cluster also wait to this timeout pass. This parameter is ignored if it is a cluster starting.

StartPartitionedTimeout: StartPartialTimeout を待たずにクラスタが起動できる状態にり、分離状態の場合は、クラスタはこのタイムアウトが過ぎるのを待ちます。このパラメータはクラスタ起動時は無視されます。

StartFailureTimeout: If a node has not finished its starting time and when this timeout ends the start fails, a 0 value shows that is indefinitely waited.If the node has much information (several data gigabytes), the this parameters should be increased ( the start with big amount of data could take 10 or 15 minutes).

StartFailureTimeout: ノードの起動がこれで指定した時間内に完了しなかった場合は、起動が失敗します。0 を指定すると永久に待ち続け増す。ノードが多くの情報(ギガバイト単位のデータ)を持つ場合は、このパラメータは大きくする必要があります。(データ量が多いと、起動に 10 または 15分かかります)

HeartbeatIntervalDbDb: Shows how often are sent the pulse signals and how often we can expect to receive pulse signals. If we do not receive pulse signals from a node for 3 consecutive intervales, the node will be considered as down, so the maximum time for discovering a fail through the pulse sending process 4 times the value of this parameter. This parameter should not be changed very often and it should have the same value for all modes.

HeartbeatIntervalDbDb: ハートビート信号を送信および受信する時間間隔を示します。3回ノードからハートビート信号を受信しなかった場合、ノードがダウンしていると認識します。つまり、このパラメータに指定した 4倍の値が障害と認識する時間となります。このパラメータは頻繁に変更すべきではありません。また、すべてのノードで同じ値である必要があります。

HeartbeatIntervalDbApi:Each node sends pulse signals to each MySQL or API node in order to make sure that the contact is kept. If a MySQL node can not send the pulse in time (following the criteria of the 3 pulses explained in HeartbeatIntervalDbDb), the it will be considered as down and all current transactions are finished and the resources will be released. A node can not reconnect until the resources of the previous instance would be released.

HeartbeatIntervalDbApi: それぞれのノードは、接続が維持されているかどうか確認するために、MySQL または API ノードへハートビート信号を送ります。MySQL ノードが時間内(HeartbeatIntervalDbDbで説明した 3回のハートビート)にハートビート信号を送信できなかった場合、ダウンしていると認識し現在のトランザクションを終了します。そして、リソースが解放されます。前述のインスタンスのリソースが解放されるまで、ノードは再接続できません。

TimeBetweenLocalCheckpoints: is useful to avoid that in a cluster with low load will be done local checkpoints (if there is much load usually we start a new one inmediately after ending with the previous one). It is a value given as a logarithm in base 2 with the size to store in any checkpoint.

TimeBetweenLocalCheckpoints: これは、負荷の低いクラスタでローカルのチェックポイントの実行を避けるのに便利です。(もし負荷が高いと、以前のチェックポイントが終了したあと通常すぐに新たなチェックポイントを起動します) これには、2を底とした対数の値をチェックポイントとして指定します。

TimeBetweenGlobalCheckpoints: Shows how often the transactions are dumped into disk.

TimeBetweenGlobalCheckpoints: トランザクションのディスクへの書き込み頻度を示します。

TimeBetweenEpochs: Shows the interval of the replication times of the cluster.Defines a timeout for the synchronization times of the cluster reply, if a module is not able to participate in a global checkpoint in the period fixed for this parameter, the node will be switched off.

TimeBetweenEpochs: クラスタのレプリケーション時間間隔を示します。もし、モジュールがグローバルチェックポイントに同期できず切り放されてしまう場合は、このパラメータを調整してクラスタが同期に応答するタイムアウトを設定してください。

TransactionDeadlockDetectionTimeout: Shows how long the transaction coordinator will wait for another mode will complete a query before aborting the transaction. This parameter is important for the deadlocks management and the nodes fail.

TransactionDeadlockDetectionTimeout: トランザクションを中止せずに他のノードでのトランザクションが終了するのをどれくらい待つかを示します。このパラメータはデッドロック管理およびノード障害に対して重要です。

DiskSyncSize: Maximum size stored before dumping data to a local checkpoint file.

DiskSyncSize: ローカルのチェックポイントファイルにデータを書き込む前に保持するデータサイズです。

DiskCheckpointSpeed: transfer velocity in bytes by second of data sent to disk during a local checkpoint.

DiskCheckpointSpeed: ローカルチェックポイントでの、データをディスクに書き込む速度をバイト/秒で指定します。

DiskCheckpointSpeedInRestart: transfer velocity in bytes by second of data sent to disk during a local checkpoint that is part of a Restart operation.

DiskCheckpointSpeedInRestart: 再起動処理における、ローカルチェックポイントでの、データをディスクに書き込む速度をバイト/秒で指定します。

ArbitrationTimeout: times that a node waits for an arbitrator message. If this time is out, then it will be assumed that the network is divided.

ArbitrationTimeout: ノードが調停メッセージを待つ時間です。この時間を超過するとネットワークが切れたと想定します。

UndoIndexBuffer: is used during the local checkpoints to registry the activities during the local checkpoints writting. IMPORTANT: it is not safe to reduce the value of this parameter.

UndoIndexBuffer: ローカルチェックポイントの書き込みの時に処理を記録するのに使われます。重要:このパラメータを小さくするのは安全ではありません。

UndoDataBuffer: has the same function that the previous one, except that in this case it refers to the data memory instead of that of the index. IMPORTANT: it is not safe to reduce the value of this parameter.

UndoDataBuffer: 上記と同じ機能です。ただし、インデックスではなくデータメモリの参照が対象です。重要:このパラメータを小さくするのは安全ではありません。

RedoBuffer: registry the update activities in order they could be executed again in case of the system restart and leave the cluster in a consistent state.

RedoBuffer: システムが再起動しクラスタに不整合が発生した場合に処理を再実行できるようにする記録の設定です。

log levels comes from 0(nothing is reported to the log) to 15 (all related activity is reported to the log).

ログレベルは、0(ログに何も記録されない)から 15(ログに全てが記録される)です。

LogLevelStartup: log level of activity during the starting process.

LogLevelStartup: プロセス開始時の処理のログレベルです。

LogLevelShutdown: log level of activity during the stopping process.

LogLevelShutdown: プロセス停止時の処理のログレベルです。

LogLevelStatistic:log level of statistic events activity (reading of primary keys, updates, insertions, etc...)

LogLevelStatistic: 静的イベント(プライマリキーの読み込み、更新、インサート、その他)のログレベルです。

LogLevelCheckpoint: log level of activity during local and global checkpoints.

LogLevelCheckpoint: ローカルおよびグローバルチェックポイント時のログレベルです。

LogLevelNodeRestart: log level of activity during the restart of a Node.

LogLevelNodeRestart: ノードの再起動時のログレベルです。

LogLevelConnection: log level of activity of events generated through connections between nodes.

LogLevelConnection: ノード間接続を通して生成されるイベントのログレベルです。

LogLevelError: log level of warning and error activity.

LogLevelError: ワーニングおよびエラーのログレベルです。

LogLevelCongestion: Log level of cluster congestion activity.

LogLevelCongestion: クラスター混雑で生成されるログレベルです。

LogLevelInfo: Log level of the cluster general information activity.

LogLevelInfo: クラスタ全体の情報のログレベルです。

MemReportFrequency:Number of seconds between registers of memory use of the data nodes. The data and index memory is recorded either in percentage as in 32KB pages number.

MemReportFrequency: データノードのメモリ使用量をログに書く頻度です。データおよびインデックスメモリは、パーセンテージまたは 32KB 単位のページ数で記録されます。

StartupStatusReportFrequency: Shows the reports when the redologs are started because a data node has been fired. The redologs start process could be large if the size of these are big, and this parameter allow to register the evolution of this start.

StartupStatusReportFrequency: データノード起動時に redo ログの処理を出力します。値を大きくすると、redo ログの開始プロセスは長くなります。また、このパラメータは開始時の進捗を記録します。

BackupReportFrequency: Shows the frecuency with witch the backup evolution is registered in the log during the process of creating a security copy.

BackupReportFrequency: バックアップ作成処理実行時にバックアップの進捗をログに記録する頻度を示します。

BackupDataBufferSize: During the Backup process there are two buffers that are used to send data to the disk, when the buffer is full to the BackupWriteSize size and the Backup process could continue filling this buffer while it has space. The size of this parameter should be at least that of the BackupWriteSize + 188 KB

BackupDataBufferSize: バックアップ処理の間、データをディスクに書くために使われる 2つのバッファがあります。バッファのサイズを BackupWriteSize で指定します。バックアッププロセスは、バッファに空きがある間書き込み続けます。このパラメータのサイズは、少なくとも BackupWriteSize + 188KB ある必要があります。

BackupLogBufferSize: Register the writing in tables during the Backup process. If it has no space in the backup log buffer, then the backup will fail. The size of this parameter should be at least the one of BackupWriteSize + 16 KB.

BackupLogBufferSize: バックアップ処理中にテーブルに書き込まれるログのバッファサイズです。バックアップログのバッファに空きが無いと、バックアップは失敗します。このパラメータのサイズは、少なくとも BackupWriteSize + 16KB ある必要があります。

BackupMemory: Simply the sum of BackupDataBufferSize and BackupLogBufferSize.

BackupMemory: BackupDataBufferSize と BackupLogBufferSize の合計です。

BackupWriteSize: Tamaño por defecto de los mensajes almacenados en disco por el backup log buffer y el backup data buffer.

BackupWriteSize: バックアップログおよびバックアップデータバッファによりディスクに書かれるメッセージのデフォルトサイズです。

BackupMaxWriteSize:Size by default of the messages stored in the disk by the backup log buffer and the backup data buffer. The size of this parameter should be at least the one of BackupWriteSize.

BackupMaxWriteSize: バックアップログおよびバックアップデータバッファによりディスクに書かれるメッセージの最大サイズです。このパラメータのサイズは、少なくとも BackupWriteSize 以上である必要があります。

BackupDataDir: Directory where the security copies are kept, in this directory is created a subdirectory called BACKUPS an in it one for each security copy that is called BACKUP-X (where X is the number of the security copy).

BackupDataDir: バックアップを保存するディレクトリです。このディレクトリ内に BACKUPS というサブディレクトリが作成され、その中に個々のバックアップ BACKUP-X (Xはバックアップの番号です) が作成されます。

LockExecuteThreadToCPU:String with the CPUs identifiers in which the data node threads (ndbmtd) will be executed. It should be as many identifiers as the MaxNoOfExecutionThreads parameters say.

LockExecuteThreadToCPU: データノードの実行スレッド (ndbmtd) における CPU の識別子です。MaxNoOfExecutionThreads での指定と同じ量が必要です。

RealTimeScheduler: Fix this parameter to 1 activates the real time scheduler of the threads.

RealTimeScheduler: このパラメータを 1 に設定すると、スレッドのリアルタイムスケジューラが有効になります。

SchedulerExecutionTimer: Time in microseconds of thread execution in the scheduler before they be sent.

SchedulerExecutionTimer: スケジューラからスレッドが実行されるまでの時間をミリ秒で表します。

SchedulerSpinTimer: Time of execution in microseconds of the threads before sleeping.

SchedulerSpinTimer: スリープ前のスレッドの実行時間をミリ秒単位で表します。

MaxNoOfExecutionThreads: Number of execution threads (for 8 or more cores it is recommended to fix this parameter with an 8 value).

MaxNoOfExecutionThreads: 実行スレッドの数です。(8個以上のコアがある場合は、8に設定することをお勧めします。)

データノードの個別の設定パラメータ

It should be a section [ndbd] for each data node.

それぞれのデータノードに関しては、[ndbd] のセクションに設定します。

id: node identifier, it should be unique in all the configuration file.

id: ノードの識別子で、全ての設定ファイルでユニークである必要があります。

Hostname: host name or IP address of the data node.

Hostname: データノードのホスト名または IP アドレスです。

API や SQL の共通パラメータ

ArbitrationRank: this parameter is useful to define which node works as arbitrator(the management nodes and the SQL nodes could work as arbitrators, it is recommended that the management nodes would have high priority(), you can take values from 0 to 2:

ArbitrationRank: このパラメータは、どのノードが調停ノードとして動作するかを定義するのに便利です。管理ノードおよび SQL ノードが調停ノードとして動作可能です。管理ノードは優先順位を高くする必要があります。0 から 2 の値を設定できます。

  • 0:The node will never be used as arbitrator.
  • 0:ノードは調停ノードにはなりません。
  • 1: the node has high priority.It will have priority on nodes of low priority.
  • 1: ノードが高い優先順位を持ちます。低い優先順位のノードよりも優先順位が高くなります。
  • 2: the node has low priority, and will be only used as arbitrator if there are no other priority nodes.
  • 2: ノードが低い優先順位を持ち、他に高い優先順位のノードが無い場合にのみ調停ノードとなります。

In case of API or SQL nodes, it is recommended to fix the ArbitrationRank value to 2, allowing that it would be the manager nodes (that should have ArbitrationRank to 1) which have the rule of arbitrator

API または SQL ノードの場合、ArbitrationRank の値を 2に設定する必要があります。調停ルールを持っている(ArbitrationRank が 1 の)ノードは管理ノードとみなします。

BatchByteSize: limits the process blocks by batchs that are used when we do complete scans of the tables or scans by ranks on indexes.

BatchByteSize: テーブルのスキャンまたはインデックスの範囲によるスキャンを実施する時に使われるバッチサイズです。

BatchSize: limits the process blocks by batchs that are used when we do complete scans of the tables or scans by ranks on indexes.

BatchSize: このパラメータはレコード数で測定されます。

MaxScanBatchSize: total limit for all the cluster of the size of process blocks by batchs that are used when we do complete scans of the tables or scans by ranks on index. This parameter avoid that too many data would be sent from many nodes in parallel.

MaxScanBatchSize: テーブルのスキャンまたはインデックスの範囲によるスキャンを実施する時に使われるバッチサイズの全体制限です。このパラメータは、多くのノードから平行してたくさんのデータが送られるのを避けます。

API や SQL ノードの個別の設定パラメータ

It should be a section [mysqld] for each API or SQL node, there should be also extra sections [mysqld] to allow check or backup connections. For it, it is recommended to define these extra connections giving them a node identifier, but not a hostname, so any host could connect through the extra connections.

それぞれの API または SQL ノードについては、[mysqld] のセクションに設定します。また、[mysqld] セクションには、バックアップ接続の設定もできます。そのためには、ホスト名ではなくノードの識別子を指定して拡張接続を定義する必要があります。ホストは拡張接続を通して接続することができます。

id: node identifier.It should be unique in all the configuration file.

id: ノードの識別子です。全ての設定ファイルでユニークである必要があります。

Hostname: host name or Ip adress of the data node.

Hostname: データノードのホスト名または IP アドレスです。

NOTE: in our example documentation an architecture, we have done that the API/SQL nodes and the NDB data node would be phisically in the same system. This has not to be like this.

注意: ここで例にあげているアーキテクチャでは、API/SQL ノードと NDB データノードは物理的に同じシステムに乗っています。そのため、このようにはなっていません。

クラスタの起動

マネージャの起動

NOTA: we have configured the servers for the automatic stop/launch of the cluster management demons.The procedures that we detail here are to do the manual stops and starts and to know the functioning of them. We have developed an script for the stop and start and we have scheduled the default start level of the systems (level 3).

注意: クラスタ管理デーモンで自動起動/停止をするようにサーバを設定しています。ここでは、機能を理解いただくために手動での停止・起動の手順を示しています。停止および起動スクリプトを作成し、デフォルトのランレベル(レベル3)に設定しています。

Once we have done the installing and configuring procedures of the Manager system, we should start the service.

インストールが完了し管理システムの設定手順を実行したら、サービスを起動できます。

To start the administration node, we should execute the following command of the console: (as root) Administration node 1:

管理ノードを起動するには、コンソールから次のコマンドを(rootで)実行します。

管理ノード1:

ndb_mgmd  --config-file=/var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster

In the same way, through the script that has been developped for this at:

同じ処理を、作成したスクリプトでは次のようにします。

/etc/init.d/cluster_mgmt start

Administration node 2:

管理ノード2:

ndb_mgmd -c 10.1.1.221:1186 –-ndb-nodeid=2

In the same way, through the script that has been developed for this at:

同じ処理を、作成したスクリプトでは次のようにします。

/etc/init.d/cluster_mgmt start

If you want also load a new version of the configuration file, you should pass to both nodes start the –initial parameter.

新しい設定ファイルをロードしたい場合は、双方のノードの起動時に -initial パラメータを指定します。

The control script of the service (etc/init/cluster_mgmt) could be used to start the node (start) and to stop it (stop) or restart it (restart) and also to know its status (status).

サービスの制御スクリプト(/etc/init.d/cluster_mgmt)は、ノードを起動(start)・停止(stop)・再起動(restart)、また状態を確認する(status)のに利用できます。

クラスタデータノードの起動 (インストールのみ)

Once that the Manager has been launched, we start to launch the nodes with the following command in the console (as root):

マネージャを起動したら、コンソールから(rootにて)以下のコマンドでノードを起動します。

ndbmtd -–initial

This fix the initial configuration of the nodes (that obtain from the manager) and keep the redo log space. "Normal" start of the cluster data nodes.

これは、ノードの初期設定を(管理ノードから取得して)修正し、redo ログの領域を確保します。クラスタデータノードの "通常" の起動です。

In case of the restart of one of the nodes, due to fall or to some kind of technical stop, the nodes will be started using only ndbmtd, sin el --initial, so this parameter does that the configuration loads from zero and it restart the node data files and the redo logs (making necessary to restore data from a Backup).

ダウンしたり何らかの理由で停止した状態からノードを再起動する場合は、--initial 無しで sdbmtd のみで起動します。--initial パラメータは、ゼロから設定を読み込みノードデータファイルおより redo ログ(バックアップからのデータのリストアに必要)を再起動します。

ndbmtd

You could use the script developed for the control of the demon of the cluster storage node:

クラスタのストレージノードデーモンの制御には作成したスクリプトを利用できます。

/etc/init.d/cluster_node start

This script could be used to start the node (start) and to stop it (stop) or to restart it (restart), and also to know its status (status).

このスクリプトでは、ノードの起動(start)、停止(stop)、再起動(restart)、また状態の確認(status)ができます。

IMPORTANT NOTE:due to the importance of the starting process of the cluster data nodes, this process WILL BE NOT AUTOMATED.This is, you have to do it manually after a restart.

重要な注意事項: クラスタのデータノードのプロセス起動に重要であるため、この処理は 自動化しない でください。再起動後に手動で実行する必要があります。

the starting process of nodes is very delicate (if you have done a messy stop, or if the cluster has been left in a non synchronized status, then you should check the logs and the manufacturer documentation (MySQL) to know how solving the problem before firing the nodes.

ノードの起動処理にはとても注意が必要です。停止させたり、クラスタを非同期状態でおいた場合は、ノードを起動する前に問題をどのように解決すべきか、ログを確認し、(MySQLの)ドキュメントを確認する必要があります。

The start process of a data node could be an SLOW process. It could take between 10 and 20 minutes.To check the status, in the starting proccess, use the "SHOW" command in the MySQL cluster manager console, such as we are going to show later.

データノードの起動処理は遅い可能性があります。10から20分かかることがあります。状態を確認するには、プロセス起動時に後ほど示す MySQL クラスタマネージャコンソールにて "SHOW" コマンドを利用します。

SQL ノードの起動

The SQL Nodes are started using the command:

SQL ノードは次のコマンドで起動します。

/etc/init.d/mysql start

And they are stopped with

また、次のように停止します。

/etc/init.d/mysql stop 

As if it were a normal Mysql server.This does that all the threads defined in the /etc/my.cnf would be connectoed to the cluster, finishing this way the complete start of the cluster.

通常の MySQL サーバと似ています。これにより /etc/my.cnf に定義されている全てのスレッドがクラスタに接続し、クラスタの起動を完了させます。

クラスタステータスの表示

Once we have all the elements started, es can see if they have been correctly connected to the cluster. For it, in the Manager console we should writte:

全ての要素を起動したら、それらがクラスタに正しく接続されているかどうか確認することができます。そのためには、マネージャのコンソールで次のように入力します。

ndb_mgm

And we enter in the cluster administration interface, once in it, we write:

そして、クラスタ管理インタフェースで次のコマンドを入力します。

show

And we will obtain something like this:

すると次のような表示がされます。

Connected to Management Server at: 10.1.1.221:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=3    @10.1.1.215  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0, Master)
id=4    @10.1.1.216  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @10.1.1.221  (mysql-5.1.34 ndb-7.0.6)
id=2    @10.1.1.216  (mysql-5.1.34 ndb-7.0.6)

[mysqld(API)]   29 node(s)
id=11   @10.1.1.215  (mysql-5.1.34 ndb-7.0.6)
id=12   @10.1.1.216  (mysql-5.1.34 ndb-7.0.6)
id=13   @10.1.1.216  (mysql-5.1.34 ndb-7.0.6)
id=14   @10.1.1.216  (mysql-5.1.34 ndb-7.0.6)
id=15   @10.1.1.216  (mysql-5.1.34 ndb-7.0.6)
id=16   @10.1.1.216  (mysql-5.1.34 ndb-7.0.6)
.

As we can see in this exit, we have the management nodes, the Data nodes and the SQL or API nodes connected to the cluster. There are also a serial of SQL or API nodes that are free, without connections, that accept connections from any host, and that are used to status checks, backup creation, etc...

この出力からは、管理ノード、データノードおよび、SQL または API ノードがクラスタに接続していることがわかります。また、ホストからの接続を受け付けることができる、接続されていない空いている SQL または API ノードの番号もあります。また、番号は、状態確認、バックアップ作成などにも利用されます。

If we have just started the data nodes, we could see a message as the following:

データノードを起動すると、次のようなメッセージが表示されます。

[ndbd(NDB)] 2 node(s) id=3 @10.1.1.215 (mysql-5.1.34 ndb-7.0.6, starting, Nodegroup: 0, Master) id=4 @10.1.1.216 (mysql-5.1.34 ndb-7.0.6, starting, Nodegroup: 0)

This shows that the system is still starting the data nodes.

これは、システムがまだデータノードを開始状態であることを示します。

マネージャからのノードの起動と停止

It is possible to start and stop the nodes in the cluster from the Manager, this is, without having to go to the console of each node.

それぞれのノードのコンソールを使わずにマネージャからクラスタのノードの起動・停止ができます。

To stop a node, we will use the order:

ノードを停止するには次のようにします。

<id> stop

Being the <id> the number that is shown when you do a show.

<id> は、show コマンドで表示された番号です。

2 stop

To start the node that we have stopped, we use the order:

停止したノードを起動するには次のようにします。

<id> start

Being the <id> the number that is shown when we do a show.Example:

<id> は、show コマンドで表示された番号です。例えば次のようにします。

2 start

クラスタバックアップ

It is recommended to do a security copy of the cluster data and structures. For it, you have to follow these instructions:

クラスタのデータと設定はバックアップを取得することをお勧めします。そのためには、次の手順を実行します。

  1. .Start the administration server (ndb_mgm).
  2. .管理サーバ(ndb_mgm)を起動します。
  3. .Execute the START BACKUP command.
  4. .START BACKUP コマンドを実行します。
  5. .We will get an exit like this:
  6. .次のような出力が表示されます。
ndb_mgm> START BACKUP
Waiting for completed, this may take several minutes
Node 2: Backup 6 started from node 1
Node 2: Backup 6 started from node 1 completed
StartGCP: 267411 StopGCP: 267414
#Records: 2050 #LogRecords: 0
Data: 32880 bytes Log: 0 bytes

It is possible to start the shell security copy of the system using:

コマンドラインからは、次のように実行することができます。

ndb_mgm -e "START BACKUP"

These backups will create a serial of files in the directory: /var/lib/mysql-cluster/BACKUP/BACKUP-X of each node of the cluster, where the x is the backup number.

これらのバックアップは、クラスタのそれぞれのノードごとに /var/lib/mysql-cluster/BACKUP/BACKUP-X ディレクトリに順番に作成されます。x はバックアップ番号です。

In this directory are kept a serial of files with the following extensions:

このディレクトリには次の拡張子のファイルが置かれます。

  • Data: cluster data
  • Data: クラスタデータ
  • .ctl: cluster metadata
  • .ctl: クラスタのメタデータ
  • .log: cluster LOG files.
  • .log: クラスタのログファイル

リストア

Each node keeps "one part" of the DDBB in the backups, so to recompose the "complete balance" yo should do a restore of all the elements of the cluster, in order and one by one.

それぞれのノードがバックアップ内にデータベースの一部を持ちます。そのため復旧するためには、クラスタの全要素を一つずつリストアする必要があります。

事前準備

To restore a backup, you have previously to "restart" the nodes and eliminate their content. This is, to start them with the –initial parameter.

バックアップからリストアするには、事前にノードを再起動しデータを削除する必要があります。そのためには、-initial パラメータを付けて起動します。

ndbmtd –initial

リストア処理の順番

To restore a backup, you have to do it first with the node selected as "master". The first restoring will create the metadata, the rest only the data.

バックアップからリストアするには、マスターとして選択したノードから実行します。最初のリストアでメタデータを作成し、データはその次です。

リストア手順

The order to restore a backup is this (we take as example the restore of the backup #5 on the node id #3):

バックアップからのリストアの順番は以下の通りです。(この例では、ノード id #3 のバックアップ #5 のリストアを示します)

In the first node, we execute this in the Linux console:

最初のノードで、コマンドラインから以下のコマンドを実行します。

ndb_restore -b 5 -n 3 -m -r /var/lib/mysql-cluster/BACKUP/BACKUP-5

And we get the following exit:

次のような出力が表示されます。

Backup Id = 5
Nodeid = 3
backup path = /var/lib/mysql-cluster/BACKUP/BACKUP-5
Ndb version in backup files: Version 5.0.51

In the second and consecutive nodes, it will be similar, but without the “-m” parameter.

2台目およびそれ以降のノードでも似ていますが、"-m" パラメータをつけません。

ndb_restore -b 5 -n 4 -r /var/lib/mysql-cluster/BACKUP/BACKUP-5

The options that will be given to it are detailed next:

指定したオプションの詳細は次の通りです。

  • -b: Shows the backup number.
  • -b: バックアップ番号を指定します。
  • -n: shows the specific node (that could be seen in the manager with a "show").
  • -n: ノードを指定します。(マネージャから "show" で参照できます)
  • -m: shows that the cluster meta data should be restored.
  • -m: クラスタのメタデータが保存されていることを指定します。
  • -r: shows that data should be restored in the cluster.
  • -r: クラスタ内のリストアするデータを指定します。

After this, you should put the path to the directory ( put the path in the backup we have put in the -b)

この後に、ディレクトリのパス(-b で指定したバックアップがあるパス)を指定します。

クラスターログ

The MySQL cluster provides two kinds of logs.

MySQL クラスタには、2種類のログがあります。

クラスターログ

Includes the events generated by each node of the cluster. It is the most recommended log to see if something fails, so it includes the information of the whole cluster.

クラスタのそれぞれのノードで生成されたイベントが出力されます。クラスタ全体の情報が含まれているため、何か問題が発生した場合に真っ先に見るべきログです。

By default this log is at the directory /var/lib/mysql-cluster/ndb_1_cluster.log

デフォルトでは、このログは /var/lib/mysql-cluster/ndb_1_cluster.log にあります。

An example of this kind of logs is this:

ログのサンプルを以下に示します。

2009-05-26 11:56:59 [MgmSrvr] INFO     -- Node 5: mysqld --server-id=0
2009-05-26 12:14:32 [MgmSrvr] INFO     -- Mgmt server state: nodeid 6 reserved for ip 10.1.1.220, m_reserved_nodes 0000000000000062.
2009-05-26 12:14:32 [MgmSrvr] INFO     -- Node 6: mysqld --server-id=0
2009-05-26 13:35:47 [MgmSrvr] INFO     -- Mgmt server state: nodeid 6 freed, m_reserved_nodes 0000000000000022.
2009-05-26 13:46:44 [MgmSrvr] INFO     -- Mgmt server state: nodeid 6 reserved for ip 10.1.1.220, m_reserved_nodes 0000000000000062.
2009-05-26 13:46:44 [MgmSrvr] INFO     -- Node 6: mysqld --server-id=0
2009-05-26 13:46:44 [MgmSrvr] INFO     -- Node 2: Node 6 Connected
2009-05-26 13:46:45 [MgmSrvr] INFO     -- Node 3: Node 6 Connected
2009-05-26 13:46:45 [MgmSrvr] INFO     -- Node 3: Node 6: API version 5.0.51
2009-05-26 13:46:45 [MgmSrvr] INFO     -- Node 2: Node 6: API version 5.0.51

The useful information is identified with the words WARNING, ERROR y CRITICAL.

情報は、WARNING、ERROR、CRITICAL で区別されます。

ノードのログ

Each node of the cluster has its own logs, that are divided in two sub-logs. (all logs are at the directory/var/lib/mysql-cluster/).

クラスタのそれぞれのノードには、2つに分割されたそれぞれのログがあります。(全てのログは、/var/lib/mysql-cluster/にあります)

ndb_X_out.log

The first and most general log is: ndb_X_out.log (being X the node id).This log has the cluster general information and it is like this:

最初の全体のログは、ndb_X_out.log (X はノードid)です。このログは、クラスタの全体の情報を持っており、次のようなものです。


2009-09-29 13:15:51 [ndbd] INFO     -- Angel pid: 30514 ndb pid: 30515
NDBMT: MaxNoOfExecutionThreads=8
NDBMT: workers=4 threads=4
2009-09-29 13:15:51 [ndbd] INFO     -- NDB Cluster -- DB node 3
2009-09-29 13:15:51 [ndbd] INFO     -- mysql-5.1.34 ndb-7.0.6 --
2009-09-29 13:15:51 [ndbd] INFO     -- WatchDog timer is set to 40000 ms
2009-09-29 13:15:51 [ndbd] INFO     -- Ndbd_mem_manager::init(1) min: 4266Mb initial: 4286Mb
Adding 4286Mb to ZONE_LO (1,137151)
NDBMT: num_threads=7
thr: 1 tid: 30520 cpu: 1 OK BACKUP(0) DBLQH(0) DBACC(0) DBTUP(0) SUMA(0) DBTUX(0) TSMAN(0) LGMAN(0) PGMAN(0) RESTORE(0) DBINFO(0) PGMAN(5) 
thr: 0 tid: 30519 cpu: 0 OK DBTC(0) DBDIH(0) DBDICT(0) NDBCNTR(0) QMGR(0) NDBFS(0) TRIX(0) DBUTIL(0) 
thr: 2 tid: 30521 cpu: 2 OK PGMAN(1) DBACC(1) DBLQH(1) DBTUP(1) BACKUP(1) DBTUX(1) RESTORE(1) 
thr: 3 tid: 30522 cpu: 3 OK PGMAN(2) DBACC(2) DBLQH(2) DBTUP(2) BACKUP(2) DBTUX(2) RESTORE(2) 
thr: 4 tid: 30523 cpu: 4 OK PGMAN(3) DBACC(3) DBLQH(3) DBTUP(3) BACKUP(3) DBTUX(3) RESTORE(3) 
thr: 6 tid: 30515 cpu: 6 OK CMVMI(0) 
thr: 5 tid: 30524 cpu: 5 OK PGMAN(4) DBACC(4) DBLQH(4) DBTUP(4) BACKUP(4) DBTUX(4) RESTORE(4) 
saving 0x7f6161d38000 at 0x994538 (0)
2009-09-29 13:15:53 [ndbd] INFO     -- Start initiated (mysql-5.1.34 ndb-7.0.6)
saving 0x7f61621e8000 at 0x9ab2d8 (0)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
ndb_X_error.log

The second kind of log is the cluster error log that is named: ndb_X_error.log (being X the node id). In this log we have the errors that are made in the cluster and that link us to another log created at a higher leve of debug.

2つ目のログはクラスタのエラーログで、ndb_X_error.log (X はノードid) という名前です。このログには、クラスタで発生したエラーが記録されます。デバッグする場合の他のログへのポインタを示してくれます。

Here we see the exit of a error log file linked to another trace log:

他のトレースログへのポインタを示したエラーログの出力を以下に示します。

Current byte-offset of file-pointer is: 1067                      

Time: Friday 9 October 2009 - 12:57:13
Status: Temporary error, restart node
Message: Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s) (Arbitration error)
Error: 2305
Error data: Arbitrator decided to shutdown this node
Error object: QMGR (Line: 5300) 0x0000000e
Program: ndbmtd
Pid: 30515
Trace: /var/lib/mysql-cluster/ndb_3_trace.log.1 /var/lib/mysql-cluster/ndb_3_trace.log.1_t1 /var/lib/mysql-cluster/ndb_3_
Time: Tuesday 24 November 2009 - 12:01:59
Status: Temporary error, restart node
Message: Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s) (Arbitration error)
Error: 2305
Error data: Arbitrator decided to shutdown this node
Error object: QMGR (Line: 5300) 0x0000000a
Program: /usr/sbin/ndbmtd
Pid: 10348
Trace: /var/lib/mysql-cluster/ndb_3_trace.log.2 /var/lib/mysql-cluster/ndb_3_trace.log.2_t1 /var/lib/mysql-c 

As we can see it leaves a trace in the following files: /var/lib/mysql-cluster/ndb_3_trace.log.2, /var/lib/mysql-cluster/ndb_3_trace.log.2_t1, ...

見ての通り、トレースが /var/lib/mysql-cluster/ndb_3_trace.log.2, /var/lib/mysql-cluster/ndb_3_trace.log.2_t1, ... に残ります。

We can see a piece of one of these files and see how it is:

これらのファイルの一部分を以下に示します。

--------------- Signal ----------------
r.bn: 252 "QMGR", r.proc: 3, r.sigId: -411879481 gsn: 164 "CONTINUEB" prio: 0
s.bn: 252 "QMGR", s.proc: 3, s.sigId: -411879485 length: 3 trace: 0 #sec: 0 fragInf: 0
H'00000005 H'00000002 H'00000007
--------------- Signal ----------------
r.bn: 253 "NDBFS", r.proc: 3, r.sigId: -411879482 gsn: 164 "CONTINUEB" prio: 0
s.bn: 253 "NDBFS", s.proc: 3, s.sigId: -411879492 length: 1 trace: 0 #sec: 0 fragInf: 0
Scanning the memory channel every 10ms

It is easy to monitor these logs with Pandora itself doing searches of the words WARNING y CRITICAL.

これらのログは、WARNING および CRITICAL という単語を検索することで、Pandora 自身でモニタリングすることが簡単にできます。

一般的な手順

The management individual procedures of each kind module are given in the first place, and later the start and stop procedure for the cluster.

それぞれの種類のモジュールの個々の管理手順は最初に示しました。最後にクラスタの起動、停止手順を示します。

クラスタマネージャプロセスの管理

As root:

root にて:

To start the cluster manager:

クラスタマネージャの起動:

/etc/init.d/cluster_mgmt start

To check that it is running:

実行状態の確認:

/etc/init.d/cluster_mgmt status

To stop the Manager process:

マネージャプロセスの停止:

/etc/init.d/cluster_mgmt stop

マネージャでのノード管理

We enter in the shell of the cluster Manager with:

クラスタマネージャのシェルに入ります。

ndb_mgm

We stop the node that we want with:

停止したいノードを停止します。

2 stop

Being the "2" the ID of the node to stop.

"2" は停止するノードの ID です。

To start a node we will use the order:

ノードを起動するには次のようにします。

2 start

起動スクリプトでのデータノード管理

As root:

root にて:

To start a data node

データノードの起動には次のようにします。

/etc/init.d/cluster_node start

to stop a data node:

データノード停止は次の通りです。

/etc/init.d/cluster_node stop

To start a data node:

データノードの起動:

Warning, this operation delete the node data of the cluster and restart the redologs and could require a recovery from the backup.

警告: この操作は、クラスタのノードデータを削除します。また、redoログを作り直しバックアップからの復旧が必要になります。

/etc/init.d/ndbmtd initial

起動スクリプトによる SQL ノード管理

The SQL nodes are managed in the same way that a MySQL server that is not in cluster, through the starting script /etc/init.d/mysql

SQL ノードはクラスタではない MySQL サーバと同じように、/etc/init.d/mysql の起動スクリプトより操作します。

To start as many SQL nodes as the /etc/my.cnf file indicates.

/etc/my.cnf にある複数の SQL ノードを起動するには次のようにします。

/etc/init.d/mysql start

To stop as many SQL nodes as the /etc/my.cnf indicates.

/etc/my.cnf にある複数の SQL ノードを停止するには次のようにします。

/etc/init.d/mysql stop

Launching of a node manually if it is down. If a node downs we should start it manually from the command line following this sequence: First we need to be sure that there is no instance of the Node running:

ノードが停止していたら手動で起動します。ノードが停止している場合、次の手順でコマンドラインから手動で起動します。 最初に、稼働中のノードインスタンスが無いことを確認する必要があります。

ps -fea | grep -v grep | grep ndbmtd

Or also:

または、以下のコマンドを使います。

/etc/init.d/cluster_node status


If the command shows any ndbmtd process running, we should check the losgs to see why even with the process running it has been considered as down.

稼働している ndbmtd プロセスが表示されたら、停止すべきものがなぜ起動しているのかをログから確認します。

To start the node we use:

ノードを起動するには次のコマンドを使います。

/etc/init.d/cluster_node start

コマンドラインでのバックアップの作成

This is the method for creating a backup manually from the command line:

これは、コマンドラインから手動でバックアップを作成する方法です。

ndb_mgm -e "START BACKUP”

The backups are kept in:

バックアップは以下に作成されます。

/var/lib/mysql-cluster/BACKUP

The script of the daily backup is in the Annex 1.

日次バックアップは、補足1に示します。

コマンドラインからのバックアップのリストア

Once in the Node of which we want to restore the backup:

バックアップからリストアしたい場合は次のようにします。

ndb_restore -b X -n Y -m -r /var/lib/mysql-cluster/BACKUP/BACKUP-X

The “X” should be replaced by the number of the backup that you want to change and the "Y" by the number of the Node in which we are.

"X" はバックアップの番号で、"Y" はノードの番号です。

クラスタ全体の停止手順

Before stopping the cluster, you should do a backup of it, following the procedure previously defined or using the backup script described in the Annex 1.

クラスタを停止する前に、そのバックアップを取得します。事前に定義した次の手順に従うか、補足1に示すバックアップスクリプトを利用します。

Once we have finish the backup, it is also recommended to stop the Pandora FMS servers before stopping the cluster.

バックアップが完了したら、クラスタを停止する前に Pandora FMS サーバも停止します。

With all the necessary preparations done, the cluster will be stopped from the manager with the order SHUTDOWN.From the console:

全ての必要な準備が整ったら、クラスタはマネージャで SHUTDOWN で停止します。コンソールから次のようにします。

ndb_mgm
ndbm_mgm> SHUTDOWN

Or also from the command line:

または、コマンドラインからは次の通りです。

ndb_mgm -e SHUTDOWN

This will stop the management nodes and the cluster data ones, and the SQL (ore API) nodes stop separately, as we have said before.

これにより管理ノードおよびクラスタのデータノードが停止します。SQL (API) ノードは前に述べた用に別途停止します。

クラスタの起動手順

The start of the complete cluster is an operation that should be checked and while it is being done you should check the cluster main log and check that all has worked right.

クラスタの起動は、実行状況を確認しながら行う必要がある操作です。クラスタのメインのログを確認し、全ての処理が正しいか確認します。

When all the nodes are stopped, we should start first the main manager (the one of pandoradbhis), showing it the cluster configuration file.

全てのノードが停止しているとき、最初にクラスタ設定ファイルに記載されているメインのマネージャ(pandoradbhis のひとつ)を起動します。

Using the starting script.

起動スクリプトを利用します。

/etc/init.d/cluster_mgmt start

Or also from the command line.

または、コマンドラインから以下を実行します。

/usr/sbin/ndb_mgmd –config-file=/var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster

Next we start the secondary manager of the cluster ( the one of pandora2) giving the connection string and its node id the main manager.

次に、クラスタのセカンダリマネージャを、接続文字列およびノードIDを与えて起動します。

Using the starting script.

起動スクリプトを利用します。

/etc/init.d/cluster_mgmt start

Or also from the command line

または、コマンドラインから以下を実行します。

/usr/sbin/ndb_mgmd -c pandoradbhis –-ndb-nodeid=2 –configdir=/var/lib/mysql-cluster


At this point it is possible to connect to any of the two managers and show the status with a SHOW, but it is important to show that at this moment of the process the starting, the manager nodes do not see each other so they communicate through the data nodes and because of this any of them will show a different exit in which the only connected node of the cluster is the manager node itself.

この時点で、2つのマネージャに接続することができ、SHOW で状態を表示することができます。しかし、プロセスの起動を見ることが重要です。管理ノードはお互いががデータノードと通信できるかを確認しません。そのため、それぞれが異なる出力をし、クラスタに接続されたノードのみが自分自身が管理ノードとなることがあります。

Once the 2 manager nodes have been started, we can start launching the 2 data nodes (both in pandoradb1 and in pandoradb2) as it has been shown before, for example with the starting script:

2つの管理ノードを起動したら、前に示したように 2つのデータノード(pandoradb1 および pandoradb2 の両方)を起動できます。例えば、起動スクリプトで次のようにします。

/etc/init.d/cluster_node start

The process for starting the data nodes is slow and has several stages that could be followed in the cluster log.

データノードの起動プロセスはゆっくりで、いくつかのステージがあります。それはクラスタログで確認できます。

While doing this you should start the SQL and API nodes (both in pandoradb1 as inpandoradb2)as we have said before.

この間に、前に示した通り SQL および API ノード(pandoradb1 および pandoradb2)を起動します。

/etc/init.d/mysql start

Once all the starting orders have been given, you should check in the cluster log that the starting is completed without any error. At the end you could see that all the servers are connected form the manager with the SHOW command.

すべての起動手順を実施したら、エラーなく起動が完了しているかクラスタログを確認します。最後にマネージャから SHOW コマンドで全てのサーバが接続されているか見ることができます。

ndb_mgm -e SHOW

And seeing that all the started nodes are connected.

接続済の全ての起動ノードが見られます。

補足: 設定ファイル例

/etc/mysql/ndb_mgmd.cnf

File of the Cluster Manager. The secondary manager gets the configuration from the primary one (that should be active when the secondary is started),but this file should be in both nodes.

クラスタマネージャのファイルです。セカンダリマネージャは、プライマリマネージャ(セカンダリが起動したときにアクティブだったもの)から設定を取得します。ただし、このファイルは両方のノードに存在します。

# MySQL Cluster Configuration file
# By Pablo de la Concepcion Sanz <pablo.concepcion@artica.es>
# This file must be present on ALL the management nodes
# in the directory /var/lib/mysql-cluster/
# For some of the  parameters there is an explanation of the
# possible values that the parameter can take following this
# format:
# ParameterName (MinValue, MaxValue) [DefaultValue]

##########################################################
# MANAGEMENT NODES                                       #
# This nodes are the ones running the management console #
##########################################################
# More info at:
# http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-ndbd-definition.html
# Common configuration for all management nodes:

[ndb_mgmd default]

# This parameter is used to define which nodes can act as arbitrators.
# Only management nodes and SQL nodes can be arbitrators.
# ArbitrationRank can take one of the following values:
#    * 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 arbitrator
#         only if a node with a higher priority is not available
#         for that purpose.
#
# Normally, the management server should be configured as an
# arbitrator by setting its ArbitrationRank to 1 (the default for
# management nodes) and those for all SQL nodes to 0 (the default
# for SQL nodes).
ArbitrationRank=1

# Directory for management node log files
datadir=/var/lib/mysql-cluster

# Using 2 management servers helps guarantee that there is always an
# arbitrator in the event of network partitioning, and so is
# recommended for high availability. Each management server must be
# identified by a HostName. You may for the sake of convenience specify
# a node ID for any management server, although one will be allocated
# for it automatically; if you do so, it must be in the range 1-255
# inclusive and must be unique among all IDs specified for cluster
# nodes.

[ndb_mgmd]

id=1
# Hostname or IP address of management node

hostname=10.1.1.230


[ndb_mgmd]
id=2
# Hostname or IP address of management node
hostname=10.1.1.220


#################
# STORAGE NODES #
#################
# More info at:
# http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-ndbd-definition.html

# Options affecting ndbd processes on all data nodes:
[ndbd default]

# Redundancy (number of replicas):
# Using 2 replicas is recommended to guarantee availability of data;
# using only 1 replica does not provide any redundancy, which means
# that the failure of a single data node causes the entire cluster to
# shut down. We do not recommend using more than 2 replicas, since 2 is
# sufficient to provide high availability, and we do not currently test
# with greater values for this parameter.

NoOfReplicas=2

# Directory for storage node trace files, log files, pid files and error logs.
datadir=/var/lib/mysql-cluster



### Data Memory, Index Memory, and String Memory ###
# This parameter defines the amount of space (in bytes) available for storing
# database records. The entire amount specified by this value is allocated in
# memory, so it is extremely important that the machine has sufficient
# physical memory to accommodate it.
# DataMemory (memory for records and ordered indexes) (recomended 70% of RAM)
# DataMemory antes 22938MB (recomended 70% of RAM)
DataMemory=4096MB

# IndexMemory (memory for Primary key hash index and unique hash index)
# Usually between 1/6 or 1/8 of the DataMemory is enough, but depends on the
# number of unique hash indexes (UNIQUE in table def)
# Also can be calculated as 15% of RAM
# IndexMemory antes 4915MB
IndexMemory= 512MB

# This parameter determines how much memory is allocated for strings
# such as table names
#  * A value between 0 and 100 inclusive is interpreted as a percent of the
#    maximum default value (wich depends on a number of factors)
#  * A value greater than 100 is interpreted as a number of bytes.
StringMemory=25

### Transaction Parameters ###
# MaxNoOfConcurrentTransactions (32,4G) [4096]
# Sets the number of parallel transactions possible in a node
#
# This parameter must be set to the same value for all cluster data nodes.
# This is due to the fact that, when a data node fails, the oldest surviving
# node re-creates the transaction state of all transactions that were ongoing
# in the failed node.
#
# Changing the value of MaxNoOfConcurrentTransactions requires a complete
# shutdown and restart of the cluster.
# MaxNoOfConcurrentTransactions antes 4096
MaxNoOfConcurrentTransactions=8192

# MaxNoOfConcurrentOperations (32,4G) [32k]
# Sets the number of records that can be in update phase or locked
# simultaneously.
MaxNoOfConcurrentOperations=10000000

# MaxNoOfLocalOperations (32,4G)
# Recomentded to set (110% of MaxNoOfConcurrentOperations)
MaxNoOfLocalOperations=11000000

### Transaction Temporary Storage ###
# MaxNoOfConcurrentIndexOperations (0,4G) [8k]
# For queries using a unique hash index, another temporary set of operation
# records is used during a query's execution phase. This parameter sets the
# size of that pool of records. Thus, this record is allocated only while
# executing a part of a query. As soon as this part has been executed, the
# record is released. The state needed to handle aborts and commits is handled
# by the normal operation records, where the pool size is set by the parameter
# MaxNoOfConcurrentOperations.

#
# The default value of this parameter is 8192. Only in rare cases of extremely
# high parallelism using unique hash indexes should it be necessary to increase
# this value. Using a smaller value is possible and can save memory if the DBA
# is certain that a high degree of parallelism is not required for the cluster.
MaxNoOfConcurrentIndexOperations=8192

# MaxNoOfFiredTriggers (0,4G) [4000]
# The default value is sufficient for most situations. In some cases it can
# even be decreased if the DBA feels  certain the need for parallelism in the
# cluster is not high.
MaxNoOfFiredTriggers=4000

# TransactionBufferMemory (1k,4G) [1M]
# The memory affected by this parameter is used for tracking operations fired
# when updating index tables and reading unique indexes. This memory is used to
# store the key and column information for these operations. It is only very
# rarely that the value for this parameter needs to be altered from the default.
TransactionBufferMemory=1M

### Scans and Buffering ###

# MaxNoOfConcurrentScans (2,500) [256]
# This parameter is used to control the number of parallel scans that can be
# performed in the cluster. Each transaction coordinator can handle the number
# of parallel scans defined for this parameter. Each scan query is performed
# by scanning all partitions in parallel. Each partition scan uses a scan
# record in the node where the partition is located, the number of records
# being the value of this parameter times the number of nodes. The cluster
# should be able to sustain MaxNoOfConcurrentScans scans concurrently from all
# nodes in the cluster.
MaxNoOfConcurrentScans=400

# MaxNoOfLocalScans (32,4G)
# Specifies the number of local scan records if many scans are not fully
# parallelized. If the number of local scan records is not provided, it is
# calculated as the product of MaxNoOfConcurrentScans and the number of data
# nodes in the system. The minimum value is 32.
# MaxNoOfLocalScans antes 32
MaxNoOfLocalScans=6400

# BatchSizePerLocalScan (1,992) [64]
# This parameter is used to calculate the number of lock records used to
# handle concurrent scan operations.
#

# The default value is 64; this value has a strong connection to the
# ScanBatchSize defined in the SQL nodes.
BatchSizePerLocalScan=512

# LongMessageBuffer (512k,4G) (4M)
# This is an internal buffer used for passing messages within individual nodes
# and between nodes. Although it is highly unlikely that this would need to be
# changed, it is configurable. In MySQL Cluster NDB 6.4.3 and earlier, the
# default is 1MB; beginning with MySQL Cluster NDB 7.0.4, it is 4MB.
# LongMessageBuffer antes 32M
LongMessageBuffer=4M

### Logging and Checkpointing ###

# Redolog
# Set NoOfFragmentLogFiles to 6xDataMemory [in MB]/(4 *FragmentLogFileSize [in MB]
# The "6xDataMemory" is a good heuristic and is STRONGLY recommended.
# NoOfFragmentLogFiles=135
NoOfFragmentLogFiles=300

# FragmentLogFileSize (3,4G) [16M]
# Size of each redo log fragment, 4 redo log fragment makes up on fragment log
# file. A bigger Fragment log file size thatn the default 16M works better with

# high write load and is strongly recommended!!
# FragmentLogFileSize=256M
FragmentLogFileSize=16M

# By default, fragment log files are created sparsely when performing an
# initial start of a data node â that is, depending on the operating system
# and file system in use, not all bytes are necessarily written to disk.
# Beginning with MySQL Cluster NDB 6.3.19, it is possible to override this
# behavior and force all bytes to be written regardless of the platform
# and file system type being used by mean of this parameter.
# InitFragmentLogFiles takes one of two values:

#  * SPARSE. Fragment log files are created sparsely. This is the default value

#  * FULL. Force all bytes of the fragment log file to be written to disk.

# InitFragmentLogFiles (SPARSE,FULL) [SPARSE]

InitFragmentLogFiles=FULL

# This parameter sets a ceiling on how many internal threads to allocate for
# open files. Any situation requiring a change in this parameter should be
# reported as a bug.
MaxNoOfOpenFiles=80

# This parameter sets the initial number of internal threads to allocate for
# open files.
InitialNoOfOpenFiles=37

# MaxNoOfSavedMessages [25]
# This parameter sets the maximum number of trace files that are kept before
# overwriting old ones. Trace files are generated when, for whatever reason,
# the node crashes.
MaxNoOfSavedMessages=25



### Metadata Objects ###
# MaxNoOfAttributes (32, 4294967039) [1000]
# Defines the number of attributes that can be defined in the cluster.
#MaxNoOfAttributes antes 25000
MaxNoOfAttributes=4096

# MaxNoOfTables  (8, 4G) [128]
# A table object is allocated for each table and for each unique hash
# index in the cluster. This parameter sets the maximum number of table
# objects for the cluster as a whole.
MaxNoOfTables=8192

# MaxNoOfOrderedIndexes (0, 4G) [128]
# Sets the total number of hash indexes that can be in use in the system
# at any one time
#MaxNoOfOrderedIndexes antes 27000
MaxNoOfOrderedIndexes=2048
#MaxNoOfUniqueHashIndexes: Default value 64 Each Index 15 KB per node
#MaxNoOfUniqueHashIndexes antes 2500
MaxNoOfUniqueHashIndexes=1024
# MaxNoOfTriggers (0, 4G) [768]
# This parameter sets the maximum number of trigger objects in the cluster.
#MaxNoOfTriggers antes 770
MaxNoOfTriggers=4096

### Boolean Parameters ###

# Most of this parameters can be set to true (1 or Y) or false (0 or N)

# LockPagesInMainMemory (0,2) [0]
# On Linux and Solaris systems, setting this parameter locks data node
# processes into memory. Doing so prevents them from swapping to disk,
# which can severely degrade cluster performance.
# Possible values:
#       * 0: Disables locking. This is the default value.
#       * 1: Performs the lock after allocating memory for the process.
#       * 2: Performs the lock before memory for the process is allocated.
LockPagesInMainMemory=1
# This parameter specifies whether an ndbd  process should exit or perform
# an automatic restart when an error condition is encountered.
StopOnError=1
# This feature causes the entire  cluster to operate in diskless mode.
# When this feature is enabled, Cluster online backup is disabled. In
# addition, a partial start of the cluster is not possible.
Diskless=0
# Enabling this parameter causes NDBCLUSTER to try using O_DIRECT
# writes for local checkpoints and redo logs; this can reduce load on
# CPUs. We recommend doing so when using MySQL Cluster NDB 6.2.3 or
# newer on systems running Linux kernel 2.6 or later.
ODirect=1
# Setting this parameter to 1 causes backup files to be compressed. The
# compression used is equivalent to gzip --fast, and can save 50% or more
# of the space required on the data node to store uncompressed backup files
CompressedBackup=1
# Setting this parameter to 1 causes local checkpoint files to be compressed.
# The compression used is equivalent to gzip --fast, and can save 50% or
# more of the space required on the data node to store uncompressed
# checkpoint files
CompressedLCP=1

### Controlling Timeouts, Intervals, and Disk Paging ###

# Most of the timeout values are specified in milliseconds. Any exceptions
# to this are mentioned where applicable.
# TimeBetweenWatchDogCheck (70,4G) [6000]
# To prevent the main thread from getting stuck in an endless loop at some
# point, a âwatchdogâ
                     # the number of milliseconds between checks. If the process remains in the
# same state after three checks, the watchdog thread terminates it.
TimeBetweenWatchDogCheck=40000
# TimeBetweenWatchDogCheckInitial (70,4G) [6000]
# This is similar to the TimeBetweenWatchDogCheck parameter, except that
# TimeBetweenWatchDogCheckInitial controls the amount of time that passes
# between execution checks inside a database node in the early start phases
# during which memory is allocated.
TimeBetweenWatchDogCheckInitial=60000
# StartPartialTimeout (0,4G) [30000]
# This parameter specifies how long the Cluster waits for all data nodes to
# come up before the cluster initialization routine is invoked. This timeout
# is used to avoid a partial Cluster startup whenever possible.
#
# This parameter is overridden when performing an initial start or initial
# restart of the cluster.
#
# The default value is 30000 milliseconds (30 seconds). 0 disables the timeout,
# in which case the cluster may start only if all nodes are available.
StartPartialTimeout=30000
# StartPartitionedTimeout (0, 4G) [60000]
# If the cluster is ready to start after waiting for StartPartialTimeout
# milliseconds but is still possibly in a partitioned state, the cluster waits
# until this timeout has also passed. If StartPartitionedTimeout is set to 0,
# the cluster waits indefinitely.
#
# This parameter is overridden when performing an initial start or initial
# restart of the cluster.
StartPartitionedTimeout=60000
# StartFailureTimeout (0, 4G) [0]
# If a data node has not completed its startup sequence within the time
# specified by this parameter, the node startup fails. Setting this
# parameter to 0 (the default value) means that no data node timeout
# is applied.
StartFailureTimeout=1000000
# HeartbeatIntervalDbDb (10,4G)[1500]
# One of the primary methods of discovering failed nodes is by the use of
# heartbeats. This parameter states how often heartbeat signals are sent
# and how often to expect to receive them. After missing three heartbeat
# intervals in a row, the node is declared dead. Thus, the maximum time
# for discovering a failure through the heartbeat mechanism is four times
# the heartbeat interval.
# This parameter must not be changed drastically
HeartbeatIntervalDbDb=2000
# HeartbeatIntervalDbApi (100,4G)[1500]
# Each data node sends heartbeat signals to each MySQL server (SQL node)
# to ensure that it remains in contact. If a MySQL server fails to send
# a heartbeat in time it is declared âdead,â
                                            # transactions are completed and all resources released. The SQL node
# cannot reconnect until all activities initiated by the previous MySQL
# instance have been completed. The three-heartbeat criteria for this
# determination are the same as described for HeartbeatIntervalDbDb.
HeartbeatIntervalDbApi=3000
# TimeBetweenLocalCheckpoints (0,31)[20] Base-2 Logarithm
# This parameter is an exception in that it does not specify a time to
# wait before starting a new local checkpoint; rather, it is used to
# ensure that local checkpoints are not performed in a cluster where
# relatively few updates are taking place. In most clusters with high
# update rates, it is likely that a new local checkpoint is started
# immediately after the previous one has been completed.
#
# The size of all write operations executed since the start of the
# previous local checkpoints is added. This parameter is also exceptional
# in that it is specified as the base-2 logarithm of the number of 4-byte
# words, so that the default value 20 means 4MB (4 Ã 220) of write
# operations, 21 would mean 8MB, and so on up to a maximum value of 31,
# which equates to 8GB of write operations.
# All the write operations in the cluster are added together.
TimeBetweenLocalCheckpoints=20
# TimeBetweenGlobalCheckpoints (10,32000)[2000]
#  When a transaction is committed, it is committed in main memory in all
# nodes on which the data is mirrored. However, transaction log records
# are not flushed to disk as part of the commit. The reasoning behind this
# behavior is that having the transaction safely committed on at least two
# autonomous host machines should meet reasonable standards for durability.
#
# It is also important to ensure that even the worst of cases â a complete
# crash of the cluster â is handled properly. To guarantee that this happens,
# all transactions taking place within a given interval are put into a global
# checkpoint, which can be thought of as a set of committed transactions that
# has been flushed to disk. In other words, as part of the commit process, a
# transaction is placed in a global checkpoint group. Later, this group's log
# records are flushed to disk, and then the entire group of transactions is
# safely committed to disk on all computers in the cluster.
TimeBetweenGlobalCheckpoints=2000
# TimeBetweenEpochs (0,32000)[100]
# This parameter defines the interval between synchronisation epochs for MySQL
# Cluster Replication.
TimeBetweenEpochs=100
# TransactionInactiveTimeout (0,32000)[4000]
# This parameter defines a timeout for synchronisation epochs for MySQL Cluster
# Replication. If a node fails to participate in a global checkpoint within
# the time determined by this parameter, the node is shut down.
TransactionInactiveTimeout=30000

# TransactionDeadlockDetectionTimeout (50,4G)[1200]

# When a node executes a query involving a transaction, the node waits for
# the other nodes in the cluster to respond before continuing. A failure to
# respond can occur for any of the following reasons:
#       * The node is âdeadâ
#       * The node requested to perform the action could be heavily overloaded.
# This timeout parameter states how long the transaction coordinator waits
# for query execution by another node before aborting the transaction, and
# is important for both node failure handling and deadlock detection.
TransactionDeadlockDetectionTimeout=1200
# DiskSyncSize (32k,4G)[4M]
# This is the maximum number of bytes to store before flushing data to a
# local checkpoint file. This is done in order to prevent write buffering,
# which can impede performance significantly. This parameter is NOT
# intended to take the place of TimeBetweenLocalCheckpoints.
DiskSyncSize=4M
# DiskCheckpointSpeed (1M,4G)[10M]
# The amount of data,in bytes per second, that is sent to disk during a
# local checkpoint.
DiskCheckpointSpeed=10M
# DiskCheckpointSpeedInRestart (1M,4G)[100M]
# The amount of data,in bytes per second, that is sent to disk during a
# local checkpoint as part of a restart operation.
DiskCheckpointSpeedInRestart=100M
# ArbitrationTimeout (10,4G)[1000]
# This parameter specifies how long data nodes wait for a response from
# the arbitrator to an arbitration message. If this is exceeded, the
# network is assumed to have split.
ArbitrationTimeout=10

### Buffering and Logging ###

# UndoIndexBuffer (1M,4G)[2M]
# The UNDO index buffer, is used during local checkpoints. The NDB storage
# engine uses a recovery scheme based on checkpoint consistency in
# conjunction with an operational REDO log. To produce a consistent
# checkpoint without blocking the entire system for writes, UNDO logging
# is done while performing the local checkpoint.

# This buffer is 2MB by default. The minimum value is 1MB, which is
# sufficient for most applications. For applications doing extremely
# large or numerous inserts and deletes together with large
# transactions and large primary keys, it may be necessary to
# increase the size of this buffer. If this buffer is too small,
# the NDB storage engine issues internal error code 677 (Index UNDO
# buffers overloaded).
# IMPORTANT: It is not safe to decrease the value of this parameter
# during a rolling restart.
UndoIndexBuffer=2M
# UndoDataBuffer (1M,4G)[16M]
# This parameter sets the size of the UNDO data buffer, which performs
# a function similar to that of the UNDO index buffer, except the UNDO
# data buffer is used with regard to data memory rather than index memory
# If this buffer is too small and gets congested, the NDB storage
# engine issues internal error code 891 (Data UNDO buffers overloaded).
# IMPORTANT: It is not safe to decrease the value of this parameter
# during a rolling restart.

UndoDataBuffer=16M
# RedoBuffer (1M,4G)[32M]
# All update activities also need to be logged. The REDO log makes it
# possible to replay these updates whenever the system is restarted.
# The NDB recovery algorithm uses a âfuzzyâ
                                           # together with the UNDO log, and then applies the REDO log to play
# back all changes up to the restoration point.
# If this buffer is too small, the NDB storage engine issues error
# code 1221 (REDO log buffers overloaded).
# IMPORTANT: It is not safe to decrease the value of this parameter
# during a rolling restart.
RedoBuffer=32M
#

## Logging ##

#
# In managing the cluster, it is very important to be able to control
# the number of log messages sent for various event types to stdout.
# For each event category, there are 16 possible event levels (numbered
# 0 through 15). Setting event reporting for a given event category to
# level 15 means all event reports in that category are sent to stdout;
# setting it to 0 means that there will be no event reports made in
# that category.
# More info at:
# http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-log-events.html
#
# LogLevelStartup (0,15)[1]
# The reporting level for events generated during startup of the process.
LogLevelStartup=15
# LogLevelShutdown (0,15)[0]
# The reporting level for events generated as part of graceful shutdown
# of a node.

LogLevelShutdown=15
# LogLevelStatistic (0,15)[0]
# The reporting level for statistical events such as number of primary
# key reads, number of updates, number of inserts, information relating
# to buffer usage, and so on.
LogLevelStatistic=15
# LogLevelCheckpoint (0,15)[0]
# The reporting level for events generated by local and global checkpoints.
LogLevelCheckpoint=8
# LogLevelNodeRestart (0,15)[0]
# The reporting level for events generated during node restart.
LogLevelNodeRestart=15
# LogLevelConnection (0,15)[0]
# The reporting level for events generated by connections between cluster
# nodes.
LogLevelConnection=0
# LogLevelError (0,15)[0]
# The reporting level for events generated by errors and warnings by the
# cluster as a whole. These errors do not cause any node failure but are
# still considered worth reporting.

LogLevelError=15

# LogLevelCongestion (0,15)[0]
# The reporting level for events generated by congestion. These errors do
# not cause node failure but are still considered worth reporting.
LogLevelCongestion=0
# LogLevelInfo (0,15)[0]
# The reporting level for events generated for information about the general

# state of the cluster.
LogLevelInfo=3
# MemReportFrequency (0,4G)[0]
# This parameter controls how often data node memory usage reports are recorded
# in the cluster log; it is an integer value representing the number of seconds
# between reports.
# Each data node's data memory and index memory usage is logged as both a
# percentage and a number of 32 KB pages of the DataMemory and IndexMemory.
# The minimum value in which case memory reports are logged only when memory
# usage reaches certain percentages (80%, 90%, and 100%)

MemReportFrequency=900
# When a data node is started with the --initial, it initializes the redo log
# file during Start Phase 4. When very large values are set for
# NoOfFragmentLogFiles, FragmentLogFileSize, or both, this initialization can
# take a long time. StartupStatusReportFrequency configuration parameter
# make reports on the progress of this process to be logged periodically.
StartupStatusReportFrequency=30


### Backup Parameters ###

# This section define memory buffers set aside for execution of
# online backups.
# IMPORTANT: When specifying these parameters, the following relationships
# must hold true. Otherwise, the data node will be unable to start:
#       * BackupDataBufferSize >= BackupWriteSize + 188KB
#       * BackupLogBufferSize >= BackupWriteSize + 16KB
#       * BackupMaxWriteSize >= BackupWriteSize
#
# BackupReportFrequency (0,4G)[0]
# This parameter controls how often backup status reports are issued in
# the management client during a backup, as well as how often such reports
# are written to the cluster log. BackupReportFrequency represents the time
# in seconds between backup status reports.
BackupReportFrequency=10

# BackupDataBufferSize (0,4G)[16M]
# In creating a backup, there are two buffers used for sending data to the
# disk. The backup data buffer is used to fill in data recorded by scanning
# a node's tables. Once this buffer has been filled to the level specified
# as BackupWriteSize (see below), the pages are sent to disk. While
# flushing data to disk, the backup process can continue filling this
# buffer until it runs out of space. When this happens, the backup process
# pauses the scan and waits until some disk writes have completed freed up
# memory so that scanning may continue.
BackupDataBufferSize=16M

# BackupLogBufferSize (0,4G)[16M]
# The backup log buffer fulfills a role similar to that played by the backup
# data buffer, except that it is used for generating a log of all table
# writes made during execution of the backup. The same principles apply for
# writing these pages as with the backup data buffer, except that when
# there is no more space in the backup log buffer, the backup fails.
# The default value for this parameter should be sufficient for most
# applications. In fact, it is more likely for a backup failure to be
# caused by insufficient disk write speed than it is for the backup
# log buffer to become full.
# It is preferable to configure cluster nodes in such a manner that the
# processor becomes the bottleneck rather than the disks or the network
# connections.

BackupLogBufferSize=16M

# BackupMemory (0,4G)[32]
# This parameter is simply the sum of BackupDataBufferSize and
# BackupLogBufferSize.
BackupMemory=64M

# BackupWriteSize (2k,4G)[256k]

# This parameter specifies the default size of messages written to disk
# by the backup log and backup data buffers.
BackupWriteSize=256K
# BackupMaxWriteSize (2k,4G)[1M]
# This parameter specifies the maximum size of messages written to disk
# by the backup log and backup data buffers.
BackupMaxWriteSize=1M
# This parameter specifies the directory in which backups are placed
# (The backups are stored in a subdirectory called BACKUPS)
BackupDataDir=/var/lib/mysql-cluster/


### Realtime Performance Parameters ###


# This parameters are used in scheduling and locking of threads to specific
# CPUs on multiprocessor data node hosts.
# NOTE: To make use of these parameters, the data node process must be run as
# system root.
# Setting these parameters allows you to take advantage of real-time scheduling
# of NDBCLUSTER threads (introduced in MySQL Cluster NDB 6.3.4) to get higher
# throughput.

# On systems with multiple CPUs, these parameters can be used to lock
# NDBCLUSTER
# threads to specific CPUs
# LockExecuteThreadToCPU (0,64k)
# When used with ndbd, this parameter (now a string) specifies the ID of the
# CPU assigned to handle the NDBCLUSTER  execution thread. When used with
# ndbmtd, the value of this parameter is a comma-separated list of CPU IDs
# assigned to handle execution threads. Each CPU ID in the list should be
# an integer in the range 0 to 65535 (inclusive)
# The number of IDs specified should match the number of execution threads
# determined by MaxNoOfExecutionThreads
LockExecuteThreadToCPU=0,1,2,3,4,5,6,7
# RealTimeScheduler (0,1)[0]
# Setting this parameter to 1 enables real-time scheduling of NDBCLUSTER
# threads
RealTimeScheduler=1
# SchedulerExecutionTimer (0,110000)[50]
#  This parameter specifies the time in microseconds for threads to be
# executed in the scheduler before being sent. Setting it to 0 minimizes
# the response time; to achieve higher throughput, you can increase the
# value at the expense of longer response times.
# The default is 50 ÎŒsec, which our testing shows to increase throughput
# slightly in high-load cases without materially delaying requests.

SchedulerExecutionTimer=100
# SchedulerSpinTimer (0,500)[0]
# This parameter specifies the time in microseconds for threads to be executed
# in the scheduler before sleeping.
SchedulerSpinTimer=400
#Threads
# MaxNoOfExecutionThreads (2,8)
# For 8 or more cores the recomended value is 8
MaxNoOfExecutionThreads=8

# Options for data node "A":
[ndbd]
id=3
hostname=10.1.1.215         # Hostname or IP address

# Options for data node "B":
[ndbd]
id=4
hostname=10.1.1.216         # Hostname or IP address

#######################################
# SQL NODES (also known as API NODES) #
#######################################

# Common SQL Nodes Parameters

[mysqld default]
# This parameter is used to define which nodes can act as arbitrators.
# Only management nodes and SQL nodes can be arbitrators.
# ArbitrationRank can take one of the following values:
#    * 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 arbitrator
#         only if a node with a higher priority is not available
#         for that purpose.
#
# Normally, the management server should be configured as an
# arbitrator by setting its ArbitrationRank to 1 (the default for
# management nodes) and those for all SQL nodes to 0 (the default
# for SQL nodes).

ArbitrationRank=2



# BatchByteSize (1024,1M) [32k]

# For queries that are translated into full table scans or range scans on

# indexes, it is important for best performance to fetch records in properly
# sized batches. It is possible to set the proper size both in terms of number
# of records (BatchSize) and in terms of bytes (BatchByteSize). The actual
# batch size is limited by both parameters.
# The speed at which queries are performed can vary by more than 40% depending
# upon how this parameter is set
# This parameter is measured in bytes and by default is equal to 32KB.
BatchByteSize=32k
# BatchSize (1,992) [64]
# This parameter is measured in number of records.
BatchSize=512
# MaxScanBatchSize (32k,16M) [256k]
# The batch size is the size of each batch sent from each data node.
# Most scans are performed in parallel to protect the MySQL Server from
# receiving too much data from many nodes in parallel; this parameter sets
# a limit to the total batch size over all nodes.

MaxScanBatchSize=8MB

# SQL node options:
[mysqld]
id=11
# Hostname or IP address
hostname=10.1.1.215
[mysqld]
id=12
# Hostname or IP address
hostname=10.1.1.216

# Extra SQL nodes (also used for backup & checks)
[mysqld]
id=13
[mysqld]
id=14
[mysqld]
id=15
[mysqld]
id=16
[mysqld]
id=17
[mysqld]
id=18


##################
# TCP PARAMETERS #
##################
[tcp default]



# Increasing the sizes of these 2 buffers beyond the default values
# helps prevent bottlenecks due to slow disk I/O.
SendBufferMemory=3M

ReceiveBufferMemory=3M

/etc/mysql/my.cf

Configuration file of the SQL Nodes (that are also the NDB nodes).

SQL ノードの設定ファイル (NDB ノードも同様)


# MySQL SQL node config 
# =====================
# Written by Pablo de la Concepcion, pablo.concepcion@artica.es
#
# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
skip-locking
key_buffer_size = 4000M
table_open_cache = 5100
sort_buffer_size = 64M
net_buffer_length = 512K
read_buffer_size = 128M
read_rnd_buffer_size = 256M
myisam_sort_buffer_size = 64M

query_cache_size = 256M
query_cache_limit = 92M

#slow_query_log = /var/log/mysql/mysql-slow.log
max_connections = 500
table_cache = 9060


# Thread parameters
thread_cache_size = 1024
thread_concurrency = 64
thread_stack = 256k

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

# The safe_mysqld script
[safe_mysqld]
log-error       = /var/log/mysql/mysqld.log
socket          = /var/lib/mysql/mysql.sock

[mysqldump]
socket          = /var/lib/mysql/mysql.sock
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 10000M
sort_buffer_size = 20M
read_buffer = 10M
write_buffer = 10M

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin

#log        = /var/log/mysqld_multi.log
# user       = multi_admin
# password   = secret

# If you want to use mysqld_multi uncomment 1 or more mysqld sections
# below or add your own ones.

# WARNING
# --------
# If you uncomment mysqld1 than make absolutely sure, that database mysql,
# configured above, is not started.  This may result in corrupted data!
# [mysqld1]
# port       = 3306
# datadir    = /var/lib/mysql
 pid-file   = /var/lib/mysql/mysqld.pid
# socket     = /var/lib/mysql/mysql.sock
# user       = mysql


# Cluster configuration
#       by Pablo de la Concepcion <pablo.concepcion@artica.es>


# Options for mysqld process:
[mysqld]

# Run NDB storage engine
ndbcluster

# Location of management servers
ndb-connectstring="10.1.1.215:1186;10.1.1.216:1186"

# Number of connections in the connection pool, the config.ini file of the
# cluster have to define also [API] nodes at least for each connection.
ndb-cluster-connection-pool=3

# Forces sending of buffers to NDB  immediately, without waiting
# for other threads. Defaults to ON.
ndb-force-send=1

# Forces NDB to use a count of records during SELECT COUNT(*) query planning
# to speed up this type of query. The default value is ON. For faster queries
# overall, disable this feature by setting the value of ndb_use_exact_count
# to OFF.
ndb-use-exact-count=0

#  This variable can be used to enable recording in the MySQL error log
# of information specific to the NDB storage engine. It is normally of
# interest only when debugging NDB storage engine code.
# The default value is 0, which means that the only NDB-specific
# information written to the MySQL error log relates to transaction
# handling. If the value is greater than 0 but less than 10, NDB table
# schema and connection events are also logged, as well as whether or
# not conflict resolution is in use, and other NDB errors and information.
# If the value is set to 10 or more, information about NDB internals, such
# as the progress of data distribution among cluster nodes, is also
# written to the MySQL error log.

ndb-extra-logging=00

# Determines the probability of gaps in an autoincremented column.
# Set it to 1 to minimize this. Setting it to a high value for
# optimization â makes inserts faster, but decreases the likelihood
# that consecutive autoincrement numbers will be used in a batch
# of inserts. Default value: 32. Minimum value: 1.

ndb-autoincrement-prefetch-sz=256

engine-condition-pushdown=1

# Options for ndbd process:

[mysql_cluster]
# Location of management servers (list of host:port separated by ;)

ndb-connectstring="10.1.1.230:1186;10.1.1.220:1186"

/etc/cron.daily/backup_cluster

NOTE: as it is a cluster, the mysldump is not reliable because the writting is distributed and the coherence could not be warranted. Though it is not recommended, and it is preferable to do a complete backup of the cluster (see the following section), you could try to get a valid backup if you limit the writting in the cluster (stopping the pandora servers) and in the mode single user (ver http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-single-user-mode.html ).

注意: クラスタでは mysqldump は信頼できません。なぜなら、書き込みが分散され、一貫性が保証されないからです。お勧めではありませんが、クラスタの完全なバックアップを行うことが望ましいです(次の説明を参照)。クラスタへの書き込みを制限(pandoraサーバの停止)したり、単一ユーザモード( http://dev.mysql.com/doc/refman/5.1/ja/mysql-cluster-single-user-mode.html )にすることによって、正しくバックアップを取得することができます。

This backup script does the backup through the "secure" system (command START BACKUP) from the cluster management console.

このバックアップスクリプトは、クラスタマネージャコンソールから "secure" システム(START BACKUP コマンド)を通してバックアップを実行します。

#!/bin/bash

LOG_TEMPORAL=/tmp/mysql_cluster_backup_script.log

#Directorios de los Backups

DIR_NODO3=/var/lib/mysql-cluster/BACKUPS/Nodo_03
DIR_NODO4=/var/lib/mysql-cluster/BACKUPS/Nodo_04

# Se lanza el backup y se espera a que se complete
/usr/bin/ndb_mgm -e "START BACKUP WAIT COMPLETED" > $LOG_TEMPORAL
echo "Procesando Log $LOG_TEMPORAL"
NUM_BACKUP=`grep Backup $LOG_TEMPORAL | grep completed | awk '{print $4}'`
echo "Procesando backup $NUM_BACKUP"

# Se copian por scp los backups
scp -i /root/.ssh/backup_key_rsa -r root@10.1.1.215:/var/lib/mysql-cluster/BACKUP/BACKUP-$NUM_BACKUP/ $DIR_NODO3 >>$LOG_TEMPORAL 2>> /var/lib/mysql-cluster/BACKUPS/logs/backup_$NUM_BACKUP.err

scp -i /root/.ssh/backup_key_rsa -r root@10.1.1.216:/var/lib/mysql-cluster/BACKUP/BACKUP-$NUM_BACKUP/ $DIR_NODO4 >>$LOG_TEMPORAL 2>> /var/lib/mysql-cluster/BACKUPS/logs/backup_$NUM_BACKUP.err

#Se almacena el log
mv $LOG_TEMPORAL  /var/lib/mysql-cluster/BACKUPS/logs/backup_$NUM_BACKUP.log

	Para programar este script diariamente debemos poner la siguiente linea en el fichero 	/etc/crontab (Esto hará un backup diario a las 5 de la mañana)

00 5   * * *   root    /tmp/backup_cluster

/etc/init.d/cluster_mgmt

NOTE: this script is slightly different in the secondary cluster management console (different parameters in DAEMON_PARAMETERS).

注意: このスクリプトは、セカンダリクラスタ管理コンソールでは若干異なります。(DAEMON_PARAMETERS のパラメータが異なります)


#!/bin/bash
# Copyright (c) 2005-2009 Artica ST
#
# Author: Sancho Lerena <slerena@artica.es> 2006-2009
#
# /etc/init.d/cluster_mgmt
#
# System startup script for MYSQL Cluster Manager
#
### BEGIN INIT INFO
# Provides:       cluster_mgmt
# Required-Start: $syslog cron
# Should-Start:   $network cron
# Required-Stop:  $syslog
# Should-Stop:    $network
# Default-Start:  2 3 5
# Default-Stop:   0 1 6
# Short-Description: MySQL Cluster Management console startup script
# Description:    See short description
### END INIT INFO

export PROCESS_DAEMON=ndb_mgmd
export PROCESS_PARAMETERS="--config-file=/var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster"

# Uses a wait limit before sending a KILL signal, before trying to stop
# Pandora FMS server nicely. Some big systems need some time before close
# all pending tasks / threads.

export MAXWAIT=300

# Check for SUSE status scripts
if [ -f /etc/rc.status ]
then
        . /etc/rc.status
        rc_reset
else
        # Define rc functions for non-suse systems, "void" functions.
        function rc_status () (VOID=1;)
        function rc_exit () (exit;)
        function rc_failed () (VOID=1;)

fi

# This function replace pidof, not working in the same way in different linux distros

function pidof_process () (
        # This sets COLUMNS to XXX chars, because if command is run
        # in a "strech" term, ps aux don't report more than COLUMNS
        # characters and this will not work.
        COLUMNS=400
        PROCESS_PID=`ps aux | grep "$PROCESS_DAEMON $PROCESS_PARAMETERS" | grep -v grep | tail -1 | awk '{ print $2 }'`
        echo $PROCESS_PID
)

# Main script

if [ `which $PROCESS_DAEMON | wc -l` == 0 ]
then
        echo "Server not found, please check setup and read manual"
        rc_status -s
        rc_exit
fi

case "$1" in
        start)
                PROCESS_PID=`pidof_process`
                if [ ! -z "$PROCESS_PID" ]
                then
                        echo "Server is currently running on this machine with PID ($PROCESS_PID). Aborting now..."
                        rc_failed 1
                        rc_exit
                fi

                $PROCESS_DAEMON $PROCESS_PARAMETERS
                sleep 1

                PANDORA_PID=`pidof_process`

                if [ ! -z "$PANDORA_PID" ]
                then
                        echo "Server is now running with PID $PANDORA_PID"
                        rc_status -v
                else
                        echo "Cannot start Server. Aborted."
                        rc_status -s
                fi
        ;;

        stop)
                PANDORA_PID=`pidof_process`
                if [ -z "$PANDORA_PID" ]
                then
                        echo "Server is not running, cannot stop it."
                        rc_failed
                else
                        echo "Stopping Server"
                        kill $PANDORA_PID
                        COUNTER=0

                        while [  $COUNTER -lt $MAXWAIT ]
                        do
                                PANDORA_PID=`pidof_process`
                                if [ -z "$PANDORA_PID" ]
                                then
                                        COUNTER=$MAXWAIT
                                fi
                                COUNTER=`expr $COUNTER + 1`
                                sleep 1
                        done

                        # Send a KILL -9 signal to process, if it's alive after 60secs, we need
                        # to be sure is really dead, and not pretending...
                        if [ ! -z "$PANDORA_PID" ]
                        then
                                kill -9 $PANDORA_PID
                        fi
                        rc_status -v
                fi
        ;;
        status)
                PANDORA_PID=`pidof_process`
                if [ -z "$PANDORA_PID" ]
                then
                        echo "Server is not running."
                        rc_status
                else
                        echo "Server is running with PID $PANDORA_PID."
                        rc_status
                fi
        ;;
  force-reload|restart)
                $0 stop
                $0 start
                ;;
  *)
                echo "Usage: server { start | stop | restart | status }"
                exit 1
esac
rc_exit

/etc/init.d/cluster_node


#!/bin/bash
# Copyright (c) 2005-2009 Artica ST
#
# Author: Sancho Lerena <slerena@artica.es> 2006-2009
#
# /etc/init.d/cluster_node
#
# System startup script for MYSQL Cluster Node storage
#
### BEGIN INIT INFO
# Provides:       cluster_node
# Required-Start: $syslog cron
# Should-Start:   $network cron
# Required-Stop:  $syslog
# Should-Stop:    $network
# Default-Start:  2 3 5
# Default-Stop:   0 1 6
# Short-Description: MySQL Cluster Node startup script
# Description:    See short description
### END INIT INFO

export PROCESS_DAEMON=ndb_ndb
export PROCESS_PARAMETERS="-d"

# Uses a wait limit before sending a KILL signal, before trying to stop
# Pandora FMS server nicely. Some big systems need some time before close
# all pending tasks / threads.

export MAXWAIT=300

# Check for SUSE status scripts
if [ -f /etc/rc.status ]
then
        . /etc/rc.status
        rc_reset
else
        # Define rc functions for non-suse systems, "void" functions.
        function rc_status () (VOID=1;)
        function rc_exit () (exit;)
        function rc_failed () (VOID=1;)

fi

# This function replace pidof, not working in the same way in different linux distros

function pidof_process () (
        # This sets COLUMNS to XXX chars, because if command is run
        # in a "strech" term, ps aux don't report more than COLUMNS
        # characters and this will not work.
        COLUMNS=400
        PROCESS_PID=`ps aux | grep "$PROCESS_DAEMON $PROCESS_PARAMETERS" | grep -v grep | tail -1 | awk '{ print $2 }'`
        echo $PROCESS_PID
)

# Main script

if [ `which $PROCESS_DAEMON | wc -l` == 0 ]
then
        echo "Server not found, please check setup and read manual"
        rc_status -s
        rc_exit
fi

case "$1" in
        start)
                PROCESS_PID=`pidof_process`
                if [ ! -z "$PROCESS_PID" ]
                then
                        echo "Server is currently running on this machine with PID ($PROCESS_PID). Aborting now..."
                        rc_failed 1
                        rc_exit
                fi

                $PROCESS_DAEMON $PROCESS_PARAMETERS
                sleep 1

                PANDORA_PID=`pidof_process`

                if [ ! -z "$PANDORA_PID" ]
                then
                        echo "Server is now running with PID $PANDORA_PID"
                        rc_status -v
                else
                        echo "Cannot start Server. Aborted."
                        rc_status -s
                fi
        ;;

        stop)
                PANDORA_PID=`pidof_process`
                if [ -z "$PANDORA_PID" ]
                then
                        echo "Server is not running, cannot stop it."
                        rc_failed
                else
                        echo "Stopping Server"
                        kill $PANDORA_PID
                        COUNTER=0

                        while [  $COUNTER -lt $MAXWAIT ]
                        do
                                PANDORA_PID=`pidof_process`
                                if [ -z "$PANDORA_PID" ]
                                then
                                        COUNTER=$MAXWAIT
                                fi
                                COUNTER=`expr $COUNTER + 1`
                                sleep 1
                        done

                        # Send a KILL -9 signal to process, if it's alive after 60secs, we need
                        # to be sure is really dead, and not pretending...
                        if [ ! -z "$PANDORA_PID" ]
                        then
                                kill -9 $PANDORA_PID
                        fi
                        rc_status -v
                fi
        ;;
        status)
                PANDORA_PID=`pidof_process`
                if [ -z "$PANDORA_PID" ]
                then
                        echo "Server is not running."
                        rc_status
                else
                        echo "Server is running with PID $PANDORA_PID."
                        rc_status
                fi
        ;;
  force-reload|restart)
                $0 stop
                $0 start
                ;;
  *)
                echo "Usage: server { start | stop | restart | status }"
                exit 1
esac
rc_exit