2018-05-08 22:46:52 0x7f7f52e74700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 40 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 6169 srv_active, 0 srv_shutdown, 2300 srv_idle srv_master_thread log flush and writes: 8468 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1341589 --Thread 140184278025984 has waited at dict0dict.cc line 1160 for 128.00 seconds the semaphore: Mutex at 0x563589733d90, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2 --Thread 140184736397056 has waited at ibuf0ibuf.cc line 4578 for 252.00 seconds the semaphore: S-lock on RW-latch at 0x7f805f7eaf48 created in file buf0buf.cc line 1471 a writer (thread id 140184744789760) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 --Thread 140184728004352 has waited at ibuf0ibuf.cc line 4578 for 252.00 seconds the semaphore: S-lock on RW-latch at 0x7f805f7eaf48 created in file buf0buf.cc line 1471 a writer (thread id 140184744789760) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 --Thread 140184803284736 has waited at row0purge.cc line 853 for 253.00 seconds the semaphore: S-lock on RW-latch at 0x563589733d10 created in file dict0dict.cc line 1107 a writer (thread id 140184283293440) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0purge.cc line 853 Last time write locked in file handler0alter.cc line 8546 --Thread 140184783165184 has waited at row0mysql.cc line 1780 for 252.00 seconds the semaphore: S-lock on RW-latch at 0x563589733d10 created in file dict0dict.cc line 1107 a writer (thread id 140184283293440) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0purge.cc line 853 Last time write locked in file handler0alter.cc line 8546 --Thread 140184270636800 has waited at dict0dict.cc line 1160 for 245.00 seconds the semaphore: Mutex at 0x563589733d90, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2 --Thread 140184853640960 has waited at srv0srv.cc line 2089 for 252.00 seconds the semaphore: X-lock on RW-latch at 0x563589733d10 created in file dict0dict.cc line 1107 a writer (thread id 140184283293440) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0purge.cc line 853 Last time write locked in file handler0alter.cc line 8546 --Thread 140184753182464 has waited at ibuf0ibuf.cc line 4578 for 252.00 seconds the semaphore: S-lock on RW-latch at 0x7f805f7eaf48 created in file buf0buf.cc line 1471 a writer (thread id 140184744789760) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 --Thread 140184744789760 has waited at mtr0mtr.ic line 147 for 252.00 seconds the semaphore: X-lock on RW-latch at 0x7f805f8749d8 created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode SX number of readers 0, waiters flag 1, lock_word: 10000000 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file buf0flu.cc line 1213 --Thread 140184820070144 has waited at dict0stats_bg.cc line 341 for 247.00 seconds the semaphore: Mutex at 0x563589733d90, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2 --Thread 140184648525568 has waited at dict0dict.cc line 1160 for 47.00 seconds the semaphore: Mutex at 0x563589733d90, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2 OS WAIT ARRAY INFO: signal count 3248304 RW-shared spins 0, rounds 1748002, OS waits 330692 RW-excl spins 0, rounds 7071702, OS waits 131983 RW-sx spins 1176856, rounds 2652096, OS waits 3095 Spin rounds per wait: 1748002.00 RW-shared, 7071702.00 RW-excl, 2.25 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 25307451252 Purge done for trx's n:o < 25307446647 undo n:o < 2215580 state: running History list length 1130 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421665128608832, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128597296, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128589056, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128616248, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128600592, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128594824, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128594000, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128592352, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128590704, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128589880, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128581640, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128585760, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128571752, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128570928, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128611304, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128608008, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128605536, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128607184, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128603888, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128596472, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128603064, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128601416, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128599768, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128579168, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128591528, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128587408, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128586584, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128584112, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128578344, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128582464, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128577520, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128598944, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128598120, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128621192, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128618720, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128617896, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128615424, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128604712, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128619544, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128617072, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128614600, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128612952, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128610480, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128609656, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128606360, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128602240, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128576696, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128595648, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128588232, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128573400, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128583288, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128575048, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128574224, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128568456, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128572576, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128570104, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128569280, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128593176, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128584936, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128580816, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128579992, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128575872, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421665128567632, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 25307451239, ACTIVE 272 sec mysql tables in use 1, locked 1 1 lock struct(s), heap size 1136, 0 row lock(s) MySQL thread id 963, OS thread handle 140184283293440, query id 199486 172.16.19.71 root committing alter table to storage engine /*72f770a8a364573d*/ ALTER TABLE order_line_items ADD COLUMN `is_bundle_parent` tinyint(1) NOT NULL default 0, ADD INDEX `index_order_line_items_on_is_bundle_parent` (`is_bundle_parent`) Trx read view will not see trx with id >= 25307451241, sees < 25307451132 ---TRANSACTION 25307451132, ACTIVE 321 sec updating or deleting mysql tables in use 4, locked 4 3653 lock struct(s), heap size 401616, 52458 row lock(s), undo log entries 47693 MySQL thread id 948, OS thread handle 140184783165184, query id 199402 172.16.19.71 root updating /*6c43816d2b27ab2b*/ UPDATE activities SET end_datetime = due_datetime, end_date = due_date WHERE type = 'Private::Activity::Task' AND due_datetime IS NOT NULL AND end_datetime IS NULL AND is_transient = 0 AND is_deleted = 0 -------- 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: complete io for buf page (read thread) I/O thread 3 state: complete io for buf page (read thread) I/O thread 4 state: complete io for buf page (read thread) I/O thread 5 state: complete io for buf page (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: [4, 5, 1, 5] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 2254164 OS file reads, 3802387 OS file writes, 931784 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- 2018-05-08 22:47:01 140184283293440 [Warning] InnoDB: Cannot rename file ./private_104550_production/order_line_items.ibd (space id 51213308), retried 13000 times. There are either pending IOs or flushes or the file is being extended. InnoDB: ###### Diagnostic info printed to the standard error stream 2018-05-08 22:47:11 140184836855552 [Warning] InnoDB: A long semaphore wait: --Thread 140184736397056 has waited at ibuf0ibuf.cc line 4578 for 271.00 seconds the semaphore: S-lock on RW-latch at 0x7f805f7eaf48 created in file buf0buf.cc line 1471 a writer (thread id 140184744789760) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-05-08 22:47:11 140184836855552 [Warning] InnoDB: A long semaphore wait: --Thread 140184728004352 has waited at ibuf0ibuf.cc line 4578 for 271.00 seconds the semaphore: S-lock on RW-latch at 0x7f805f7eaf48 created in file buf0buf.cc line 1471 a writer (thread id 140184744789760) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-05-08 22:47:11 140184836855552 [Warning] InnoDB: A long semaphore wait: --Thread 140184803284736 has waited at row0purge.cc line 853 for 272.00 seconds the semaphore: S-lock on RW-latch at 0x563589733d10 created in file dict0dict.cc line 1107 a writer (thread id 140184283293440) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0purge.cc line 853 Last time write locked in file handler0alter.cc line 8546 2018-05-08 22:47:11 140184836855552 [Warning] InnoDB: A long semaphore wait: --Thread 140184783165184 has waited at row0mysql.cc line 1780 for 271.00 seconds the semaphore: S-lock on RW-latch at 0x563589733d10 created in file dict0dict.cc line 1107 a writer (thread id 140184283293440) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0purge.cc line 853 Last time write locked in file handler0alter.cc line 8546 2018-05-08 22:47:11 140184836855552 [Warning] InnoDB: A long semaphore wait: --Thread 140184270636800 has waited at dict0dict.cc line 1160 for 264.00 seconds the semaphore: Mutex at 0x563589733d90, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2 2018-05-08 22:47:11 140184836855552 [Warning] InnoDB: A long semaphore wait: --Thread 140184853640960 has waited at srv0srv.cc line 2089 for 271.00 seconds the semaphore: X-lock on RW-latch at 0x563589733d10 created in file dict0dict.cc line 1107 a writer (thread id 140184283293440) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0purge.cc line 853 Last time write locked in file handler0alter.cc line 8546 2018-05-08 22:47:11 140184836855552 [Warning] InnoDB: A long semaphore wait: --Thread 140184753182464 has waited at ibuf0ibuf.cc line 4578 for 271.00 seconds the semaphore: S-lock on RW-latch at 0x7f805f7eaf48 created in file buf0buf.cc line 1471 a writer (thread id 140184744789760) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-05-08 22:47:11 140184836855552 [Warning] InnoDB: A long semaphore wait: --Thread 140184744789760 has waited at mtr0mtr.ic line 147 for 271.00 seconds the semaphore: X-lock on RW-latch at 0x7f805f8749d8 created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode SX number of readers 0, waiters flag 1, lock_word: 10000000 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file buf0flu.cc line 1213 2018-05-08 22:47:11 140184836855552 [Warning] InnoDB: A long semaphore wait: --Thread 140184820070144 has waited at dict0stats_bg.cc line 341 for 266.00 seconds the semaphore: Mutex at 0x563589733d90, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2 2018-05-08 22:47:11 140184836855552 [Note] InnoDB: A semaphore wait: --Thread 140184278025984 has waited at dict0dict.cc line 1160 for 147.00 seconds the semaphore: Mutex at 0x563589733d90, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2 2018-05-08 22:47:11 140184836855552 [Note] InnoDB: A semaphore wait: --Thread 140184736397056 has waited at ibuf0ibuf.cc line 4578 for 271.00 seconds the semaphore: S-lock on RW-latch at 0x7f805f7eaf48 created in file buf0buf.cc line 1471 a writer (thread id 140184744789760) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-05-08 22:47:11 140184836855552 [Note] InnoDB: A semaphore wait: --Thread 140184728004352 has waited at ibuf0ibuf.cc line 4578 for 271.00 seconds the semaphore: S-lock on RW-latch at 0x7f805f7eaf48 created in file buf0buf.cc line 1471 a writer (thread id 140184744789760) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-05-08 22:47:11 140184836855552 [Note] InnoDB: A semaphore wait: --Thread 140184828462848 has waited at ibuf0ibuf.cc line 4921 for 19.00 seconds the semaphore: Mutex at 0x56358255afa0, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 2018-05-08 22:47:11 140184836855552 [Note] InnoDB: A semaphore wait: --Thread 140184803284736 has waited at row0purge.cc line 853 for 272.00 seconds the semaphore: S-lock on RW-latch at 0x563589733d10 created in file dict0dict.cc line 1107 a writer (thread id 140184283293440) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0purge.cc line 853 Last time write locked in file handler0alter.cc line 8546 2018-05-08 22:47:11 140184836855552 [Note] InnoDB: A semaphore wait: --Thread 140184783165184 has waited at row0mysql.cc line 1780 for 271.00 seconds the semaphore: S-lock on RW-latch at 0x563589733d10 created in file dict0dict.cc line 1107 a writer (thread id 140184283293440) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0purge.cc line 853 Last time write locked in file handler0alter.cc line 8546 2018-05-08 22:47:11 140184836855552 [Note] InnoDB: A semaphore wait: --Thread 140184270636800 has waited at dict0dict.cc line 1160 for 264.00 seconds the semaphore: Mutex at 0x563589733d90, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2 2018-05-08 22:47:11 140184836855552 [Note] InnoDB: A semaphore wait: --Thread 140184853640960 has waited at srv0srv.cc line 2089 for 271.00 seconds the semaphore: X-lock on RW-latch at 0x563589733d10 created in file dict0dict.cc line 1107 a writer (thread id 140184283293440) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0purge.cc line 853 Last time write locked in file handler0alter.cc line 8546 2018-05-08 22:47:11 140184836855552 [Note] InnoDB: A semaphore wait: --Thread 140184753182464 has waited at ibuf0ibuf.cc line 4578 for 271.00 seconds the semaphore: S-lock on RW-latch at 0x7f805f7eaf48 created in file buf0buf.cc line 1471 a writer (thread id 140184744789760) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-05-08 22:47:11 140184836855552 [Note] InnoDB: A semaphore wait: --Thread 140184744789760 has waited at mtr0mtr.ic line 147 for 271.00 seconds the semaphore: X-lock on RW-latch at 0x7f805f8749d8 created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode SX number of readers 0, waiters flag 1, lock_word: 10000000 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file buf0flu.cc line 1213 2018-05-08 22:47:11 140184836855552 [Note] InnoDB: A semaphore wait: --Thread 140184820070144 has waited at dict0stats_bg.cc line 341 for 266.00 seconds the semaphore: Mutex at 0x563589733d90, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2 2018-05-08 22:47:11 140184836855552 [Note] InnoDB: A semaphore wait: --Thread 140184648525568 has waited at dict0dict.cc line 1160 for 66.00 seconds the semaphore: Mutex at 0x563589733d90, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2