Detailed description of the config. ini configuration file of the MySQLCluster management Node 1. Define the TCPIP connection of MySQLCluster. TCPIP is the default transmission protocol used by the MySQL cluster to establish a connection. Normally, no connection needs to be defined. You can use [TCPDEFAULT] or [TCP] for definition. 1. SendBufferMemoryTCP transmission cache. The default value is 256 kb.
Details about the config of the MySQL Cluster management node. ini configuration file 1. Define the TCP/IP connection of the MySQL Cluster as the default transmission protocol used by the MySQL Cluster to establish connections. Normally, no connection needs to be defined. You can use [tcp default] or [TCP] for definition. 1. SendBufferMemory TCP transmission cache. The default value is 256 kb.
Detailed description of the config. ini configuration file of the MySQL Cluster management Node
1. Define the TCP/IP connection of the MySQL ClusterTCP/IP is the default transmission protocol used by the MySQL cluster to establish connections. Normally, no connection needs to be defined. You can use [tcp default] or [TCP] for definition.
1. SendBufferMemoryTCP transmission cache. The default value is 256KB.
2. SendSignalIdMessage ID transmitted over the network. This feature is disabled by default (value: Y/N or 1/0 ).
3. ChecksumEnabling this parameter 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. ReceiveBufferMemorySpecifies 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.
Ii. Define default behavior of data nodesNoOfReplicas is a required parameter and is defined using [ndbd default.
1. NoOfReplicasDefines the number of copies saved for each table in the cluster, and also specifies the size of the node group. 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.
Iii. Define 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. NodeIdThe unique ID of the node in the cluster. Valid value: 1 ~ 63.
2. HostNameSpecify the node host name or IP address.
3. ExecuteOnComputerReference one of the computers defined in the [COMPUTER] section.
4. PortNumberManage the server listening port (default value: 2202 ).
5. LogDestinationThe following values can be used for handling node logs:
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. ArbitrationRankSpecify 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. ArbitrationDelaySpecifies the latency of the Management Server for decision-making requests, in milliseconds. The default value is 0. Generally, you do not need to change it.
8. DataDirStores the location of the output file of the management server, including logs, process output files, and pid files of the program. You can set the FILE parameter of LogDestination to overwrite a log FILE.
Iv. Define data
Node
(NDBD)Used to configure the behavior of data nodes. 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. NodeIdWhen you start a node, you can assign an ID (that is, a data node ID) in the command line, or in the configuration file.
2. HostNameSpecify the node host name or IP address.
3. ExecuteOnComputerReference one of the computers defined in the [COMPUTER] section.
4. DataDirSpecifies the directory for storing trace files, log files, pid files, and error logs.
5. BackupDataDirSpecifies the directory for storing backups. The default value is {FileSystemPath}/BACKUP.
6. DataMemorySpecify the data memory. The default value is 80 MB and the minimum value is 1 MB.
7. IndexMemorySpecify the index memory. The default value is 18 MB and the minimum value is 1 MB.
8. MaxNoOfConcurrentTransactionsThis parameter is used to set the number of possible concurrent transactions in a node. The default value is 4096. For all nodes, the parameter must be set to the same value.
9. MaxNoOfConcurrentOperationsSet the number of records that can appear in the update phase or be locked at the same time. The default value is 32768.
10. MaxNoOfLocalOperationsBy default, this parameter is calculated according to 1.1 * MaxNoOfConcurrentOperations. It is suitable for systems with many concurrent transactions but no major transaction. 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. MaxNoOfConcurrentIndexOperationsThe default value of this parameter 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. MaxNoOfFiredTriggersThe default value 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. TransactionBufferMemoryThe 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. MaxNoOfConcurrentScansThis parameter is used to control the number of parallel scans that can be performed in the cluster. The default value is 256, and the maximum value is 500.
15. MaxNoOfLocalScansIf many scans are not completely parallel, specify the number of local scan records.
16. BatchSizePerLocalScanThis 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. LongMessageBufferThe internal buffer 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. NoOfFragmentLogFilesSet the REDO log file size of the node. The default value is 8.
19. MaxNoOfSavedMessagesSet the maximum number of trace files. The default value is 25.
20. MaxNoOfAttributesSet the number of attributes that can be defined in the cluster. The default value is 1000 and the minimum value is 32.
21. MaxNoOfTablesSet 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. MaxNoOfOrderedIndexesFor each ordered index in the cluster, an object is assigned to describe the index content 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. MaxNoOfUniqueHashIndexesFor each unique index that is not a primary key, a table is allocated 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. MaxNoOfTriggersThis parameter is used to set the maximum number of triggers in the cluster.
25. LockPagesInMainMemoryFor many operating systems, the process can be locked in the memory to avoid switching with the disk. It ensures the real-time features of the cluster. By default, this feature is disabled (value: Y/N or 1/0 ).
26. StopOnErrorWhen an 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. DisklessIf the cluster is specified as "no disk", it means that 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. RestartOnErrorInsertThis feature can be accessed only when it is compiled as a debug version. This feature is disabled by default.
29. TimeBetweenWatchDogCheckSpecifies the interval between monitoring thread checks. The default value is 4000 milliseconds.
30. StartPartialTimeoutThis parameter specifies the time when 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. StartPartitionedTimeoutIf the cluster is ready to start but may still be isolated, the cluster will wait until the timeout period ends. The default value is 60000 milliseconds.
32. StartFailureTimeoutIf 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. HeartbeatIntervalDbDbThe interval at which each data node sends a heartbeat signal to the SQL node. The default value is 1500 milliseconds.
34. HeartbeatIntervalDbApiEach 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. TimeBetweenLocalCheckpointsThe default value is 20.
36. TimeBetweenGlobalCheckpointsThis parameter defines the interval between global checkpoint operations. The default value is 2000 milliseconds.
37. TimeBetweenInactiveTransactionAbortCheckThe default value is 1000 ms.
38. TransactionInactiveTimeoutIf the transaction does not execute any query, 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. TransactionDeadlockDetectionTimeoutThe timeout parameter specifies the time for the Transaction Coordinator to wait for another node to execute the query before the transaction is abandoned.
40. NoOfDiskPagesToDiskAfterRestartTUPThis 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. NoOfDiskPagesToDiskAfterRestartACCThe unit used by this parameter is the same as that of 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. NoOfDiskPagesToDiskDuringRestartTUPThis parameter involves pages written from the data memory. The default value is 40 (3.2MB per second ).
43. NoOfDiskPagesToDiskDuringRestartACCThe default value of this parameter is 20 (1.6MB per second ).
44. ArbitrationTimeoutSpecifies the time when the data node waits for the decision-making program to respond to the decision message. The default value is 1000 milliseconds.
45. UndoIndexBufferSpecify the size of the UNDO index buffer. The default value is 2 MB and the minimum value is 1 MB.
46. UndoDataBufferSpecify the size of the UNDO data buffer. The default value is 16 MB, and the minimum value is 1 MB.
47. RedoBufferSpecify the size of the REDO data buffer. The default value is 8 MB and the minimum value is 1 MB.
48. LogLevelStartupLog Level, used for events generated during process startup. The default value is 1.
49. LogLevelShutdownLog Level, used as an event generated when the node properly closes a process component. The default value is 0.
50. LogLevelStatisticLog 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 the buffer usage. The default value is 0.
51. LogLevelCheckpointLog Level, used for events generated by local and global checkpoint operations. The default value is 0.
52. LogLevelNodeRestartLog Level, used for events generated during node restart. The default value is 0.
53. LogLevelConnectionLog Level, used for events generated by connections between cluster nodes. The default value is 0.
54. LogLevelErrorLog 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. LogLevelInfoLog Level, used to generate events for general cluster status information. The default value is 0.
56. BackupDataBufferSizeSpecifies the size of the Data Backup buffer. The default value is 2 MB.
57. BackupLogBufferSizeSpecify the size of the log backup buffer. The default value is 2 MB.
58. BackupMemoryThis parameter is the sum of BackupDataBufferSize and BackupLogBufferSize. The default value is 2 MB + 2 MB = 4 MB.
59. BackupWriteSizeThis 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. FileSystemPathThis 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.
V. Define MySQL Server (SQL)Defines the behavior of a 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. NodeIdThe unique identifier of the node in the cluster. Value Range: 1 ~ 63.
2. HostNameSpecify the node host name or IP address.
3. ExecuteOnComputerReference one of the computers defined in the [COMPUTER] section.
4. ArbitrationRankFor normal configuration, use 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. ArbitrationDelaySpecifies 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. BatchByteSizeFor queries that are converted to full table scans or index range scans, you can obtain records in batches at an appropriate size to achieve optimal 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. BatchSizeThis parameter is in bytes. The default value is 64 and the maximum value is 992.
8. MaxScanBatchSizeSpecify 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 |
