17.3.2.2. Рекомендуемая Начальная Конфигурация для MySQL Cluster

Достижение лучшей производительности от MySQL Cluster зависит в ряде факторов включая следующее:

Поэтому, получение оптимальной конфигурации, вероятно, будет итеративным процессом, результат которого может значительно различаться в зависимости от специфических особенностей каждого развертывания MySQL Cluster. Изменения в конфигурации, вероятно, будут, также обозначены, когда изменения будут произведены в платформе, на которой кластер выполняется, или в приложениях, которые используют данные Кластера MySQL. По этим причинам не возможно предложить единственную конфигурацию, которая идеальна для всех сценариев использования. Однако, в этом разделе, мы обеспечиваем рекомендуемую основную конфигурацию.

Запуск config.ini файл. Следующий config.ini файл является рекомендуемой начальной точкой для того, чтобы сконфигурировать кластер рабочий MySQL Cluster NDB 7.3:

# TCP PARAMETERS[tcp default]SendBufferMemory=2MReceiveBufferMemory=2M# Increasing the sizes of these 2 buffers beyond the default values# helps prevent bottlenecks due to slow disk I/O.# MANAGEMENT NODE PARAMETERS[ndb_mgmd default]DataDir=path/to/management/server/data/directory# It is possible to use a different data directory for each management# server, but for ease of administration it is preferable to be# consistent.[ndb_mgmd]HostName=management-server-A-hostname# NodeId=management-server-A-nodeid[ndb_mgmd]HostName=management-server-B-hostname# NodeId=management-server-B-nodeid# 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 NodeId 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.# DATA NODE PARAMETERS[ndbd default]NoOfReplicas=2# 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.LockPagesInMainMemory=1# 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.DataMemory=3072MIndexMemory=384M# The values provided for DataMemory and IndexMemory assume 4 GB RAM# per data node. However, for best results, you should first calculate# the memory that would be used based on the data you actually plan to# store (you may find the ndb_size.pl utility helpful in estimating# this), then allow an extra 20% over the calculated values. Naturally,# you should ensure that each data node host has at least as much# physical memory as the sum of these two values.# ODirect=1# 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 on systems running # Linux kernel 2.6 or later.NoOfFragmentLogFiles=300DataDir=path/to/data/node/data/directoryMaxNoOfConcurrentOperations=100000SchedulerSpinTimer=400SchedulerExecutionTimer=100RealTimeScheduler=1# Setting these parameters allows you to take advantage of real-time scheduling# of NDB threads to achieve increased throughput when using ndbd. They # are not needed when using ndbmtd; in particular, you should not set # RealTimeScheduler for ndbmtd data nodes.TimeBetweenGlobalCheckpoints=1000TimeBetweenEpochs=200DiskCheckpointSpeed=10MDiskCheckpointSpeedInRestart=100MRedoBuffer=32M# CompressedLCP=1# CompressedBackup=1# Enabling CompressedLCP and CompressedBackup causes, respectively, localcheckpoint files and backup files to be compressed, which can result in a spacesavings of up to 50% over noncompressed LCPs and backups.# MaxNoOfLocalScans=64MaxNoOfTables=1024MaxNoOfOrderedIndexes=256[ndbd]HostName=data-node-A-hostname# NodeId=data-node-A-nodeidLockExecuteThreadToCPU=1LockMaintThreadsToCPU=0# On systems with multiple CPUs, these parameters can be used to lock NDBCLUSTER# threads to specific CPUs[ndbd]HostName=data-node-B-hostname# NodeId=data-node-B-nodeidLockExecuteThreadToCPU=1LockMaintThreadsToCPU=0# You must have an [ndbd] section for every data node in the cluster;# each of these sections must include a HostName. Each section may# optionally include a NodeId for convenience, but in most cases, it is# sufficient to allow the cluster to allocate node IDs dynamically. If# you do specify the node ID for a data node, it must be in the range 1# to 48 inclusive and must be unique among all IDs specified for# cluster nodes.# SQL NODE / API NODE PARAMETERS[mysqld]# HostName=sql-node-A-hostname# NodeId=sql-node-A-nodeid[mysqld][mysqld]# Each API or SQL node that connects to the cluster requires a [mysqld]# or [api] section of its own. Each such section defines a connection# "slot"; you should have at least as many of these sections in the# config.ini file as the total number of API nodes and SQL nodes that# you wish to have connected to the cluster at any given time. There is# no performance or other penalty for having extra slots available in# case you find later that you want or need more API or SQL nodes to# connect to the cluster at the same time.# If no HostName is specified for a given [mysqld] or [api] section,# then any API or SQL node may use that slot to connect to the# cluster. You may wish to use an explicit HostName for one connection slot# to guarantee that an API or SQL node from that host can always# connect to the cluster. If you wish to prevent API or SQL nodes from# connecting from other than a desired host or hosts, then use a# HostName for every [mysqld] or [api] section in the config.ini file.# You can if you wish define a node ID (NodeId parameter) for any API or# SQL node, but this is not necessary; if you do so, it must be in the# range 1 to 255 inclusive and must be unique among all IDs specified# for cluster nodes.

Рекомендуемый my.cnf опции для узлов SQL. MySQL Servers, действующий как узлы SQL MySQL Cluster, должен всегда запускаться с --ndbcluster и --ndb-connectstring опции, или на командной строке или в my.cnf. Кроме того, установите следующие опции для всех процессов mysqld в кластере, если Ваша установка не требует иначе: