Detailed description of the config. ini configuration file of the MySQL Cluster management Node

1. Defining the TCP/IP connection of the MySQL Cluster is the default transmission protocol used by the MySQL Cluster to establish connections. Normally, no connection is required. You can use [tcp default] or [TCP] for definition. 1. SendBufferMemoryTCP transmission cache. The default value is 256KB. 2. SendSignalId transmits the Message ID over the network. This feature is disabled by default (value: Y/N or 1/0 ). 3. Enabling this parameter in Checksum will calculate the Checksum for all parameters before all messages are placed in the sending buffer. This feature is disabled by default (value: Y/N or 1/0 ). 4. ReceiveBufferMemory specifies the buffer size used to receive data from TCP/IP Socket. You almost do not need to change the default value of this parameter. The default value is 64 KB. 2. Define the DEFAULT behavior of the data node NoOfReplicas as a required parameter and use "[ndbd default]" for definition. 1. NoOfReplicas defines the number of copies stored in each table in the cluster, and also specifies the node group size. A node group is a set of nodes that store the same information. Generally, you do not need to specify a value for this parameter. NoOfReplicas has no default value. The maximum value is 4. 3. Define the Management Server (MGM) used to configure and manage server behavior. The following parameters can be ignored. If so, the default value is used. If ExecuteOnComputer or HostName is not defined, it is specified as localhost. You can use [NDB_MGMD] to define the behavior of a single management node, or use [NDB_MGMD DEFAULT] to define the DEFAULT behavior of multiple management nodes. 1. Unique node ID in the NodeId cluster. value range: 1 ~ 63. 2. HostName specifies the node host name or IP address. 3. ExecuteOnComputer references one of the computers defined in "[COMPUTER. 4. PortNumber manages the server listening port (default value: 2202 ). 5. LogDestination:
5.1. CONSOLE
Output logs to the standard output device (stdout ).
5.2. SYSLOG: facility = syslog
Send the log to the syslog (System Log) soft device. Possible values:
Auth, authpriv, cron, daemon, ftp, kern, lpr, mail, news, syslog
, User, uucp, local0, local12 ~ 7
5.3. FILE: filename =/var/log/mgmd. log, maxsize = 1000000, maxfiles = 6
To output logs to a file, you can specify the following values:
Filename: name of the log file.
Maxsize: the maximum size of a log file. If the size is greater than this size, a new log file is automatically created.
Maxfiles: Maximum number of log files. 6. ArbitrationRank specifies the node that plays the decision role. Only MGM and SQL nodes can be used (default value: 1 ). Generally, set the value to 1, set all SQL nodes to 0, and use the MGM server as the Decision-Making Program. One of the following values is recommended:
0: this node is never used for decision-making.
1: The node has a high priority.
2: The node has a low limit. 7. ArbitrationDelay specifies the latency of the Management Server for decision-making requests. The default value is 0, in milliseconds. Generally, you do not need to change it. 8. DataDir stores the location of the Management Server output files, including logs, process output files, and program pid files. You can set the FILE parameter of LogDestination to overwrite a log FILE. 4. Define the data node (NDBD) for configuring the behavior of the data node. ExecuteOnComputer or HostName is a required parameter. For various parameters, you can use the suffix k, M, or G to specify the unit. Use [NDBD] for definition. 1. When NodeId is enabled, you can assign an ID (that is, a data node ID) in the command line, or in the configuration file. 2. HostName specifies the node host name or IP address. 3. ExecuteOnComputer references one of the computers defined in "[COMPUTER. 4. DataDir specifies the directory for storing trace files, log files, pid files, and error logs. 5. BackupDataDir specifies the directory for storing backups. The default value is {FileSystemPath}/BACKUP. 6. DataMemory specifies the data memory. The default value is 80 MB and the minimum value is 1 MB. There is no size limit. 7. IndexMemory specifies the index memory. The default value is 18 MB, and the minimum value is 1 MB. There is no size limit. 8. MaxNoOfConcurrentTransactions is used to set the number of possible concurrent transactions in the node. The default value is 4096. For all nodes, the parameter must be set to the same value. 9. MaxNoOfConcurrentOperations: specifies the number of records that can be simultaneously locked in the update phase. The default value is 32768. 10. By default, MaxNoOfLocalOperations calculates this parameter according to 1.1 * MaxNoOfConcurrentOperations. It is suitable for systems with many concurrent transactions but no extra large transactions. If you need to process extra-large transactions at a certain time and there are many nodes, it is best to specify this parameter to overwrite the default value. 11. The default value of MaxNoOfConcurrentIndexOperations is 8192. It is necessary to increase the value only when a unique hash index is required to perform extremely high parallel operations. If you are sure that the cluster does not require high parallel operations, you can use a smaller value and save memory. 12. The default value of MaxNoOfFiredTriggers is 4000, which is sufficient for most cases. In some cases, if you think that the requirements for parallel operations in the cluster are not high, you can even reduce it. 13. TransactionBufferMemory the memory affected by this parameter is used to track the operations performed when updating the index table and reading the unique index. This memory is used to save key and column information about such operations. You almost do not need to change the default value of this parameter. 14. MaxNoOfConcurrentScans this number is used to control the number of parallel scans that can be executed in the cluster. The default value is 256, and the maximum value is 500. 15. MaxNoOfLocalScans if many scans are not completely parallel, specify the number of local scan records. 16. BatchSizePerLocalScan: this parameter is used to calculate the number of locked records. This type of record is required to process many concurrent scanning operations. The default value is 64, which is closely related to the ScanBatchSize defined in the SQL node. 17. LongMessageBuffer is used to transmit messages between a single node and a node. Although it almost does not need to be changed, it is still configurable. By default, it is set to 1 MB. 18. NoOfFragmentLogFiles: Set the REDO log file size of the node. The default value is 8. 19. MaxNoOfSavedMessages sets the maximum number of Trace files. The default value is 25. 20. MaxNoOfAttributes sets the number of attributes that can be defined in the cluster. The default value is 1000 and the minimum value is 32. 21. MaxNoOfTables sets the maximum number of table objects in the cluster. The default value is 128, the minimum value is 8, and the maximum value is 1600. 22. MaxNoOfOrderedIndexes assigns an object to each ordered index in the cluster to describe the content of the index and its storage fragments. By default, each index defined in this way also defines an ordered index. Each unique index and primary key have an ordered index and a hash index. MaxNoOfOrderedIndexes sets the total number of ordered indexes, which is the total number of ordered indexes that the system can use at any time. The default value of this parameter is 128. The size of each index object in each node is approximately 10 KB. 23. MaxNoOfUniqueHashIndexes assigns a table for each unique index that is not the primary key to map the unique key to the primary key of the index table. By default, an ordered index is also defined for each unique index. To avoid this situation, you must specify the "using hash" option when defining a unique index. The default value is 64. The size of each index in each node is about 15 kb. 24. MaxNoOfTriggers this parameter is used to set the maximum number of triggers in the cluster. 25. LockPagesInMainMemory can lock processes in the memory of Many operating systems to avoid disk exchange. It ensures the real-time features of the cluster. By default, this feature is disabled (value: Y/N or 1/0 ). 26. When a StopOnError error occurs, this parameter specifies whether the NDBD process exits or automatically restarts. This feature is enabled by default (values: Y/N or 1/0 ). 27. Diskless indicates that the cluster is "no disk", which means no checkpoints are set up for the table on the disk and no logs are recorded. By default, this feature is disabled (value: Y/N or 1/0 ). 28. RestartOnErrorInsert can access this feature only when it is compiled as a debug version. This feature is disabled by default. 29. TimeBetweenWatchDogCheck specifies the interval between monitoring thread checks. The default value is 4000 milliseconds. 30. StartPartialTimeout this parameter specifies the time the cluster waits for all storage nodes to appear before the cluster initialization subroutine is called. The default value is 30000 milliseconds (0 indicates infinite timeout ). 31. StartPartitionedTimeout if the cluster is ready for startup but may still be isolated, the cluster will wait until the timeout period ends. The default value is 60000 milliseconds. 32. StartFailureTimeout if the data node does not complete its startup sequence within the time specified by this parameter, the node startup will fail. If this parameter is set to 0, data node timeout is not used. The default value is 60000 milliseconds. 33. HeartbeatIntervalDbDb the interval at which each data node sends a heartbeat signal to the SQL node. The default value is 1500 milliseconds. 34. HeartbeatIntervalDbApi each data node sends a heartbeat signal to each MySQL Server (SQL node) to ensure that they remain in touch. If a MySQL Server fails to send a heartbeat packet in time, it will be declared as "invalid". In this case, all ongoing transactions will end, and release all resources. The SQL node cannot be reconnected until all activities initialized by the previous MySQL instance have ended. The default interval is 1500 milliseconds (1.5 seconds). The interval between a single data node may be different, because each data node monitors the MySQL server connected to it, it is independent of all other data nodes. 35. TimeBetweenLocalCheckpoints this parameter defaults to 20. 36. TimeBetweenGlobalCheckpoints this parameter defines the interval between global checkpoint operations. The default value is 2000 milliseconds. 37. TimeBetweenInactiveTransactionAbortCheck the default value of this parameter is 1000 milliseconds. 38. TransactionInactiveTimeout if the transaction does not execute any query at present, but waits for further user input, this parameter specifies the maximum time that the user can wait before giving up the transaction. The default value is 0. 39. TransactionDeadlockDetectionTimeout this timeout parameter specifies the time for the Transaction Coordinator to wait for another node to execute the query before the transaction is abandoned. 40. NoOfDiskPagesToDiskAfterRestartTUP this parameter specifies the speed at which the local checkpoint operation is executed and can be used with NoOfFragmentLogFiles, DataMemory, and IndexMemory. The default value is 40 (mb data page per second ). 41. NoOfDiskPagesToDiskAfterRestartACC this parameter uses the same unit as NoOfDiskPagesToDiskAfterRestartTUP. The operation method is similar, but the speed of writing index pages from the index memory is limited. The default value of this parameter is 20 index memory pages per second (1, 6 MB per second ). 42. NoOfDiskPagesToDiskDuringRestartTUP this parameter involves pages written from the data memory. The default value is 40 (3.2MB per second ). 43. NoOfDiskPagesToDiskDuringRestartACC the default value of this parameter is 20 (mb per second ). 44. ArbitrationTimeout specifies the time for the data node to wait for the response of the Decision-Making Program to the decision message. The default value is 1000 milliseconds. 45. UndoIndexBuffer specifies the size of the UNDO index buffer. The default value is 2 MB and the minimum value is 1 MB. 46. UndoDataBuffer specifies the size of the UNDO data buffer. The default value is 16 MB, and the minimum value is 1 MB. 47. RedoBuffer specifies the size of the REDO data buffer. The default value is 8 MB and the minimum value is 1 MB. 48. LogLevelStartup Log Level, used for events generated during process startup. The default value is 1. 49. LogLevelShutdown Log Level, used as an event generated when the node properly closes the process component. The default value is 0. 50. LogLevelStatistic Log Level, used to count events, such as the number of reads by the primary key method, number of updates, number of inserts, and information related to buffer usage. The default value is 0. 51. LogLevelCheckpoint Log Level, used for events generated by local and global checkpoint operations. The default value is 0. 52. LogLevelNodeRestart Log Level, used for events generated during node restart. The default value is 0. 53. LogLevelConnection Log Level, used for events generated by connections between cluster nodes. The default value is 0. 54. LogLevelError Log Level, used for events generated by errors and warnings throughout the cluster. Such errors do not cause any node failure, but are worth recording. The default value is 0. 55. LogLevelInfo Log Level, used for events generated for the general status information of the cluster. The default value is 0 56. BackupDataBufferSize, which specifies the size of the Data Backup buffer. The default value is 2 MB. 57. BackupLogBufferSize specifies the size of the log backup buffer. The default value is 2 MB. 58. BackupMemory this parameter is the sum of BackupDataBufferSize and BackupLogBufferSize. The default value is 2 MB + 2 MB = 4 MB. 59. BackupWriteSize this parameter specifies the size of messages written to the disk by backup log buffering and backup data buffering. The default value is 32 KB. 60. FileSystemPath this parameter specifies all files, REDO logs, UNDO logs, and data file directories created for metadata. Note: The directory must exist before the ndbd process starts. 5. Define the MySQL server (SQL) to define the behavior of the MySQL server (SQL node) used to access cluster data. You can use [MYSQLD] to define the behavior of a single SQL node, or use [mysqld default] to define the DEFAULT behavior of Multiple SQL nodes. 1. The unique ID of the node in the NodeId cluster. Value Range: 1 ~ 63. 2. HostName specifies the node host name or IP address. 3. ExecuteOnComputer references one of the computers defined in "[COMPUTER. 4. For normal configuration, ArbitrationRank uses the Management Server as the Decision-Making Program. Set the ArbitrationRank of the Management Server to 1 (default), and set the ArbitrationRank of all SQL nodes to 0. 5. ArbitrationDelay specifies the latency of the Management Server for decision-making requests, in milliseconds. The default value is 0. Normally, you do not need to change it. 6. BatchByteSize for queries that are converted to full table scan or index range scan, obtain records in batches with the appropriate size to achieve the best performance. The proper size can be expressed by the number of records (BatchSize) or the number of bytes (BatchByteSize. The actual batch size is limited by these two parameters. According to the setting method of this parameter, the query performance can be improved by up to 40%. This parameter is in bytes and the default value is 32 KB. 7. BatchSize the parameter is in bytes. The default value is 64 and the maximum value is 992. 8. MaxScanBatchSize specifies the size of each batch of data sent from each data node. The default value is 256KB and the maximum value is 16 MB. Vi. configuration file example

[Ndbd default] # default configuration of data nodes NoOfReplicas = 2 # data has two copies in the Cluster DataMemory = 80 M # the data memory size is 80 MB IndexMemory = 18 M # index memory size is 18 MB [Ndb_mgmd] # management node configuration NodeId = 1 Hostname = 192.168.124.141 # IP address of the Management Node Datadir =/var/lib/mysql-cluster # Save the location of the output file of the Management Node [Ndbd] # data node configuration NodeId = 2 Hostname = 192.168.124.142 # IP address of the Data Node Datadir =/usr/local/mysql/data # specifies the directory for storing trace files, log files, pid files, and error logs. [Ndbd] # data node configuration NodeId = 3 Hostname = 192.168.124.143 # IP address of the Data Node Datadir =/usr/local/mysql/data [Mysqld] # SQL node configuration NodeId = 4 Hostname = 192.168.124.144 # IP address of the SQL Node [Mysqld] # SQL node configuration NodeId = 5 Hostname = 192.168.124.145 # IP address of the SQL Node

MySQL Cluster details: click here
MySQL Cluster: click here

Recommended reading:

MySQL Cluster backup and recovery

MySQL Cluster installation Configuration

Create a Cluster environment using three MySQL clusters. DOC

MySQL Cluster7.2 online addition of data nodes has major drawbacks