INNODB STATUS
=====================================
2025-05-19 20:36:59 22772387817216 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 25 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 169851 srv_active, 0 srv_shutdown, 2375906 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 106183
OS WAIT ARRAY INFO: signal count 146324
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-05-08 12:09:00 22770906212096
*** (1) TRANSACTION:
TRANSACTION 113314646, ACTIVE 1 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 831 lock struct(s), heap size 106616, 127462 row lock(s), undo log entries 1
MySQL thread id 2604697, OS thread handle 22772365793024, query id 635796088 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 = 5005449
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 1013 page no 580 n bits 312 index PRIMARY of table `richmond`.`tblPlacements` trx id 113314646 lock_mode X locks rec but not gap
Record lock, heap no 244 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8831555e; asc 1U^;;
1: len 6; hex 000006c10b56; asc V;;
2: len 7; hex 0100000c4c0790; asc L ;;
3: len 4; hex 804c6089; asc L` ;;
4: len 7; hex 50533133343320; asc PS1343 ;;
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 30353235353433323131; asc 0525543211;;
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 581 n bits 320 index PRIMARY of table `richmond`.`tblPlacements` trx id 113314646 lock mode S locks rec but not gap waiting
Record lock, heap no 250 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8831578b; asc 1W ;;
1: len 6; hex 000006c10b58; asc X;;
2: len 7; hex 010000113e3024; asc >0$;;
3: len 4; hex 804c60ba; asc L` ;;
4: len 5; hex 4152313435; asc AR145;;
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 80; asc ;;
15: len 1; hex 80; asc ;;
16: len 8; hex 4163636570746564; asc Accepted;;
17: SQL NULL;
18: len 9; hex 417661696c61626c65; asc Available;;
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 113314648, ACTIVE 1 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 830 lock struct(s), heap size 106616, 126179 row lock(s), undo log entries 1
MySQL thread id 2604707, OS thread handle 22772388357888, query id 635796093 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 = 5005498
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1013 page no 581 n bits 320 index PRIMARY of table `richmond`.`tblPlacements` trx id 113314648 lock_mode X locks rec but not gap
Record lock, heap no 250 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8831578b; asc 1W ;;
1: len 6; hex 000006c10b58; asc X;;
2: len 7; hex 010000113e3024; asc >0$;;
3: len 4; hex 804c60ba; asc L` ;;
4: len 5; hex 4152313435; asc AR145;;
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 80; asc ;;
15: len 1; hex 80; asc ;;
16: len 8; hex 4163636570746564; asc Accepted;;
17: SQL NULL;
18: len 9; hex 417661696c61626c65; asc Available;;
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 580 n bits 312 index PRIMARY of table `richmond`.`tblPlacements` trx id 113314648 lock mode S locks rec but not gap waiting
Record lock, heap no 244 PHYSICAL RECORD: n_fields 75; compact format; info bits 64
0: len 4; hex 8831555e; asc 1U^;;
1: len 6; hex 000006c10b56; asc V;;
2: len 7; hex 0100000c4c0790; asc L ;;
3: len 4; hex 804c6089; asc L` ;;
4: len 7; hex 50533133343320; asc PS1343 ;;
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 30353235353433323131; asc 0525543211;;
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 114384291
Purge done for trx's n:o < 114384291 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 304247258224832, 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 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
202007 OS file reads, 8216433 OS file writes, 4480461 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, 6385 merges
merged operations:
insert 8094, delete mark 3467, delete 2085
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 1676 buffer(s)
Hash table size 276707, node heap has 255 buffer(s)
Hash table size 276707, node heap has 104 buffer(s)
Hash table size 276707, node heap has 189 buffer(s)
Hash table size 276707, node heap has 43 buffer(s)
Hash table size 276707, node heap has 75 buffer(s)
Hash table size 276707, node heap has 256 buffer(s)
Hash table size 276707, node heap has 162 buffer(s)
0.56 hash searches/s, 2.88 non-hash searches/s
---
LOG
---
Log sequence number 215737909680
Log buffer assigned up to 215737909680
Log buffer completed up to 215737909680
Log written up to 215737909680
Log flushed up to 215737909680
Added dirty pages up to 215737909680
Pages flushed up to 215737909680
Last checkpoint at 215737909680
Log minimum file id is 1292
Log maximum file id is 1301
3963478 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2549166
Buffer pool size 65530
Free buffers 8196
Database pages 54574
Old database pages 19982
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1655277, not young 3876306
0.00 youngs/s, 0.00 non-youngs/s
Pages read 201608, created 154644, written 3027181
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: 54574, 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 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 213666, not young 422766
0.00 youngs/s, 0.00 non-youngs/s
Pages read 25178, created 24068, written 561686
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 1
Buffer pool size 8192
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 169472, not young 535765
0.00 youngs/s, 0.00 non-youngs/s
Pages read 25985, created 15101, written 432871
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: 6814, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 8191
Free buffers 1024
Database pages 6820
Old database pages 2497
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 270832, not young 267915
0.00 youngs/s, 0.00 non-youngs/s
Pages read 26107, created 23292, written 223210
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: 6820, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 8191
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 137675, not young 415669
0.00 youngs/s, 0.00 non-youngs/s
Pages read 25157, created 14254, written 203546
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[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 8191
Free buffers 1024
Database pages 6822
Old database pages 2498
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 189005, not young 803034
0.00 youngs/s, 0.00 non-youngs/s
Pages read 25328, created 23189, written 326232
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: 6822, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 8192
Free buffers 1024
Database pages 6825
Old database pages 2499
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 253169, not young 614608
0.00 youngs/s, 0.00 non-youngs/s
Pages read 23201, created 15958, written 437113
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: 6825, 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 6825
Old database pages 2499
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 253112, not young 367007
0.00 youngs/s, 0.00 non-youngs/s
Pages read 24914, created 23928, written 273569
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: 6825, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 8191
Free buffers 1025
Database pages 6822
Old database pages 2498
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 168346, not young 449542
0.00 youngs/s, 0.00 non-youngs/s
Pages read 25738, created 14854, written 568954
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: 6822, 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 2106610, updated 1116430, deleted 1121044, read 325270008306
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 51957.92 reads/s
Number of system rows inserted 187650, updated 100076, deleted 147808, read 25175227
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.48 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Process List
|
5 |
event_scheduler |
localhost |
|
Daemon |
2546280 |
Waiting on empty queue |
|
|
2045221 |
rdsadmin |
localhost |
mysql |
Sleep |
2 |
|
|
|
4038695 |
admin |
172.31.45.113:59698 |
opera |
Sleep |
576 |
|
|
|
4038696 |
admin |
172.31.45.113:59704 |
opera |
Sleep |
577 |
|
|
|
4314955 |
admin |
172.31.45.113:49362 |
richmond |
Query |
0 |
init |
show full processlist |
|
4314956 |
admin |
172.31.45.113:49364 |
richmond_registrations |
Sleep |
0 |
|
|
|
4314957 |
admin |
172.31.45.113:49366 |
data_warehouse |
Sleep |
0 |
|
|
Running Transactions