高配置服务器MySQL执行insert等操作时mysqld服务挂死问题排查求助

1. 锁与死锁验证 #

• 执行set global innodb_print_all_deadlocks = ON开启死锁日志，服务挂死后直接查看MySQL error日志，确认是否为真实死锁，以及锁对应的索引、行记录信息。
• 执行show engine innodb status查看当前InnoDB事务状态，确认是否存在长事务持有行锁未释放的情况。
• 用explain分析异常表的update、delete语句执行计划，确认where条件是否命中索引，未命中索引的写入语句会触发全表扫描加锁，极容易引发大规模锁冲突。

2. 配置有效性核查 #

• 你提供的my.cnf中所有InnoDB核心参数全部处于注释状态，200G内存的服务器默认InnoDB缓冲池仅为128M，会导致高并发写入时频繁触发磁盘IO，CPU异常升高。
• 核查异常表和300万行正常表的存储引擎，若均为InnoDB，当前配置的2G key_buffer_size完全无用（该参数仅针对MyISAM引擎）。
• 当前max_connections配置为1000，4000在线用户场景下会出现连接排队，引发资源竞争。

3. 索引与结构对比 #

• 对比异常表和正常表的索引结构，确认异常表的2个索引是否存在低基数索引（区分度<10%）、重复索引、冗余索引，多余索引会大幅提升写入时的索引维护开销。
• 查看异常表碎片率：select concat(round(data_free/1024/1024,2),'M') as data_free from information_schema.tables where table_schema='你的库名' and table_name='你的表名';，碎片率超过30%会导致写入开销翻倍。

4. 运行时性能剖析 #

• 高并发场景下执行show processlist，查看MySQL线程的等待状态，确认是行锁等待、表锁等待还是IO等待。
• 用perf top -p $(pidof mysqld)观测CPU占用最高的内核函数，定位性能瓶颈是锁竞争、缓冲池miss还是日志刷盘。

1. 核心配置修复 #

调整InnoDB核心参数（根据200G内存配置）：

# 注释掉原无用key_buffer_size配置，改为32M足够
key_buffer_size = 32M
# InnoDB缓冲池设为内存的70%，缓存数据和索引
innodb_buffer_pool_size = 140G
innodb_log_file_size = 4G
innodb_log_buffer_size = 64M
# 强一致场景设为1，允许丢1秒数据提升写入性能设为2
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
# 开启死锁自动检测
innodb_deadlock_detect = ON
# 匹配4000在线用户规模
max_connections = 5000

2. 锁冲突优化 #

• 若死锁日志确认是间隙锁导致的冲突，业务允许的前提下将事务隔离级别改为READ COMMITTED，可消除90%以上的间隙锁问题。
• 清理异常表中不必要的冗余索引、低基数索引，减少写入时的索引维护开销。
• 业务侧避免使用长事务，单条写入无需额外包裹事务，事务执行完成后立即提交，减少锁持有时间。

3. 写入逻辑优化 #

• 高频单条insert可合并为批量insert，单次批量写入100-1000条，大幅减少锁竞争和IO交互次数。
• 若表使用自增主键，避免乱序插入主键值，减少页分裂带来的额外CPU开销。
• 碎片率过高的表执行optimize table 你的表名整理碎片，注意操作会锁表，需在低峰期执行。

内容的提问来源于stack exchange，提问作者Mahmoud Sohail