===================================== 2015-06-10 23:33:52 7f58b75fe700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 18 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 5996 srv_active, 0 srv_shutdown, 13205 srv_idle srv_master_thread log flush and writes: 19200 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 15257 --Thread 140019116971776 has waited at trx0undo.ic line 171 for 255.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 0x7f58c4ec4540 created in file buf0buf.cc line 1065 a writer (thread id 140019116971776) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file buf0flu.cc line 1056 Last time write locked in file /export/home/pb2/build/sb_0-15205200-1430829639.81/rpm/BUILD/mysql-5.6.25/mysql-5.6.25/storage/innobase/trx/trx0rec.cc line 1295 --Thread 140019116705536 has waited at fsp0fsp.cc line 2840 for 145.00 seconds the semaphore: Mutex at 0x3361788 created file dict0dict.cc line 1026, lock var 1 waiters flag 1 OS WAIT ARRAY INFO: signal count 15325 Mutex spin waits 11997, rounds 329810, OS waits 10862 RW-shared spins 1884, rounds 51750, OS waits 1694 RW-excl spins 436, rounds 84360, OS waits 2682 Spin rounds per wait: 27.49 mutex, 27.47 RW-shared, 193.49 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 7320 Purge done for trx's n:o < 7316 undo n:o < 0 state: running but idle History list length 623 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 7319, not started MySQL thread id 32, OS thread handle 0x7f58bdb27700, query id 22150218 static-173-49-198-138.phlapa.fios.verizon.net 173.49.198.138 mharraz cleaning up ---TRANSACTION 5263, not started returning various info to MySQL MySQL thread id 31, OS thread handle 0x7f58bdb68700, query id 22150225 static-173-49-198-138.phlapa.fios.verizon.net 173.49.198.138 mharraz Opening tables select * from information_schema.tables WHERE table_schema = 'opendata_1' AND table_name = 'address' ---TRANSACTION 7314, ACTIVE 257 sec committing mysql tables in use 46, locked 46 89 lock struct(s), heap size 13864, 6823 row lock(s), undo log entries 308 MySQL thread id 109, OS thread handle 0x7f58bdba9700, query id 22150196 static-173-49-198-138.phlapa.fios.verizon.net 173.49.198.138 kettle_user_1 init commit Trx read view will not see trx with id >= 7315, sees < 7315 -------- 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: 1; buffer pool: 0 4255 OS file reads, 268206 OS file writes, 32386 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 14, seg size 16, 23 merges merged operations: insert 0, delete mark 1281, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276671, node heap has 3 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 6480680055 Log flushed up to 6480637662 Pages flushed up to 6480637662 Last checkpoint at 6480637662 1 pending log writes, 0 pending chkp writes 12594 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 285810 Buffer pool size 8191 Free buffers 7827 Database pages 361 Old database pages 0 Modified db pages 21 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 3581, not young 189387 0.00 youngs/s, 0.00 non-youngs/s Pages read 4213, created 220289, written 242976 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 361, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 1984, id 140018999875328, state: flushing log Number of rows inserted 11061049, updated 5, deleted 29570, read 363775 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 140019116971776 has waited at trx0undo.ic line 171 for 272.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 0x7f58c4ec4540 created in file buf0buf.cc line 1065 a writer (thread id 140019116971776) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file buf0flu.cc line 1056 Last time write locked in file /export/home/pb2/build/sb_0-15205200-1430829639.81/rpm/BUILD/mysql-5.6.25/mysql-5.6.25/storage/innobase/trx/trx0rec.cc line 1295 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 2015-06-10 23:34:12 7f58b75fe700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 5996 srv_active, 0 srv_shutdown, 13205 srv_idle srv_master_thread log flush and writes: 19200 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 15257 --Thread 140019116971776 has waited at trx0undo.ic line 171 for 275.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 0x7f58c4ec4540 created in file buf0buf.cc line 1065 a writer (thread id 140019116971776) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file buf0flu.cc line 1056 Last time write locked in file /export/home/pb2/build/sb_0-15205200-1430829639.81/rpm/BUILD/mysql-5.6.25/mysql-5.6.25/storage/innobase/trx/trx0rec.cc line 1295 --Thread 140019116705536 has waited at fsp0fsp.cc line 2840 for 165.00 seconds the semaphore: Mutex at 0x3361788 created file dict0dict.cc line 1026, lock var 1 waiters flag 1 OS WAIT ARRAY INFO: signal count 15325 Mutex spin waits 11997, rounds 329810, OS waits 10862 RW-shared spins 1884, rounds 51750, OS waits 1694 RW-excl spins 436, rounds 84360, OS waits 2682 Spin rounds per wait: 27.49 mutex, 27.47 RW-shared, 193.49 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 7320 Purge done for trx's n:o < 7316 undo n:o < 0 state: running but idle History list length 623 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 7319, not started MySQL thread id 32, OS thread handle 0x7f58bdb27700, query id 22150218 static-173-49-198-138.phlapa.fios.verizon.net 173.49.198.138 mharraz cleaning up ---TRANSACTION 5263, not started returning various info to MySQL MySQL thread id 31, OS thread handle 0x7f58bdb68700, query id 22150225 static-173-49-198-138.phlapa.fios.verizon.net 173.49.198.138 mharraz Opening tables select * from information_schema.tables WHERE table_schema = 'opendata_1' AND table_name = 'address' ---TRANSACTION 7314, ACTIVE 277 sec committing mysql tables in use 46, locked 46 89 lock struct(s), heap size 13864, 6823 row lock(s), undo log entries 308 MySQL thread id 109, OS thread handle 0x7f58bdba9700, query id 22150196 static-173-49-198-138.phlapa.fios.verizon.net 173.49.198.138 kettle_user_1 init commit Trx read view will not see trx with id >= 7315, sees < 7315 -------- 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: 1; buffer pool: 0 4255 OS file reads, 268206 OS file writes, 32386 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 14, seg size 16, 23 merges merged operations: insert 0, delete mark 1281, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276671, node heap has 3 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 6480680055 Log flushed up to 6480637662 Pages flushed up to 6480637662 Last checkpoint at 6480637662 1 pending log writes, 0 pending chkp writes 12594 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 285810 Buffer pool size 8191 Free buffers 7827 Database pages 361 Old database pages 0 Modified db pages 21 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 3581, not young 189387 0.00 youngs/s, 0.00 non-youngs/s Pages read 4213, created 220289, written 242976 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 361, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 1984, id 140018999875328, state: flushing log Number of rows inserted 11061049, updated 5, deleted 29570, read 363775 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 2015-06-10 23:34:32 7f58b75fe700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 6001 srv_active, 0 srv_shutdown, 13205 srv_idle srv_master_thread log flush and writes: 19206 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 15268 OS WAIT ARRAY INFO: signal count 15339 Mutex spin waits 12005, rounds 330050, OS waits 10870 RW-shared spins 1887, rounds 51810, OS waits 1696 RW-excl spins 436, rounds 84392, OS waits 2683 Spin rounds per wait: 27.49 mutex, 27.46 RW-shared, 193.56 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 7339 Purge done for trx's n:o < 7332 undo n:o < 0 state: running but idle History list length 626 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 7326, not started MySQL thread id 154, OS thread handle 0x7f58bc169700, query id 22150385 static.phlapa.fios.verizon.net 173.49.198.138 kettle_user_1 cleaning up ---TRANSACTION 7325, not started MySQL thread id 157, OS thread handle 0x7f58bdbea700, query id 22150924 localhost 127.0.0.1 kettle_user_1 cleaning up ---TRANSACTION 7319, not started MySQL thread id 32, OS thread handle 0x7f58bdb27700, query id 22150218 static.phlapa.fios.verizon.net 173.49.198.138 mharraz cleaning up ---TRANSACTION 5263, not started MySQL thread id 31, OS thread handle 0x7f58bdb68700, query id 22150560 static.phlapa.fios.verizon.net 173.49.198.138 mharraz cleaning up ---TRANSACTION 7338, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 141 MySQL thread id 159, OS thread handle 0x7f58bc128700, query id 22165229 localhost 127.0.0.1 pdiuser update INSERT INTO address (address_line_1__v, address_line_2__v, address_line_3__v, address_ordinal__v, address_status__v, address_type__v, address_verification_status__v, administrative_area__v, building__v, building_leading_type__v, building_name__v, building_trailing_type__v, cbsa__v, congressional_district__v, country__v, created_date__v, custom_keys__v, delivery_address_1__v, delivery_address_2__v, delivery_address_3__v, delivery_address__v, delivery_point_bar_code__v, department__v, dependent_locality__v, dependent_thoroughfare__v, dependent_thoroughfare_leading_type__v, dependent_thoroughfare -------- 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 4269 OS file reads, 268286 OS file writes, 32449 OS fsyncs 0.70 reads/s, 16384 avg bytes/read, 4.00 writes/s, 3.15 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 14, seg size 16, 23 merges merged operations: insert 0, delete mark 1281, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276671, node heap has 6 buffer(s) 449.83 hash searches/s, 110.14 non-hash searches/s --- LOG --- Log sequence number 6486040133 Log flushed up to 6485933143 Pages flushed up to 6480696823 Last checkpoint at 6480637662 0 pending log writes, 0 pending chkp writes 12612 log i/o's done, 0.90 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 285810 Buffer pool size 8191 Free buffers 7579 Database pages 606 Old database pages 203 Modified db pages 260 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3587, not young 189387 0.30 youngs/s, 0.00 non-youngs/s Pages read 4227, created 220520, written 243011 0.70 reads/s, 11.55 creates/s, 1.75 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: 606, unzip_LRU len: 0 I/O sum[0]:cur[49], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 1984, id 140018999875328, state: sleeping Number of rows inserted 11068192, updated 5, deleted 29570, read 371578 357.13 inserts/s, 0.00 updates/s, 0.00 deletes/s, 390.13 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream [mohamed.harraz@ip-10-0-5-120 ~]$ sudo vi /var/log/mysqld.log ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 285810 Buffer pool size 8191 Free buffers 7827 Database pages 361 Old database pages 0 Modified db pages 21 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 3581, not young 189387 0.00 youngs/s, 0.00 non-youngs/s Pages read 4213, created 220289, written 242976 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 361, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 1984, id 140018999875328, state: flushing log Number of rows inserted 11061049, updated 5, deleted 29570, read 363775 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 140019116971776 has waited at trx0undo.ic line 171 for 272.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 0x7f58c4ec4540 created in file buf0buf.cc line 1065 a writer (thread id 140019116971776) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file buf0flu.cc line 1056 Last time write locked in file /export/home/pb2/build/sb_0-15205200-1430829639.81/rpm/BUILD/mysql-5.6.25/mysql-5.6.25/storage/innobase/trx/trx0rec.cc line 1295 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 2015-06-10 23:34:12 7f58b75fe700 INNODB MONITOR OUTPUT =====================================