Everything in our production environment was working well. Yesterday, suddenly out of no where we were getting "lock wait" on any transaction related to a specific row. Same transaction on another row worked when the lock was removed, but editing that one row caused the entire table to lock up. Now, another problem is that the DB doesn't return all rows at times, ex. it returns 1 instead of 10. We are using java + hibernate. Like I said, there were no problems whatsoever, this is odd. I followed this https://mysql.wisborg.dk/2017/05/30/innodb-locks-analysis-why-is-blocking-query-null-and-how-to-find-more-information-about-the-blocking-transaction/
But still, the blocking transaction show trx_query as NULL. When running
select * from information_schema.innodb_trx;
Many transactions are null in the query column.
SHOW ENGINE INNODB STATUS;
---------------------------------------
=====================================
2019-08-02 21:24:39 2add8607e700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 57 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3498 srv_active, 0 srv_shutdown, 61234 srv_idle
srv_master_thread log flush and writes: 64732
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 22165
OS WAIT ARRAY INFO: signal count 118693
Mutex spin waits 269941, rounds 833090, OS waits 9670
RW-shared spins 25736, rounds 372709, OS waits 6243
RW-excl spins 29187, rounds 404172, OS waits 5955
Spin rounds per wait: 3.09 mutex, 14.48 RW-shared, 13.85 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-08-02 16:29:28 2adf53544700
*** (1) TRANSACTION:
TRANSACTION 22187462, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 2
MySQL thread id 26615, OS thread handle 0x2adecc1de700, query id 1141023 172.31.8.177 dbuser updating
/* update com.catchapp.server.persistence.user.User */ update user set cloudkit_id=null, created='2019-08-02 16:20:55', entity_status='active', unique_id='48df7cf7bffe474d85787bddf8425189', updated='2019-08-02 16:29:28', address_street_1=null, address_street_2=null, api_version='3.1', bio=null, city=null, country=null, distance_unit='mile', dob='1994-09-03', email='souzasire@gmail.com', email_sent=0, external_code='WHSPGI', facebook_id=null, facebook_token=null, first_name='Renata', gender='female', google_access_token=null, google_id=null, google_refresh_token=null, hometown=null, image_url='http://catchapp-prod.s3.amazonaws.com/e7c1df0c87434fffb1b00fabd7def76b-profile.jpg', last_name='Souza', membership='basic', needs_password_change=0, new_user=1, num_completed_events=0, password='{SSHA}4wKS9ZG7hhhpbxQRtsi6zVyERS0/bRHMCXpuLA==', phone=null, phone_normalized=null, role_flag=1, smoker=null, state=null, time
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1380 page no 790 n bits 104 index `PRIMARY` of table `catchappprod`.`user` trx id 22187462 lock_mode X locks rec but not gap waiting
Record lock, heap no 36 PHYSICAL RECORD: n_fields 46; compact format; info bits 0
0: len 30; hex 326339663838333136633531353832393031366335333232356133303032; asc 2c9f88316c515829016c53225a3002; (total 32 bytes);
1: len 6; hex 000001528dbd; asc R ;;
2: len 7; hex 420000015204cb; asc B R ;;
3: SQL NULL;
4: len 5; hex 99a3c50537; asc 7;;
5: len 6; hex 616374697665; asc active;;
6: len 30; hex 343864663763663762666665343734643835373837626464663834323531; asc 48df7cf7bffe474d85787bddf84251; (total 32 bytes);
7: len 5; hex 99a3c50537; asc 7;;
8: SQL NULL;
9: SQL NULL;
10: SQL NULL;
11: SQL NULL;
12: SQL NULL;
13: len 4; hex 6d696c65; asc mile;;
14: len 3; hex 8f9523; asc #;;
15: len 19; hex 736f757a617369726540676d61696c2e636f6d; asc souzasire@gmail.com;;
16: len 6; hex 574853504749; asc WHSPGI;;
17: SQL NULL;
18: SQL NULL;
19: len 6; hex 52656e617461; asc Renata;;
20: len 6; hex 66656d616c65; asc female;;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: len 30; hex 687474703a2f2f63617463686170702d70726f642e73332e616d617a6f6e; asc http://catchapp-prod.s3.amazon; (total 82 bytes);
26: len 5; hex 536f757a61; asc Souza;;
27: len 4; hex 80000000; asc ;;
28: len 4; hex 80000000; asc ;;
29: len 30; hex 7b535348417d34774b53395a47376868687062785152747369367a567945; asc {SSHA}4wKS9ZG7hhhpbxQRtsi6zVyE; (total 46 bytes);
30: SQL NULL;
31: SQL NULL;
32: len 4; hex 80000001; asc ;;
33: SQL NULL;
34: SQL NULL;
35: len 15; hex 416d65726963612f4368696361676f; asc America/Chicago;;
36: len 9; hex 536f757a6173697265; asc Souzasire;;
37: len 5; hex 3437353833; asc 47583;;
38: len 4; hex 80000001; asc ;;
39: SQL NULL;
40: SQL NULL;
41: len 1; hex 31; asc 1;;
42: len 4; hex 80000000; asc ;;
43: len 1; hex 81; asc ;;
44: len 5; hex 6261736963; asc basic;;
45: len 6; hex 5055424c4943; asc PUBLIC;;
*** (2) TRANSACTION:
TRANSACTION 22187459, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 2
MySQL thread id 26616, OS thread handle 0x2adf53544700, query id 1141027 172.31.8.177 dbuser updating
/* update com.catchapp.server.persistence.user.User */ update user set cloudkit_id=null, created='2019-08-02 16:20:55', entity_status='active', unique_id='48df7cf7bffe474d85787bddf8425189', updated='2019-08-02 16:29:28', address_street_1=null, address_street_2=null, api_version='3.1', bio=null, city=null, country=null, distance_unit='mile', dob='1994-09-03', email='souzasire@gmail.com', email_sent=0, external_code='WHSPGI', facebook_id=null, facebook_token=null, first_name='Renata', gender='female', google_access_token=null, google_id=null, google_refresh_token=null, hometown=null, image_url='http://catchapp-prod.s3.amazonaws.com/e7c1df0c87434fffb1b00fabd7def76b-profile.jpg', last_name='Souza', membership='basic', needs_password_change=0, new_user=1, num_completed_events=0, password='{SSHA}4wKS9ZG7hhhpbxQRtsi6zVyERS0/bRHMCXpuLA==', phone=null, phone_normalized=null, role_flag=1, smoker=null, state=null, time
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1380 page no 790 n bits 104 index `PRIMARY` of table `catchappprod`.`user` trx id 22187459 lock mode S locks rec but not gap
Record lock, heap no 36 PHYSICAL RECORD: n_fields 46; compact format; info bits 0
0: len 30; hex 326339663838333136633531353832393031366335333232356133303032; asc 2c9f88316c515829016c53225a3002; (total 32 bytes);
1: len 6; hex 000001528dbd; asc R ;;
2: len 7; hex 420000015204cb; asc B R ;;
3: SQL NULL;
4: len 5; hex 99a3c50537; asc 7;;
5: len 6; hex 616374697665; asc active;;
6: len 30; hex 343864663763663762666665343734643835373837626464663834323531; asc 48df7cf7bffe474d85787bddf84251; (total 32 bytes);
7: len 5; hex 99a3c50537; asc 7;;
8: SQL NULL;
9: SQL NULL;
10: SQL NULL;
11: SQL NULL;
12: SQL NULL;
13: len 4; hex 6d696c65; asc mile;;
14: len 3; hex 8f9523; asc #;;
15: len 19; hex 736f757a617369726540676d61696c2e636f6d; asc souzasire@gmail.com;;
16: len 6; hex 574853504749; asc WHSPGI;;
17: SQL NULL;
18: SQL NULL;
19: len 6; hex 52656e617461; asc Renata;;
20: len 6; hex 66656d616c65; asc female;;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: len 30; hex 687474703a2f2f63617463686170702d70726f642e73332e616d617a6f6e; asc http://catchapp-prod.s3.amazon; (total 82 bytes);
26: len 5; hex 536f757a61; asc Souza;;
27: len 4; hex 80000000; asc ;;
28: len 4; hex 80000000; asc ;;
29: len 30; hex 7b535348417d34774b53395a47376868687062785152747369367a567945; asc {SSHA}4wKS9ZG7hhhpbxQRtsi6zVyE; (total 46 bytes);
30: SQL NULL;
31: SQL NULL;
32: len 4; hex 80000001; asc ;;
33: SQL NULL;
34: SQL NULL;
35: len 15; hex 416d65726963612f4368696361676f; asc America/Chicago;;
36: len 9; hex 536f757a6173697265; asc Souzasire;;
37: len 5; hex 3437353833; asc 47583;;
38: len 4; hex 80000001; asc ;;
39: SQL NULL;
40: SQL NULL;
41: len 1; hex 31; asc 1;;
42: len 4; hex 80000000; asc ;;
43: len 1; hex 81; asc ;;
44: len 5; hex 6261736963; asc basic;;
45: len 6; hex 5055424c4943; asc PUBLIC;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1380 page no 790 n bits 104 index `PRIMARY` of table `catchappprod`.`user` trx id 22187459 lock_mode X locks rec but not gap waiting
Record lock, heap no 36 PHYSICAL RECORD: n_fields 46; compact format; info bits 0
0: len 30; hex 326339663838333136633531353832393031366335333232356133303032; asc 2c9f88316c515829016c53225a3002; (total 32 bytes);
1: len 6; hex 000001528dbd; asc R ;;
2: len 7; hex 420000015204cb; asc B R ;;
3: SQL NULL;
4: len 5; hex 99a3c50537; asc 7;;
5: len 6; hex 616374697665; asc active;;
6: len 30; hex 343864663763663762666665343734643835373837626464663834323531; asc 48df7cf7bffe474d85787bddf84251; (total 32 bytes);
7: len 5; hex 99a3c50537; asc 7;;
8: SQL NULL;
9: SQL NULL;
10: SQL NULL;
11: SQL NULL;
12: SQL NULL;
13: len 4; hex 6d696c65; asc mile;;
14: len 3; hex 8f9523; asc #;;
15: len 19; hex 736f757a617369726540676d61696c2e636f6d; asc souzasire@gmail.com;;
16: len 6; hex 574853504749; asc WHSPGI;;
17: SQL NULL;
18: SQL NULL;
19: len 6; hex 52656e617461; asc Renata;;
20: len 6; hex 66656d616c65; asc female;;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: len 30; hex 687474703a2f2f63617463686170702d70726f642e73332e616d617a6f6e; asc http://catchapp-prod.s3.amazon; (total 82 bytes);
26: len 5; hex 536f757a61; asc Souza;;
27: len 4; hex 80000000; asc ;;
28: len 4; hex 80000000; asc ;;
29: len 30; hex 7b535348417d34774b53395a47376868687062785152747369367a567945; asc {SSHA}4wKS9ZG7hhhpbxQRtsi6zVyE; (total 46 bytes);
30: SQL NULL;
31: SQL NULL;
32: len 4; hex 80000001; asc ;;
33: SQL NULL;
34: SQL NULL;
35: len 15; hex 416d65726963612f4368696361676f; asc America/Chicago;;
36: len 9; hex 536f757a6173697265; asc Souzasire;;
37: len 5; hex 3437353833; asc 47583;;
38: len 4; hex 80000001; asc ;;
39: SQL NULL;
40: SQL NULL;
41: len 1; hex 31; asc 1;;
42: len 4; hex 80000000; asc ;;
43: len 1; hex 81; asc ;;
44: len 5; hex 6261736963; asc basic;;
45: len 6; hex 5055424c4943; asc PUBLIC;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 22211040
Purge done for trx's n:o < 22203907 undo n:o < 0 state: running but idle
History list length 469
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 22210742, not started
MySQL thread id 36778, OS thread handle 0x2adf5bf44700, query id 1697117 172.31.8.177 dbuser
---TRANSACTION 22210970, not started
MySQL thread id 36777, OS thread handle 0x2add87be4700, query id 1697742 172.31.8.177 dbuser
---TRANSACTION 22210749, not started
MySQL thread id 36776, OS thread handle 0x2adf499ce700, query id 1697119 172.31.8.177 dbuser
---TRANSACTION 22211019, not started
MySQL thread id 36775, OS thread handle 0x2adf5b103700, query id 1699117 172.31.8.177 dbuser
---TRANSACTION 22210964, not started
MySQL thread id 36772, OS thread handle 0x2add860bf700, query id 1697737 172.31.8.177 dbuser
---TRANSACTION 22210969, not started
MySQL thread id 36770, OS thread handle 0x2adf58385700, query id 1697744 172.31.8.177 dbuser
---TRANSACTION 22211035, not started
MySQL thread id 36774, OS thread handle 0x2adf53544700, query id 1699340 172.31.8.177 dbuser
---TRANSACTION 22210966, not started
MySQL thread id 36767, OS thread handle 0x2adf50689700, query id 1697720 172.31.8.177 dbuser
---TRANSACTION 22209850, not started
MySQL thread id 36768, OS thread handle 0x2adf59185700, query id 1697121 172.31.8.177 dbuser
---TRANSACTION 22209849, not started
MySQL thread id 36766, OS thread handle 0x2adf55a81700, query id 1697124 172.31.8.177 dbuser
---TRANSACTION 22209835, not started
MySQL thread id 36090, OS thread handle 0x2adf4b144700, query id 1697128 172.31.8.177 dbuser
---TRANSACTION 22209836, not started
MySQL thread id 36083, OS thread handle 0x2adf56785700, query id 1697125 172.31.8.177 dbuser
---TRANSACTION 22210745, not started
MySQL thread id 36081, OS thread handle 0x2adf4e503700, query id 1697115 172.31.8.177 dbuser
---TRANSACTION 22210968, not started
MySQL thread id 36084, OS thread handle 0x2adf5b040700, query id 1697789 172.31.8.177 dbuser
---TRANSACTION 22209838, not started
MySQL thread id 36086, OS thread handle 0x2adf41884700, query id 1697126 172.31.8.177 dbuser
---TRANSACTION 22210755, not started
MySQL thread id 35747, OS thread handle 0x2adf5d3c6700, query id 1697116 172.31.8.177 dbuser
---TRANSACTION 22211036, not started
MySQL thread id 34033, OS thread handle 0x2adf4b185700, query id 1699338 172.31.8.177 dbuser
---TRANSACTION 22209846, not started
MySQL thread id 34032, OS thread handle 0x2adf5d344700, query id 1697123 172.31.8.177 dbuser
---TRANSACTION 22210753, not started
MySQL thread id 34031, OS thread handle 0x2adf5b640700, query id 1697118 172.31.8.177 dbuser
---TRANSACTION 22210965, not started
MySQL thread id 34027, OS thread handle 0x2adf50b03700, query id 1697714 172.31.8.177 dbuser
---TRANSACTION 22209841, not started
MySQL thread id 34024, OS thread handle 0x2adf4ef44700, query id 1697120 172.31.8.177 dbuser
---TRANSACTION 22210971, not started
MySQL thread id 34021, OS thread handle 0x2adf545c6700, query id 1697743 172.31.8.177 dbuser
---TRANSACTION 22209843, not started
MySQL thread id 34016, OS thread handle 0x2ade3c5de700, query id 1697122 172.31.8.177 dbuser
---TRANSACTION 22197155, not started
MySQL thread id 30647, OS thread handle 0x2add8607e700, query id 1699343 cpe-104-35-234-14.socal.res.rr.com 104.35.234.14 dbuser init
SHOW ENGINE INNODB STATUS
---TRANSACTION 22211030, not started
MySQL thread id 32, OS thread handle 0x2adf2bfde700, query id 1699264 localhost 127.0.0.1 rdsadmin
---TRANSACTION 22211039, ACTIVE 0 sec
MySQL thread id 36769, OS thread handle 0x2adf5d281700, query id 1699342 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22211040, sees < 22203902
---TRANSACTION 22211038, ACTIVE 0 sec
MySQL thread id 36085, OS thread handle 0x2add86182700, query id 1699332 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22211039, sees < 22203902
---TRANSACTION 22211037, ACTIVE 0 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 36773, OS thread handle 0x2adf47681700, query id 1699324 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22211038, sees < 22203902
---TRANSACTION 22210953, ACTIVE 35 sec
MySQL thread id 36089, OS thread handle 0x2adf52585700, query id 1697555 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22210954, sees < 22203902
---TRANSACTION 22210908, ACTIVE 60 sec
MySQL thread id 34025, OS thread handle 0x2adefc15c700, query id 1696679 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22210909, sees < 22203902
---TRANSACTION 22210501, ACTIVE 230 sec
MySQL thread id 36087, OS thread handle 0x2ade6c3de700, query id 1687130 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22210502, sees < 22203902
---TRANSACTION 22210001, ACTIVE 618 sec
MySQL thread id 36771, OS thread handle 0x2adf51c81700, query id 1674318 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22210002, sees < 22203376
---TRANSACTION 22209496, ACTIVE 927 sec
MySQL thread id 35746, OS thread handle 0x2adf524c2700, query id 1661990 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22209497, sees < 22203376
---TRANSACTION 22209480, ACTIVE 938 sec
MySQL thread id 34026, OS thread handle 0x2adf59dc6700, query id 1661378 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22209481, sees < 22203376
---TRANSACTION 22209429, ACTIVE 950 sec
MySQL thread id 34030, OS thread handle 0x2adf5c048700, query id 1660333 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22209430, sees < 22203376
---TRANSACTION 22209377, ACTIVE 986 sec
MySQL thread id 34029, OS thread handle 0x2adf5cac2700, query id 1659438 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22209378, sees < 22203376
---TRANSACTION 22208804, ACTIVE 1384 sec
MySQL thread id 35050, OS thread handle 0x2adf50a81700, query id 1647082 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22208805, sees < 22203376
---TRANSACTION 22208533, ACTIVE 1715 sec
MySQL thread id 36088, OS thread handle 0x2adf40945700, query id 1640372 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22208534, sees < 22202050
---TRANSACTION 22208450, ACTIVE 1774 sec
MySQL thread id 34670, OS thread handle 0x2adf5b744700, query id 1637977 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22208451, sees < 22202050
---TRANSACTION 22208365, ACTIVE 1797 sec
MySQL thread id 35048, OS thread handle 0x2adf59c40700, query id 1635901 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22208366, sees < 22202050
---TRANSACTION 22208235, ACTIVE 1870 sec
MySQL thread id 36082, OS thread handle 0x2adf534c2700, query id 1632175 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22208236, sees < 22202050
---TRANSACTION 22208140, ACTIVE 1909 sec
MySQL thread id 35047, OS thread handle 0x2adf58448700, query id 1630189 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22208141, sees < 22202050
---TRANSACTION 22207535, ACTIVE 2347 sec
MySQL thread id 35051, OS thread handle 0x2adf47385700, query id 1618162 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22207536, sees < 22201548
---TRANSACTION 22206742, ACTIVE 2822 sec
MySQL thread id 34019, OS thread handle 0x2adf59703700, query id 1599131 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22206743, sees < 22201548
---TRANSACTION 22206622, ACTIVE 2878 sec
4 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 1
MySQL thread id 34028, OS thread handle 0x2adf506ca700, query id 1605612 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22206623, sees < 22200943
---TRANSACTION 22205263, ACTIVE 3779 sec
MySQL thread id 34671, OS thread handle 0x2adf57144700, query id 1565956 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22205264, sees < 22199169
---TRANSACTION 22205260, ACTIVE 3781 sec
MySQL thread id 34015, OS thread handle 0x2adf4e481700, query id 1565892 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22205261, sees < 22199169
---TRANSACTION 22204496, ACTIVE 4296 sec
MySQL thread id 34013, OS thread handle 0x2adf5bfc6700, query id 1547697 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22204497, sees < 22198567
---TRANSACTION 22204481, ACTIVE 4317 sec
MySQL thread id 34023, OS thread handle 0x2adecc1de700, query id 1547398 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22204482, sees < 22198567
---TRANSACTION 22203902, ACTIVE 4938 sec
MySQL thread id 34017, OS thread handle 0x2add87ba3700, query id 1531921 172.31.8.177 dbuser
Trx read view will not see trx with id >= 22203903, sees < 22198567
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
I/O thread 10 state: waiting for i/o request (write thread)
I/O thread 11 state: waiting for i/o request (write thread)
I/O thread 12 state: waiting for i/o request (write thread)
I/O thread 13 state: waiting for i/o request (write thread)
I/O thread 14 state: waiting for i/o request (write thread)
I/O thread 15 state: waiting for i/o request (write thread)
I/O thread 16 state: waiting for i/o request (write thread)
I/O thread 17 state: waiting for i/o request (write thread)
I/O thread 18 state: waiting for i/o request (write thread)
I/O thread 19 state: waiting for i/o request (write thread)
I/O thread 20 state: waiting for i/o request (write thread)
I/O thread 21 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
29552 OS file reads, 175874 OS file writes, 98491 OS fsyncs
0.02 reads/s, 16384 avg bytes/read, 2.47 writes/s, 1.67 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 48746, seg size 48748, 20592 merges
merged operations:
insert 841, delete mark 3317352, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 12119119, node heap has 442 buffer(s)
909.30 hash searches/s, 351.57 non-hash searches/s
---
LOG
---
Log sequence number 4441306931
Log flushed up to 4441306098
Pages flushed up to 4441306098
Last checkpoint at 4441306098
0 pending log writes, 0 pending chkp writes
18712 log i/o's done, 0.28 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 6267207680; in additional pool allocated 0
Dictionary memory allocated 822706
Buffer pool size 373760
Free buffers 252854
Database pages 120464
Old database pages 44620
Modified db pages 4
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4184, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 54318, created 66146, written 348418
0.02 reads/s, 0.00 creates/s, 1.84 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: 120464, 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 46720
Free buffers 31671
Database pages 14992
Old database pages 5552
Modified db pages 2
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 568, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6678, created 8314, written 49253
0.00 reads/s, 0.00 creates/s, 0.47 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: 14992, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 46720
Free buffers 31761
Database pages 14901
Old database pages 5519
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 486, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6755, created 8146, written 42935
0.00 reads/s, 0.00 creates/s, 0.18 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
...
----------------------------
END OF INNODB MONITOR OUTPUT
============================
I added more ram to the instance to no avail. I'm using a custom parameter group, and set IO threads to 16. default was 4. This did not help.
public ChatMessage addMessage(
Chat chat,
User user,
String message,
MessageType type) throws CatchappRdbException {
ChatMessage chatMessage = new ChatMessage();
chatMessage.setChat(chat);
chatMessage.setMessage(message);
chatMessage.setUser(user);
chatMessage.setType(type);
chat.getMessages().add(chatMessage);
chat.setLast_message(new Date());
Session session = getSession();
try {
// session.saveOrUpdate(chat);
session.saveOrUpdate(chatMessage);
} catch (HibernateException he) {
throw new CatchappRdbException(he);
} finally {
flushSession(session);
}
return chatMessage;
}
this works for anything other row. If running this for the one particular row, the seems like a few tables get locked. I found the transaction causing the block but the trx_query is null.
I wonder if it's a max open files issue or within hibernate trying to access some file that is taking forever but I couldn't find anything.
I'm wondering if paid AWS support can help with this.
Best Answer
I am getting something like this and occurred random, in my case I was set wait_timeout and interactive_timeout both 200.
I am completely sure that is your table with more movement in your DB.
And possible program maintenance windows to optimize specific tables and rebuild statistics.
https://www.znetlive.com/blog/what-is-sleep-query-in-mysql-database/