INNODB STATUS
=====================================
2025-12-24 09:43:55 22849289606912 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 29 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 241941 srv_active, 0 srv_shutdown, 3646593 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 160352
OS WAIT ARRAY INFO: signal count 223044
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-11-25 15:41:12 22849737901824
*** (1) TRANSACTION:
TRANSACTION 132954137, ACTIVE 1 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 632 lock struct(s), heap size 90232, 101215 row lock(s), undo log entries 1
MySQL thread id 2577467, OS thread handle 22851108976384, query id 522432169 172.31.45.113 admin executing
UPDATE tblJobRequests j, (SELECT jobid,
sum(CASE when `Status` in ('Confirmed', 'Accepted') then 1 else 0 end ) as `Found`,
sum(CASE when `Status` ='Cancelled' then 1 else 0 end ) as Cancelled,
sum(CASE when `Status` ='Pulled' then 1 else 0 end ) as Pulled,
sum(CASE when `Status` ='Confirmed' then 1 else 0 end ) as Confirmed
from tblPlacements group by jobid ) p
SET j.NursesFound = p.`Found`,
j.NoConfirmed = p.Confirmed,
j.NoPulled = p.Pulled,
j.NoCancelled = p.Cancelled,
ShiftRate1=0
WHERE j.jobid = p.jobid and j.jobid = 5067748
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 1013 page no 3213 n bits 224 index PRIMARY of table `richmond`.`tblPlacements` trx id 132954137 lock_mode X locks rec but not gap
Record lock, heap no 155 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 88520922; asc R ";;
1: len 6; hex 000007ecb819; asc ;;
2: len 7; hex 020000008911b8; asc ;;
3: len 4; hex 804d53e4; asc MS ;;
4: len 7; hex 50533435383820; asc PS4588 ;;
5: len 1; hex 81; asc ;;
6: len 1; hex 81; asc ;;
7: len 1; hex 80; asc ;;
8: SQL NULL;
9: len 0; hex ; asc ;;
10: SQL NULL;
11: SQL NULL;
12: len 1; hex 80; asc ;;
13: len 1; hex 80; asc ;;
14: len 1; hex 81; asc ;;
15: len 1; hex 80; asc ;;
16: len 9; hex 436f6e6669726d6564; asc Confirmed;;
17: len 10; hex 31313235363735303032; asc 1125675002;;
18: SQL NULL;
19: len 1; hex 81; asc ;;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: SQL NULL;
30: SQL NULL;
31: SQL NULL;
32: SQL NULL;
33: SQL NULL;
34: SQL NULL;
35: SQL NULL;
36: SQL NULL;
37: SQL NULL;
38: SQL NULL;
39: SQL NULL;
40: SQL NULL;
41: SQL NULL;
42: SQL NULL;
43: SQL NULL;
44: SQL NULL;
45: SQL NULL;
46: SQL NULL;
47: SQL NULL;
48: SQL NULL;
49: SQL NULL;
50: SQL NULL;
51: SQL NULL;
52: SQL NULL;
53: SQL NULL;
54: SQL NULL;
55: SQL NULL;
56: SQL NULL;
57: SQL NULL;
58: SQL NULL;
59: SQL NULL;
60: SQL NULL;
61: SQL NULL;
62: SQL NULL;
63: SQL NULL;
64: SQL NULL;
65: SQL NULL;
66: SQL NULL;
67: SQL NULL;
68: SQL NULL;
69: SQL NULL;
70: SQL NULL;
71: SQL NULL;
72: SQL NULL;
73: SQL NULL;
74: SQL NULL;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1013 page no 3225 n bits 280 index PRIMARY of table `richmond`.`tblPlacements` trx id 132954137 lock mode S locks rec but not gap waiting
Record lock, heap no 148 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8851d393; asc Q ;;
1: len 6; hex 000007ecb81e; asc ;;
2: len 7; hex 0100000ac62c14; asc , ;;
3: len 4; hex 804d53a8; asc MS ;;
4: len 6; hex 505336333339; asc PS6339;;
5: len 1; hex 81; asc ;;
6: len 1; hex 81; asc ;;
7: len 1; hex 80; asc ;;
8: SQL NULL;
9: len 20; hex 5072652d617272616e67656420426f6f6b696e67; asc Pre-arranged Booking;;
10: SQL NULL;
11: SQL NULL;
12: len 1; hex 80; asc ;;
13: len 1; hex 81; asc ;;
14: len 1; hex 80; asc ;;
15: len 1; hex 80; asc ;;
16: len 9; hex 43616e63656c6c6564; asc Cancelled;;
17: len 4; hex 30303030; asc 0000;;
18: SQL NULL;
19: len 1; hex 80; asc ;;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: SQL NULL;
30: SQL NULL;
31: SQL NULL;
32: SQL NULL;
33: SQL NULL;
34: SQL NULL;
35: SQL NULL;
36: SQL NULL;
37: SQL NULL;
38: SQL NULL;
39: SQL NULL;
40: SQL NULL;
41: SQL NULL;
42: SQL NULL;
43: SQL NULL;
44: SQL NULL;
45: SQL NULL;
46: SQL NULL;
47: SQL NULL;
48: SQL NULL;
49: SQL NULL;
50: SQL NULL;
51: SQL NULL;
52: SQL NULL;
53: SQL NULL;
54: SQL NULL;
55: SQL NULL;
56: SQL NULL;
57: SQL NULL;
58: SQL NULL;
59: SQL NULL;
60: SQL NULL;
61: SQL NULL;
62: SQL NULL;
63: SQL NULL;
64: SQL NULL;
65: SQL NULL;
66: SQL NULL;
67: SQL NULL;
68: SQL NULL;
69: SQL NULL;
70: SQL NULL;
71: SQL NULL;
72: SQL NULL;
73: SQL NULL;
74: SQL NULL;
*** (2) TRANSACTION:
TRANSACTION 132954142, ACTIVE 1 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 645 lock struct(s), heap size 90232, 105972 row lock(s), undo log entries 1
MySQL thread id 2577488, OS thread handle 22849713575680, query id 522432187 172.31.45.113 admin executing
UPDATE tblJobRequests j, (SELECT jobid,
sum(CASE when `Status` in ('Confirmed', 'Accepted') then 1 else 0 end ) as `Found`,
sum(CASE when `Status` ='Cancelled' then 1 else 0 end ) as Cancelled,
sum(CASE when `Status` ='Pulled' then 1 else 0 end ) as Pulled,
sum(CASE when `Status` ='Confirmed' then 1 else 0 end ) as Confirmed
from tblPlacements group by jobid ) p
SET j.NursesFound = p.`Found`,
j.NoConfirmed = p.Confirmed,
j.NoPulled = p.Pulled,
j.NoCancelled = p.Cancelled,
ShiftRate1=0
WHERE j.jobid = p.jobid and j.jobid = 5067688
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1013 page no 3225 n bits 280 index PRIMARY of table `richmond`.`tblPlacements` trx id 132954142 lock_mode X locks rec but not gap
Record lock, heap no 148 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8851d393; asc Q ;;
1: len 6; hex 000007ecb81e; asc ;;
2: len 7; hex 0100000ac62c14; asc , ;;
3: len 4; hex 804d53a8; asc MS ;;
4: len 6; hex 505336333339; asc PS6339;;
5: len 1; hex 81; asc ;;
6: len 1; hex 81; asc ;;
7: len 1; hex 80; asc ;;
8: SQL NULL;
9: len 20; hex 5072652d617272616e67656420426f6f6b696e67; asc Pre-arranged Booking;;
10: SQL NULL;
11: SQL NULL;
12: len 1; hex 80; asc ;;
13: len 1; hex 81; asc ;;
14: len 1; hex 80; asc ;;
15: len 1; hex 80; asc ;;
16: len 9; hex 43616e63656c6c6564; asc Cancelled;;
17: len 4; hex 30303030; asc 0000;;
18: SQL NULL;
19: len 1; hex 80; asc ;;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: SQL NULL;
30: SQL NULL;
31: SQL NULL;
32: SQL NULL;
33: SQL NULL;
34: SQL NULL;
35: SQL NULL;
36: SQL NULL;
37: SQL NULL;
38: SQL NULL;
39: SQL NULL;
40: SQL NULL;
41: SQL NULL;
42: SQL NULL;
43: SQL NULL;
44: SQL NULL;
45: SQL NULL;
46: SQL NULL;
47: SQL NULL;
48: SQL NULL;
49: SQL NULL;
50: SQL NULL;
51: SQL NULL;
52: SQL NULL;
53: SQL NULL;
54: SQL NULL;
55: SQL NULL;
56: SQL NULL;
57: SQL NULL;
58: SQL NULL;
59: SQL NULL;
60: SQL NULL;
61: SQL NULL;
62: SQL NULL;
63: SQL NULL;
64: SQL NULL;
65: SQL NULL;
66: SQL NULL;
67: SQL NULL;
68: SQL NULL;
69: SQL NULL;
70: SQL NULL;
71: SQL NULL;
72: SQL NULL;
73: SQL NULL;
74: SQL NULL;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1013 page no 3213 n bits 224 index PRIMARY of table `richmond`.`tblPlacements` trx id 132954142 lock mode S locks rec but not gap waiting
Record lock, heap no 155 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 88520922; asc R ";;
1: len 6; hex 000007ecb819; asc ;;
2: len 7; hex 020000008911b8; asc ;;
3: len 4; hex 804d53e4; asc MS ;;
4: len 7; hex 50533435383820; asc PS4588 ;;
5: len 1; hex 81; asc ;;
6: len 1; hex 81; asc ;;
7: len 1; hex 80; asc ;;
8: SQL NULL;
9: len 0; hex ; asc ;;
10: SQL NULL;
11: SQL NULL;
12: len 1; hex 80; asc ;;
13: len 1; hex 80; asc ;;
14: len 1; hex 81; asc ;;
15: len 1; hex 80; asc ;;
16: len 9; hex 436f6e6669726d6564; asc Confirmed;;
17: len 10; hex 31313235363735303032; asc 1125675002;;
18: SQL NULL;
19: len 1; hex 81; asc ;;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: SQL NULL;
30: SQL NULL;
31: SQL NULL;
32: SQL NULL;
33: SQL NULL;
34: SQL NULL;
35: SQL NULL;
36: SQL NULL;
37: SQL NULL;
38: SQL NULL;
39: SQL NULL;
40: SQL NULL;
41: SQL NULL;
42: SQL NULL;
43: SQL NULL;
44: SQL NULL;
45: SQL NULL;
46: SQL NULL;
47: SQL NULL;
48: SQL NULL;
49: SQL NULL;
50: SQL NULL;
51: SQL NULL;
52: SQL NULL;
53: SQL NULL;
54: SQL NULL;
55: SQL NULL;
56: SQL NULL;
57: SQL NULL;
58: SQL NULL;
59: SQL NULL;
60: SQL NULL;
61: SQL NULL;
62: SQL NULL;
63: SQL NULL;
64: SQL NULL;
65: SQL NULL;
66: SQL NULL;
67: SQL NULL;
68: SQL NULL;
69: SQL NULL;
70: SQL NULL;
71: SQL NULL;
72: SQL NULL;
73: SQL NULL;
74: SQL NULL;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 135572784
Purge done for trx's n:o < 135572782 undo n:o < 0 state: running but idle
History list length 10
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 304326090169880, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304326090170688, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304326090169072, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304326090168264, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304326090167456, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304326090166648, 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 (read 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 (write 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)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:
Pending flushes (fsync) log: 0; buffer pool: 0
261550 OS file reads, 11849553 OS file writes, 6619759 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 3.33 writes/s, 2.31 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 12099, seg size 12101, 6106 merges
merged operations:
insert 8810, delete mark 3894, delete 2593
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 1639 buffer(s)
Hash table size 276707, node heap has 265 buffer(s)
Hash table size 276707, node heap has 102 buffer(s)
Hash table size 276707, node heap has 188 buffer(s)
Hash table size 276707, node heap has 36 buffer(s)
Hash table size 276707, node heap has 53 buffer(s)
Hash table size 276707, node heap has 245 buffer(s)
Hash table size 276707, node heap has 164 buffer(s)
3416.12 hash searches/s, 40.55 non-hash searches/s
---
LOG
---
Log sequence number 236729115355
Log buffer assigned up to 236729115355
Log buffer completed up to 236729115355
Log written up to 236729115355
Log flushed up to 236729115355
Added dirty pages up to 236729115355
Pages flushed up to 236729115355
Last checkpoint at 236729115355
Log minimum file id is 1594
Log maximum file id is 1614
6032564 log i/o's done, 0.93 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2420861
Buffer pool size 65531
Free buffers 8204
Database pages 54635
Old database pages 20004
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2856906, not young 5585502
1.94 youngs/s, 0.00 non-youngs/s
Pages read 260693, created 257509, written 4117332
0.00 reads/s, 0.03 creates/s, 1.60 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: 54635, unzip_LRU len: 0
I/O sum[584]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 8191
Free buffers 1025
Database pages 6825
Old database pages 2499
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 406584, not young 566150
0.07 youngs/s, 0.00 non-youngs/s
Pages read 31755, created 39094, written 839953
0.00 reads/s, 0.00 creates/s, 0.17 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: 6825, unzip_LRU len: 0
I/O sum[73]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 8192
Free buffers 1024
Database pages 6834
Old database pages 2502
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 330081, not young 932275
0.00 youngs/s, 0.00 non-youngs/s
Pages read 34694, created 22883, written 610294
0.00 reads/s, 0.00 creates/s, 0.34 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: 6834, unzip_LRU len: 0
I/O sum[73]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 8191
Free buffers 1025
Database pages 6822
Old database pages 2498
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 464108, not young 395942
0.61 youngs/s, 0.00 non-youngs/s
Pages read 35270, created 36781, written 310954
0.00 reads/s, 0.00 creates/s, 0.14 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: 6822, unzip_LRU len: 0
I/O sum[73]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 8191
Free buffers 1027
Database pages 6834
Old database pages 2502
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 219537, not young 384784
0.00 youngs/s, 0.00 non-youngs/s
Pages read 30099, created 20544, written 216269
0.00 reads/s, 0.00 creates/s, 0.10 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: 6834, unzip_LRU len: 0
I/O sum[73]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 8192
Free buffers 1027
Database pages 6839
Old database pages 2504
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 313653, not young 1047294
0.00 youngs/s, 0.00 non-youngs/s
Pages read 33056, created 36839, written 427959
0.00 reads/s, 0.00 creates/s, 0.20 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: 6839, unzip_LRU len: 0
I/O sum[73]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 8191
Free buffers 1026
Database pages 6825
Old database pages 2499
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 347154, not young 783626
0.00 youngs/s, 0.00 non-youngs/s
Pages read 32421, created 22966, written 601693
0.00 reads/s, 0.03 creates/s, 0.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: 6825, unzip_LRU len: 0
I/O sum[73]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 8191
Free buffers 1026
Database pages 6821
Old database pages 2497
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 452034, not young 774517
1.12 youngs/s, 0.00 non-youngs/s
Pages read 30792, created 56077, written 335515
0.00 reads/s, 0.00 creates/s, 0.10 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: 6821, unzip_LRU len: 0
I/O sum[73]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 8192
Free buffers 1024
Database pages 6835
Old database pages 2503
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 323755, not young 700914
0.14 youngs/s, 0.00 non-youngs/s
Pages read 32606, created 22325, written 774695
0.00 reads/s, 0.00 creates/s, 0.24 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: 6835, unzip_LRU len: 0
I/O sum[73]: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=978, Main thread ID=22849720932096 , state=sleeping
Number of rows inserted 3173695, updated 796415, deleted 1252333, read 614840091695
0.14 inserts/s, 0.07 updates/s, 0.00 deletes/s, 175426.64 reads/s
Number of system rows inserted 286536, updated 153113, deleted 225714, read 40227891
0.00 inserts/s, 0.03 updates/s, 0.00 deletes/s, 50.21 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Process List
|
5 |
event_scheduler |
localhost |
|
Daemon |
3889684 |
Waiting on empty queue |
|
|
2195422 |
rdsadmin |
localhost |
mysql |
Sleep |
11 |
|
|
|
3197667 |
admin |
172.31.45.113:56592 |
richmond |
Sleep |
428 |
|
|
|
4267280 |
admin |
172.31.45.113:45980 |
richmond |
Sleep |
428 |
|
|
|
7135429 |
admin |
172.31.45.113:41438 |
richmond |
Query |
0 |
init |
show full processlist |
|
7135430 |
admin |
172.31.45.113:41446 |
richmond_registrations |
Sleep |
0 |
|
|
|
7135431 |
admin |
172.31.45.113:41460 |
data_warehouse |
Sleep |
0 |
|
|
Running Transactions