===================================== 2020-12-13 17:20:14 0x7fef08258700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 6 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1104 srv_active, 0 srv_shutdown, 6 srv_idle srv_master_thread log flush and writes: 1110 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 62775 --Thread 140664614188800 has waited at btr0cur.cc line 1492 for 377.00 seconds the semaphore: SX-lock on RW-latch at 0x7fdf3812ac18 created in file dict0dict.cc line 1954 a writer (thread id 140599973009152) has reserved it in mode SX number of readers 0, waiters flag 1, lock_word: 10000000 Last time write locked in file dict0stats.cc line 1968 OS WAIT ARRAY INFO: signal count 125808 RW-shared spins 324450, rounds 1883888, OS waits 22222 RW-excl spins 1773384, rounds 4891849, OS waits 18955 RW-sx spins 12945, rounds 137731, OS waits 2704 Spin rounds per wait: 5.81 RW-shared, 2.76 RW-excl, 10.64 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 54381375766 Purge done for trx's n:o < 54381375765 undo n:o < 0 state: running but idle History list length 33 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422139614957912, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422139614970680, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 54381375765, ACTIVE (PREPARED) 0 sec flushing log 2 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 2 MySQL thread id 12, OS thread handle 140664613574400, query id 634454 Closing tables ---TRANSACTION 54381212057, ACTIVE 402 sec inserting mysql tables in use 14, locked 14 9101 lock struct(s), heap size 991352, 168443 row lock(s), undo log entries 176017 MySQL thread id 10, OS thread handle 140664614188800, query id 479417 Write_rows_log_event::write_row(-1) INSERT INTO `agg_customer_activity_per_pin_and_direction` ( `date`, `company_id`, `pin`, `direction`, `voice_calls`, `voice_minutes`, `voice_inbound_minutes`, `voice_answered_calls`, `voice_short_calls`, `voice_noanswered_calls`, `voice_failed_calls`, `voice_prompt_cancel_calls`, `voice_blocked_calls`, `voice_rop_calls`, `voice_rated_calls`, `voice_pinless_calls`, `voice_speeddial_calls`, `voice_autorecharge_calls`, `voice_in_buy_cost`, `voice_in_sell_cost`, `voice_out_buy_cost`, `voice_out_sell_cost`) SELECT a.`date` AS `date`, a.`company_id` AS `company_id`, a.`pin` AS `pin`, a. ---TRANSACTION 422139614953656, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- 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: 1; buffer pool: 0 419302 OS file reads, 1306706 OS file writes, 442880 OS fsyncs 1 pending reads, 0 pending writes 792.53 reads/s, 8431 avg bytes/read, 451.92 writes/s, 308.12 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 14398, free list len 1138157, seg size 1152556, 44832 merges merged operations: insert 130852, delete mark 1865998, delete 257004 discarded operations: insert 190, delete mark 190, delete 21 0.00 hash searches/s, 3091.32 non-hash searches/s --- LOG --- Log sequence number 108919208028004 Log flushed up to 108919208026953 Pages flushed up to 108919159874778 Last checkpoint at 108919159434950 1 pending log flushes, 0 pending chkp writes 314268 log i/o's done, 215.17 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 64441286656 Dictionary memory allocated 479952 Buffer pool size 3852780 Free buffers 3191891 Database pages 476133 Old database pages 175914 Modified db pages 21799 Percent of dirty pages(LRU & free pages): 0.594 Max dirty pages percent: 75.000 Pending reads 1 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 81479, not young 2633216 27.16 youngs/s, 716.71 non-youngs/s Pages read 418035, created 58097, written 959184 792.53 reads/s, 8.83 creates/s, 213.63 writes/s Buffer pool hit rate 950 / 1000, young-making rate 1 / 1000 not 45 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 476133, unzip_LRU len: 369494 I/O sum[1520128]:cur[102744], unzip sum[1336904]:cur[78032] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 481620 Free buffers 391273 Database pages 67242 Old database pages 24841 Modified db pages 3261 Percent of dirty pages(LRU & free pages): 0.711 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 11021, not young 267325 4.33 youngs/s, 98.32 non-youngs/s Pages read 52423, created 14819, written 135407 97.82 reads/s, 5.17 creates/s, 27.50 writes/s Buffer pool hit rate 974 / 1000, young-making rate 1 / 1000 not 25 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 67242, unzip_LRU len: 46214 I/O sum[190016]:cur[12843], unzip sum[167113]:cur[9754] ---BUFFER POOL 1 Buffer pool size 481620 Free buffers 401100 Database pages 57709 Old database pages 21322 Modified db pages 2520 Percent of dirty pages(LRU & free pages): 0.549 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 5305, not young 106688 3.00 youngs/s, 106.48 non-youngs/s Pages read 51832, created 5877, written 106728 104.98 reads/s, 0.33 creates/s, 27.00 writes/s Buffer pool hit rate 898 / 1000, young-making rate 2 / 1000 not 102 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 57709, unzip_LRU len: 45615 I/O sum[190016]:cur[12843], unzip sum[167113]:cur[9754] ---BUFFER POOL 2 Buffer pool size 481620 Free buffers 399014 Database pages 59172 Old database pages 21862 Modified db pages 3008 Percent of dirty pages(LRU & free pages): 0.657 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 17218, not young 509781 4.83 youngs/s, 97.48 non-youngs/s Pages read 53073, created 6099, written 127129 98.82 reads/s, 0.17 creates/s, 25.83 writes/s Buffer pool hit rate 955 / 1000, young-making rate 2 / 1000 not 43 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 59172, unzip_LRU len: 46883 I/O sum[190016]:cur[12843], unzip sum[167113]:cur[9754] ---BUFFER POOL 3 Buffer pool size 481560 Free buffers 399289 Database pages 58907 Old database pages 21764 Modified db pages 2451 Percent of dirty pages(LRU & free pages): 0.535 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 16303, not young 587093 3.50 youngs/s, 122.65 non-youngs/s Pages read 52533, created 6374, written 123822 122.98 reads/s, 0.33 creates/s, 25.33 writes/s Buffer pool hit rate 893 / 1000, young-making rate 3 / 1000 not 106 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 58907, unzip_LRU len: 46726 I/O sum[190016]:cur[12843], unzip sum[167113]:cur[9754] ---BUFFER POOL 4 Buffer pool size 481620 Free buffers 399848 Database pages 58625 Old database pages 21660 Modified db pages 2601 Percent of dirty pages(LRU & free pages): 0.567 Max dirty pages percent: 75.000 Pending reads 1 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 12879, not young 478973 3.67 youngs/s, 91.82 non-youngs/s Pages read 52418, created 6206, written 125968 89.49 reads/s, 0.00 creates/s, 26.50 writes/s Buffer pool hit rate 948 / 1000, young-making rate 2 / 1000 not 53 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 58625, unzip_LRU len: 46290 I/O sum[190016]:cur[12843], unzip sum[167113]:cur[9754] ---BUFFER POOL 5 Buffer pool size 481560 Free buffers 402305 Database pages 57008 Old database pages 21063 Modified db pages 2706 Percent of dirty pages(LRU & free pages): 0.589 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 10403, not young 498683 3.67 youngs/s, 70.49 non-youngs/s Pages read 50817, created 6191, written 117942 68.82 reads/s, 1.00 creates/s, 26.50 writes/s Buffer pool hit rate 969 / 1000, young-making rate 1 / 1000 not 31 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 57008, unzip_LRU len: 44492 I/O sum[190016]:cur[12843], unzip sum[167113]:cur[9754] ---BUFFER POOL 6 Buffer pool size 481620 Free buffers 400414 Database pages 58150 Old database pages 21485 Modified db pages 2542 Percent of dirty pages(LRU & free pages): 0.554 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1263, not young 0 0.67 youngs/s, 0.00 non-youngs/s Pages read 51920, created 6230, written 108646 77.82 reads/s, 0.83 creates/s, 27.33 writes/s Buffer pool hit rate 911 / 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: 58150, unzip_LRU len: 46095 I/O sum[190016]:cur[12843], unzip sum[167113]:cur[9754] ---BUFFER POOL 7 Buffer pool size 481560 Free buffers 398648 Database pages 59320 Old database pages 21917 Modified db pages 2710 Percent of dirty pages(LRU & free pages): 0.592 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 7087, not young 184673 3.50 youngs/s, 129.48 non-youngs/s Pages read 53019, created 6301, written 113542 131.81 reads/s, 1.00 creates/s, 27.66 writes/s Buffer pool hit rate 952 / 1000, young-making rate 1 / 1000 not 46 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 59320, unzip_LRU len: 47179 I/O sum[190016]:cur[12843], unzip sum[167113]:cur[9754] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=10054, Main thread ID=140599956223744, state: sleeping Number of rows inserted 1568329, updated 5022646, deleted 1401121, read 6539130 64.82 inserts/s, 115.81 updates/s, 0.17 deletes/s, 150.31 reads/s Number of system rows inserted 304447, updated 0, deleted 304443, read 304486 210.96 inserts/s, 0.00 updates/s, 213.30 deletes/s, 213.30 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================