INNODB STATUS
=====================================
2025-07-01 14:36:43 22772365821696 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 414048 srv_active, 0 srv_shutdown, 5824508 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 252941
OS WAIT ARRAY INFO: signal count 365517
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 118707612
Purge done for trx's n:o < 118707611 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
404696 OS file reads, 20716484 OS file writes, 11352647 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 4.08 writes/s, 2.49 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 12099, seg size 12101, 12595 merges
merged operations:
insert 16092, delete mark 5844, delete 3633
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 1911 buffer(s)
Hash table size 276707, node heap has 279 buffer(s)
Hash table size 276707, node heap has 106 buffer(s)
Hash table size 276707, node heap has 191 buffer(s)
Hash table size 276707, node heap has 40 buffer(s)
Hash table size 276707, node heap has 53 buffer(s)
Hash table size 276707, node heap has 249 buffer(s)
Hash table size 276707, node heap has 165 buffer(s)
9970.50 hash searches/s, 542.35 non-hash searches/s
---
LOG
---
Log sequence number 219812545193
Log buffer assigned up to 219812545193
Log buffer completed up to 219812545193
Log written up to 219812545193
Log flushed up to 219812545193
Added dirty pages up to 219812545193
Pages flushed up to 219812545193
Last checkpoint at 219812545193
Log minimum file id is 1352
Log maximum file id is 1362
10234828 log i/o's done, 1.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2883457
Buffer pool size 65530
Free buffers 8198
Database pages 54338
Old database pages 19895
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4248922, not young 8583226
0.50 youngs/s, 0.00 non-youngs/s
Pages read 404225, created 398181, written 7476832
0.00 reads/s, 0.00 creates/s, 1.99 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: 54338, unzip_LRU len: 0
I/O sum[256]:cur[112], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 8191
Free buffers 1025
Database pages 6792
Old database pages 2487
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 544878, not young 1084981
0.10 youngs/s, 0.00 non-youngs/s
Pages read 51454, created 61279, written 1410887
0.00 reads/s, 0.00 creates/s, 0.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: 6792, unzip_LRU len: 0
I/O sum[32]:cur[14], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 8192
Free buffers 1025
Database pages 6789
Old database pages 2486
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 456307, not young 1273273
0.20 youngs/s, 0.00 non-youngs/s
Pages read 52206, created 37031, written 1073393
0.00 reads/s, 0.00 creates/s, 0.30 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: 6789, unzip_LRU len: 0
I/O sum[32]:cur[14], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 8191
Free buffers 1025
Database pages 6785
Old database pages 2484
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 713273, not young 615815
0.00 youngs/s, 0.00 non-youngs/s
Pages read 53147, created 58788, written 518759
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: 6785, unzip_LRU len: 0
I/O sum[32]:cur[14], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 8191
Free buffers 1025
Database pages 6798
Old database pages 2489
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 377868, not young 807529
0.00 youngs/s, 0.00 non-youngs/s
Pages read 48552, created 35164, written 535079
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: 6798, unzip_LRU len: 0
I/O sum[32]:cur[14], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 8191
Free buffers 1024
Database pages 6797
Old database pages 2489
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 486256, not young 1811356
0.00 youngs/s, 0.00 non-youngs/s
Pages read 51995, created 58183, written 787941
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: 6797, unzip_LRU len: 0
I/O sum[32]:cur[14], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 8192
Free buffers 1024
Database pages 6790
Old database pages 2486
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 565608, not young 1194697
0.00 youngs/s, 0.00 non-youngs/s
Pages read 46540, created 38599, written 1093834
0.00 reads/s, 0.00 creates/s, 0.30 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: 6790, unzip_LRU len: 0
I/O sum[32]:cur[14], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 8191
Free buffers 1025
Database pages 6793
Old database pages 2487
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 685394, not young 932685
0.00 youngs/s, 0.00 non-youngs/s
Pages read 50198, created 73137, written 681997
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: 6793, unzip_LRU len: 0
I/O sum[32]:cur[14], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 8191
Free buffers 1025
Database pages 6794
Old database pages 2487
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 419338, not young 862890
0.20 youngs/s, 0.00 non-youngs/s
Pages read 50133, created 36000, written 1374942
0.00 reads/s, 0.00 creates/s, 0.70 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: 6794, unzip_LRU len: 0
I/O sum[32]:cur[14], 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 5414154, updated 2611729, deleted 2793295, read 800189825674
0.10 inserts/s, 0.00 updates/s, 0.10 deletes/s, 98314.17 reads/s
Number of system rows inserted 458788, updated 244281, deleted 361317, read 66658236
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.60 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Process List
|
5 |
event_scheduler |
localhost |
|
Daemon |
6239864 |
Waiting on empty queue |
|
|
10516462 |
rdsadmin |
localhost |
mysql |
Sleep |
3 |
|
|
|
10846490 |
admin |
172.31.45.113:40054 |
richmond |
Query |
0 |
init |
show full processlist |
|
10846491 |
admin |
172.31.45.113:40056 |
richmond_registrations |
Sleep |
0 |
|
|
|
10846492 |
admin |
172.31.45.113:40058 |
data_warehouse |
Sleep |
0 |
|
|
Running Transactions