Elena Stepanova added a comment - 2022-10-07 08:14 Hi TheWitness , Thanks. While getting the stack trace, please also get an example of SHOW FULL PROCESSLIST from the server (for (2) above). See also questions from lstartseva in a previous comment.

Larry Adams added a comment - 2022-10-07 13:56 - edited Tried repeatedly to paste the config file. Bah! my.cnf

Larry Adams added a comment - 2022-10-07 14:00 At one time, we had replication setup, but not now due to statement replication bugs, and then BSL compliance. Trying to get the later fixed, and then the former. Replication was having a hard time keeping up, and had to add all sorts of error exclusions to keep it running. Felt a bit naked. global_status.txt global_variables.txt

Larry Adams added a comment - 2022-11-02 15:08 - edited Okay, it happened this morning. I've got a show engine innodb status output and I'll show a "mariadb top" command output for you. I'm not certain of what to trace or how to do it though... *+Engine Status+* | InnoDB | | ===================================== 2022-11-02 08:04:48 0x7e93ec3d6700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 5 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 8355825 srv_active, 0 srv_shutdown, 6365 srv_idle srv_master_thread log flush and writes: 2950784 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 101160298869 --Thread 139161843701504 has waited at row0ins.cc line 2632 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x7f3516e3f378 created in file buf0buf.cc line 1226 a writer (thread id 139165858187008) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time write locked in file mtr0mtr.ic line 145 --Thread 139165832075008 has waited at row0ins.cc line 2632 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x7f3516e3f378 created in file buf0buf.cc line 1226 a writer (thread id 139165858187008) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time write locked in file mtr0mtr.ic line 145 --Thread 139166232577792 has waited at row0ins.cc line 2632 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x7f3516e3f378 created in file buf0buf.cc line 1226 a writer (thread id 139165858187008) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time write locked in file mtr0mtr.ic line 145 OS WAIT ARRAY INFO: signal count 75021075114 RW-shared spins 2734026196, rounds 37279365604, OS waits 581943508 RW-excl spins 8075670898, rounds 57875335623, OS waits 810301617 RW-sx spins 7609107913, rounds 163178605070, OS waits 3618678739 Spin rounds per wait: 13.64 RW-shared, 7.17 RW-excl, 21.45 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2022-11-02 08:02:51 0x7e9168636700 *** (1) TRANSACTION: TRANSACTION 29869607496, ACTIVE 33 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 7 lock struct(s), heap size 1128, 7 row lock(s) MySQL thread id 127800497, OS thread handle 139165261580032, query id 129316486451 rtm-web-02.qualcomm.com 10.23.248.51 rtm Updating UPDATE grid_jobs SET end_time=0, start_time=0, job_end_logged=0, job_start_logged=0, run_time=0, unkwn_time=0, ssusp_time=0, ususp_time=0, exitStatus=0, exitInfo=0 WHERE clusterid='50' AND stat='PEND' AND end_time!=0 AND start_time!=0 AND last_updated>='2022-11-02 08:02:17' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345785 page no 525 n bits 80 index PRIMARY of table `cacti`.`grid_jobs` trx id 29869607496 lock_mode X locks rec but not gap waiting Record lock, heap no 8 PHYSICAL RECORD: n_fields 115; compact format; info bits 32 0: len 4; hex 00000032; asc 2;; 1: len 8; hex 000000000000173f; asc ?;; 2: len 4; hex 00000000; asc ;; 3: len 4; hex 635e52fa; asc c^R ;; 4: len 6; hex 0006f45de0d8; asc ] ;; 5: len 7; hex 1c000837670819; asc 7g ;; 6: len 4; hex 02050007; asc ;; 7: len 4; hex 00200410; asc ;; 8: len 4; hex 02000000; asc ;; 9: len 4; hex 00040000; asc ;; 10: len 8; hex 7a64617368657673; asc zdashevs;; 11: len 4; hex 45584954; asc EXIT;; 12: len 7; hex 52554e4e494e47; asc RUNNING;; 13: len 4; hex 00000002; asc ;; 14: len 1; hex 00; asc ;; 15: len 4; hex 00000000; asc ;; 16: len 0; hex ; asc ;; 17: len 6; hex 6e6f726d616c; asc normal;; 18: len 0; hex ; asc ;; 19: len 9; hex 72756d697072643033; asc rumiprd03;; 20: len 13; hex 73616e2d7334622d3232323531; asc san-s4b-22251;; 21: len 4; hex 00245b2b; asc $[+;; 22: len 0; hex ; asc ;; 23: len 14; hex 2f757372322f7a64617368657673; asc /usr2/zdashevs;; 24: len 1; hex 2f; asc /;; 25: len 1; hex 2f; asc /;; 26: len 4; hex 80000000; asc ;; 27: len 4; hex 80000009; asc ;; 28: len 0; hex ; asc ;; 29: len 0; hex ; asc ;; 30: len 8; hex 7a64617368657673; asc zdashevs;; 31: len 0; hex ; asc ;; 32: len 30; hex 65794a705a48686663484a76616d566a64434936496a59794e534973496d; asc eyJpZHhfcHJvamVjdCI6IjYyNSIsIm; (total 112 bytes); 33: len 4; hex 00000032; asc 2;; 34: len 4; hex 00000000; asc ;; 35: len 4; hex 00000032; asc 2;; 36: len 9; hex 50696e6e61636c6573; asc Pinnacles;; 37: len 1; hex 2f; asc /;; 38: len 0; hex ; asc ;; 39: len 0; hex ; asc ;; 40: len 9; hex 50696e6e61636c6573; asc Pinnacles;; 41: len 30; hex 2f706b672f696365746f6f6c732f76656e646f72732f656d756361642f72; asc /pkg/icetools/vendors/emucad/r; (total 74 bytes); 42: len 9; hex 2f6465762f6e756c6c; asc /dev/null;; 43: len 0; hex ; asc ;; 44: len 0; hex ; asc ;; 45: len 0; hex ; asc ;; 46: len 30; hex 73656c6563745b766970725f617661696c5d207275736167655b6d656d3d; asc select[vipr_avail] rusage[mem=; (total 35 bytes); 47: len 0; hex ; asc ;; 48: len 0; hex ; asc ;; 49: len 8; hex 00000000c44a1641; asc J A;; 50: len 8; hex 0000000000000000; asc ;; 51: len 8; hex 00000000d07d1e41; asc } A;; 52: len 8; hex 0000000000000000; asc ;; 53: len 8; hex 0000000000000000; asc ;; 54: len 8; hex 0000000000000000; asc ;; 55: len 8; hex 0000000000000000; asc ;; 56: len 8; hex 0000000000000000; asc ;; 57: len 0; hex ; asc ;; 58: len 8; hex 0000000000402f41; asc @/A;; 59: len 8; hex 0000000000000000; asc ;; 60: len 8; hex 00000000a03fe140; asc ? @;; 61: len 8; hex 0000000000000000; asc ;; 62: len 5; hex 8000000000; asc ;; 63: len 1; hex 00; asc ;; 64: len 4; hex 00000001; asc ;; 65: len 4; hex 00000001; asc ;; 66: len 4; hex 00000001; asc ;; 67: len 1; hex 00; asc ;; 68: len 4; hex 00000001; asc ;; 69: len 4; hex 00000001; asc ;; 70: len 4; hex 00000001; asc ;; 71: len 4; hex 00000001; asc ;; 72: len 4; hex 00000000; asc ;; 73: len 4; hex 00000000; asc ;; 74: len 4; hex 00000000; asc ;; 75: len 4; hex 00000000; asc ;; 76: len 4; hex 635e52fc; asc c^R ;; 77: len 4; hex 63625712; asc cbW ;; 78: len 4; hex 00000000; asc ;; 79: len 4; hex 00000000; asc ;; 80: len 4; hex 00000000; asc ;; 81: len 4; hex 00000002; asc ;; 82: len 4; hex 00000000; asc ;; 83: len 4; hex 00040416; asc ;; 84: len 4; hex 00000000; asc ;; 85: len 4; hex 00000000; asc ;; 86: len 4; hex 00000000; asc ;; 87: len 4; hex 00000000; asc ;; 88: len 4; hex 00000000; asc ;; 89: len 4; hex 00000000; asc ;; 90: SQL NULL; 91: len 4; hex 00000000; asc ;; 92: len 4; hex 00040290; asc ;; 93: len 4; hex 00000000; asc ;; 94: len 4; hex 00000000; asc ;; 95: len 4; hex 00000000; asc ;; 96: len 4; hex 00000000; asc ;; 97: len 4; hex 00000000; asc ;; 98: len 4; hex 0000404b; asc @K;; 99: len 1; hex 01; asc ;; 100: len 1; hex 01; asc ;; 101: len 1; hex 00; asc ;; 102: len 0; hex ; asc ;; 103: len 30; hex 52756d693432685f70696e6e6163656c735f666c6578355f646d5f6d7469; asc Rumi42h_pinnacels_flex5_dm_mti; (total 180 bytes); 104: len 30; hex 73656c6563745b28766970725f617661696c29202626202874797065203d; asc select[(vipr_avail) && (type =; (total 80 bytes); 105: len 30; hex 73656c6563745b2828766970725f617661696c2920262620287479706520; asc select[((vipr_avail) && (type ; (total 83 bytes); 106: len 0; hex ; asc ;; 107: len 0; hex ; asc ;; 108: len 10; hex 2f6f74686572732a3334; asc /others*34;; 109: len 4; hex 00000000; asc ;; 110: len 4; hex 7fffffff; asc ;; 111: len 4; hex 00000000; asc ;; 112: len 4; hex 00000000; asc ;; 113: len 1; hex 00; asc ;; 114: len 4; hex 636259c6; asc cbY ;;   *** (2) TRANSACTION: TRANSACTION 29869596888, ACTIVE 56 sec updating or deleting mysql tables in use 3, locked 3 13127 lock struct(s), heap size 1384568, 60001 row lock(s), undo log entries 23333 MySQL thread id 127747436, OS thread handle 139162986702592, query id 129316355631 rtm-web-02.qualcomm.com 10.23.248.51 rtm Deleting from reference tables DELETE gj2 FROM grid_jobs AS gj2 INNER JOIN ( SELECT gj.clusterid, gj.jobid, gj.indexid, gj.submit_time FROM grid_jobs AS gj INNER JOIN grid_jobs_removal_1667400140 AS tt ON gj.clusterid=tt.clusterid AND gj.jobid=tt.jobid AND gj.indexid=tt.indexid AND gj.submit_time=tt.submit_time LIMIT 30000 ) sub1 ON gj2.clusterid=sub1.clusterid AND gj2.jobid=sub1.jobid AND gj2.submit_time=sub1.submit_time AND gj2.indexid=sub1.indexid *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 345785 page no 525 n bits 80 index PRIMARY of table `cacti`.`grid_jobs` trx id 29869596888 lock mode S locks rec but not gap Record lock, heap no 8 PHYSICAL RECORD: n_fields 115; compact format; info bits 32 0: len 4; hex 00000032; asc 2;; 1: len 8; hex 000000000000173f; asc ?;; 2: len 4; hex 00000000; asc ;; 3: len 4; hex 635e52fa; asc c^R ;; 4: len 6; hex 0006f45de0d8; asc ] ;; 5: len 7; hex 1c000837670819; asc 7g ;; 6: len 4; hex 02050007; asc ;; 7: len 4; hex 00200410; asc ;; 8: len 4; hex 02000000; asc ;; 9: len 4; hex 00040000; asc ;; 10: len 8; hex 7a64617368657673; asc zdashevs;; 11: len 4; hex 45584954; asc EXIT;; 12: len 7; hex 52554e4e494e47; asc RUNNING;; 13: len 4; hex 00000002; asc ;; 14: len 1; hex 00; asc ;; 15: len 4; hex 00000000; asc ;; 16: len 0; hex ; asc ;; 17: len 6; hex 6e6f726d616c; asc normal;; 18: len 0; hex ; asc ;; 19: len 9; hex 72756d697072643033; asc rumiprd03;; 20: len 13; hex 73616e2d7334622d3232323531; asc san-s4b-22251;; 21: len 4; hex 00245b2b; asc $[+;; 22: len 0; hex ; asc ;; 23: len 14; hex 2f757372322f7a64617368657673; asc /usr2/zdashevs;; 24: len 1; hex 2f; asc /;; 25: len 1; hex 2f; asc /;; 26: len 4; hex 80000000; asc ;; 27: len 4; hex 80000009; asc ;; 28: len 0; hex ; asc ;; 29: len 0; hex ; asc ;; 30: len 8; hex 7a64617368657673; asc zdashevs;; 31: len 0; hex ; asc ;; 32: len 30; hex 65794a705a48686663484a76616d566a64434936496a59794e534973496d; asc eyJpZHhfcHJvamVjdCI6IjYyNSIsIm; (total 112 bytes); 33: len 4; hex 00000032; asc 2;; 34: len 4; hex 00000000; asc ;; 35: len 4; hex 00000032; asc 2;; 36: len 9; hex 50696e6e61636c6573; asc Pinnacles;; 37: len 1; hex 2f; asc /;; 38: len 0; hex ; asc ;; 39: len 0; hex ; asc ;; 40: len 9; hex 50696e6e61636c6573; asc Pinnacles;; 41: len 30; hex 2f706b672f696365746f6f6c732f76656e646f72732f656d756361642f72; asc /pkg/icetools/vendors/emucad/r; (total 74 bytes); 42: len 9; hex 2f6465762f6e756c6c; asc /dev/null;; 43: len 0; hex ; asc ;; 44: len 0; hex ; asc ;; 45: len 0; hex ; asc ;; 46: len 30; hex 73656c6563745b766970725f617661696c5d207275736167655b6d656d3d; asc select[vipr_avail] rusage[mem=; (total 35 bytes); 47: len 0; hex ; asc ;; 48: len 0; hex ; asc ;; 49: len 8; hex 00000000c44a1641; asc J A;; 50: len 8; hex 0000000000000000; asc ;; 51: len 8; hex 00000000d07d1e41; asc } A;; 52: len 8; hex 0000000000000000; asc ;; 53: len 8; hex 0000000000000000; asc ;; 54: len 8; hex 0000000000000000; asc ;; 55: len 8; hex 0000000000000000; asc ;; 56: len 8; hex 0000000000000000; asc ;; 57: len 0; hex ; asc ;; 58: len 8; hex 0000000000402f41; asc @/A;; 59: len 8; hex 0000000000000000; asc ;; 60: len 8; hex 00000000a03fe140; asc ? @;; 61: len 8; hex 0000000000000000; asc ;; 62: len 5; hex 8000000000; asc ;; 63: len 1; hex 00; asc ;; 64: len 4; hex 00000001; asc ;; 65: len 4; hex 00000001; asc ;; 66: len 4; hex 00000001; asc ;; 67: len 1; hex 00; asc ;; 68: len 4; hex 00000001; asc ;; 69: len 4; hex 00000001; asc ;; 70: len 4; hex 00000001; asc ;; 71: len 4; hex 00000001; asc ;; 72: len 4; hex 00000000; asc ;; 73: len 4; hex 00000000; asc ;; 74: len 4; hex 00000000; asc ;; 75: len 4; hex 00000000; asc ;; 76: len 4; hex 635e52fc; asc c^R ;; 77: len 4; hex 63625712; asc cbW ;; 78: len 4; hex 00000000; asc ;; 79: len 4; hex 00000000; asc ;; 80: len 4; hex 00000000; asc ;; 81: len 4; hex 00000002; asc ;; 82: len 4; hex 00000000; asc ;; 83: len 4; hex 00040416; asc ;; 84: len 4; hex 00000000; asc ;; 85: len 4; hex 00000000; asc ;; 86: len 4; hex 00000000; asc ;; 87: len 4; hex 00000000; asc ;; 88: len 4; hex 00000000; asc ;; 89: len 4; hex 00000000; asc ;; 90: SQL NULL; 91: len 4; hex 00000000; asc ;; 92: len 4; hex 00040290; asc ;; 93: len 4; hex 00000000; asc ;; 94: len 4; hex 00000000; asc ;; 95: len 4; hex 00000000; asc ;; 96: len 4; hex 00000000; asc ;; 97: len 4; hex 00000000; asc ;; 98: len 4; hex 0000404b; asc @K;; 99: len 1; hex 01; asc ;; 100: len 1; hex 01; asc ;; 101: len 1; hex 00; asc ;; 102: len 0; hex ; asc ;; 103: len 30; hex 52756d693432685f70696e6e6163656c735f666c6578355f646d5f6d7469; asc Rumi42h_pinnacels_flex5_dm_mti; (total 180 bytes); 104: len 30; hex 73656c6563745b28766970725f617661696c29202626202874797065203d; asc select[(vipr_avail) && (type =; (total 80 bytes); 105: len 30; hex 73656c6563745b2828766970725f617661696c2920262620287479706520; asc select[((vipr_avail) && (type ; (total 83 bytes); 106: len 0; hex ; asc ;; 107: len 0; hex ; asc ;; 108: len 10; hex 2f6f74686572732a3334; asc /others*34;; 109: len 4; hex 00000000; asc ;; 110: len 4; hex 7fffffff; asc ;; 111: len 4; hex 00000000; asc ;; 112: len 4; hex 00000000; asc ;; 113: len 1; hex 00; asc ;; 114: len 4; hex 636259c6; asc cbY ;;   *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345785 page no 58096 n bits 704 index clusterid of table `cacti`.`grid_jobs` trx id 29869596888 lock_mode X locks rec but not gap waiting Record lock, heap no 47 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 00000032; asc 2;; 1: len 8; hex 000000000000173f; asc ?;; 2: len 4; hex 00000000; asc ;; 3: len 4; hex 635e52fa; asc c^R ;;   *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 29869726555 Purge done for trx's n:o < 29822352292 undo n:o < 4526 state: running History list length 21420979 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421733169433296, ACTIVE 0 sec fetching rows mysql tables in use 2, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127803295, OS thread handle 139166254081792, query id 129317661290 rtm-web-03.qualcomm.com 10.23.248.52 rtm Sending data SELECT gt.id, gt.name, COUNT(*) AS graphs FROM graph_local AS gl LEFT JOIN graph_templates AS gt ON gt.id = gl.graph_template_id LEFT JOIN host AS h ON h.id = gl.host_id WHERE (gt.id IS NOT NULL) GROUP BY gl.graph_template_id ORDER BY name Trx read view will not see trx with id >= 29869726548, sees < 29869715778 ---TRANSACTION 421733169603536, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169463088, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169658864, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169646096, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169641840, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 29869726542, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 40 MySQL thread id 127803243, OS thread handle 139165858187008, query id 129317661239 rtm-web-01-new.qualcomm.com 10.23.248.50 rtm Update INSERT INTO lic_services_feature_details (service_id, vendor_daemon, feature_name, subfeature, feature_version, username, groupname, hostname, chkoutid, restype, status, tokens_acquired, tokens_acquired_date, present) VALUES ('394','snpslmd','VCS-3-Apex-Elite-Runtime-Pkg','','v2021.09','qdsp6','','iad-colo2-c21-22','3774885','0','start','1','2022-11-2 8:03:00', 1),('394','snpslmd','VCS-3-Apex-Elite-Runtime-Pkg','','v2021.09','svarakun','','san-p174-e20-20-02','3717534','0','start','1','2022-11-2 8:03:00', 1),('394','snpslmd','VCS-3-Apex-Elite-Runtime-Pkg','','v2021.09','mzangvil','','san-w170 ---TRANSACTION 29869726545, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 14 lock struct(s), heap size 1128, 15 row lock(s), undo log entries 15 MySQL thread id 127803241, OS thread handle 139161843701504, query id 129317661283 rtm-web-01-new.qualcomm.com 10.23.248.50 rtm Update INSERT INTO lic_services_feature_details (service_id, vendor_daemon, feature_name, subfeature, feature_version, username, groupname, hostname, chkoutid, restype, status, tokens_acquired, tokens_acquired_date, present) VALUES ('393','snpslmd','VCS-ELITE-RUNTIME','','v2019.09','qdsp6','','iad-colo2-g24-23','5766871','0','start','1','2022-11-2 7:24:00', 1),('393','snpslmd','VCS-ELITE-RUNTIME','','v2019.09','qdsp6','','iad-colo2-g23-19','5478393','0','start','1','2022-11-2 7:24:00', 1),('393','snpslmd','VCS-ELITE-RUNTIME','','v2019.09','qdsp6','','iad-colo2-j10-18','8993957','0','start','1','2022 ---TRANSACTION 421733169650352, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169612048, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 29869726548, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127803226, OS thread handle 139165832075008, query id 129317661286 rtm-web-01-new.qualcomm.com 10.23.248.50 rtm Update INSERT INTO lic_services_feature_details (service_id, vendor_daemon, feature_name, subfeature, feature_version, username, groupname, hostname, chkoutid, restype, status, tokens_acquired, tokens_acquired_date, present) VALUES ('384','snpslmd','VCS-BASE-RUNTIME','','v2020.06','oharari','','iad-colo1-k30-31','5308574','0','start','1','2022-11-2 5:37:00', 1),('384','snpslmd','VCS-BASE-RUNTIME','','v2020.06','oharari','','iad-colo2-flex-q22-1-08','3586870','0','start','1','2022-11-2 5:37:00', 1),('384','snpslmd','VCS-BASE-RUNTIME','','v2020.06','bgoychma','','iad-colo2-d25-16','1234836','0','start ---TRANSACTION 29869726539, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 14 lock struct(s), heap size 1128, 23 row lock(s), undo log entries 80 MySQL thread id 127803224, OS thread handle 139166232577792, query id 129317661211 rtm-web-01-new.qualcomm.com 10.23.248.50 rtm Update INSERT INTO lic_services_feature_details (service_id, vendor_daemon, feature_name, subfeature, feature_version, username, groupname, hostname, chkoutid, restype, status, tokens_acquired, tokens_acquired_date, present) VALUES ('383','snpslmd','qsb_axi_mst','','v1.0','qdsp6','','san-p174-a11-21-04','11481363','0','start','1','2022-11-2 7:59:00', 1),('383','snpslmd','qsb_axi_mst','','v1.0','qdsp6','','san-p174-a11-24-02','3728542','0','start','1','2022-11-2 7:59:00', 1),('383','snpslmd','qsb_axi_mst','','v1.0','qdsp6','','san-pcdc1-a19-1-16','6407268','0','start','1','2022-11-2 7:59:00', 1),('38 ---TRANSACTION 29869726138, ACTIVE 1 sec fetching rows mysql tables in use 2, locked 1 2 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 4120 MySQL thread id 127803222, OS thread handle 139166616528640, query id 129317657836 rtm-web-01-new.qualcomm.com 10.23.248.50 rtm Sending data INSERT INTO lic_services_feature_history (service_id, vendor_daemon, feature_name, subfeature, feature_version, username, groupname, hostname, chkoutid, restype, status, tokens_acquired, tokens_acquired_date, last_poll_time) SELECT service_id, vendor_daemon, feature_name, subfeature, feature_version, username, groupname, hostname, chkoutid, restype, status, tokens_acquired, tokens_acquired_date, '2022-11-02 08:01:16' AS last_poll_time FROM lic_services_feature_details WHERE service_id='380' AND present='0' AND status='start' Trx read view will not see trx with id >= 29869726138, sees < 29869715778 ---TRANSACTION 421733169616304, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169429040, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169624816, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169531184, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169586512, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169556720, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169518416, ACTIVE 0 sec mysql tables in use 1, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127803114, OS thread handle 139165821937408, query id 129317661876 rtm-web-02.qualcomm.com 10.23.248.51 rtm Sending data SELECT table_name, `partition` FROM grid_table_partitions WHERE (('2022-11-02 07:04:48' BETWEEN min_time AND max_time) OR ('2022-11-02 07:04:48' < min_time AND '2022-11-02 08:04:48' > max_time) OR ('2022-11-02 07:04:48' > min_time AND '2022-11-02 08:04:48' < max_time) OR ('2022-11-02 08:04:48' BETWEEN min_time AND max_time)) AND table_name='grid_jobs_rusage' Trx read view will not see trx with id >= 29869726555, sees < 29869715778 ---TRANSACTION 421733169522672, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169501392, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169484368, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169539696, ACTIVE 0 sec mysql tables in use 1, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127802021, OS thread handle 139165240076032, query id 129317661869 rtm-web-02.qualcomm.com 10.23.248.51 rtm Sending data SELECT table_name, `partition` FROM grid_table_partitions WHERE (('2022-11-02 07:04:48' BETWEEN min_time AND max_time) OR ('2022-11-02 07:04:48' < min_time AND '2022-11-02 08:04:48' > max_time) OR ('2022-11-02 07:04:48' > min_time AND '2022-11-02 08:04:48' < max_time) OR ('2022-11-02 08:04:48' BETWEEN min_time AND max_time)) AND table_name='grid_jobs_rusage' Trx read view will not see trx with id >= 29869726555, sees < 29869715778 ---TRANSACTION 421733169569488, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127801943, OS thread handle 139165865252608, query id 129317661881 rtm-web-02.qualcomm.com 10.23.248.51 rtm Sending data SELECT table_name, `partition` FROM grid_table_partitions WHERE (('2022-11-02 07:04:48' BETWEEN min_time AND max_time) OR ('2022-11-02 07:04:48' < min_time AND '2022-11-02 08:04:48' > max_time) OR ('2022-11-02 07:04:48' > min_time AND '2022-11-02 08:04:48' < max_time) OR ('2022-11-02 08:04:48' BETWEEN min_time AND max_time)) AND table_name='grid_jobs_rusage' Trx read view will not see trx with id >= 29869726555, sees < 29869715778 ---TRANSACTION 29869726550, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 40 lock struct(s), heap size 8312, 1016 row lock(s), undo log entries 1066 MySQL thread id 127801606, OS thread handle 139173816395520, query id 129317661373 rtm-web-02.qualcomm.com 10.23.248.51 rtm Update INSERT INTO grid_jobs_pendreasons (clusterid,jobid,indexid,submit_time,reason,subreason,type,detail,ratio,issusp,start_time,end_time,last_updated,present) VALUES ('119','6767009','155','2022-11-02 07:52:58','3501','slots','15','Resource: slots, Queue: normal, Limit Name: normal.jlu.60000, Limit Value: 60000','0','0','2022-11-02 08:03:34','0000-00-00 00:00:00',NOW(),'1'),('119','6767009','156','2022-11-02 07:52:58','3501','slots','15','Resource: slots, Queue: normal, Limit Name: normal.jlu.60000, Limit Value: 60000','0','0','2022-11-02 08:03:34','0000-00-00 00:00:00',NOW(),'1'),('119','676700 ---TRANSACTION 421733171263376, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 29869720666, ACTIVE 5 sec unlock_row mysql tables in use 1, locked 1 75360 lock struct(s), heap size 7921784, 1 row lock(s) MySQL thread id 127800678, OS thread handle 139165855729408, query id 129317621776 rtm-web-02.qualcomm.com 10.23.248.51 rtm Updating UPDATE grid_jobs SET stat='EXIT', last_updated=NOW() WHERE end_time>'1971-02-01' AND stat NOT IN ('EXIT', 'DONE') AND job_end_logged='1' AND last_updated<'2022-11-02 07:56:46' AND clusterid='86' ---TRANSACTION 421733170016368, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 29869726507, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 2075 lock struct(s), heap size 221304, 0 row lock(s) MySQL thread id 127800320, OS thread handle 139166237492992, query id 129317660896 rtm-web-02.qualcomm.com 10.23.248.51 rtm Updating UPDATE grid_jobs SET prev_stat=stat, stat_changes=CASE WHEN (stat LIKE 'P%') THEN 1 ELSE (CASE WHEN (stat='RUNNING') THEN 2 ELSE (CASE WHEN (stat='USUSP' OR stat='SSUSP' OR stat='ZOMBI') THEN 3 ELSE stat_changes END) END) END WHERE (clusterid='36') AND (prev_stat IS NULL OR prev_stat='') ---TRANSACTION 29869726444, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1610 MySQL thread id 127800430, OS thread handle 139165260044032, query id 129317660185 rtm-web-02.qualcomm.com 10.23.248.51 rtm Update INSERT INTO grid_jobs_rusage (clusterid,jobid,indexid,submit_time,update_time,utime,stime,mem,swap,mem_reserved,npids,npgids,nthreads,num_cpus,pids,pgids) VALUES ('49', '8047462', '121', '2022-11-02 07:58:43', NOW(), '0', '0', '21612', '0', '1048576', '0', '0', '0', '1', '-', '-'),('49', '8043221', '157', '2022-11-02 07:43:58', NOW(), '0', '0', '20756', '0', '1048576', '0', '0', '0', '2', '-', '-'),('49', '8047462', '28', '2022-11-02 07:58:43', NOW(), '0', '0', '15272', '0', '1048576', '0', '0', '0', '1', '-', '-'),('49', '8047846', '29', '2022-11-02 08:00:21', NOW(), '0', '0', '22816', '0', ---TRANSACTION 29869720145, ACTIVE 6 sec fetching rows mysql tables in use 1, locked 1 12 lock struct(s), heap size 1128, 60 row lock(s), undo log entries 30 MySQL thread id 127800433, OS thread handle 139166257153792, query id 129317610772 rtm-web-02.qualcomm.com 10.23.248.51 rtm Updating UPDATE grid_jobs FORCE INDEX (clusterid_stat_last_updated) SET psusp_time=psusp_time+301 WHERE clusterid='49' AND last_updated>='2022-11-02 08:02:17' AND stat='PSUSP' ---TRANSACTION 29869715778, ACTIVE 15 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1128, 9 row lock(s), undo log entries 9 MySQL thread id 127800107, OS thread handle 139165815179008, query id 129317532612 rtm-web-02.qualcomm.com 10.23.248.51 rtm Update INSERT INTO grid_jobs_pendreasons (clusterid,jobid,indexid,submit_time,reason,subreason,type,detail,ratio,issusp,start_time,end_time,last_updated,present) VALUES ('1','3310087','0','2022-09-01 12:02:31','4401','slots','15','Resource: slots, Limit Name: crae.arineut.allow, Limit Value: 0','0.20','0','2022-11-02 08:02:41','0000-00-00 00:00:00',NOW(),'1'),('1','5495895','0','2022-10-31 05:49:46','4705','-1','15','Limit Name: int.jlu.default, Limit Value: 7','0.50','0','2022-11-02 08:02:41','0000-00-00 00:00:00',NOW(),'1'),('1','7201579','0','2022-11-02 03:46:31','62','-1','2','','0','0','2022-1 ------- TRX HAS BEEN WAITING 15 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 344025 page no 243541 n bits 480 index clusterid_end_time of table `cacti`.`grid_jobs_pendreasons` trx id 29869715778 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 412 PHYSICAL RECORD: n_fields 8; compact format; info bits 0 0: len 4; hex 00000001; asc ;; 1: len 4; hex 00000000; asc ;; 2: len 8; hex 00000000007324ee; asc s$ ;; 3: len 4; hex 00000000; asc ;; 4: len 4; hex 6352b536; asc cR 6;; 5: len 1; hex 00; asc ;; 6: len 4; hex 0000003e; asc >;; 7: len 2; hex 2d31; asc -1;;   ------------------ ---TRANSACTION 421733169931248, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169909968, ACTIVE 4 sec fetching rows mysql tables in use 1, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127799887, OS thread handle 139162986395392, query id 129317631827 rtm-web-02.qualcomm.com 10.23.248.51 rtm Creating sort index SELECT SUBSTRING_INDEX(SUBSTRING(projectName,10), '.', 1) AS ptag, GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(projectName, '.', 1) SEPARATOR ', ') AS ptags, SUM(CASE WHEN stat = 'PEND' THEN num_cpus ELSE 0 END) AS pend_slots, SUM(CASE WHEN stat = 'RUNNING' THEN num_cpus ELSE 0 END) AS run_slots, SUM(CASE WHEN stat IN ('DONE', 'EXIT') AND end_time >= DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()-300), INTERVAL 1 HOUR) THEN num_cpus ELSE 0 END) AS tput_hour, SUM(CASE WHEN stat IN ('DONE', 'EXIT') AND end_time >= DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()-300), INTERVAL 5 MINUTE) THEN num_cpus ELSE 0 END) AS t Trx read view will not see trx with id >= 29869721783, sees < 29869715778 ---TRANSACTION 421733169892944, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169552464, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169543952, ACTIVE 1 sec mysql tables in use 4, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127799876, OS thread handle 139163921618688, query id 129317653855 rtm-web-02.qualcomm.com 10.23.248.51 rtm Creating sort index SELECT clusterid, queue, SUM(1gSlots) AS free1gSlots, SUM(2gSlots) AS free2gSlots, SUM(4gSlots) AS free4gSlots, SUM(8gSlots) AS free8gSlots, SUM(16gSlots) AS free16gSlots, SUM(32gSlots) AS free32gSlots, SUM(64gSlots) AS free64gSlots, SUM(128gSlots) AS free128gSlots, SUM(256gSlots) AS free256gSlots, SUM(512gSlots) AS free512gSlots, SUM(1024gSlots) AS free1024gSlots FROM ( SELECT clusterid, host, queue, (CASE WHEN unReservedMem > 1024 THEN FLOOR(LEAST(freeSlots, unReservedMem/1024)) ELSE 0 END) AS 1gSlots, (CASE WHEN unReservedMem > 2048 THEN FLOOR(LEAST(freeSlots, unReservedMem/2048)) E Trx read view will not see trx with id >= 29869725578, sees < 29869715778 ---TRANSACTION 421733169505648, ACTIVE 1 sec fetching rows mysql tables in use 3, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127799868, OS thread handle 139173820081920, query id 129317657522 rtm-web-02.qualcomm.com 10.23.248.51 rtm Sending data SELECT gj.mailUser, gj.user, gj.rlimit_max_wallt AS runlimit, gj.runtimeEstimation AS runestimation, grj.* FROM grid_jobs_runtime grj INNER JOIN grid_jobs gj ON grj.clusterid = gj.clusterid AND grj.jobid = gj.jobid AND grj.indexid = gj.indexid AND grj.submit_time = gj.submit_time WHERE gj.clusterid = '119' AND gj.mailuser = '' AND gj.user = 'rampra' AND grj.type = '1' AND grj.notified = 0 Trx read view will not see trx with id >= 29869726077, sees < 29869715778 ---TRANSACTION 421733169446064, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 29869720330, ACTIVE 6 sec updating or deleting mysql tables in use 1, locked 1 4437 lock struct(s), heap size 516216, 130312 row lock(s), undo log entries 130312 MySQL thread id 127799865, OS thread handle 139166260532992, query id 129317616252 rtm-web-02.qualcomm.com 10.23.248.51 rtm Updating UPDATE grid_blstat_users SET present=0 WHERE lsid=1 ---TRANSACTION 421733169573744, ACTIVE 62 sec starting index read mysql tables in use 2, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127799864, OS thread handle 139165809342208, query id 129317050921 rtm-web-02.qualcomm.com 10.23.248.51 rtm Sending data SELECT reason, detail, type, SUM(pendTime) AS pendTime, COUNT(DISTINCT jobid, indexid) AS jobs FROM ( SELECT prm.reason, gjp.detail, gjp.jobid, gjp.indexid, IF(end_time = '0000-00-00', 'active', 'inactive') AS type, IF(end_time = '0000-00-00', UNIX_TIMESTAMP() - UNIX_TIMESTAMP(start_time), UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS pendTime FROM grid_jobs_pendreasons AS gjp INNER JOIN grid_resreq_jobs AS grj ON grj.clusterid = gjp.clusterid AND grj.jobid = gjp.jobid AND grj.indexid = gjp.indexid INNER JOIN grid_jobs_pendreason_maps AS prm ON gjp.reason = prm.reason_code AND gjp. Trx read view will not see trx with id >= 29869661414, sees < 29869599607 ---TRANSACTION 421733169548208, ACTIVE 3 sec fetching rows mysql tables in use 2, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127799860, OS thread handle 139165864945408, query id 129317642251 rtm-web-02.qualcomm.com 10.23.248.51 rtm Sending data SELECT grid_hostgroups.clusterid, grid_hostgroups.groupName, avg(avg_efficiency) AS efficiency, avg(avg_mem) AS avg_mem, max(max_mem) AS max_mem, avg(avg_max_swap) AS avg_swap, max(max_max_swap) AS max_swap, sum(total_cpu) AS total_cpu, SUM(sum_numRUN) AS numRUN, SUM(sum_numJOBS) AS numJOBS, SUM(mem_reserved) AS memReserved, SUM(mem_used) AS memUsed, SUM(mem_requested) AS memRequested, NOW() AS last_updated, '1' as present FROM (SELECT clusterid, exec_host, (SUM(CASE WHEN stat IN ('RUNNING','PROV') AND run_time > '60' THEN (stime+utime) ELSE 0 END) / SUM(CASE WHEN stat IN ('RUNNING','PROV') AN Trx read view will not see trx with id >= 29869723185, sees < 29869715778 ---TRANSACTION 421733169599280, ACTIVE 0 sec starting index read mysql tables in use 2, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127799802, OS thread handle 139165821015808, query id 129317661631 rtm-web-02.qualcomm.com 10.23.248.51 rtm Sending data SELECT SUM(shares) FROM grid_queues_shares AS gqs FORCE INDEX (clusterid_user_or_group) INNER JOIN grid_users_or_groups AS gug ON gqs.clusterid = gug.clusterid AND gqs.user_or_group = gug.user_or_group WHERE type = 'G' AND shareAcctPath = '/QCOM/dv/dv.soc/dv.soc.any/dv.soc.any.all/dv.soc.any.all.p3' AND queue = 'p0Q' AND gqs.clusterid = '54' Trx read view will not see trx with id >= 29869726554, sees < 29869715778 ---TRANSACTION 421733169560976, ACTIVE 0 sec starting index read mysql tables in use 2, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127799774, OS thread handle 139166256846592, query id 129317661759 rtm-web-02.qualcomm.com 10.23.248.51 rtm Sending data SELECT SUM(shares) FROM grid_queues_shares AS gqs FORCE INDEX (clusterid_user_or_group) INNER JOIN grid_users_or_groups AS gug ON gqs.clusterid = gug.clusterid AND gqs.user_or_group = gug.user_or_group WHERE type = 'G' AND shareAcctPath = '/QCOM/rad/rad.all/rad.all.any/rad.all.any.all/rad.all.any.all.helios' AND queue = 'priority' AND gqs.clusterid = '36' Trx read view will not see trx with id >= 29869726555, sees < 29869715778 ---TRANSACTION 421733169390736, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169382224, ACTIVE 0 sec mysql tables in use 1, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127798550, OS thread handle 139166211073792, query id 129317661882 rtm-web-02.qualcomm.com 10.23.248.51 rtm Sending data SELECT table_name, `partition` FROM grid_table_partitions WHERE (('2022-11-02 07:04:48' BETWEEN min_time AND max_time) OR ('2022-11-02 07:04:48' < min_time AND '2022-11-02 08:04:48' > max_time) OR ('2022-11-02 07:04:48' > min_time AND '2022-11-02 08:04:48' < max_time) OR ('2022-11-02 08:04:48' BETWEEN min_time AND max_time)) AND table_name='grid_jobs_rusage' Trx read view will not see trx with id >= 29869726555, sees < 29869715778 ---TRANSACTION 421733169416272, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169424784, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169480112, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169454576, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169492880, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169407760, ACTIVE 14 sec starting index read mysql tables in use 2, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127747436, OS thread handle 139165804427008, query id 129317547657 rtm-web-02.qualcomm.com 10.23.248.51 rtm Sending data SELECT COUNT(gj.clusterid) FROM grid_jobs AS gj INNER JOIN grid_jobs_removal_1667400140 AS tt ON gj.clusterid=tt.clusterid AND gj.jobid=tt.jobid AND gj.indexid=tt.indexid AND gj.submit_time=tt.submit_time Trx read view will not see trx with id >= 29869716370, sees < 29869715752 ---TRANSACTION 421733169394992, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169526928, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169654608, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169403504, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733170458992, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733170412176, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169441808, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169437552, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169514160, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169509904, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169471600, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169450320, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169590768, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169565232, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169475856, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169488624, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169467344, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169399248, ACTIVE 17544 sec mysql tables in use 4, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 127550346, OS thread handle 139166213838592, query id 129053995241 rtm-web-02.qualcomm.com 10.23.248.51 rtm Sending data SELECT gj.clusterid, clustername, queue, user, gjpm.reason, gjp.detail, gjpm.reason_code, gjpm.sub_reason_code, gjp.issusp, COUNT(DISTINCT gj.clusterid, gj.jobid, gj.indexid, gj.submit_time) AS jobs, SUM( IF(gjp.end_time='0000-00-00 00:00:00', UNIX_TIMESTAMP() - UNIX_TIMESTAMP(gjp.start_time), UNIX_TIMESTAMP(gjp.end_time) - UNIX_TIMESTAMP(gjp.start_time) ) ) AS total_pend, MAX( IF(gjp.end_time='0000-00-00 00:00:00', UNIX_TIMESTAMP() - UNIX_TIMESTAMP(gjp.start_time), UNIX_TIMESTAMP(gjp.end_time) - UNIX_TIMESTAMP(gjp.start_time) ) ) AS max_pend, AVG( IF(gjp.end_time = '0000-00-00 00:00:00', UNIX Trx read view will not see trx with id >= 29840769106, sees < 29839568648 ---TRANSACTION 421733169497136, ACTIVE 17167 sec fetching rows mysql tables in use 3, locked 3 15470 lock struct(s), heap size 2416760, 5651188 row lock(s) MySQL thread id 127521359, OS thread handle 139166219982592, query id 129057620323 rtm-web-02.qualcomm.com 10.23.248.51 rtm Sending data DELETE gj2 FROM grid_jobs_pendreasons AS gj2 INNER JOIN ( SELECT gj.clusterid, gj.jobid, gj.indexid, gj.submit_time FROM grid_jobs_pendreasons AS gj INNER JOIN grid_jobs_removal_1667384077 AS tt ON gj.clusterid=tt.clusterid AND gj.jobid=tt.jobid AND gj.indexid=tt.indexid AND gj.submit_time=tt.submit_time LIMIT 30000 ) sub1 ON gj2.clusterid=sub1.clusterid AND gj2.jobid=sub1.jobid AND gj2.submit_time=sub1.submit_time AND gj2.indexid=sub1.indexid ---TRANSACTION 421733169582256, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169377968, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169458832, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169420528, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169705680, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169535440, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169373712, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169412016, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169369456, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421733169365200, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 3516901709 OS file reads, 112746631412 OS file writes, 111789196 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 11927.01 writes/s, 8.40 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 109, free list len 447369, seg size 447479, 29076 merges merged operations: insert 227428, delete mark 24, delete 22 discarded operations: insert 1244596, delete mark 2, delete 2 0.00 hash searches/s, 1134604.88 non-hash searches/s --- LOG --- Log sequence number 585906488083118 Log flushed up to 585906476653305 Pages flushed up to 585904582183148 Last checkpoint at 585898712810360 0 pending log flushes, 0 pending chkp writes 6453501819 log i/o's done, 534.60 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 1073876041728 Dictionary memory allocated 7098422424 Buffer pool size 64520000 Free buffers 1191 Database pages 64518132 Old database pages 23816243 Modified db pages 742942 Percent of dirty pages(LRU & free pages): 1.152 Max dirty pages percent: 0.000 Pending reads 0 Pending writes: LRU 0, flush list 2805 Pages made young 4143441040, not young 295625028000 483.30 youngs/s, 0.00 non-youngs/s Pages read 3516429542, created 7522968851, written 106298421324 0.00 reads/s, 921.02 creates/s, 10830.63 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: 64518132, unzip_LRU len: 0 I/O sum[727654]:cur[157317], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 12 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 107372917576, updated 550961033128, deleted 14240364072, read 40635538834126 22870.03 inserts/s, 112115.18 updates/s, 557.09 deletes/s, 5372325.73 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ MariaDB Top 08:07:55 up 128 days, 1:37, 2 users, load average: 53.00, 42.26, 41.66 --------------------------------------------------------- Host: rtm-db-02 Pending Items: 0 Sleeping Processes: 71 Spine Processes Running: 0 Boost Processes: 0 Memory Topology --------------------------------------------------------- total used free shared buff/cache available Mem: 1510 1206 96 1 207 295 Swap: 31 16 15 MariaDB Processes +-----------+------+--------------------------------+---------+----------------------------------------------------------------------------------------------------------------------------------------------+ | id | user | state | time | info | +-----------+------+--------------------------------+---------+----------------------------------------------------------------------------------------------------------------------------------------------+ | 127550346 | rtm | Sending data | 17731.1 | SELECT gj.clusterid, clustername, queue, user, gjpm.reason, gjp.detail, gjpm.reason_code, gjpm.sub_reason_code, gjp.issusp, COUNT(DISTINCT g | | 127521359 | rtm | Sending data | 17353.7 | DELETE gj2 FROM grid_jobs_pendreasons AS gj2 INNER JOIN ( SELECT gj.clusterid, gj.jobid, gj.indexid, gj.submit_time FROM grid_jobs_pendreaso | | 127747436 | rtm | Deleting from reference tables | 48.4 | DELETE gj2 FROM grid_jobs AS gj2 INNER JOIN ( SELECT gj.clusterid, gj.jobid, gj.indexid, gj.submit_time FROM grid_jobs AS gj INNER JOIN grid | | 127805022 | rtm | Updating | 35.5 | UPDATE grid_jobs SET job_end_logged=0 WHERE clusterid='49' AND stat NOT IN ('DONE', 'EXIT') AND job_end_logged='1' | | 127805177 | rtm | Updating | 35.4 | UPDATE grid_jobs SET job_end_logged=0 WHERE clusterid='49' AND stat NOT IN ('DONE', 'EXIT', 'PEND', 'PSUSP') AND job_end_logged='1' | | 127805117 | rtm | Updating | 35.4 | UPDATE grid_jobs SET job_end_logged=0 WHERE clusterid='36' AND stat NOT IN ('DONE', 'EXIT') AND job_end_logged='1' | | 127805769 | rtm | Updating | 35.2 | UPDATE grid_jobs SET job_end_logged=0 WHERE clusterid='86' AND stat NOT IN ('DONE', 'EXIT', 'PEND', 'PSUSP') AND job_end_logged='1' | | 127804820 | rtm | Updating | 35.2 | UPDATE grid_jobs SET job_end_logged=0 WHERE clusterid='54' AND stat NOT IN ('DONE', 'EXIT') AND job_end_logged='1' | | 127805928 | rtm | Updating | 35.1 | UPDATE grid_jobs SET job_end_logged=0 WHERE clusterid='86' AND stat NOT IN ('DONE', 'EXIT') AND job_end_logged='1' | | 127805900 | rtm | Updating | 34.8 | UPDATE grid_jobs SET job_end_logged=0 WHERE clusterid='119' AND stat NOT IN ('DONE', 'EXIT') AND job_end_logged='1' | | 127805278 | rtm | Updating | 34.0 | UPDATE grid_jobs SET job_end_logged=0 WHERE clusterid='119' AND stat NOT IN ('DONE', 'EXIT', 'PEND', 'PSUSP') AND job_end_logged='1' | | 127805992 | rtm | Updating | 31.9 | UPDATE grid_jobs SET job_end_logged=0 WHERE clusterid='54' AND stat NOT IN ('DONE', 'EXIT', 'PEND', 'PSUSP') AND job_end_logged='1' | | 127806087 | rtm | Sending data | 29.6 | SELECT gj.clusterid, clustername, queue, user, gjpm.reason, gjp.detail, gjpm.reason_code, gjpm.sub_reason_code, gjp.issusp, COUNT(DISTINCT g | | 127806097 | rtm | Creating sort index | 28.4 | SELECT SUBSTRING_INDEX(projectName, '.', 1) AS ptag, GROUP_CONCAT(DISTINCT REPLACE(SUBSTRING_INDEX(projectName, '.', 3), CONCAT(SUBSTRING_IN | | 127806099 | rtm | Sending data | 25.8 | UPDATE grid_jobs_pendhist_hourly AS gjpd INNER JOIN analytics_reason_classes AS arc ON gjpd.reason = arc.reason_code AND gjpd.subreason = ar | +-----------+------+--------------------------------+---------+----------------------------------------------------------------------------------------------------------------------------------------------+ Poller Not running

Larry Adams added a comment - 2022-11-02 15:14 The delete thread was actually stuck (not started) as when I killed it, there was no rollback. So, the "Sending Data" message was bonkers.