INNODB STATUS
=====================================
2025-07-04 03:15:31 22770734302976 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 26 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 428573 srv_active, 0 srv_shutdown, 6028266 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 263113
OS WAIT ARRAY INFO: signal count 379598
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-06-24 09:55:54 22770906212096
*** (1) TRANSACTION:
TRANSACTION 117955465, ACTIVE 1 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 737 lock struct(s), heap size 106616, 112813 row lock(s), undo log entries 1
MySQL thread id 9775474, OS thread handle 22770797995776, query id 2332918704 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 = 5008160
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 1013 page no 581 n bits 160 index PRIMARY of table `richmond`.`tblPlacements` trx id 117955465 lock_mode X locks rec but not gap
Record lock, heap no 55 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 88329ebc; asc 2 ;;
1: len 6; hex 00000707db89; asc ;;
2: len 7; hex 010000166b12c8; asc k ;;
3: len 4; hex 804c6b20; asc Lk ;;
4: len 6; hex 505336353633; asc PS6563;;
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 80; asc ;;
14: len 1; hex 80; asc ;;
15: len 1; hex 81; asc ;;
16: len 6; hex 50756c6c6564; asc Pulled;;
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 4144 n bits 688 index job_idx of table `richmond`.`tblPlacements` trx id 117955465 lock mode S waiting
Record lock, heap no 614 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 804c9016; asc L ;;
1: len 4; hex 8837bcea; asc 7 ;;
*** (2) TRANSACTION:
TRANSACTION 117955466, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 701 lock struct(s), heap size 90232, 102334 row lock(s), undo log entries 2
MySQL thread id 9775488, OS thread handle 22772366063360, query id 2332918710 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,
j.`Notes` = CONCAT(
'Accepted by ',
CASE
WHEN LongDay > 0 THEN ' (L/D):'
ELSE ':'
END,
DATE_FORMAT(NOW(), '%d-%m-%y %H:%i'),
' - mbowen_rna',
'
',
COALESCE(j.`Notes`, '')
),
`Status` =
CASE
WHEN NoGivenBack > 0
AND NoGivenBack + NoCancelled + NoCancelled2 = NursesNeeded
THEN 'Given Back'
WHEN NoCancelled + NoCancelled2 = NursesNeeded
THEN 'Cancelled'
WHEN NursesNeeded - (NursesFound + NoGivenBack + NoCancelled + NoCancelled2) > 0
THEN 'Allocating'
WHEN NursesNeeded = NursesFound + NoGivenBack + NoCancelled + NoCancelled2
AND NoConfirmed <> NursesFound
THEN 'Allocated'
WHEN NoConfirmed + NoGivenBack + NoCancelled + NoCancelled2 = NursesNeeded
AND NoConfirmed = NursesFound
THEN 'Confirmed'
ELSE 'Error'
END
WHERE j.jobid = p.jobid and j.jobid = '5017622'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1013 page no 4144 n bits 688 index job_idx of table `richmond`.`tblPlacements` trx id 117955466 lock_mode X locks rec but not gap
Record lock, heap no 614 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 804c9016; asc L ;;
1: len 4; hex 8837bcea; asc 7 ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1013 page no 581 n bits 160 index PRIMARY of table `richmond`.`tblPlacements` trx id 117955466 lock mode S locks rec but not gap waiting
Record lock, heap no 55 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 88329ebc; asc 2 ;;
1: len 6; hex 00000707db89; asc ;;
2: len 7; hex 010000166b12c8; asc k ;;
3: len 4; hex 804c6b20; asc Lk ;;
4: len 6; hex 505336353633; asc PS6563;;
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 80; asc ;;
14: len 1; hex 80; asc ;;
15: len 1; hex 81; asc ;;
16: len 6; hex 50756c6c6564; asc Pulled;;
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 118968030
Purge done for trx's n:o < 118968028 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 304247258222408, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304247258224024, 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
417157 OS file reads, 21472347 OS file writes, 11773740 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.87 writes/s, 0.50 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 12099, seg size 12101, 13206 merges
merged operations:
insert 18944, delete mark 6157, delete 3869
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 1715 buffer(s)
Hash table size 276707, node heap has 269 buffer(s)
Hash table size 276707, node heap has 114 buffer(s)
Hash table size 276707, node heap has 195 buffer(s)
Hash table size 276707, node heap has 39 buffer(s)
Hash table size 276707, node heap has 55 buffer(s)
Hash table size 276707, node heap has 258 buffer(s)
Hash table size 276707, node heap has 168 buffer(s)
0.54 hash searches/s, 5.65 non-hash searches/s
---
LOG
---
Log sequence number 220057973549
Log buffer assigned up to 220057973549
Log buffer completed up to 220057973549
Log written up to 220057973549
Log flushed up to 220057973549
Added dirty pages up to 220057973549
Pages flushed up to 220057973549
Last checkpoint at 220057973549
Log minimum file id is 1352
Log maximum file id is 1366
10623098 log i/o's done, 0.08 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2883457
Buffer pool size 65530
Free buffers 8199
Database pages 54518
Old database pages 19961
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4400968, not young 8965989
0.00 youngs/s, 0.00 non-youngs/s
Pages read 416684, created 413270, written 7736694
0.00 reads/s, 0.00 creates/s, 1.45 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: 54518, unzip_LRU len: 0
I/O sum[312]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 8191
Free buffers 1025
Database pages 6816
Old database pages 2496
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 567134, not young 1138214
0.00 youngs/s, 0.00 non-youngs/s
Pages read 53144, created 63498, written 1458783
0.00 reads/s, 0.00 creates/s, 0.42 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: 6816, unzip_LRU len: 0
I/O sum[39]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 8192
Free buffers 1025
Database pages 6811
Old database pages 2494
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 471060, not young 1332059
0.00 youngs/s, 0.00 non-youngs/s
Pages read 53940, created 38361, written 1111462
0.00 reads/s, 0.00 creates/s, 0.38 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: 6811, unzip_LRU len: 0
I/O sum[39]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 8191
Free buffers 1025
Database pages 6809
Old database pages 2493
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 737277, not young 631432
0.00 youngs/s, 0.00 non-youngs/s
Pages read 54500, created 60878, written 534683
0.00 reads/s, 0.00 creates/s, 0.15 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: 6809, unzip_LRU len: 0
I/O sum[39]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
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 390432, not young 837703
0.00 youngs/s, 0.00 non-youngs/s
Pages read 50045, created 36466, written 555182
0.00 reads/s, 0.00 creates/s, 0.11 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[39]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 8191
Free buffers 1025
Database pages 6819
Old database pages 2497
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 502232, not young 1899354
0.00 youngs/s, 0.00 non-youngs/s
Pages read 53493, created 60375, written 817024
0.00 reads/s, 0.00 creates/s, 0.11 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: 6819, unzip_LRU len: 0
I/O sum[39]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 8192
Free buffers 1025
Database pages 6807
Old database pages 2492
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 583222, not young 1262297
0.00 youngs/s, 0.00 non-youngs/s
Pages read 48033, created 39977, written 1130098
0.00 reads/s, 0.00 creates/s, 0.08 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: 6807, unzip_LRU len: 0
I/O sum[39]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 8191
Free buffers 1024
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 713900, not young 977916
0.00 youngs/s, 0.00 non-youngs/s
Pages read 51782, created 76391, written 702791
0.00 reads/s, 0.00 creates/s, 0.11 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[39]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 8191
Free buffers 1025
Database pages 6814
Old database pages 2495
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 435711, not young 887014
0.00 youngs/s, 0.00 non-youngs/s
Pages read 51747, created 37324, written 1426671
0.00 reads/s, 0.00 creates/s, 0.08 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: 6814, unzip_LRU len: 0
I/O sum[39]: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 5617267, updated 2708557, deleted 2896072, read 830879415371
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 67538.17 reads/s
Number of system rows inserted 474981, updated 252771, deleted 374114, read 69320174
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.58 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Process List
|
5 |
event_scheduler |
localhost |
|
Daemon |
6458192 |
Waiting on empty queue |
|
|
10516462 |
rdsadmin |
localhost |
mysql |
Sleep |
6 |
|
|
|
11223781 |
admin |
172.31.45.113:43352 |
richmond |
Query |
0 |
init |
show full processlist |
|
11223782 |
admin |
172.31.45.113:43354 |
richmond_registrations |
Sleep |
0 |
|
|
|
11223783 |
admin |
172.31.45.113:43356 |
data_warehouse |
Sleep |
0 |
|
|
Running Transactions