WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long WSREP: BF lock wait long ===================================== 131008 21:33:54 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 81194 1_second, 81194 sleeps, 8119 10_second, 1259 background, 1259 flush srv_master_thread log flush and writes: 82029 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5056778, signal count 18396750 Mutex spin waits 79042224, rounds 98208826, OS waits 259354 RW-shared spins 11831003, rounds 92265701, OS waits 2166401 RW-excl spins 4440977, rounds 120380436, OS waits 2448882 Spin rounds per wait: 1.24 mutex, 7.80 RW-shared, 27.11 RW-excl -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1310826 OS file reads, 25213143 OS file writes, 280446 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.30 writes/s, 0.20 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 223645 merges merged operations: insert 97717, delete mark 1835686, delete 11952 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 169998197, node heap has 95489 buffer(s) 0.00 hash searches/s, 0.20 non-hash searches/s --- LOG --- Log sequence number 9766009826065 Log flushed up to 9766009826065 Last checkpoint at 9766009826065 Max checkpoint age 3394315838 Checkpoint age target 3288243469 Modified age 0 Checkpoint age 0 0 pending log writes, 0 pending chkp writes 22835805 log i/o's done, 0.15 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 88206213120; in additional pool allocated 0 Total memory allocated by read views 516640 Internal hash tables (constant factor + variable factor) Adaptive hash index 2924481568 (1359985576 + 1564495992) Page hash 84999848 (buffer pool 0 only) Dictionary cache 412071727 (339997808 + 72073919) File system 83536 (82672 + 864) Lock system 212938032 (212498744 + 439288) Recovery system 0 (0 + 0) Dictionary memory allocated 72073919 Buffer pool size 5242879 Buffer pool size, bytes 85899329536 Free buffers 3034813 Database pages 2112577 Old database pages 779856 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 25656, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 1308725, created 803852, written 2273724 0.00 reads/s, 0.00 creates/s, 0.10 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 2112577, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 40 queries inside InnoDB, 153 queries in queue 2 read views open inside InnoDB 269 transactions active inside InnoDB 269 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 130AA92E5F Read view up limit trx id 130AA9295F Read view low limit trx id 130AA92E5F Read view individually stored trx ids: Read view trx id 130AA92E59 Read view trx id 130AA92E17 Read view trx id 130AA92D3E Read view trx id 130AA9295F ----------------- Main thread process no. 31263, id 140620819060480, state: waiting for server activity Number of rows inserted 265946051, updated 1803598, deleted 8191677, read 25038472682 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ------------------------ LATEST DETECTED DEADLOCK ------------------------ 131008 21:17:23 *** (1) TRANSACTION: TRANSACTION 130A88E499, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 224217, OS thread handle 0x7fe1cba0e700, query id 201781327 192.168.100.31 fbhub Updating UPDATE users SET utc_offset = IF(NULL IS NULL, utc_offset, NULL), last_used=NOW(), last_host_id = if(last_host_id = 19,19,'1735') WHERE id='18700125' and last_used < now() - interval 30 minute *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6423238 n bits 136 index `PRIMARY` of table `lib_area_100`.`users` trx id 130A88E499 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 130A88DEA3, ACTIVE 1 sec starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 8 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 225033, OS thread handle 0x7fe1db71c700, query id 201781913 192.168.100.31 fbhub Updating UPDATE users SET s_subscr_text_authors = '<87>\r\n', subscr_type = IF(subscr_type = 1, 1, 2), subscribe_new_buys = 1 WHERE id = '18700125' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 6423238 n bits 136 index `PRIMARY` of table `lib_area_100`.`users` trx id 130A88DEA3 lock mode S locks rec b ut not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6423238 n bits 136 index `PRIMARY` of table `lib_area_100`.`users` trx id 130A88DEA3 lock_mode X locks rec b ut not gap waiting *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 130AA965FD Purge done for trx's n:o < 130AA92F73 undo n:o < 0 History list length 978 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 226376, OS thread handle 0x7fe1ca8ca700, query id 204935441 192.168.100.31 fbhub Updating UPDATE users SET utc_offset = IF(NULL IS NULL, utc_offset, NULL), last_used=NOW(), last_host_id = if(last_host_id = 19,19,'19') WHERE id='2882195' and last_used < now() - interval 30 minute ---TRANSACTION 0, not started waiting in InnoDB queue ---TRANSACTION 130AA92E17, ACTIVE 138 sec 5 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 226258, OS thread handle 0x7fe470bd6700, query id 204922012 192.168.100.31 fbhub wsrep in pre-commit stage commit Trx read view will not see trx with id >= 130AA92E5F, sees < 130AA9295F TABLE LOCK table `lib_area_100`.`basket` trx id 130AA92E17 lock mode IX TABLE LOCK table `lib_area_100`.`basket_temp_set` trx id 130AA92E17 lock mode IS RECORD LOCKS space id 0 page no 1196050 n bits 160 index `PRIMARY` of table `lib_area_100`.`basket_temp_set` trx id 130AA92E17 lock mode S locks rec but not gap TABLE LOCK table `lib_area_100`.`users` trx id 130AA92E17 lock mode IS RECORD LOCKS space id 0 page no 380834 n bits 136 index `PRIMARY` of table `lib_area_100`.`users` trx id 130AA92E17 lock mode S locks rec but not gap ---------------------------- END OF INNODB MONITOR OUTPUT ============================