Vryjdfkk

We have a mysql database (version 5.7) with following configuration:

skip-host-cache

skip-name-resolve

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

secure-file-priv=/var/lib/mysql-files

user=mysql



symbolic-links=0



log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid



transaction-isolation=READ-COMMITTED

max_allowed_packet=32M

max_connections=1000

innodb_buffer_pool_size=2G 

innodb_buffer_pool_instances=2

innodb_thread_concurrency=64

innodb_concurrency_tickets=5000

innodb_thread_sleep_delay=10000

innodb_commit_concurrency=32

The total size of the database is around 1.1GB.

We are getting long semaphore waiting warnings and it ends up crashing the mysql server. Please find the logs below:

2017-06-23T03:13:57.542728Z 0 [Warning] InnoDB: A long semaphore wait:

--Thread 140357336332032 has waited at row0sel.cc line 5006 for 241.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

2017-06-23T03:13:57.556914Z 0 [Warning] InnoDB: A long semaphore wait:

--Thread 140357326214912 has waited at buf0lru.cc line 1298 for 241.00 seconds the semaphore:

Mutex at 0x425f808, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1



InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:

InnoDB: Pending preads 0, pwrites 0



=====================================

2017-06-23 05:14:08 0x7fa79f95c700 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 63 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 85797 srv_active, 0 srv_shutdown, 15834 srv_idle

srv_master_thread log flush and writes: 101631

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 127814

--Thread 140357337929472 has waited at row0sel.cc line 5006 for 230.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357340858112 has waited at fsp0fsp.cc line 2613 for 223.00 seconds the semaphore:

X-lock on RW-latch at 0x78fe3e8 created in file fil0fil.cc line 1381

a writer (thread id 140357339526912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: ffffffffe0000000

Last time read locked in file not yet reserved line 0

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/fsp/fsp0fsp.cc line 3361

--Thread 140357719758592 has waited at trx0trx.cc line 1182 for 157.00 seconds the semaphore:

Mutex at 0x78db1b0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1



--Thread 140357333137152 has waited at row0sel.cc line 5006 for 214.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357326747392 has waited at row0sel.cc line 5006 for 249.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357345916672 has waited at row0sel.cc line 5006 for 232.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357331539712 has waited at row0sel.cc line 5006 for 205.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357319825152 has waited at trx0undo.ic line 171 for 51.00 seconds the semaphore:

X-lock on RW-latch at 0x7fa80f99cef8 created in file buf0buf.cc line 1457

a writer (thread id 140357789882112) has reserved it in mode  SX

number of readers 0, waiters flag 1, lock_word: 10000000

Last time read locked in file trx0undo.ic line 190

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/buf/buf0flu.cc line 1198

--Thread 140357324617472 has waited at row0sel.cc line 5006 for 224.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357337130752 has waited at row0sel.cc line 5006 for 209.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357348845312 has waited at fsp0fsp.cc line 3361 for 215.00 seconds the semaphore:

X-lock on RW-latch at 0x7892d08 created in file fil0fil.cc line 1381

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: ffffffffe0000000

Last time read locked in file not yet reserved line 0

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/fsp/fsp0fsp.cc line 2613

--Thread 140357344585472 has waited at row0sel.cc line 5006 for 251.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357891815168 has waited at trx0trx.cc line 1182 for 122.00 seconds the semaphore:

Mutex at 0x78db1b0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1



--Thread 140357342189312 has waited at buf0lru.cc line 1120 for 151.00 seconds the semaphore:

Mutex at 0x425f808, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1



--Thread 140357349377792 has waited at row0sel.cc line 5006 for 250.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357320091392 has waited at fsp0fsp.cc line 2613 for 247.00 seconds the semaphore:

X-lock on RW-latch at 0x78fe3e8 created in file fil0fil.cc line 1381

a writer (thread id 140357339526912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: ffffffffe0000000

Last time read locked in file not yet reserved line 0

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/fsp/fsp0fsp.cc line 3361

--Thread 140357337663232 has waited at row0sel.cc line 5006 for 251.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357336332032 has waited at row0sel.cc line 5006 for 252.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357320357632 has waited at fsp0fsp.cc line 2613 for 243.00 seconds the semaphore:

X-lock on RW-latch at 0x78fe3e8 created in file fil0fil.cc line 1381

a writer (thread id 140357339526912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: ffffffffe0000000

Last time read locked in file not yet reserved line 0

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/fsp/fsp0fsp.cc line 3361

--Thread 140357339526912 has waited at buf0lru.cc line 1298 for 249.00 seconds the semaphore:

Mutex at 0x425f808, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1



--Thread 140357761988352 has waited at trx0purge.cc line 531 for 247.00 seconds the semaphore:

Mutex at 0x78db1b0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1



--Thread 140357333403392 has waited at trx0trx.cc line 1182 for 233.00 seconds the semaphore:

Mutex at 0x78db1b0, Mutex REDO_RSEG created trx0rseg.cc:211, lock var 1



--Thread 140357324883712 has waited at row0sel.cc line 5006 for 239.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357326214912 has waited at buf0lru.cc line 1298 for 252.00 seconds the semaphore:

Mutex at 0x425f808, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1



--Thread 140357335000832 has waited at row0sel.cc line 5006 for 249.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357894477568 has waited at row0sel.cc line 5006 for 250.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357341656832 has waited at row0sel.cc line 5006 for 251.00 seconds the semaphore:

S-lock on RW-latch at 0x7fa7de53e6c0 created in file buf0buf.cc line 1457

a writer (thread id 140357326214912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0sel.cc line 5006

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/row/row0upd.cc line 2831

--Thread 140357344319232 has waited at fsp0fsp.cc line 2613 for 242.00 seconds the semaphore:

X-lock on RW-latch at 0x78fe3e8 created in file fil0fil.cc line 1381

a writer (thread id 140357339526912) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: ffffffffe0000000

Last time read locked in file not yet reserved line 0

Last time write locked in file /export/home3/pb2/build/sb_0-19016729-1464156204.56/rpm/BUILD/mysql-5.7.13/mysql-5.7.13/storage/innobase/fsp/fsp0fsp.cc line 3361

OS WAIT ARRAY INFO: signal count 143834

RW-shared spins 0, rounds 73033, OS waits 16888

RW-excl spins 0, rounds 642844, OS waits 6196

RW-sx spins 143, rounds 2142, OS waits 40

Spin rounds per wait: 73033.00 RW-shared, 642844.00 RW-excl, 14.98 RW-sx

------------

TRANSACTIONS

------------

Trx id counter 250411503

Purge done for trx's n:o < 250411300 undo n:o < 0 state: running

History list length 1928

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 421835180840896, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180819920, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180819008, not started creating index

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180813536, not started creating index

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180812624, not started creating index

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180810800, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180808976, not started creating index

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180808064, not started creating index

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180798944, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180796208, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180817184, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180815360, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180799856, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180814448, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180787088, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180820832, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180795296, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180802592, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180836336, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180803504, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180830864, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180804416, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180829952, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180826304, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180825392, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180792560, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180791648, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180790736, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180788000, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180785264, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180784352, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421835180783440, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 250411495, ACTIVE 35 sec

1 lock struct(s), heap size 1136, 0 row lock(s)

MySQL thread id 152, OS thread handle 140357349644032, query id 345708715 10.10.0.23 neon cleaning up

---TRANSACTION 250411490, ACTIVE 51 sec updating or deleting, thread declared inside InnoDB 4999

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1136, 2 row lock(s)

MySQL thread id 4001, OS thread handle 140357319825152, query id 345707833 10.10.0.26 neon updating

UPDATE SCH_QRTZ_SIMPLE_TRIGGERS SET REPEAT_COUNT = -1, REPEAT_INTERVAL = 300000, TIMES_TRIGGERED = 10468 WHERE TRIGGER_NAME = 'datajob-3' AND TRIGGER_GROUP = 'DEFAULT'

---TRANSACTION 250411450, ACTIVE 151 sec updating or deleting, thread declared inside InnoDB 4999

mysql tables in use 1, locked 1

2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 132, OS thread handle 140357342189312, query id 345702398 10.10.0.26 neon updating

UPDATE SCH_QRTZ_TRIGGERS SET TRIGGER_STATE = 'ACQUIRED' WHERE TRIGGER_NAME = 'datajob-3' AND TRIGGER_GROUP = 'DEFAULT' AND TRIGGER_STATE = 'WAITING'

---TRANSACTION 250411359, ACTIVE 215 sec updating or deleting, thread declared inside InnoDB 4999

mysql tables in use 1, locked 1

2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 124, OS thread handle 140357348845312, query id 345697370 10.10.0.22 neon updating

update broadcast_traffic_info SET AVAILABLE_TPS = 180,CURRENT_TPS = 0, LAST_UPDATED = '2017-06-23 05:10:33.031'  where APPLICATION_INSTANCE_ID = 36771

---TRANSACTION 250411294, ACTIVE 249 sec starting index read, thread declared inside InnoDB 5000

mysql tables in use 1, locked 1

1 lock struct(s), heap size 1136, 0 row lock(s)

MySQL thread id 72095, OS thread handle 140357335000832, query id 345695543 10.201.40.32 neon updating

update oz_minor_splits_task_status set last_proccesed_key=258844680790 where split_start_key=258844552791

---TRANSACTION 250411293, ACTIVE 249 sec

mysql tables in use 5, locked 1

21 lock struct(s), heap size 3520, 0 row lock(s)

MySQL thread id 162, OS thread handle 140357339526912, query id 345695524 10.10.0.22 neon executing

UPDATE app_instance ap

   SET broadcast_complete_date = CURRENT_TIMESTAMP(), status_id = 31

 WHERE application_instance_type = 8

   AND status_id = 25

   AND EXISTS

       (SELECT 1

         FROM (select * from app_instance) ap

        WHERE application_instance_id = (SELECT rewarding_app_instance_id

                                           FROM app_seeding_bc_child_map

                                          WHERE parent_app_instance_id = ap.application_instance_id)

          AND status_id IN (31, 71))

   AND EXISTS

       (SELECT 1

          FROM (select * from app_instance) ap

         WHERE application_instance_id = (SELECT messaging_app_instan

---TRANSACTION 250411292, ACTIVE 249 sec starting index read, thread declared inside InnoDB 5000

mysql tables in use 1, locked 1

1 lock struct(s), heap size 1136, 0 row lock(s)

MySQL thread id 72105, OS thread handle 140357326747392, query id 345695464 10.201.40.32 neon updating

update oz_minor_splits_task_status set last_proccesed_key=258842169001 where split_start_key=258842037002

---TRANSACTION 250411291, ACTIVE 250 sec starting index read, thread declared inside InnoDB 5000

mysql tables in use 1, locked 1

1 lock struct(s), heap size 1136, 0 row lock(s)

MySQL thread id 72089, OS thread handle 140357894477568, query id 345695430 10.201.40.32 neon updating

update oz_minor_splits_task_status set last_proccesed_key=258846050490 where split_start_key=258845910491

---TRANSACTION 250411288, ACTIVE 250 sec starting index read, thread declared inside InnoDB 5000

mysql tables in use 1, locked 1

1 lock struct(s), heap size 1136, 0 row lock(s)

MySQL thread id 72101, OS thread handle 140357349377792, query id 345695411 10.201.40.32 neon updating

update oz_minor_splits_task_status set last_proccesed_key=258844173954 where split_start_key=258844021953

---TRANSACTION 250411287, ACTIVE 251 sec starting index read, thread declared inside InnoDB 5000

mysql tables in use 1, locked 1

1 lock struct(s), heap size 1136, 0 row lock(s)

MySQL thread id 72154, OS thread handle 140357341656832, query id 345695407 10.201.40.31 neon updating

update oz_minor_splits_task_status set last_proccesed_key=258842899988 where split_start_key=258842875989

---TRANSACTION 250411286, ACTIVE 251 sec starting index read, thread declared inside InnoDB 5000

mysql tables in use 1, locked 1

1 lock struct(s), heap size 1136, 0 row lock(s)

MySQL thread id 72107, OS thread handle 140357344585472, query id 345695397 10.201.40.32 neon updating

update oz_minor_splits_task_status set last_proccesed_key=258845459403 where split_start_key=258845313404

---TRANSACTION 250411285, ACTIVE 251 sec starting index read, thread declared inside InnoDB 5000

mysql tables in use 1, locked 1

1 lock struct(s), heap size 1136, 0 row lock(s)

MySQL thread id 72152, OS thread handle 140357337663232, query id 345695393 10.201.40.36 neon updating

update oz_minor_splits_task_status set last_proccesed_key=258849464211 where split_start_key=258849444212

---TRANSACTION 250411284, ACTIVE 252 sec starting index read, thread declared inside InnoDB 5000

mysql tables in use 1, locked 1

1 lock struct(s), heap size 1136, 0 row lock(s)

MySQL thread id 72100, OS thread handle 140357336332032, query id 345695389 10.201.40.32 neon updating

update oz_minor_splits_task_status set last_proccesed_key=258843993098 where split_start_key=258843841019

---TRANSACTION 250411283, ACTIVE 252 sec updating or deleting, thread declared inside InnoDB 4999

mysql tables in use 1, locked 1

2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 72150, OS thread handle 140357326214912, query id 345695374 10.201.40.31 neon updating

update oz_minor_splits_task_status set last_proccesed_key=258841030037 where split_start_key=258840620159

---TRANSACTION 250405337, ACTIVE 753 sec

2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 38, OS thread handle 140357894211328, query id 345649410 10.10.0.17 neon cleaning up

---TRANSACTION 249991023, ACTIVE 28320 sec

1 lock struct(s), heap size 1136, 0 row lock(s)

MySQL thread id 118, OS thread handle 140357343520512, query id 345710975 10.10.0.23 neon cleaning up

--------

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 (log 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 (read thread)

I/O thread 6 state: complete io for buf page (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)

I/O thread 9 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:, log i/o's:, sync i/o's:

Pending flushes (fsync) log: 0; buffer pool: 2

34368 OS file reads, 26520041 OS file writes, 775324 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.08 writes/s, 0.08 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 104, seg size 106, 449 merges

merged operations:

 insert 182, delete mark 4, delete 4

discarded operations:

 insert 0, delete mark 0, delete 0

Hash table size 553229, node heap has 87 buffer(s)

Hash table size 553229, node heap has 57 buffer(s)

Hash table size 553229, node heap has 52 buffer(s)

Hash table size 553229, node heap has 63 buffer(s)

Hash table size 553229, node heap has 9 buffer(s)

Hash table size 553229, node heap has 9 buffer(s)

Hash table size 553229, node heap has 4 buffer(s)

Hash table size 553229, node heap has 3 buffer(s)

24.94 hash searches/s, 28.97 non-hash searches/s

---

LOG

---

Log sequence number 35973333595

Log flushed up to   35973333595

Pages flushed up to 35973208487

Last checkpoint at  35973207485

0 pending log flushes, 0 pending chkp writes

723224 log i/o's done, 0.08 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total large memory allocated 2198863872

Dictionary memory allocated 21083322

What is the real cause of the crash ? Is it caused by long running queries or wrong configurations ?

Please provide SHOW CREATE TABLE. In particular, do you have:

INDEX(trigger_name, trigger_group, trigger_state)

INDEX(application_instance_id)

INDEX(split_start_key)

INDEX(application_instance_type, status id)

And try turning your nested subqueries into JOINs.

How much RAM do you have?