INNODB STATUS
=====================================
2025-10-26 23:48:43 22770608953088 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1080250 srv_active, 0 srv_shutdown, 15297369 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 679521
OS WAIT ARRAY INFO: signal count 1058637
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-09-29 12:32:23 22770906212096
*** (1) TRANSACTION:
TRANSACTION 127656794, ACTIVE 1 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 653 lock struct(s), heap size 90232, 107245 row lock(s), undo log entries 1
MySQL thread id 24149599, OS thread handle 22772275709696, query id 5969421769 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 = 5051938
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 1013 page no 176 n bits 312 index PRIMARY of table `richmond`.`tblPlacements` trx id 127656794 lock_mode X locks rec but not gap
Record lock, heap no 158 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8848efe1; asc H ;;
1: len 6; hex 0000079be35a; asc Z;;
2: len 7; hex 01000011130773; asc s;;
3: len 4; hex 804d1622; asc M ";;
4: len 6; hex 505334353035; asc PS4505;;
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;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1013 page no 184 n bits 312 index PRIMARY of table `richmond`.`tblPlacements` trx id 127656794 lock mode S locks rec but not gap waiting
Record lock, heap no 246 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8848fa60; asc H `;;
1: len 6; hex 0000079be35d; asc ];;
2: len 7; hex 0200000f921d35; asc 5;;
3: len 4; hex 804d1666; asc M f;;
4: len 6; hex 505331323234; asc PS1224;;
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: len 4; hex 80000001; asc ;;
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: SQL NULL;
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;
*** (2) TRANSACTION:
TRANSACTION 127656797, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 650 lock struct(s), heap size 90232, 105091 row lock(s), undo log entries 1
MySQL thread id 24149586, OS thread handle 22770882144000, query id 5969421781 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 = 5052006
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1013 page no 184 n bits 312 index PRIMARY of table `richmond`.`tblPlacements` trx id 127656797 lock_mode X locks rec but not gap
Record lock, heap no 246 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8848fa60; asc H `;;
1: len 6; hex 0000079be35d; asc ];;
2: len 7; hex 0200000f921d35; asc 5;;
3: len 4; hex 804d1666; asc M f;;
4: len 6; hex 505331323234; asc PS1224;;
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: len 4; hex 80000001; asc ;;
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: SQL NULL;
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;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1013 page no 176 n bits 312 index PRIMARY of table `richmond`.`tblPlacements` trx id 127656797 lock mode S locks rec but not gap waiting
Record lock, heap no 158 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8848efe1; asc H ;;
1: len 6; hex 0000079be35a; asc Z;;
2: len 7; hex 01000011130773; asc s;;
3: len 4; hex 804d1622; asc M ";;
4: len 6; hex 505334353035; asc PS4505;;
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;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 130274414
Purge done for trx's n:o < 130274414 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 304247258224024, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304247258223216, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304247258222408, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304247258221600, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304247258220792, 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
865892 OS file reads, 55153798 OS file writes, 30177602 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 12099, seg size 12101, 25878 merges
merged operations:
insert 35691, delete mark 11520, delete 6447
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 1536 buffer(s)
Hash table size 276707, node heap has 277 buffer(s)
Hash table size 276707, node heap has 116 buffer(s)
Hash table size 276707, node heap has 207 buffer(s)
Hash table size 276707, node heap has 51 buffer(s)
Hash table size 276707, node heap has 82 buffer(s)
Hash table size 276707, node heap has 259 buffer(s)
Hash table size 276707, node heap has 170 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 232209195025
Log buffer assigned up to 232209195025
Log buffer completed up to 232209195025
Log written up to 232209195025
Log flushed up to 232209195025
Added dirty pages up to 232209195025
Pages flushed up to 232209195025
Last checkpoint at 232209195025
Log minimum file id is 1532
Log maximum file id is 1547
27638195 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 3175138
Buffer pool size 65530
Free buffers 8200
Database pages 54632
Old database pages 20003
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11097998, not young 19815990
0.00 youngs/s, 0.00 non-youngs/s
Pages read 865335, created 1075676, written 19632917
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 54632, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 8191
Free buffers 1025
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 1474438, not young 2590007
0.00 youngs/s, 0.00 non-youngs/s
Pages read 110472, created 163860, written 3841908
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
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[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 8192
Free buffers 1024
Database pages 6831
Old database pages 2501
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1222609, not young 3088920
0.00 youngs/s, 0.00 non-youngs/s
Pages read 112713, created 97414, written 2872810
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6831, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 8191
Free buffers 1025
Database pages 6829
Old database pages 2500
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1986190, not young 1511382
0.00 youngs/s, 0.00 non-youngs/s
Pages read 115394, created 155176, written 1389559
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6829, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 8191
Free buffers 1025
Database pages 6831
Old database pages 2501
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 961119, not young 1854526
0.00 youngs/s, 0.00 non-youngs/s
Pages read 102588, created 90634, written 1277308
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6831, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 8191
Free buffers 1025
Database pages 6833
Old database pages 2502
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1261521, not young 4033061
0.00 youngs/s, 0.00 non-youngs/s
Pages read 111423, created 155133, written 2210097
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6833, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 8192
Free buffers 1025
Database pages 6831
Old database pages 2501
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1371042, not young 2651801
0.00 youngs/s, 0.00 non-youngs/s
Pages read 98787, created 99211, written 2783108
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6831, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 8191
Free buffers 1025
Database pages 6828
Old database pages 2501
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1684365, not young 2318377
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108588, created 220482, written 1657841
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6828, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 8191
Free buffers 1026
Database pages 6828
Old database pages 2500
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1136714, not young 1767916
0.00 youngs/s, 0.00 non-youngs/s
Pages read 105370, created 93766, written 3600286
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6828, unzip_LRU len: 0
I/O sum[0]: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=878, Main thread ID=22770888988416 , state=sleeping
Number of rows inserted 15734462, updated 8988875, deleted 8573423, read 2271629824517
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 1203786, updated 641062, deleted 948066, read 173159346
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Process List
|
5 |
event_scheduler |
localhost |
|
Daemon |
16381784 |
Waiting on empty queue |
|
|
27389197 |
rdsadmin |
localhost |
mysql |
Sleep |
10 |
|
|
|
28666850 |
admin |
172.31.45.113:50192 |
richmond |
Sleep |
280 |
|
|
|
28713929 |
admin |
172.31.45.113:41566 |
richmond |
Query |
0 |
init |
show full processlist |
|
28713930 |
admin |
172.31.45.113:41574 |
richmond_registrations |
Sleep |
0 |
|
|
|
28713931 |
admin |
172.31.45.113:41576 |
data_warehouse |
Sleep |
0 |
|
|
Running Transactions