mysql 服务异常crash 原因收集

1. 打开文件描述符数超出限制，造成crash 重启，ERROR信息，提示too many open files0

2022-06-27T08:03:56.201006+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 407 exceeds the limit 400
2022-06-27T08:03:56.201011+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 407 exceeds the limit 400
2022-06-27T08:13:20.471561+08:00 0 [ERROR] [MY-012592] [InnoDB] Operating system error number 24 in a file operation.
2022-06-27T08:13:20.471608+08:00 0 [ERROR] [MY-012596] [InnoDB] Error number 24 means 'Too many open files'
2022-06-27T08:13:20.471631+08:00 0 [ERROR] [MY-012646] [InnoDB] File ./bznaqgk/drp_synlog_detail.ibd: 'open' returned OS error 124. Cannot continue operation
2022-06-27T08:13:20.471644+08:00 0 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2022-06-27T00:13:21.227135Z mysqld_safe Number of processes running now: 0
2022-06-27T00:13:21.233845Z mysqld_safe mysqld restarted

验证一下: open_files_limit 与 innodb_open_files 这两个参数的作用

怀疑 open_files_limit超限会造成error 造成重启

innodb_open_files超限会产生warning。

2.后台线程 semaphore wait超过600会造成crash。

 

通常这种情况都是因为后台线程被阻塞，导致后台线程semaphore wait超过600秒，比如主线程要清理table open cache，需要数据字典上RW_latch的锁，但如果此锁会被执行create drop table的线程所持有，如果这个执行create drop table 的线程被阻塞时间很久，就有可能造成主线程同时被阻塞，从而造成数据库crash而被重启。