MySQL Forums
Forum List  »  NDB clusters

SQL nodes fail to connect Management Nodes
Posted by: urgen sherpa
Date: August 14, 2016 04:48AM

mysqlcluster v 7.4.11
Total memory available = 95G on two physical servers with 8core processors on both.

Scenario
PhysicalServer1 : 1Management, 1 SQL and 1Datanode
PhysicalServer2: 1SQL and 1Datanode

As my clusters IndexMemory was growing I needed to make room for index memory by adjusting both IndexMemory and DataMemory below is what i did

1) stop both sql1 and sql2 servers- service mysql stop
2) shutdown cluster using management node - ndb_mgm -e "shutdown"
3) perform rolling restart with - ndb_mgmd --config-file /var/config.ini --reload
4) connect data nodes with -run 'ndbd' on both datanodes
5) run mysql server on both SQL nodes - "service mysql start"
but this step didnt connect both sql node to management node, although the service is started, the management node only sees(using: ndb_mgm -e "show") the 2 datanodes that are connected and itself.


#Old variable in config.ini
#Datamemory = 87G where 33.9G is used
#IndexMemory = 3G where 1.92 G used

#New value
DataMemory = 85899345920 #(80GB)
IndexMemory = 8589934592 #(8GB)

I also increased the maxconcurrenttransaction and maxconcurrentoperations and added new parameter MaxNoOfLocalOperations , in config.ini
#old
#MaxNoOfConcurrentOperations=100000
#MaxNoOfConcurrentTransactions=16384

#new values changed/added
MaxNoOfConcurrentOperations=150000
MaxNoOfConcurrentTransactions=150000
MaxNoOfLocalOperations=150000


Error seen in management .../mysqlcluster/ndb_1_cluster.log :-

2016-08-13 09:45:22 [MgmtSrvr] WARNING -- Failed to allocate nodeid for API at 202.94.66.45. Returned error: 'No free node id found for mysqld(API).'
2016-08-13 09:45:31 [MgmtSrvr] INFO -- Node 3: Data usage is 43%(1129522 32K pages of total 2621440)
2016-08-13 09:45:31 [MgmtSrvr] INFO -- Node 3: Index usage is 24%(251798 8K pages of total 1048608)
2016-08-13 09:45:50 [MgmtSrvr] WARNING -- Failed to allocate nodeid for API at 202.94.66.34. Returned error: 'No free node id found for mysqld(API).'
2016-08-13 09:45:52 [MgmtSrvr] WARNING -- Failed to allocate nodeid for API at 202.94.66.45. Returned error: 'No free node id found for mysqld(API).'

Error in sql1 node
2016-08-13 06:55:58 14407 [Note] Shutting down plugin 'mysql_native_password'
2016-08-13 06:55:58 14407 [Note] Shutting down plugin 'binlog'
2016-08-13 06:55:58 14407 [Note] /usr/sbin/mysqld: Shutdown complete

160813 06:55:58 mysqld_safe mysqld from pid file /var/lib/mysql/sqlnode2.classic.com.np.pid ended
160813 07:02:44 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2016-08-13 07:02:48 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_times
tamp server option (see documentation for more details).
2016-08-13 07:02:48 0 [Note] /usr/sbin/mysqld (mysqld 5.6.29-ndb-7.4.11-cluster-gpl-log) starting as process 14884 ...
2016-08-13 07:02:48 14884 [Note] Plugin 'FEDERATED' is disabled.
2016-08-13 07:02:48 14884 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-08-13 07:02:48 14884 [Note] InnoDB: The InnoDB memory heap is disabled
2016-08-13 07:02:48 14884 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-08-13 07:02:48 14884 [Note] InnoDB: Memory barrier is not used
2016-08-13 07:02:48 14884 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-08-13 07:02:48 14884 [Note] InnoDB: Using Linux native AIO
2016-08-13 07:02:48 14884 [Note] InnoDB: Using CPU crc32 instructions
2016-08-13 07:02:48 14884 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-08-13 07:02:48 14884 [Note] InnoDB: Completed initialization of buffer pool
2016-08-13 07:02:48 14884 [Note] InnoDB: Highest supported file format is Barracuda.
2016-08-13 07:02:48 14884 [Note] InnoDB: 128 rollback segment(s) are active.
2016-08-13 07:02:48 14884 [Note] InnoDB: Waiting for purge to start
2016-08-13 07:02:48 14884 [Note] InnoDB: 5.6.29 started; log sequence number 3271214
2016-08-13 07:02:48 14884 [Note] NDB: Changed global value of binlog_format from STATEMENT to MIXED
2016-08-13 07:03:19 14884 [Warning] NDB: server id set to zero - changes logged to bin log with server id zero will be logged with another server id by slave mysqlds
..
..
2016-08-13 07:02:48 14884 [Note] Plugin 'FEDERATED' is disabled.
2016-08-13 07:02:48 14884 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-08-13 07:02:48 14884 [Note] InnoDB: The InnoDB memory heap is disabled
2016-08-13 07:02:48 14884 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-08-13 07:02:48 14884 [Note] InnoDB: Memory barrier is not used
2016-08-13 07:02:48 14884 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-08-13 07:02:48 14884 [Note] InnoDB: Using Linux native AIO
2016-08-13 07:02:48 14884 [Note] InnoDB: Using CPU crc32 instructions
2016-08-13 07:02:48 14884 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-08-13 07:02:48 14884 [Note] InnoDB: Completed initialization of buffer pool
2016-08-13 07:02:48 14884 [Note] InnoDB: Highest supported file format is Barracuda.
2016-08-13 07:02:48 14884 [Note] InnoDB: 128 rollback segment(s) are active.
2016-08-13 07:02:48 14884 [Note] InnoDB: Waiting for purge to start
2016-08-13 07:02:48 14884 [Note] InnoDB: 5.6.29 started; log sequence number 3271214
2016-08-13 07:02:48 14884 [Note] NDB: Changed global value of binlog_format from STATEMENT to MIXED
2016-08-13 07:03:19 14884 [Warning] NDB: server id set to zero - changes logged to bin log with server id zero will be logged with another server id by slave mysqlds
2016-08-13 07:03:19 14884 [Note] NDB Binlog: Starting...
2016-08-13 07:03:19 14884 [Note] NDB Util: Starting...
2016-08-13 07:03:19 14884 [Note] NDB Binlog: Started
2016-08-13 07:03:19 14884 [Note] NDB Binlog: Setting up
2016-08-13 07:03:19 14884 [Note] NDB Util: Wait for server start completed
2016-08-13 07:03:19 14884 [Note] NDB Binlog: Created schema Ndb object, reference: 0x0, name: 'Ndb Binlog schema change monitoring'
2016-08-13 07:03:19 14884 [Note] NDB Index Stat: Starting...
2016-08-13 07:03:19 14884 [Note] NDB Index Stat: Wait for server start completed
2016-08-13 07:03:19 14884 [Note] NDB Binlog: Created injector Ndb object, reference: 0x0, name: 'Ndb Binlog data change monitoring'
2016-08-13 07:03:19 14884 [Note] NDB Binlog: Setup completed
2016-08-13 07:03:19 14884 [Note] NDB Binlog: Wait for server start completed
2016-08-13 07:03:19 14884 [Note] Server hostname (bind-address): '*'; port: 3306
2016-08-13 07:03:19 14884 [Note] IPv6 is available.
2016-08-13 07:03:19 14884 [Note] - '::' resolves to '::';
2016-08-13 07:03:19 14884 [Note] Server socket created on IP: '::'.
2016-08-13 07:03:19 14884 [Note] Event Scheduler: Loaded 0 events
2016-08-13 07:03:19 14884 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.29-ndb-7.4.11-cluster-gpl-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Cluster Community Server (GPL)
2016-08-13 07:03:19 14884 [Note] NDB Util: Wait for cluster to start
2016-08-13 07:03:19 14884 [Note] NDB Index Stat: Wait for cluster to start
2016-08-13 07:03:19 14884 [Note] NDB Binlog: Check for incidents
2016-08-13 07:03:19 14884 [Note] NDB Binlog: Wait for cluster to start
2016-08-13 07:03:49 14884 [Warning] NDB : Tables not available after 30 seconds. Consider increasing --ndb-wait-setup value
2016-08-13 07:04:50 14884 [Note] /usr/sbin/mysqld: Normal shutdown

2016-08-13 07:04:50 14884 [Note] Giving 1 client threads a chance to die gracefully
2016-08-13 07:04:50 14884 [Note] Event Scheduler: Purging the queue. 0 events
2016-08-13 07:04:50 14884 [Note] Shutting down slave threads
2016-08-13 07:04:50 14884 [Note] NDB Binlog: Server shutdown detected while waiting for ndbcluster to start...
2016-08-13 07:04:50 14884 [Note] NDB Binlog: Shutting down
2016-08-13 07:04:50 14884 [Note] NDB Binlog: Stopping...
2016-08-13 07:04:50 14884 [Note] NDB Binlog: Stopped
2016-08-13 07:04:52 14884 [Note] Forcefully disconnecting 0 remaining clients

Config.ini file:-
config.ini
===========
[TCP DEFAULT]
SendBufferMemory=8M
ReceiveBufferMemory=8M

[NDB_MGMD DEFAULT]
PortNumber=1186
Datadir=/var/lib/mysqlcluster/

[NDB_MGMD]
NodeId=1

Hostname=100.100.100.10
LogDestination=FILE:filename=ndb_1_cluster.log,maxsize=10000000,maxfiles=6
ArbitrationRank=1

[NDBD DEFAULT]
NoOfReplicas=2
ServerPort=2200
Datadir=/var/lib/mysqlcluster/
FileSystemPathDD=/var/lib/mysqlcluster/backup
BackupDataDir=/var/lib/mysqlcluster/backup/
#FileSystemPathUndoFiles=/data/mysqlcluster/
#FileSystemPathDataFiles=/data/mysqlcluster/
DataMemory=85899345920
IndexMemory=8589934592
LockPagesInMainMemory=1

MaxNoOfConcurrentOperations=150000
MaxNoOfConcurrentTransactions=150000
MaxNoOfLocalOperations=150000
StringMemory=25
MaxNoOfTables=4096
MaxNoOfOrderedIndexes=2048
MaxNoOfUniqueHashIndexes=512
MaxNoOfAttributes=24576
MaxNoOfTriggers=14336

### Params for REDO LOG
FragmentLogFileSize=256M
InitFragmentLogFiles=SPARSE
NoOfFragmentLogFiles=40
RedoBuffer=64M

TransactionBufferMemory=8M

TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=100

TimeBetweenEpochsTimeout=0

### Params for LCP
MinDiskWriteSpeed=10M
MaxDiskWriteSpeed=20M
MaxDiskWriteSpeedOtherNodeRestart=50M
MaxDiskWriteSpeedOwnRestart=200M
TimeBetweenLocalCheckpoints=20

### Heartbeating
HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000

### Params for setting logging
MemReportFrequency=30
BackupReportFrequency=10
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15

### Params for BACKUP
BackupMaxWriteSize=1M
BackupDataBufferSize=40M
BackupLogBufferSize=20M

#BackupMemory=40M

### Params for ODIRECT
#Reports indicates that odirect=1 can cause io errors (os err code 5) on some systems. You must test.
#ODirect=1

### Watchdog
TimeBetweenWatchdogCheckInitial=60000

### TransactionInactiveTimeout - should be enabled in Production
TransactionInactiveTimeout=60000
### New 7.1.10 redo logging parameters
RedoOverCommitCounter=3
RedoOverCommitLimit=20
### REALTIME EXTENSIONS
#RealTimeScheduler=1
## REALTIME EXTENSIONS FOR 6.3 ONLY
#SchedulerExecutionTimer=80
#SchedulerSpinTimer=40

### DISK DATA
SharedGlobalMemory=20M
DiskPageBufferMemory=64M
BatchSizePerLocalScan=512
[NDBD]
#NodeId=2
Hostname=100.100.100.20
datadir=/var/lib/mysqlcluster
### REALTIME ASPECTS - THREAD BINDING
### READ http://johanandersson.blogspot.com/2008/02/mysql-cluster-features-what-they-are.html#realtime
#LockExecuteThreadToCPU=X
#LockMaintThreadsToCPU=Y

[NDBD]
#NodeId=3
Hostname=100.100.100.30
datadir=/var/lib/mysqlcluster


### REALTIME ASPECTS - THREAD BINDING
### READ http://johanandersson.blogspot.com/2008/02/mysql-cluster-features-what-they-are.html#realtime
#LockExecuteThreadToCPU=X
#LockMaintThreadsToCPU=Y

[MYSQLD DEFAULT]
DefaultOperationRedoProblemAction=QUEUE
BatchSize=512
#BatchByteSize=2048K
#MaxScanBatchSize=2048K

### SLOTS (one for each ndb_mgmd) FOR HELPER APPLICATIONS SUCH AS ndb_show_tables etc
[MYSQLD]
Hostname=100.100.100.40
[MYSQLD]
Hostname=100.100.100.50

[MYSQLD]
[MYSQLD]
[MYSQLD]
[MYSQLD]
[api]
[api]
[api]
[api]
[api]
[api]
[api]
[api]


My.cnf in SQL Nodes
## my.cnf on SQL nodes ##
[mysqld]
ndbcluster
default-storage-engine=ndbcluster
net_read_timeout=60000
connect_timeout=60000
max_allowed_packet=32M
max_connections=1000
query_cache_type=0
query_cache_size=37748736
query_cache_limit=2097152
ndb-cluster-connection-pool=4
slow-query-log=1
slow-query-log-file=/var/log/mysql/slowquery.log
long-query_time=2
log-queries-not-using-indexes
ndb-connectstring=100.100.100.10
[mysql_cluster]
ndb-connectstring=100.100.100.10

#my.cnf in DATA nodes
[mysqld]
ndbcluster
default-storage-engine=NDBCLUSTER
net_read_timeout=60000
connect_timeout=60000
max_allowed_packet=32M
[mysql_cluster]
ndb-connectstring=100.100.100.10


Since i had taken backups . I shutdown the cluster and started again with
managemnt# ndb_mgmd --config-file /var/config.ini --initial
ndb1# ndbd --initial
output: Found timedout nodeid reservation ..
ndb2# ndbd --initlal [ok]
sql1#service mysql start
sql2#service mysql start

this time only ndb2 was connected to management node and ndb1 failed to connect to management. SQL1 and 2 both had previous issues

NDB error.log for node which couldnt connect:-


Time: Saturday 13 August 2016 - 08:52:02
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: 6239) 0x00000002
Program: ndbd
Pid: 24678
Version: mysql-5.6.29 ndb-7.4.11
Trace: /var/lib/mysqlcluster/ndb_2_trace.log.11 [t1..t1]
***EOM***

On management node:-
[MgmtSrvr] WARNING -- Found timedout nodeid reservation for 2, releasing it
[MgmtSrvr] INFO -- Nodeid 2 allocated for NDB at

2016-08-13 13:44:55 [MgmtSrvr] INFO -- Nodeid 2 allocated for NDB at 100.100.100.20
2016-08-13 13:45:28 [MgmtSrvr] WARNING -- Found timedout nodeid reservation for 2, releasing it

Any suggestions and workarounds ?
Thank you
Urgen S.

Options: ReplyQuote


Subject
Views
Written By
Posted
SQL nodes fail to connect Management Nodes
3236
August 14, 2016 04:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.