MariaDB [(none)]> show engine innodb status; +--------+------+----------------------------------------------------------------- | Type | Name | Status | +--------+------+----------------------------------------------------------------- | InnoDB | | ===================================== 2017-02-15 10:19:02 6714 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 32 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 506064 srv_active, 0 srv_shutdown, 806 srv_idle srv_master_thread log flush and writes: 506870 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 85494152 OS WAIT ARRAY INFO: signal count 67746626 Mutex spin waits 13632056265, rounds 13225245805, OS waits 45436120 RW-shared spins 18395255, rounds 654967935, OS waits 21552484 RW-excl spins 7700911, rounds 570229144, OS waits 17354700 Spin rounds per wait: 0.97 mutex, 35.61 RW-shared, 74.05 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 376297009 Purge done for trx's n:o < 376296978 undo n:o < 0 state: running but idle History list length 1446 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 606387, OS thread handle 0x6714, query id 279909474 10.32.5.202 VDCROOT init show engine innodb status ---TRANSACTION 376246720, not started MySQL thread id 606366, OS thread handle 0x6714, query id 279805827 10.35.0.71 tableau_prod cleaning up ---TRANSACTION 376004166, not started MySQL thread id 605268, OS thread handle 0x6714, query id 279302134 10.35.0.56 tableau_prod cleaning up ---TRANSACTION 376295823, not started MySQL thread id 604086, OS thread handle 0x77b8, query id 279907323 10.9.0.8 mdbv2nprd cleaning up ---TRANSACTION 376297004, not started MySQL thread id 588829, OS thread handle 0x6714, query id 279909446 10.9.0.3 mdbv2lega cleaning up ---TRANSACTION 376297008, not started MySQL thread id 598730, OS thread handle 0x6714, query id 279909473 10.34.0.106 mdbv1keps cleaning up ---TRANSACTION 376256599, not started MySQL thread id 593752, OS thread handle 0x77b8, query id 279827022 10.35.0.71 tableau_prod cleaning up ---TRANSACTION 375047815, not started MySQL thread id 592405, OS thread handle 0x6714, query id 277317232 10.34.0.106 mdbv1keps cleaning up ---TRANSACTION 374194022, not started MySQL thread id 566327, OS thread handle 0x77b8, query id 279892359 10.35.0.71 tableau_prod cleaning up ---TRANSACTION 376296343, not started MySQL thread id 513994, OS thread handle 0x77b8, query id 279908660 10.9.0.3 mdbv2lega cleaning up ---TRANSACTION 375736192, not started MySQL thread id 537556, OS thread handle 0x82d0, query id 279742726 10.9.0.8 mdbv2nprd cleaning up ---TRANSACTION 374194020, not started MySQL thread id 536636, OS thread handle 0x6714, query id 279890143 10.35.0.71 tableau_prod cleaning up ---TRANSACTION 374920573, not started MySQL thread id 523694, OS thread handle 0x6714, query id 279859328 10.41.0.22 mdbv1maii cleaning up ---TRANSACTION 375448277, not started MySQL thread id 377612, OS thread handle 0xbca4, query id 278181411 10.9.0.3 mdbv2lega cleaning up ---TRANSACTION 371467598, not started MySQL thread id 12443, OS thread handle 0x6714, query id 276477299 10.35.0.61 mdbv4igns cleaning up ---TRANSACTION 376296862, not started MySQL thread id 11544, OS thread handle 0x77b8, query id 279909194 10.35.0.61 mdbv4igns cleaning up ---TRANSACTION 376295491, not started MySQL thread id 1748, OS thread handle 0x6714, query id 279907116 10.10.0.107 mdbv3keps cleaning up ---TRANSACTION 376296777, not started MySQL thread id 9, OS thread handle 0x77b8, query id 279909096 10.41.0.22 mdbv1maii cleaning up ---TRANSACTION 372056896, not started MySQL thread id 4, OS thread handle 0x7e94, query id 271112244 10.10.0.107 mdbv3keps cleaning up ---TRANSACTION 241684995, not started MySQL thread id 1, OS thread handle 0xc404, query id 0 Waiting for requests ---TRANSACTION 376296985, ACTIVE 0 sec fetching rows mysql tables in use 2, locked 0 MySQL thread id 606600, OS thread handle 0x77b8, query id 279909313 10.35.0.71 tableau_prod Copying to tmp table SELECT `v1_ds1_wireless_sensors_current`.`Building` AS `Building`, `v1_ds1_wireless_sensors_current`.`Data_Module` AS `Data_Module`, `v1_ds1_wireless_sensors_current`.`DewPt` AS `DewPt`, `v1_ds1_wireless_sensors_current`.`DewPt_sensor` AS `DewPt_sensor`, 1 AS `Number of Records`, `v1_ds1_wireless_sensors_current`.`RH` AS `RH`, `v1_ds1_wireless_sensors_current`.`RH_sensor` AS `RH_sensor`, `v1_ds1_wireless_sensors_current`.`Temp` AS `Temp`, `v1_ds1_wireless_sensors_current`.`Temp_sensor` AS `Temp_sensor`, `v1_ds1_wireless_sensors_current`.`time_stamp` AS `time_stamp` FROM `v1_d Trx read view will not see trx with id >= 376296986, sees < 376296986 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: native aio handle (insert buffer thread) I/O thread 1 state: native aio handle (log thread) I/O thread 2 state: native aio handle (read thread) I/O thread 3 state: native aio handle (read thread) I/O thread 4 state: native aio handle (read thread) I/O thread 5 state: native aio handle (read thread) I/O thread 6 state: native aio handle (write thread) I/O thread 7 state: native aio handle (write thread) I/O thread 8 state: native aio handle (write thread) I/O thread 9 state: native aio handle (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 4061429 OS file reads, 211314502 OS file writes, 104049641 OS fsyncs 0.31 reads/s, 945356 avg bytes/read, 466.08 writes/s, 232.49 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 7367, seg size 7369, 1769 merges merged operations: insert 21583, delete mark 1, delete 0 discarded operations: insert 0, delete mark 0, delete 0 76795.76 hash searches/s, 2788.82 non-hash searches/s --- LOG --- Log sequence number 1023099331235 Log flushed up to 1023099330329 Pages flushed up to 1023077552332 Last checkpoint at 1023077003012 Max checkpoint age 84223550 Checkpoint age target 81591565 Modified age 21778903 Checkpoint age 22328223 0 pending log writes, 0 pending chkp writes 129816383 log i/o's done, 286.16 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 117587312640; in additional pool allocated 0 Total memory allocated by read views 478953736 Internal hash tables (constant factor + variable factor) Adaptive hash index 1995189888 (1784978968 + 210210920) Page hash 13946168 (buffer pool 0 only) Dictionary cache 926663887 (892490864 + 34173023) File system 3419632 (812272 + 2607360) Lock system 290411456 (290403896 + 7560) Recovery system 0 (0 + 0) Dictionary memory allocated 34173023 Buffer pool size 6881272 Buffer pool size, bytes 112742760448 Free buffers 8192 Database pages 6860250 Old database pages 2532232 Modified db pages 2880 Percent of dirty pages(LRU & free pages): 0.042 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 545044, not young 226933154 0.03 youngs/s, 0.06 non-youngs/s Pages read 4037466, created 78322852, written 20457501 0.03 reads/s, 175.31 creates/s, 43.50 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: 6860250, unzip_LRU len: 0 I/O sum[16264]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 860159 Buffer pool size, bytes 14092845056 Free buffers 1024 Database pages 857518 Old database pages 316524 Modified db pages 419 Percent of dirty pages(LRU & free pages): 0.049 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 68588, not young 28375534 0.03 youngs/s, 0.00 non-youngs/s Pages read 506174, created 9788061, written 2353432 0.00 reads/s, 22.09 creates/s, 5.87 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: 857518, unzip_LRU len: 0 I/O sum[2033]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 860159 Buffer pool size, bytes 14092845056 Free buffers 1024 Database pages 857551 Old database pages 316536 Modified db pages 273 Percent of dirty pages(LRU & free pages): 0.032 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 65846, not young 28968476 0.00 youngs/s, 0.00 non-youngs/s Pages read 511565, created 9791541, written 2193036 0.00 reads/s, 21.72 creates/s, 4.47 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: 857551, unzip_LRU len: 0 I/O sum[2033]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 860159 Buffer pool size, bytes 14092845056 Free buffers 1024 Database pages 857521 Old database pages 316525 Modified db pages 245 Percent of dirty pages(LRU & free pages): 0.029 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 63885, not young 28213771 0.00 youngs/s, 0.00 non-youngs/s Pages read 494086, created 9787758, written 1879628 0.00 reads/s, 21.66 creates/s, 3.50 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: 857521, unzip_LRU len: 0 I/O sum[2033]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 860159 Buffer pool size, bytes 14092845056 Free buffers 1024 Database pages 857545 Old database pages 316534 Modified db pages 350 Percent of dirty pages(LRU & free pages): 0.041 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 68044, not young 28195670 0.00 youngs/s, 0.00 non-youngs/s Pages read 502039, created 9793618, written 2891567 0.00 reads/s, 21.87 creates/s, 5.56 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: 857545, unzip_LRU len: 0 I/O sum[2033]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 860159 Buffer pool size, bytes 14092845056 Free buffers 1024 Database pages 857557 Old database pages 316539 Modified db pages 683 Percent of dirty pages(LRU & free pages): 0.080 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 72614, not young 28633270 0.00 youngs/s, 0.06 non-youngs/s Pages read 522501, created 9786637, written 3941545 0.03 reads/s, 22.53 creates/s, 7.31 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: 857557, unzip_LRU len: 0 I/O sum[2033]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 860159 Buffer pool size, bytes 14092845056 Free buffers 1024 Database pages 857502 Old database pages 316518 Modified db pages 281 Percent of dirty pages(LRU & free pages): 0.033 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 67809, not young 27629610 0.00 youngs/s, 0.00 non-youngs/s Pages read 489602, created 9790394, written 2701625 0.00 reads/s, 21.47 creates/s, 5.47 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: 857502, unzip_LRU len: 0 I/O sum[2033]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 860159 Buffer pool size, bytes 14092845056 Free buffers 1024 Database pages 857523 Old database pages 316526 Modified db pages 288 Percent of dirty pages(LRU & free pages): 0.034 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 66288, not young 27463650 0.00 youngs/s, 0.00 non-youngs/s Pages read 492047, created 9789349, written 2147003 0.00 reads/s, 22.12 creates/s, 4.91 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: 857523, unzip_LRU len: 0 I/O sum[2033]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 860159 Buffer pool size, bytes 14092845056 Free buffers 1024 Database pages 857533 Old database pages 316530 Modified db pages 341 Percent of dirty pages(LRU & free pages): 0.040 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 71970, not young 29453173 0.00 youngs/s, 0.00 non-youngs/s Pages read 519452, created 9795494, written 2349665 0.00 reads/s, 21.84 creates/s, 6.41 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: 857533, unzip_LRU len: 0 I/O sum[2033]: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 376296986 Read view up limit trx id 376296986 Read view low limit trx id 376296986 Read view individually stored trx ids: ----------------- Main thread id 51600, state: sleeping Number of rows inserted 108150496, updated 7591990, deleted 8137085, read 185549232129 259.40 inserts/s, 25.78 updates/s, 17.72 deletes/s, 306919.66 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ | +--------+------+---------------------------------------------------------------------- 1 row in set (0.06 sec) MariaDB [(none)]> notee