INNODB STATUS
=====================================
2025-09-18 02:24:22 22770881603328 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 34 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 862132 srv_active, 0 srv_shutdown, 12156425 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 538762
OS WAIT ARRAY INFO: signal count 847490
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-08-15 11:44:54 22770906212096
*** (1) TRANSACTION:
TRANSACTION 123125363, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 679 lock struct(s), heap size 90232, 107184 row lock(s), undo log entries 1
MySQL thread id 17531074, OS thread handle 22771709490944, query id 4258591886 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 = 5033166
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 1013 page no 3511 n bits 304 index PRIMARY of table `richmond`.`tblPlacements` trx id 123125363 lock_mode X locks rec but not gap
Record lock, heap no 239 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8841fead; asc A ;;
1: len 6; hex 00000756be73; asc V s;;
2: len 7; hex 01000017c10d4e; asc N;;
3: len 4; hex 804cccce; asc L ;;
4: len 6; hex 505336353234; asc PS6524;;
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 80000000; asc ;;
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 80; asc ;;
16: len 8; hex 4163636570746564; asc Accepted;;
17: SQL NULL;
18: len 11; hex 4c44417661696c61626c65; asc LDAvailable;;
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 3511 n bits 312 index PRIMARY of table `richmond`.`tblPlacements` trx id 123125363 lock mode S locks rec but not gap waiting
Record lock, heap no 240 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8841fe9c; asc A ;;
1: len 6; hex 00000756be78; asc V x;;
2: len 7; hex 0100000dec2129; asc !);;
3: len 4; hex 804cccb1; asc L ;;
4: len 6; hex 505336383532; asc PS6852;;
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 80000000; asc ;;
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 80; asc ;;
16: len 8; hex 4163636570746564; asc Accepted;;
17: SQL NULL;
18: len 11; hex 4c44417661696c61626c65; asc LDAvailable;;
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 123125368, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 679 lock struct(s), heap size 90232, 107461 row lock(s), undo log entries 1
MySQL thread id 17531081, OS thread handle 22770840487680, query id 4258591901 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 = 5033137
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1013 page no 3511 n bits 312 index PRIMARY of table `richmond`.`tblPlacements` trx id 123125368 lock_mode X locks rec but not gap
Record lock, heap no 240 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8841fe9c; asc A ;;
1: len 6; hex 00000756be78; asc V x;;
2: len 7; hex 0100000dec2129; asc !);;
3: len 4; hex 804cccb1; asc L ;;
4: len 6; hex 505336383532; asc PS6852;;
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 80000000; asc ;;
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 80; asc ;;
16: len 8; hex 4163636570746564; asc Accepted;;
17: SQL NULL;
18: len 11; hex 4c44417661696c61626c65; asc LDAvailable;;
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 3511 n bits 312 index PRIMARY of table `richmond`.`tblPlacements` trx id 123125368 lock mode S locks rec but not gap waiting
Record lock, heap no 239 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8841fead; asc A ;;
1: len 6; hex 00000756be73; asc V s;;
2: len 7; hex 01000017c10d4e; asc N;;
3: len 4; hex 804cccce; asc L ;;
4: len 6; hex 505336353234; asc PS6524;;
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 80000000; asc ;;
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 80; asc ;;
16: len 8; hex 4163636570746564; asc Accepted;;
17: SQL NULL;
18: len 11; hex 4c44417661696c61626c65; asc LDAvailable;;
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 (2)
------------
TRANSACTIONS
------------
Trx id counter 126474905
Purge done for trx's n:o < 126474905 undo n:o < 0 state: running but idle
History list length 19
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 304247258224024, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304247258224832, 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
742681 OS file reads, 43993474 OS file writes, 23992819 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 6.41 writes/s, 3.74 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 12099, seg size 12101, 22152 merges
merged operations:
insert 31028, delete mark 9003, delete 5598
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 1512 buffer(s)
Hash table size 276707, node heap has 270 buffer(s)
Hash table size 276707, node heap has 121 buffer(s)
Hash table size 276707, node heap has 199 buffer(s)
Hash table size 276707, node heap has 43 buffer(s)
Hash table size 276707, node heap has 80 buffer(s)
Hash table size 276707, node heap has 263 buffer(s)
Hash table size 276707, node heap has 169 buffer(s)
39452.16 hash searches/s, 10382.75 non-hash searches/s
---
LOG
---
Log sequence number 228434212138
Log buffer assigned up to 228434212138
Log buffer completed up to 228434212138
Log written up to 228434212138
Log flushed up to 228434212138
Added dirty pages up to 228434212138
Pages flushed up to 228434212138
Last checkpoint at 228434212138
Log minimum file id is 1472
Log maximum file id is 1491
21880150 log i/o's done, 3.24 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 3051833
Buffer pool size 65530
Free buffers 8207
Database pages 54666
Old database pages 20015
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 8856317, not young 16342484
0.32 youngs/s, 0.00 non-youngs/s
Pages read 742160, created 858149, written 15786466
0.00 reads/s, 0.03 creates/s, 2.03 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: 54666, unzip_LRU len: 0
I/O sum[560]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 8191
Free buffers 1026
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 1170217, not young 2119670
0.00 youngs/s, 0.00 non-youngs/s
Pages read 94802, created 131061, written 3057190
0.00 reads/s, 0.00 creates/s, 0.64 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: 6829, unzip_LRU len: 0
I/O sum[70]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 8192
Free buffers 1028
Database pages 6832
Old database pages 2501
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 959458, not young 2527586
0.26 youngs/s, 0.00 non-youngs/s
Pages read 96719, created 77741, written 2303993
0.00 reads/s, 0.00 creates/s, 0.52 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: 6832, unzip_LRU len: 0
I/O sum[70]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 8191
Free buffers 1026
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 1560680, not young 1218473
0.00 youngs/s, 0.00 non-youngs/s
Pages read 98307, created 124510, written 1111212
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: 6834, unzip_LRU len: 0
I/O sum[70]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 8191
Free buffers 1025
Database pages 6838
Old database pages 2504
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 781312, not young 1454628
0.06 youngs/s, 0.00 non-youngs/s
Pages read 87765, created 72698, written 1042326
0.00 reads/s, 0.03 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: 6838, unzip_LRU len: 0
I/O sum[70]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
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 1016794, not young 3454281
0.00 youngs/s, 0.00 non-youngs/s
Pages read 95803, created 124361, written 1775357
0.00 reads/s, 0.00 creates/s, 0.23 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: 6831, unzip_LRU len: 0
I/O sum[70]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 8192
Free buffers 1025
Database pages 6840
Old database pages 2504
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1105596, not young 2198731
0.00 youngs/s, 0.00 non-youngs/s
Pages read 84920, created 79859, written 2259449
0.00 reads/s, 0.00 creates/s, 0.12 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: 6840, unzip_LRU len: 0
I/O sum[70]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 8191
Free buffers 1026
Database pages 6831
Old database pages 2502
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1373175, not young 1920656
0.00 youngs/s, 0.00 non-youngs/s
Pages read 93528, created 172736, written 1350943
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: 6831, unzip_LRU len: 0
I/O sum[70]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 8191
Free buffers 1026
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 889085, not young 1448459
0.00 youngs/s, 0.00 non-youngs/s
Pages read 90316, created 75183, written 2885996
0.00 reads/s, 0.00 creates/s, 0.23 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: 6831, unzip_LRU len: 0
I/O sum[70]: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 12457030, updated 7141801, deleted 6822074, read 1735098870030
2.76 inserts/s, 5.50 updates/s, 2.76 deletes/s, 201874.24 reads/s
Number of system rows inserted 956499, updated 509775, deleted 753232, read 140535955
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.68 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Process List
|
5 |
event_scheduler |
localhost |
|
Daemon |
13021523 |
Waiting on empty queue |
|
|
17743535 |
rdsadmin |
localhost |
mysql |
Sleep |
5 |
|
|
|
18674644 |
admin |
172.31.45.113:37692 |
richmond |
Sleep |
340 |
|
|
|
18674645 |
admin |
172.31.45.113:37698 |
richmond |
Sleep |
340 |
|
|
|
22457044 |
admin |
172.31.45.113:57650 |
richmond |
Query |
0 |
init |
show full processlist |
|
22457045 |
admin |
172.31.45.113:57652 |
richmond_registrations |
Sleep |
0 |
|
|
|
22457046 |
admin |
172.31.45.113:57654 |
data_warehouse |
Sleep |
0 |
|
|
Running Transactions