Lock wait timeout during import of "large" DB in NDBCLUSTER

[Jérémie Soria]

Hello,

I built a MySQL Cluster 7.3.3 setup with:
2 datanodes
2 management nodes
2 API nodes

I am trying to migrate some db from InnoDB to NDB so I use the InnoDB dump from mysqldump, I change the engine using sed and I try to import the databases.

For small enough tables/db, it works like a charm, however for a db containing larger tables (not HUGE, around 500K rows is enough), I'm having a "General error: 1205 Lock wait timeout exceeded; try restarting transaction".

After snooping around in the forums I tried to increase TransactionDeadlockDetectionTimeout to 100000. With this I was able to go further but it ended up timing out again. This parameter just treats a symptom not the cause but I really can't figure out the cause. It happens generally during simple INSERT statements.

I even tried to shutdown the second datanode to avoid replication but it also failed. Before trying the TransactionDeadlockDetectionTimeout, I also dumped the problematic db with extended-insert=false with no effect.

I really hope you can help me, I am really stuck on this issue.

Here are my config files:

------ my.cnf ------

[mysqld]
ndbcluster # run NDB storage engine
port=3307 #non-standard port will be change to 3306 after migration is complete
socket=/var/lib/mysql/ndbcluster/mysql-cluster.sock #non-standard to avoid conflict with legacy mysql server

[mysql_cluster]
ndb-connectstring=10.0.0.9,10.0.0.10 #Location of management servers, order imports



------ config.ini ------

[tcp default]
SendBufferMemory=2M
ReceiveBufferMemory=2M

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Number of replicas
DataMemory=3000M # How much memory to allocate for data storage
IndexMemory=512M # How much memory to allocate for index storage
datadir=/var/lib/mysql/ndbcluster/data-node # Directory for this data node's data files

#OS-level options for scheduling and disk IO
ODirect=1
CompressedLCP=1
CompressedBackup=1
SchedulerSpinTimer=400
SchedulerExecutionTimer=100
RealTimeScheduler=1

#Options for sql databases
MaxNoOfConcurrentOperations=100000
MaxNoOfTables=512
MaxNoOfOrderedIndexes=512
MaxNoOfUniqueHashIndexes=256
MaxNoOfAttributes=1200
MaxNoOfTriggers=2000
MaxBufferedEpochs=10000
TimeBetweenEpochs=200
TransactionDeadlockDetectionTimeout=100000
HeartbeatIntervalDbDb=3000
HeartbeatIntervalDbApi=3000

[ndb_mgmd default]
datadir=/var/lib/mysql-cluster/logs #Directory for MGM node log files

[ndb_mgmd]
hostname=10.0.0.9
NodeId=41

[ndb_mgmd]
hostname=10.0.0.10
NodeId=42

[ndbd]
hostname=10.0.0.5
NodeId=31
ServerPort=50501

[ndbd]
hostname=10.0.0.11
NodeId=32
ServerPort=50502

[mysqld]
hostname=10.0.0.5
NodeId=51

[mysqld]
hostname=10.0.0.11
NodeId=52



Edited 1 time(s). Last edit at 01/23/2014 09:36AM by Jérémie Soria.