Storage engines

InnoDB

Supports transactions, row-level locking, foreign keys and encryption for tables

[ Variables | Buffer Pool | InnoDB Status ]

=====================================
2025-07-10 03:05:46 0x154 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 41 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 79704 srv_active, 0 srv_shutdown, 229823 srv_idle
srv_master_thread log flush and writes: 309527
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 234936
OS WAIT ARRAY INFO: signal count 207920
RW-shared spins 359719, rounds 1805170, OS waits 47892
RW-excl spins 54216, rounds 486570, OS waits 10991
RW-sx spins 615, rounds 10748, OS waits 247
Spin rounds per wait: 5.02 RW-shared, 8.97 RW-excl, 17.48 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2025-07-09 22:32:25 0x18ac Transaction:
TRANSACTION 4128777, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 7
MySQL thread id 6067795, OS thread handle 6316, query id 33035778 DESKTOP-5IO9KEK 192.168.2.23 root Update
insert into resep_dokter values('202507100017','B000002869','2.0','')
Foreign key constraint fails for table `mesrabaru`.`resep_dokter`:
,
  CONSTRAINT `resep_dokter_ibfk_1` FOREIGN KEY (`no_resep`) REFERENCES `resep_obat` (`no_resep`) ON DELETE CASCADE ON UPDATE CASCADE in parent table, in index no_resep tuple:
DATA TUPLE: 2 fields;
 0: len 12; hex 323032353037313030303137; asc 202507100017;;
 1: len 6; hex 0000015c46f6; asc    \F ;;

But in parent table `mesrabaru`.`resep_obat`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 14; compact format; info bits 0
 0: len 12; hex 323032383031333130303031; asc 202801310001;;
 1: len 6; hex 000000000000; asc       ;;
 2: len 7; hex 80000000000000; asc        ;;
 3: len 3; hex 800000; asc    ;;
 4: len 3; hex 800000; asc    ;;
 5: len 17; hex 323032352f30312f33312f303030333338; asc 2025/01/31/000338;;
 6: len 9; hex 313630353135333935; asc 160515395;;
 7: len 3; hex 8fd83f; asc   ?;;
 8: len 3; hex 800da8; asc    ;;
 9: len 1; hex 02; asc  ;;
 10: len 3; hex 800000; asc    ;;
 11: len 3; hex 800000; asc    ;;
 12: len 0; hex ; asc ;;
 13: len 1; hex 2d; asc -;;

------------
TRANSACTIONS
------------
Trx id counter 4133565
Purge done for trx's n:o < 4133565 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284334009398616, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009394448, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009390280, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009386112, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009356936, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009327760, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009361104, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009344432, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009331928, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009340264, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009381944, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009369440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009365272, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009511152, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009515320, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009523656, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009465304, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009348600, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009336096, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009377776, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009352768, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284334009373608, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
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] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1185040 OS file reads, 1132705 OS file writes, 724381 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.10 writes/s, 0.10 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 434, seg size 436, 27158 merges
merged operations:
 insert 33511, delete mark 12490, delete 10161
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1106407, node heap has 776 buffer(s)
Hash table size 1106407, node heap has 324 buffer(s)
Hash table size 1106407, node heap has 49 buffer(s)
Hash table size 1106407, node heap has 96 buffer(s)
Hash table size 1106407, node heap has 164 buffer(s)
Hash table size 1106407, node heap has 649 buffer(s)
Hash table size 1106407, node heap has 32 buffer(s)
Hash table size 1106407, node heap has 132 buffer(s)
21594.21 hash searches/s, 182.22 non-hash searches/s
---
LOG
---
Log sequence number 12174118468
Log flushed up to   12174118468
Pages flushed up to 12174118468
Last checkpoint at  12174118459
0 pending log flushes, 0 pending chkp writes
222827 log i/o's done, 0.10 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4462739456
Dictionary memory allocated 2511512
Buffer pool size   256864
Free buffers       8192
Database pages     246450
Old database pages 90811
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1027250, not young 105647622
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1184019, created 4592, written 720667
0.00 reads/s, 0.00 creates/s, 0.00 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: 246450, unzip_LRU len: 0
I/O sum[904]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   32108
Free buffers       1024
Database pages     30831
Old database pages 11360
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 129132, not young 12808290
0.00 youngs/s, 0.00 non-youngs/s
Pages read 141568, created 662, written 111432
0.00 reads/s, 0.00 creates/s, 0.00 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: 30831, unzip_LRU len: 0
I/O sum[113]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   32108
Free buffers       1024
Database pages     30766
Old database pages 11336
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 133079, not young 13386233
0.00 youngs/s, 0.00 non-youngs/s
Pages read 151327, created 537, written 70608
0.00 reads/s, 0.00 creates/s, 0.00 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: 30766, unzip_LRU len: 0
I/O sum[113]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   32108
Free buffers       1024
Database pages     30800
Old database pages 11349
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 130347, not young 12720359
0.00 youngs/s, 0.00 non-youngs/s
Pages read 143609, created 465, written 66924
0.00 reads/s, 0.00 creates/s, 0.00 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: 30800, unzip_LRU len: 0
I/O sum[113]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   32108
Free buffers       1024
Database pages     30817
Old database pages 11355
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 130443, not young 12654442
0.00 youngs/s, 0.00 non-youngs/s
Pages read 146286, created 414, written 126837
0.00 reads/s, 0.00 creates/s, 0.00 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: 30817, unzip_LRU len: 0
I/O sum[113]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   32108
Free buffers       1024
Database pages     30791
Old database pages 11346
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 123649, not young 13960784
0.00 youngs/s, 0.00 non-youngs/s
Pages read 156551, created 607, written 119715
0.00 reads/s, 0.00 creates/s, 0.00 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: 30791, unzip_LRU len: 0
I/O sum[113]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   32108
Free buffers       1024
Database pages     30826
Old database pages 11359
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 127800, not young 12516904
0.00 youngs/s, 0.00 non-youngs/s
Pages read 144785, created 532, written 62632
0.00 reads/s, 0.00 creates/s, 0.00 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: 30826, unzip_LRU len: 0
I/O sum[113]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   32108
Free buffers       1024
Database pages     30815
Old database pages 11355
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 126186, not young 13356596
0.00 youngs/s, 0.00 non-youngs/s
Pages read 151216, created 822, written 94366
0.00 reads/s, 0.00 creates/s, 0.00 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: 30815, unzip_LRU len: 0
I/O sum[113]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   32108
Free buffers       1024
Database pages     30804
Old database pages 11351
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 126614, not young 14244014
0.00 youngs/s, 0.00 non-youngs/s
Pages read 148677, created 553, written 68153
0.00 reads/s, 0.00 creates/s, 0.00 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: 30804, unzip_LRU len: 0
I/O sum[113]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=11220, Main thread ID=13620, state: sleeping
Number of rows inserted 209173, updated 14739, deleted 15864, read 48237651368
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 96363.80 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 13063
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================