===================================== 2018-12-08 05:10:15 0x7f4c061f2700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 26 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 27349 srv_active, 0 srv_shutdown, 41 srv_idle srv_master_thread log flush and writes: 27390 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 32325 --Thread 139965939672832 has waited at row0ins.cc line 2953 for 256.00 seconds the semaphore: S-lock on RW-latch at 0x7f4bbc1a9018 created in file dict0dict.cc line 2751 a writer (thread id 139965938075392) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: ffffffffdffffffe Last time read locked in file row0ins.cc line 2953 Last time write locked in file /mnt/workspace/percona-server-5.7-redhat-binary-rocks-new/label_exp/min-centos-6-x64/test/rpmbuild/BUILD/percona-server-5.7.24-26/percona-server-5.7.24-26/storage/innobase/btr/btr0bulk.cc line 53 OS WAIT ARRAY INFO: signal count 63932 RW-shared spins 0, rounds 133592, OS waits 23111 RW-excl spins 0, rounds 191153, OS waits 1431 RW-sx spins 29392, rounds 163477, OS waits 980 Spin rounds per wait: 133592.00 RW-shared, 191153.00 RW-excl, 5.56 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 3532727569 Purge done for trx's n:o < 3531756064 undo n:o < 0 state: running but idle History list length 485741 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421499138297192, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421499138300576, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421499138294936, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 3532727568, ACTIVE 256 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1 MySQL thread id 9, OS thread handle 139965939672832, query id 3286350 System lock ---TRANSACTION 3531756059, ACTIVE 19534 sec mysql tables in use 1, locked 1 0 lock struct(s), heap size 1136, 0 row lock(s) MySQL thread id 10712, OS thread handle 139965938075392, query id 1865599 localhost root altering table ALTER TABLE notifications DROP INDEX idempotency_id_created_at Trx read view will not see trx with id >= 3531756063, sees < 3531756060 -------- 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] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 1 27381159 OS file reads, 4169341 OS file writes, 751449 OS fsyncs 1 pending preads, 0 pending pwrites 86.30 reads/s, 129666 avg bytes/read, 77.42 writes/s, 35.31 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 28404, free list len 115329, seg size 143734, 367973 merges merged operations: insert 1300858, delete mark 172139, delete 21317 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 13545173, node heap has 2 buffer(s) Hash table size 13545173, node heap has 1 buffer(s) Hash table size 13545173, node heap has 11 buffer(s) Hash table size 13545173, node heap has 19 buffer(s) Hash table size 13545173, node heap has 2 buffer(s) Hash table size 13545173, node heap has 2 buffer(s) Hash table size 13545173, node heap has 1 buffer(s) Hash table size 13545173, node heap has 1 buffer(s) 3.00 hash searches/s, 31.96 non-hash searches/s --- LOG --- Log sequence number 8006974909270 Log flushed up to 8006974871927 Pages flushed up to 8006456972511 Last checkpoint at 8006456972511 Max checkpoint age 3476513711 Checkpoint age target 3367872658 Modified age 517936759 Checkpoint age 517936759 0 pending log flushes, 0 pending chkp writes 1364604 log i/o's done, 1.42 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 57013174272 Dictionary memory allocated 454875 Internal hash tables (constant factor + variable factor) Adaptive hash index 867563712 (866891072 + 672640) Page hash 6773384 (buffer pool 0 only) Dictionary cache 217177643 (216722768 + 454875) File system 849904 (812272 + 37632) Lock system 150800136 (150794456 + 5680) Recovery system 0 (0 + 0) Buffer pool size 3341928 Buffer pool size, bytes 54754148352 Free buffers 7415 Database pages 5527901 Old database pages 2040417 Modified db pages 476310 Pending reads 1 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 257564, not young 15051470 0.08 youngs/s, 0.23 non-youngs/s Pages read 27150019, created 383256, written 2204023 76.23 reads/s, 1253.57 creates/s, 49.54 writes/s Buffer pool hit rate 992 / 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: 5527901, unzip_LRU len: 553454 I/O sum[19816]:cur[5488], unzip sum[4272]:cur[1264] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 417741 Buffer pool size, bytes 6844268544 Free buffers 986 Database pages 690947 Old database pages 255036 Modified db pages 58964 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 30664, not young 1893766 0.00 youngs/s, 0.00 non-youngs/s Pages read 3418670, created 47833, written 273928 13.08 reads/s, 157.46 creates/s, 6.38 writes/s Buffer pool hit rate 894 / 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: 690947, unzip_LRU len: 69139 I/O sum[2477]:cur[686], unzip sum[534]:cur[158] ---BUFFER POOL 1 Buffer pool size 417741 Buffer pool size, bytes 6844268544 Free buffers 938 Database pages 690973 Old database pages 255046 Modified db pages 58928 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 33844, not young 1867934 0.04 youngs/s, 0.00 non-youngs/s Pages read 3409160, created 48277, written 273702 9.65 reads/s, 157.88 creates/s, 6.00 writes/s Buffer pool hit rate 373 / 1000, young-making rate 2 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 690973, unzip_LRU len: 69175 I/O sum[2477]:cur[686], unzip sum[534]:cur[158] ---BUFFER POOL 2 Buffer pool size 417741 Buffer pool size, bytes 6844268544 Free buffers 929 Database pages 690901 Old database pages 255019 Modified db pages 58712 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 30720, not young 1866235 0.00 youngs/s, 0.00 non-youngs/s Pages read 3415352, created 47379, written 268605 10.77 reads/s, 157.88 creates/s, 5.50 writes/s Buffer pool hit rate 589 / 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: 690901, unzip_LRU len: 69173 I/O sum[2477]:cur[686], unzip sum[534]:cur[158] ---BUFFER POOL 3 Buffer pool size 417741 Buffer pool size, bytes 6844268544 Free buffers 1028 Database pages 690825 Old database pages 254991 Modified db pages 64985 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 34572, not young 1830731 0.00 youngs/s, 0.00 non-youngs/s Pages read 3238919, created 45508, written 294772 6.31 reads/s, 148.42 creates/s, 5.62 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: 690825, unzip_LRU len: 69105 I/O sum[2477]:cur[686], unzip sum[534]:cur[158] ---BUFFER POOL 4 Buffer pool size 417741 Buffer pool size, bytes 6844268544 Free buffers 935 Database pages 690942 Old database pages 255036 Modified db pages 58949 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 31652, not young 1907048 0.00 youngs/s, 0.00 non-youngs/s Pages read 3408842, created 48067, written 272845 7.15 reads/s, 157.92 creates/s, 6.65 writes/s Buffer pool hit rate 745 / 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: 690942, unzip_LRU len: 69174 I/O sum[2477]:cur[686], unzip sum[534]:cur[158] ---BUFFER POOL 5 Buffer pool size 417741 Buffer pool size, bytes 6844268544 Free buffers 934 Database pages 691030 Old database pages 255073 Modified db pages 58817 Pending reads 1 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 31669, not young 1893528 0.00 youngs/s, 0.23 non-youngs/s Pages read 3412956, created 48627, written 272675 9.19 reads/s, 157.92 creates/s, 5.65 writes/s Buffer pool hit rate 738 / 1000, young-making rate 0 / 1000 not 6 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 691030, unzip_LRU len: 69180 I/O sum[2477]:cur[686], unzip sum[534]:cur[158] ---BUFFER POOL 6 Buffer pool size 417741 Buffer pool size, bytes 6844268544 Free buffers 822 Database pages 691184 Old database pages 255124 Modified db pages 58530 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 32109, not young 1873765 0.00 youngs/s, 0.00 non-youngs/s Pages read 3410188, created 48360, written 274011 12.04 reads/s, 157.84 creates/s, 6.65 writes/s Buffer pool hit rate 870 / 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: 691184, unzip_LRU len: 69269 I/O sum[2477]:cur[686], unzip sum[534]:cur[158] ---BUFFER POOL 7 Buffer pool size 417741 Buffer pool size, bytes 6844268544 Free buffers 843 Database pages 691099 Old database pages 255092 Modified db pages 58425 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 32334, not young 1918463 0.04 youngs/s, 0.00 non-youngs/s Pages read 3435932, created 49205, written 273485 8.04 reads/s, 158.26 creates/s, 7.08 writes/s Buffer pool hit rate 659 / 1000, young-making rate 1 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 691099, unzip_LRU len: 69239 I/O sum[2477]:cur[686], unzip sum[534]:cur[158] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB 2 RW transactions active inside InnoDB ---OLDEST VIEW--- Read view low limit trx n:o 3531756063 Trx read view will not see trx with id >= 3531756063, sees < 3531756060 Read view individually stored trx ids: Read view trx id 3531756060 ----------------- Process ID=7505, Main thread ID=139966093866752, state: sleeping Number of rows inserted 1334954, updated 1397384, deleted 287558, read 1770616 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 3.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================