INNODB STATUS
=====================================
2025-05-09 06:20:24 22772277303040 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 27 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 107735 srv_active, 0 srv_shutdown, 1522820 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 70344
OS WAIT ARRAY INFO: signal count 94463
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 113385835
Purge done for trx's n:o < 113385833 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---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 304247258224832, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304247258227256, 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
160567 OS file reads, 5253162 OS file writes, 2859762 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.39 writes/s, 0.36 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 12099, seg size 12101, 4965 merges
merged operations:
insert 6422, delete mark 3210, delete 1904
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 1400 buffer(s)
Hash table size 276707, node heap has 254 buffer(s)
Hash table size 276707, node heap has 94 buffer(s)
Hash table size 276707, node heap has 189 buffer(s)
Hash table size 276707, node heap has 37 buffer(s)
Hash table size 276707, node heap has 86 buffer(s)
Hash table size 276707, node heap has 259 buffer(s)
Hash table size 276707, node heap has 165 buffer(s)
14.11 hash searches/s, 15.33 non-hash searches/s
---
LOG
---
Log sequence number 214799973663
Log buffer assigned up to 214799973663
Log buffer completed up to 214799973663
Log written up to 214799973663
Log flushed up to 214799973663
Added dirty pages up to 214799973663
Pages flushed up to 214799973663
Last checkpoint at 214799973663
Log minimum file id is 1262
Log maximum file id is 1287
2545231 log i/o's done, 0.11 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2481280
Buffer pool size 65530
Free buffers 8201
Database pages 54845
Old database pages 20083
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1083570, not young 2687222
0.00 youngs/s, 0.00 non-youngs/s
Pages read 160182, created 98860, written 1929059
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: 54845, unzip_LRU len: 0
I/O sum[24]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 8191
Free buffers 1025
Database pages 6849
Old database pages 2508
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 139513, not young 271839
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19627, created 15242, written 355312
0.00 reads/s, 0.00 creates/s, 0.04 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: 6849, unzip_LRU len: 0
I/O sum[3]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 8192
Free buffers 1025
Database pages 6855
Old database pages 2510
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 108845, not young 369805
0.00 youngs/s, 0.00 non-youngs/s
Pages read 20730, created 9612, written 269976
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: 6855, unzip_LRU len: 0
I/O sum[3]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 8191
Free buffers 1025
Database pages 6854
Old database pages 2510
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 172173, not young 181837
0.00 youngs/s, 0.00 non-youngs/s
Pages read 20699, created 14838, written 145234
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: 6854, unzip_LRU len: 0
I/O sum[3]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 8191
Free buffers 1025
Database pages 6858
Old database pages 2511
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 90915, not young 317084
0.00 youngs/s, 0.00 non-youngs/s
Pages read 20192, created 9241, written 130212
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: 6858, unzip_LRU len: 0
I/O sum[3]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 8191
Free buffers 1026
Database pages 6855
Old database pages 2510
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 122792, not young 476140
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19579, created 14842, written 212739
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: 6855, unzip_LRU len: 0
I/O sum[3]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 8192
Free buffers 1025
Database pages 6854
Old database pages 2510
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 178954, not young 480065
0.00 youngs/s, 0.00 non-youngs/s
Pages read 18459, created 10312, written 277241
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: 6854, unzip_LRU len: 0
I/O sum[3]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 8191
Free buffers 1025
Database pages 6857
Old database pages 2511
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 161667, not young 259957
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19889, created 15050, written 175441
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: 6857, unzip_LRU len: 0
I/O sum[3]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 8191
Free buffers 1025
Database pages 6863
Old database pages 2513
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 108711, not young 330495
0.00 youngs/s, 0.00 non-youngs/s
Pages read 21007, created 9723, written 362904
0.00 reads/s, 0.00 creates/s, 0.07 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: 6863, unzip_LRU len: 0
I/O sum[3]: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 1379168, updated 760688, deleted 743628, read 207986295660
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 113193.77 reads/s
Number of system rows inserted 120328, updated 64333, deleted 94779, read 15716940
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.85 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Process List
|
5 |
event_scheduler |
localhost |
|
Daemon |
1630885 |
Waiting on empty queue |
|
|
1942104 |
admin |
172.31.45.113:41718 |
richmond |
Sleep |
266 |
|
|
|
1942111 |
admin |
172.31.45.113:41732 |
richmond |
Sleep |
266 |
|
|
|
2045221 |
rdsadmin |
localhost |
mysql |
Sleep |
12 |
|
|
|
2701718 |
admin |
172.31.45.113:59416 |
richmond |
Query |
0 |
init |
show full processlist |
|
2701719 |
admin |
172.31.45.113:59418 |
richmond_registrations |
Sleep |
0 |
|
|
|
2701720 |
admin |
172.31.45.113:59420 |
data_warehouse |
Sleep |
0 |
|
|
Running Transactions