*************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2016-09-20 16:39:10 7f5a00419b00 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 9 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1146 srv_active, 0 srv_shutdown, 1895 srv_idle srv_master_thread log flush and writes: 3041 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59431 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 --Thread 139796907247360 has waited at buf0flu.cc line 1099 for 152.00 seconds the semaphore: S-lock on RW-latch at 0x7f40671c3ec0 '&block->lock' a writer (thread id 140024528386816) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0flu.cc line 1093 Last time write locked in file /var/tmp/portage/dev-db/mariadb-10.1.17/work/mysql/storage/xtradb/btr/btr0btr.cc line 743 Holder thread 0 file not yet reserved line 0 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: signal count 898425 Mutex spin waits 11297021, rounds 70165740, OS waits 391788 RW-shared spins 610802, rounds 16465518, OS waits 514826 RW-excl spins 69050, rounds 479238, OS waits 2473 Spin rounds per wait: 6.21 mutex, 26.96 RW-shared, 6.94 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 995739171 Purge done for trx's n:o < 995739138 undo n:o < 0 state: running but idle History list length 598 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 4933, OS thread handle 0x7f5a00419b00, query id 14933 localhost amunroe init show engine innodb status ---TRANSACTION 995739170, not started MySQL thread id 4895, OS thread handle 0x7f5a00463b00, query id 14799 localhost amunroe altering table optimize table email ---TRANSACTION 995435267, not started MySQL thread id 1, OS thread handle 0x7f5a004f7b00, query id 0 Waiting for background binlog tasks ---TRANSACTION 995739169, ACTIVE 306 sec mysql tables in use 1, locked 1 MySQL thread id 4895, OS thread handle 0x7f5a00463b00, query id 14799 localhost amunroe altering table optimize table email Trx read view will not see trx with id >= 995739171, sees < 995739170 Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC -------- 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 (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (write thread) I/O thread 11 state: waiting for completed aio requests (write thread) I/O thread 12 state: waiting for completed aio requests (write thread) I/O thread 13 state: waiting for completed aio requests (write thread) I/O thread 14 state: waiting for completed aio requests (write thread) I/O thread 15 state: waiting for completed aio requests (write thread) I/O thread 16 state: waiting for completed aio requests (write thread) I/O thread 17 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 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 15203431 OS file reads, 5581972 OS file writes, 190716 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 579, seg size 581, 1 merges merged operations: insert 0, delete mark 1, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2257845204562 Log flushed up to 2257845204562 Pages flushed up to 2257673296406 Last checkpoint at 2257673296406 Max checkpoint age 1738750649 Checkpoint age target 1684414692 Modified age 171908156 Checkpoint age 171908156 0 pending log writes, 0 pending chkp writes 53010 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 216227905536; in additional pool allocated 0 Total memory allocated by read views 304 Internal hash tables (constant factor + variable factor) Adaptive hash index 3442883840 (3398562624 + 44321216) Page hash 25500536 (buffer pool 0 only) Dictionary cache 2783505356 (815992048 + 1967513308) File system 564064752 (812272 + 563252480) Lock system 509996584 (509995144 + 1440) Recovery system 0 (0 + 0) Dictionary memory allocated 1967513308 Buffer pool size 12582904 Buffer pool size, bytes 206158299136 Free buffers 419 Database pages 12579257 Old database pages 4643351 Modified db pages 12287 Percent of dirty pages(LRU & free pages): 0.098 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 5001, not young 5589234 0.00 youngs/s, 0.00 non-youngs/s Pages read 13647645, created 4850125, written 4863663 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: 12579257, unzip_LRU len: 2649 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 42 Database pages 1572394 Old database pages 580414 Modified db pages 1554 Percent of dirty pages(LRU & free pages): 0.099 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 527, not young 977667 0.00 youngs/s, 0.00 non-youngs/s Pages read 1708454, created 618419, written 624281 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: 1572394, unzip_LRU len: 379 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 90 Database pages 1572381 Old database pages 580409 Modified db pages 1469 Percent of dirty pages(LRU & free pages): 0.093 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 483, not young 1091842 0.00 youngs/s, 0.00 non-youngs/s Pages read 1706969, created 607474, written 607771 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: 1572381, unzip_LRU len: 309 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 76 Database pages 1572394 Old database pages 580414 Modified db pages 1510 Percent of dirty pages(LRU & free pages): 0.096 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 671, not young 545182 0.00 youngs/s, 0.00 non-youngs/s Pages read 1697341, created 605633, written 605641 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: 1572394, unzip_LRU len: 317 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 19 Database pages 1572447 Old database pages 580434 Modified db pages 1666 Percent of dirty pages(LRU & free pages): 0.106 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 498, not young 566791 0.00 youngs/s, 0.00 non-youngs/s Pages read 1694375, created 599183, written 602003 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: 1572447, unzip_LRU len: 290 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 86 Database pages 1572358 Old database pages 580401 Modified db pages 1480 Percent of dirty pages(LRU & free pages): 0.094 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 769, not young 718414 0.00 youngs/s, 0.00 non-youngs/s Pages read 1704222, created 599134, written 602050 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: 1572358, unzip_LRU len: 346 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 27 Database pages 1572446 Old database pages 580433 Modified db pages 1553 Percent of dirty pages(LRU & free pages): 0.099 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 719, not young 591376 0.00 youngs/s, 0.00 non-youngs/s Pages read 1710353, created 601494, written 601206 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: 1572446, unzip_LRU len: 324 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 0 Database pages 1572444 Old database pages 580432 Modified db pages 1569 Percent of dirty pages(LRU & free pages): 0.100 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 880, not young 505961 0.00 youngs/s, 0.00 non-youngs/s Pages read 1714172, created 610078, written 612037 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: 1572444, unzip_LRU len: 355 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 79 Database pages 1572393 Old database pages 580414 Modified db pages 1486 Percent of dirty pages(LRU & free pages): 0.095 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 454, not young 592001 0.00 youngs/s, 0.00 non-youngs/s Pages read 1711759, created 608710, written 608674 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: 1572393, unzip_LRU len: 329 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 1 RW transactions active inside InnoDB 0 RO transactions active inside InnoDB 1 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 995739171 Read view up limit trx id 995739170 Read view low limit trx id 995739171 Read view individually stored trx ids: Read view trx id 995739170 ----------------- Main thread process no. 32809, id 139796965996288, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 1 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 1 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2016-09-20 16:39:12 7f5a00419b00 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 2 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1146 srv_active, 0 srv_shutdown, 1897 srv_idle srv_master_thread log flush and writes: 3043 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59431 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 --Thread 139796907247360 has waited at buf0flu.cc line 1099 for 154.00 seconds the semaphore: S-lock on RW-latch at 0x7f40671c3ec0 '&block->lock' a writer (thread id 140024528386816) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0flu.cc line 1093 Last time write locked in file /var/tmp/portage/dev-db/mariadb-10.1.17/work/mysql/storage/xtradb/btr/btr0btr.cc line 743 Holder thread 0 file not yet reserved line 0 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: signal count 898425 Mutex spin waits 11297021, rounds 70165740, OS waits 391788 RW-shared spins 610802, rounds 16465518, OS waits 514826 RW-excl spins 69050, rounds 479238, OS waits 2473 Spin rounds per wait: 6.21 mutex, 26.96 RW-shared, 6.94 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 995739171 Purge done for trx's n:o < 995739138 undo n:o < 0 state: running but idle History list length 598 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 4934, OS thread handle 0x7f5a00419b00, query id 14936 localhost amunroe init show engine innodb status ---TRANSACTION 995739170, not started MySQL thread id 4895, OS thread handle 0x7f5a00463b00, query id 14799 localhost amunroe altering table optimize table email ---TRANSACTION 995435267, not started MySQL thread id 1, OS thread handle 0x7f5a004f7b00, query id 0 Waiting for background binlog tasks ---TRANSACTION 995739169, ACTIVE 308 sec mysql tables in use 1, locked 1 MySQL thread id 4895, OS thread handle 0x7f5a00463b00, query id 14799 localhost amunroe altering table optimize table email Trx read view will not see trx with id >= 995739171, sees < 995739170 Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC -------- 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 (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (write thread) I/O thread 11 state: waiting for completed aio requests (write thread) I/O thread 12 state: waiting for completed aio requests (write thread) I/O thread 13 state: waiting for completed aio requests (write thread) I/O thread 14 state: waiting for completed aio requests (write thread) I/O thread 15 state: waiting for completed aio requests (write thread) I/O thread 16 state: waiting for completed aio requests (write thread) I/O thread 17 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 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 15203431 OS file reads, 5581972 OS file writes, 190716 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 579, seg size 581, 1 merges merged operations: insert 0, delete mark 1, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2257845204562 Log flushed up to 2257845204562 Pages flushed up to 2257673296406 Last checkpoint at 2257673296406 Max checkpoint age 1738750649 Checkpoint age target 1684414692 Modified age 171908156 Checkpoint age 171908156 0 pending log writes, 0 pending chkp writes 53010 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 216227905536; in additional pool allocated 0 Total memory allocated by read views 304 Internal hash tables (constant factor + variable factor) Adaptive hash index 3442883840 (3398562624 + 44321216) Page hash 25500536 (buffer pool 0 only) Dictionary cache 2783505356 (815992048 + 1967513308) File system 564064752 (812272 + 563252480) Lock system 509996584 (509995144 + 1440) Recovery system 0 (0 + 0) Dictionary memory allocated 1967513308 Buffer pool size 12582904 Buffer pool size, bytes 206158299136 Free buffers 419 Database pages 12579257 Old database pages 4643351 Modified db pages 12287 Percent of dirty pages(LRU & free pages): 0.098 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 5001, not young 5589234 0.00 youngs/s, 0.00 non-youngs/s Pages read 13647645, created 4850125, written 4863663 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: 12579257, unzip_LRU len: 2649 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 42 Database pages 1572394 Old database pages 580414 Modified db pages 1554 Percent of dirty pages(LRU & free pages): 0.099 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 527, not young 977667 0.00 youngs/s, 0.00 non-youngs/s Pages read 1708454, created 618419, written 624281 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: 1572394, unzip_LRU len: 379 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 90 Database pages 1572381 Old database pages 580409 Modified db pages 1469 Percent of dirty pages(LRU & free pages): 0.093 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 483, not young 1091842 0.00 youngs/s, 0.00 non-youngs/s Pages read 1706969, created 607474, written 607771 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: 1572381, unzip_LRU len: 309 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 76 Database pages 1572394 Old database pages 580414 Modified db pages 1510 Percent of dirty pages(LRU & free pages): 0.096 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 671, not young 545182 0.00 youngs/s, 0.00 non-youngs/s Pages read 1697341, created 605633, written 605641 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: 1572394, unzip_LRU len: 317 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 19 Database pages 1572447 Old database pages 580434 Modified db pages 1666 Percent of dirty pages(LRU & free pages): 0.106 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 498, not young 566791 0.00 youngs/s, 0.00 non-youngs/s Pages read 1694375, created 599183, written 602003 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: 1572447, unzip_LRU len: 290 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 86 Database pages 1572358 Old database pages 580401 Modified db pages 1480 Percent of dirty pages(LRU & free pages): 0.094 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 769, not young 718414 0.00 youngs/s, 0.00 non-youngs/s Pages read 1704222, created 599134, written 602050 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: 1572358, unzip_LRU len: 346 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 27 Database pages 1572446 Old database pages 580433 Modified db pages 1553 Percent of dirty pages(LRU & free pages): 0.099 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 719, not young 591376 0.00 youngs/s, 0.00 non-youngs/s Pages read 1710353, created 601494, written 601206 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: 1572446, unzip_LRU len: 324 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 0 Database pages 1572444 Old database pages 580432 Modified db pages 1569 Percent of dirty pages(LRU & free pages): 0.100 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 880, not young 505961 0.00 youngs/s, 0.00 non-youngs/s Pages read 1714172, created 610078, written 612037 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: 1572444, unzip_LRU len: 355 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 79 Database pages 1572393 Old database pages 580414 Modified db pages 1486 Percent of dirty pages(LRU & free pages): 0.095 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 454, not young 592001 0.00 youngs/s, 0.00 non-youngs/s Pages read 1711759, created 608710, written 608674 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: 1572393, unzip_LRU len: 329 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 1 RW transactions active inside InnoDB 0 RO transactions active inside InnoDB 1 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 995739171 Read view up limit trx id 995739170 Read view low limit trx id 995739171 Read view individually stored trx ids: Read view trx id 995739170 ----------------- Main thread process no. 32809, id 139796965996288, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 1 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 1 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2016-09-20 16:39:14 7f5a00419b00 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 2 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1146 srv_active, 0 srv_shutdown, 1900 srv_idle srv_master_thread log flush and writes: 3046 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59431 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 --Thread 139796907247360 has waited at buf0flu.cc line 1099 for 156.00 seconds the semaphore: S-lock on RW-latch at 0x7f40671c3ec0 '&block->lock' a writer (thread id 140024528386816) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0flu.cc line 1093 Last time write locked in file /var/tmp/portage/dev-db/mariadb-10.1.17/work/mysql/storage/xtradb/btr/btr0btr.cc line 743 Holder thread 0 file not yet reserved line 0 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: reservation count 59430 OS WAIT ARRAY INFO: signal count 898425 Mutex spin waits 11297021, rounds 70165740, OS waits 391788 RW-shared spins 610802, rounds 16465518, OS waits 514826 RW-excl spins 69050, rounds 479238, OS waits 2473 Spin rounds per wait: 6.21 mutex, 26.96 RW-shared, 6.94 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 995739171 Purge done for trx's n:o < 995739138 undo n:o < 0 state: running but idle History list length 598 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 4935, OS thread handle 0x7f5a00419b00, query id 14939 localhost amunroe init show engine innodb status ---TRANSACTION 995739170, not started MySQL thread id 4895, OS thread handle 0x7f5a00463b00, query id 14799 localhost amunroe altering table optimize table email ---TRANSACTION 995435267, not started MySQL thread id 1, OS thread handle 0x7f5a004f7b00, query id 0 Waiting for background binlog tasks ---TRANSACTION 995739169, ACTIVE 310 sec mysql tables in use 1, locked 1 MySQL thread id 4895, OS thread handle 0x7f5a00463b00, query id 14799 localhost amunroe altering table optimize table email Trx read view will not see trx with id >= 995739171, sees < 995739170 Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC -------- 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 (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (write thread) I/O thread 11 state: waiting for completed aio requests (write thread) I/O thread 12 state: waiting for completed aio requests (write thread) I/O thread 13 state: waiting for completed aio requests (write thread) I/O thread 14 state: waiting for completed aio requests (write thread) I/O thread 15 state: waiting for completed aio requests (write thread) I/O thread 16 state: waiting for completed aio requests (write thread) I/O thread 17 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 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 15203431 OS file reads, 5581972 OS file writes, 190716 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 579, seg size 581, 1 merges merged operations: insert 0, delete mark 1, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2257845204562 Log flushed up to 2257845204562 Pages flushed up to 2257673296406 Last checkpoint at 2257673296406 Max checkpoint age 1738750649 Checkpoint age target 1684414692 Modified age 171908156 Checkpoint age 171908156 0 pending log writes, 0 pending chkp writes 53010 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 216227905536; in additional pool allocated 0 Total memory allocated by read views 304 Internal hash tables (constant factor + variable factor) Adaptive hash index 3442883840 (3398562624 + 44321216) Page hash 25500536 (buffer pool 0 only) Dictionary cache 2783505356 (815992048 + 1967513308) File system 564064752 (812272 + 563252480) Lock system 509996584 (509995144 + 1440) Recovery system 0 (0 + 0) Dictionary memory allocated 1967513308 Buffer pool size 12582904 Buffer pool size, bytes 206158299136 Free buffers 419 Database pages 12579257 Old database pages 4643351 Modified db pages 12287 Percent of dirty pages(LRU & free pages): 0.098 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 5001, not young 5589234 0.00 youngs/s, 0.00 non-youngs/s Pages read 13647645, created 4850125, written 4863663 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: 12579257, unzip_LRU len: 2649 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 42 Database pages 1572394 Old database pages 580414 Modified db pages 1554 Percent of dirty pages(LRU & free pages): 0.099 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 527, not young 977667 0.00 youngs/s, 0.00 non-youngs/s Pages read 1708454, created 618419, written 624281 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: 1572394, unzip_LRU len: 379 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 90 Database pages 1572381 Old database pages 580409 Modified db pages 1469 Percent of dirty pages(LRU & free pages): 0.093 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 483, not young 1091842 0.00 youngs/s, 0.00 non-youngs/s Pages read 1706969, created 607474, written 607771 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: 1572381, unzip_LRU len: 309 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 76 Database pages 1572394 Old database pages 580414 Modified db pages 1510 Percent of dirty pages(LRU & free pages): 0.096 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 671, not young 545182 0.00 youngs/s, 0.00 non-youngs/s Pages read 1697341, created 605633, written 605641 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: 1572394, unzip_LRU len: 317 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 19 Database pages 1572447 Old database pages 580434 Modified db pages 1666 Percent of dirty pages(LRU & free pages): 0.106 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 498, not young 566791 0.00 youngs/s, 0.00 non-youngs/s Pages read 1694375, created 599183, written 602003 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: 1572447, unzip_LRU len: 290 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 86 Database pages 1572358 Old database pages 580401 Modified db pages 1480 Percent of dirty pages(LRU & free pages): 0.094 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 769, not young 718414 0.00 youngs/s, 0.00 non-youngs/s Pages read 1704222, created 599134, written 602050 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: 1572358, unzip_LRU len: 346 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 27 Database pages 1572446 Old database pages 580433 Modified db pages 1553 Percent of dirty pages(LRU & free pages): 0.099 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 719, not young 591376 0.00 youngs/s, 0.00 non-youngs/s Pages read 1710353, created 601494, written 601206 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: 1572446, unzip_LRU len: 324 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 0 Database pages 1572444 Old database pages 580432 Modified db pages 1569 Percent of dirty pages(LRU & free pages): 0.100 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 880, not young 505961 0.00 youngs/s, 0.00 non-youngs/s Pages read 1714172, created 610078, written 612037 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: 1572444, unzip_LRU len: 355 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 79 Database pages 1572393 Old database pages 580414 Modified db pages 1486 Percent of dirty pages(LRU & free pages): 0.095 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 454, not young 592001 0.00 youngs/s, 0.00 non-youngs/s Pages read 1711759, created 608710, written 608674 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: 1572393, unzip_LRU len: 329 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 1 RW transactions active inside InnoDB 0 RO transactions active inside InnoDB 1 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 995739171 Read view up limit trx id 995739170 Read view low limit trx id 995739171 Read view individually stored trx ids: Read view trx id 995739170 ----------------- Main thread process no. 32809, id 139796965996288, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 1 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 1 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================