Description:
We have 2 datanodes 2 api nodes and 1 mgmd node
when we try to insert values contineously through SQL procedures into a table that is in ndbd cluster then ,it abort insertion in between showing error
Error:1205 Lock wait timeout exceeded try restarting transaction.
The configuration files are
" my.cnf " in data nodes
=========================
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
back-log=75
skip-innodb
max_connections=500
skip-locking
key_buffer = 384M
myisam_sort_buffer_size=64m
join_buffer_size=1M
max_allowed_packet = 64M
table_cache = 1800
thread_cache_size=384
wait_timeout=7200
connect_timeout=10
tmp_table_size=64M
max_heap_table_size=64M
max_connect_errors=1000
read_rnd_buffer_size=524288
bulk_insert_buffer_size=8M
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 1M
log-bin=mysql-bin
query_cache_limit=4M
query_cache_size=128M
query_cache_type=1
query_prealloc_size=65536
querry_alloc_block-size=131072
[mysqld_safe]
nice=-5
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer = 64M
sort_buffer_size = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
[mysqld]
ndbcluster
ndb-connectstring=192.168.3.97
[mysql_cluster]
ndb-connectstring=192.168.3.97
and "config.ini" in mgmd node is
================================
[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=600M
IndexMemory=200M
RedoBuffer=300M
# TCP/IP options:
[TCP DEFAULT]
portnumber=2202
# Management process options:
[NDB_MGMD]
hostname=192.168.3.97 # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster # Directory for MGM node logfiles
# Options for data node 1 :
[NDBD]
# (one [NDBD] section per data node)
RedoBuffer=500M
DataMemory=256M
IndexMemory=256M
hostname=192.168.3.99 # Hostname or IPs
datadir=/var/lib/mysql # path for data node's datafiles
MaxNoOfAttributes=15000
TimeBetweenLocalCheckPoints=6
TimeBetweenGlobalCheckPoints=10
MaxNoOfTables=1000
MaxNoOfOrderedIndexes=10000
MaxNoOfUniqueHashIndexes=10000
MaxNoOfConcurrentTransactions=5000
MaxNoOfConcurrentOperations=250000
TransactionBufferMemory=10M
NoOfFragmentLogFiles=300
TimeBetweenWatchDogCheck=600000
MaxNoOfLocalOperations=275000
# Options for data node 2 :
[NDBD]
# (one [NDBD] section per data node)
RedoBuffer=500M
DataMemory=256M
IndexMemory=256M
hostname=192.168.3.100 # Hostname or IPs
datadir=/var/lib/mysql # path for data node's datafiles
MaxNoOfAttributes=15000
TimeBetweenLocalCheckPoints=6
TimeBetweenGlobalCheckPoints=10
MaxNoOfTables=1000
MaxNoOfOrderedIndexes=10000
MaxNoOfUniqueHashIndexes=10000
MaxNoOfConcurrentTransactions=5000
MaxNoOfConcurrentOperations=250000
TransactionBufferMemory=10M
NoOfFragmentLogFiles=300
TimeBetweenWatchDogCheck=600000
MaxNoOfLocalOperations=275000
# SQL node options:
[mysqld]
hostname=192.168.3.96 # SQL Node IP
[mysqld]
hostname=192.168.3.95 # SQL/Offline IP
How to repeat:
set cluster with atleast two data nodes
and try to insert contineously into ndbd tables
observe the result after some time