INNODB STATUS
=====================================
2025-12-09 23:09:06 22849329190656 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 41 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 168743 srv_active, 0 srv_shutdown, 2472445 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 112271
OS WAIT ARRAY INFO: signal count 158855
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 134274618
Purge done for trx's n:o < 134274618 undo n:o < 0 state: running but idle
History list length 0
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
186765 OS file reads, 8223105 OS file writes, 4566607 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, 4159 merges
merged operations:
insert 5046, delete mark 3083, delete 2092
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 1842 buffer(s)
Hash table size 276707, node heap has 259 buffer(s)
Hash table size 276707, node heap has 94 buffer(s)
Hash table size 276707, node heap has 191 buffer(s)
Hash table size 276707, node heap has 43 buffer(s)
Hash table size 276707, node heap has 63 buffer(s)
Hash table size 276707, node heap has 251 buffer(s)
Hash table size 276707, node heap has 163 buffer(s)
0.51 hash searches/s, 5.66 non-hash searches/s
---
LOG
---
Log sequence number 235688818465
Log buffer assigned up to 235688818465
Log buffer completed up to 235688818465
Log written up to 235688818465
Log flushed up to 235688818465
Added dirty pages up to 235688818465
Pages flushed up to 235688818465
Last checkpoint at 235688818465
Log minimum file id is 1594
Log maximum file id is 1599
4144755 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2409729
Buffer pool size 65531
Free buffers 8201
Database pages 54424
Old database pages 19926
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1915891, not young 3607372
0.00 youngs/s, 0.00 non-youngs/s
Pages read 185912, created 176090, written 2889208
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: 54424, 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 6802
Old database pages 2490
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 274221, not young 370797
0.00 youngs/s, 0.00 non-youngs/s
Pages read 22748, created 26844, written 587771
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: 6802, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 8192
Free buffers 1025
Database pages 6815
Old database pages 2495
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 211765, not young 612853
0.00 youngs/s, 0.00 non-youngs/s
Pages read 24479, created 15660, written 427775
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: 6815, 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 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 303260, not young 229059
0.00 youngs/s, 0.00 non-youngs/s
Pages read 25123, created 25310, written 219177
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: 6792, 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 6810
Old database pages 2493
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 148374, not young 240141
0.00 youngs/s, 0.00 non-youngs/s
Pages read 21599, created 13999, written 153330
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: 6810, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 8192
Free buffers 1025
Database pages 6810
Old database pages 2493
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 209213, not young 749414
0.00 youngs/s, 0.00 non-youngs/s
Pages read 23236, created 25263, written 305310
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: 6810, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 8191
Free buffers 1025
Database pages 6802
Old database pages 2490
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 228337, not young 503707
0.00 youngs/s, 0.00 non-youngs/s
Pages read 23226, created 15638, written 426491
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: 6802, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 8191
Free buffers 1026
Database pages 6788
Old database pages 2486
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 332775, not young 602159
0.00 youngs/s, 0.00 non-youngs/s
Pages read 21932, created 38253, written 230916
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: 6788, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 8192
Free buffers 1025
Database pages 6805
Old database pages 2492
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 207946, not young 299242
0.00 youngs/s, 0.00 non-youngs/s
Pages read 23569, created 15123, written 538438
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: 6805, 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=978, Main thread ID=22849720932096 , state=sleeping
Number of rows inserted 2150438, updated 517461, deleted 873275, read 434085508292
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 65766.64 reads/s
Number of system rows inserted 194835, updated 104415, deleted 153480, read 26174556
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.80 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Process List
|
5 |
event_scheduler |
localhost |
|
Daemon |
2641995 |
Waiting on empty queue |
|
|
2195422 |
rdsadmin |
localhost |
mysql |
Sleep |
6 |
|
|
|
3197667 |
admin |
172.31.45.113:56592 |
richmond |
Sleep |
334 |
|
|
|
4267280 |
admin |
172.31.45.113:45980 |
richmond |
Sleep |
333 |
|
|
|
5010831 |
admin |
172.31.45.113:50430 |
richmond |
Query |
0 |
init |
show full processlist |
|
5010832 |
admin |
172.31.45.113:50440 |
richmond_registrations |
Sleep |
0 |
|
|
|
5010833 |
admin |
172.31.45.113:50446 |
data_warehouse |
Sleep |
0 |
|
|
Running Transactions