MySQL Forums
Forum List  »  NDB clusters

Lock wait timeout and other errors
Posted by: Odis Yuck
Date: February 13, 2015 05:57PM

Hello. I'm trying to use MySQL Cluster, but sometimes queries take long time and i get error "lock wait timeout exceeded".

There are following strings in logs:

2015-02-13 23:41:40 27483 [Note] NDB Binlog: Node: 1, down, Subscriber bitmask 00
2015-02-13 23:41:40 27483 [Note] NDB Binlog: Node: 2, down, Subscriber bitmask 00
2015-02-13 23:41:40 27483 [Note] NDB Binlog: cluster failure for ./mysql/ndb_schema at epoch 24339/0.
2015-02-13 23:41:40 27483 [Note] NDB Binlog: cluster failure for ./mysql/ndb_apply_status at epoch 24339/0.
2015-02-13 23:41:40 27483 [Note] Restarting Cluster Binlog
2015-02-13 23:41:40 27483 [Note] NDB Binlog: Ndb object created with reference : 0x80030035, name : Ndb Binlog schema change monitoring
2015-02-13 23:41:40 27483 [Note] NDB Binlog: Ndb object created with reference : 0x80050035, name : Ndb Binlog data change monitoring
2015-02-13 23:41:40 27483 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_schema
2015-02-13 23:41:40 27483 [ERROR] NDB Binlog: Creating NdbEventOperation failed for REPL$mysql/ndb_schema
2015-02-13 23:41:40 27483 [ERROR] NDB Binlog:FAILED CREATE (DISCOVER) EVENT OPERATIONS Event: REPL$mysql/ndb_schema
2015-02-13 23:41:41 27483 [Note] table './lsd_test/t_events' opened read only
2015-02-13 23:41:41 27483 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_schema
2015-02-13 23:41:41 27483 [ERROR] NDB Binlog: Creating NdbEventOperation failed for REPL$mysql/ndb_schema
2015-02-13 23:41:41 27483 [ERROR] NDB Binlog:FAILED CREATE (DISCOVER) EVENT OPERATIONS Event: REPL$mysql/ndb_schema
2015-02-13 23:41:41 27483 [Note] table './lsd_test/t_messages' opened read only


(every table opened read only, so i didn't include all those strings)

2015-02-13 23:41:41 27483 [Note] table './lsd_test/t_event_members' opened read only
2015-02-13 23:41:42 27483 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_schema
2015-02-13 23:41:42 27483 [ERROR] NDB Binlog: Creating NdbEventOperation failed for REPL$mysql/ndb_schema
2015-02-13 23:41:42 27483 [ERROR] NDB Binlog:FAILED CREATE (DISCOVER) EVENT OPERATIONS Event: REPL$mysql/ndb_schema
2015-02-13 23:41:43 27483 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_schema
2015-02-13 23:41:43 27483 [ERROR] NDB Binlog: Creating NdbEventOperation failed for REPL$mysql/ndb_schema
2015-02-13 23:41:43 27483 [ERROR] NDB Binlog:FAILED CREATE (DISCOVER) EVENT OPERATIONS Event: REPL$mysql/ndb_schema
2015-02-13 23:41:44 27483 [Note] table './lsd_test/t_relations' opened read only
2015-02-13 23:41:44 27483 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_schema
2015-02-13 23:42:11 27483 [Note] table './lsd_test/t_event_members' opened read only
2015-02-13 23:42:14 27483 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)
2015-02-13 23:42:14 27483 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_apply_status
2015-02-13 23:42:14 27483 [Note] table './lsd_test/t_relations' opened read only
2015-02-13 23:42:14 27483 [Note] NDB Binlog: logging ./mysql/ndb_apply_status (UPDATED,USE_WRITE)
2015-02-13 23:42:14 27483 [Note] NDB: Cleaning stray tables from database 'information_schema'
2015-02-13 23:42:14 27483 [Note] NDB: Cleaning stray tables from database 'lsd_test'
2015-02-13 23:42:14 27483 [Note] NDB: Cleaning stray tables from database 'lsd_test_innodb'
2015-02-13 23:42:14 27483 [Note] NDB: Cleaning stray tables from database 'mytest'
2015-02-13 23:42:14 27483 [Note] NDB: Cleaning stray tables from database 'ndbinfo'
2015-02-13 23:42:14 27483 [Note] NDB: Cleaning stray tables from database 'performance_schema'
2015-02-13 23:42:14 27483 [Note] NDB: Cleaning stray tables from database 'test'
2015-02-13 23:42:14 27483 [Note] NDB: Cleaning stray tables from database 'test1'
2015-02-13 23:42:14 27483 [Note] NDB: Cleaning stray tables from database 'test_ndb'
2015-02-13 23:42:14 [NdbApi] INFO -- Flushing incomplete GCI:s < 24372/9
2015-02-13 23:42:14 [NdbApi] INFO -- Flushing incomplete GCI:s < 24372/9
2015-02-13 23:42:14 27483 [Note] NDB Binlog: starting log at epoch 24372/9
2015-02-13 23:42:14 27483 [Note] NDB Binlog: ndb tables writable



I have following settings in config.ini:
TransactionDeadlockDetectionTimeout=52000 #default 1200
MaxNoOfConcurrentTransactions=16384
MaxNoOfConcurrentOperations=100000

BatchSizePerLocalScan=512
FragmentLogFileSize=256M
NoOfFragmentLogFiles=22 # 6xDataMemory [in MB]/(4 *FragmentLogFileSize [in MB])

RedoBuffer=48M
MaxNoOfExecutionThreads=4
StopOnError=false
LockPagesInMainMemory=1

#severalnines settings
TransactionBufferMemory=8M

TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=100

TimeBetweenEpochsTimeout=0
DiskCheckpointSpeedInRestart=100M
DiskCheckpointSpeed=10M
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=24M
BackupLogBufferSize=16M
BackupMemory=40M

TimeBetweenWatchdogCheckInitial=60000
TransactionInactiveTimeout=800
RedoOverCommitCounter=3
RedoOverCommitLimit=20
NoOfFragmentLogParts=4

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


I reset TransactionInactiveTimeout to 800 from severalnines config and TransactionDeadlockDetectionTimeout to 52000 from default.

Also there is my.cnf parameters:
#severalnines
ndbcluster=1
ndb-connectstring="192.168.1.5:1186,192.168.1.6:1186"
ndb-force-send=1
ndb-use-exact-count=0
ndb-extra-logging=1
ndb-batch-size=24M
ndb-autoincrement-prefetch-sz=1024
#OTHER THINGS, BUFFERS ETC
key_buffer_size = 32M
max_allowed_packet = 128M
#thread_cache_size=1024
#myisam_sort_buffer_size = 8M
memlock=0
sysdate_is_now=1
thread-cache-size=128
###QUERY CACHE IS DISABLED
###THE QUERY CACHE IN MYSQL CLUSTER CAN HAMPER PERFORMANCE A LOT. HENCE IT IS DISABLED
###BECAUSE INVALIDATION OF IT TAKES TIME (REMEMBER, IT IS A DISTRIBUTED ENVIRONMENT)
###ONLY ENABLE IT AND USE SQL_CACHE TO CACHE ONLY CERTAIN QUERIES ON READONLY TABLES
query_cache_type = 0
query_cache_size = 0


What can I do?

Options: ReplyQuote


Subject
Views
Written By
Posted
Lock wait timeout and other errors
3262
February 13, 2015 05:57PM


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.