MySQL Cluster 7.3.5 集群配置参数优化（优化篇）

按照前面的教程：MySQL Cluster 7.3.5 集群配置实例（入门篇），可快速搭建起基础版的MySQL Cluster集群，但是在生成环境中，还是有很多问题的，即配置参数需要优化下，

当前生产环境硬件配置参考：

Linux下查看操作系统信息、内存情况及cpu信息：cpu个数、核心数、线程数

 

当前生产环境MySQL Cluster配置参考：

# mgm配置(/usr/local/mysql/etc/config.ini）

[ndb_mgmd default]
DataDir = /usr/local/mysql/data

[ndbd default]
NoOfReplicas = 2
DataMemory = 2500M
IndexMemory = 800M
DataDir = /usr/local/mysql/data

StringMemory = 50
MaxNoOfTables = 4096
MaxNoOfOrderedIndexes = 2048
MaxNoOfUniqueHashIndexes = 1024
MaxNoOfAttributes = 24576
MaxNoOfTriggers = 10240

MaxNoOfConcurrentTransactions = 409600
MaxNoOfConcurrentOperations = 3276800
###MaxNoOfLocalOperations = 55000
TimeBetweenGlobalCheckpoints = 1000
TimeBetweenEpochs = 100
TimeBetweenWatchdogCheckInitial = 60000
TransactionBufferMemory = 20M
DiskCheckpointSpeed = 20M
DiskCheckpointSpeedInRestart = 100M
TimeBetweenLocalCheckpoints = 20

SchedulerSpinTimer = 400
SchedulerExecutionTimer = 100
RealTimeScheduler = 1

BackupMaxWriteSize = 2M
BackupDataBufferSize = 32M
BackupLogBufferSize = 8M
BackupMemory = 40M

MaxNoOfExecutionThreads = 4
TransactionDeadLockDetectionTimeOut = 15000
BatchSizePerLocalScan = 512

###Increasing the LongMessageBufferb/c of a bug (20090903)
LongMessageBuffer = 16M

###Heartbeating
HeartbeatIntervalDbDb = 15000
HeartbeatIntervalDbApi = 15000

FragmentLogFileSize = 256M
NoOfFragmentLogFiles = 16

[mysqld default]

[ndb_mgmd]
NodeId = 1
HostName = 172.16.10.160

[ndbd]
NodeId = 11  
HostName = 172.16.10.170

[ndbd]
NodeId = 12
HostName = 172.16.10.171

[ndbd]
NodeId = 13
HostName = 172.16.10.172

[ndbd]
NodeId = 14
HostName = 172.16.10.173

[mysqld]
NodeId = 81
HostName = 172.16.10.150

[mysqld] 
NodeId = 82
HostName = 172.16.10.151

 

# ndb配置(/etc/my.cnf）

[mysqld]
ndbcluster
ndb-connectstring = 172.16.10.160

[mysql_cluster]
ndb-connectstring = 172.16.10.160

 

# sql配置(/etc/my.cnf）

[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
default-storage-engine = ndbcluster

slow-query-log = on
slow_query_log_file = /usr/local/mysql/data/slow-query.log
long_query_time = 5

skip-external-locking
key_buffer_size = 600M
max_allowed_packet = 100M
table_open_cache = 2048
sort_buffer_size = 1024M
net_buffer_length = 8K
read_buffer_size = 400M
read_rnd_buffer_size = 200M

lower_case_table_names =1 
back_log = 384
thread_stack = 256K
join_buffer_size = 500M
thread_cache_size = 200
query_cache_size = 640M
tmp_table_size = 256M
max_connections = 5000
max_connect_errors = 10000000 
wait_timeout = 2880000
interactive_timeout = 2880000
thread_concurrency = 8

ndbcluster
ndb-connectstring = 172.16.10.160

[mysql_cluster]
ndb-connectstring = 172.16.10.160

 

 

 

参考：

MySQL Cluster 配置文件(config.ini)详解

MySQL Cluster7.2.4初步安装部署新特性性能测试等

【推荐】MySQL Cluster报错解决方法（不断更新中）

淘宝商品库MySQL优化实践

【转】8G内存下MySQL的优化详细方案

【转】MySQL修改配置文件以优化效率（来自discuz）