Re: MySQL crashed and process in zombie state
Posted by:
Sophal Lee
Date: November 15, 2023 06:43PM
`signal 6` is SIGABRT and the cause is because the semaphore has waiting for quite some time.
I have further analyzed the logs and it appears to be one query causing semaphore to wait. I'll have the query's logic looked and possibly have other optimisation performed on the table.
```
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 363360
--Thread 140054537078528 has waited at row0ins.cc line 2409 for 941 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x7f6117dae9d8 created in file buf0buf.cc line 794
a writer (thread id 140054537078528) has reserved it in mode wait exclusive
number of readers 1 (thread id 140051430221568), waiters flag 0, lock_word: ffffffffffffffff
Last time read locked in file row0sel.cc line 4857
Last time write locked in file /builddir/build/BUILD/mysql-8.0.32/storage/innobase/buf/buf0buf.cc line 4936
OS WAIT ARRAY INFO: signal count 813966
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2698831
Purge done for trx's n:o < 2698215 undo n:o < 0 state: running but idle
History list length 215
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421530309145384, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421530309144576, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421530309143768, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421530309148616, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421530309146192, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421530309142960, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421530309142152, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 2698715, ACTIVE 942 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s)
MySQL thread id 138425, OS thread handle 140054537078528, query id 34297984 webapps-m25 192.168.16.23 dms update
INSERT INTO `bg_cms_series` (`brch`,`model`,`series`,`description`,`price`, `gstCode`,`image`) VALUES ('Z','200SAH5G15GT','Mazda2','MAZDA2 S 6AUTO G15 GT HATCH',25381.82,'','') ON DUPLICATE KEY UPDATE `series` = 'Mazda2',`description` = 'MAZDA2 S 6AUTO G15 GT HATCH',`price` = 25381.82,`gstCode` = ''
```