MariaDB 10.4超10秒慢查询未录入慢日志问题排查求助

• 慢查询日志未捕获长耗时语句的核心原因：MariaDB 10.4默认long_query_time统计的是语句实际执行时长，默认排除锁等待时间。你遇到的14秒、60秒耗时绝大多数是锁等待占用，实际执行时长低于10秒阈值，因此不会被录入慢日志。
• 锁等待及语句耗时飙升的根因：
  • 未提交长事务持有行锁/间隙锁：InnoDB默认隔离级别为REPEATABLE READ，会产生间隙锁。你使用的delete语句即使没有匹配到记录，也会对对应范围加间隙锁，若存在其他事务持有同范围锁，或存在插入意向锁冲突，就会触发等待。
  • 插入意向锁冲突：插入意向锁是间隙锁的一种，多个事务同时向同一个索引间隙插入数据时会触发等待，若存在未提交事务占用间隙锁，后续插入、删除操作都会被阻塞。
  • 你观测到的"表级锁等待"为误判：InnoDB默认不会触发表级锁，除非执行了LOCK TABLES语句或存在运行中的DDL操作，你看到的现象本质是大范围行锁、间隙锁叠加导致的全表范围等待，外在表现类似表锁。

• 慢日志配置验证：
  执行show variables like '%slow_query_log%'确认慢日志已开启，重点检查以下参数：
  • 确认log_slow_verbosity是否包含lock_time配置，只有开启锁时间统计，慢日志才会将锁等待时长纳入统计范围
  • 临时将long_query_time调整为0.1秒，观察是否有目标语句被录入，验证是否为锁等待导致的未捕获
• 事务锁排查：
  • 执行show engine innodb status查看TRANSACTIONS板块，定位持有锁超过10秒的事务，确认事务执行语句、持有锁类型、锁数量
  • 执行select * from information_schema.innodb_lock_waits直接查询锁等待关系，定位阻塞源事务的线程ID
  • 执行select * from information_schema.processlist where command='Sleep' and time>30排查长休眠连接，这类连接大概率持有未释放的事务锁
• 字符集一致性验证：
  执行show variables like '%character%'确认客户端、连接、表、结果集的字符集是否匹配，字符集不一致会导致索引失效，触发全表扫描，进一步扩大锁范围加剧冲突。

• 慢日志捕获问题修复：
  执行set global log_slow_verbosity = 'full,lock_time'开启锁时间统计，根据业务场景合理调低long_query_time阈值，后续锁等待时长超过阈值的语句也会被录入慢日志。
• 锁冲突问题修复：
  • 调整事务隔离级别：若业务不需要REPEATABLE READ的一致性保证，调整为READ COMMITTED级别，该级别下不会产生间隙锁，可大幅降低锁冲突概率，执行set global transaction isolation level read committed，同时修改my.cnf配置永久生效
  • 优化业务代码：确保所有事务及时提交，禁止长事务，事务内不要包含外部接口调用、用户交互等耗时操作，控制事务执行时长不超过1秒
  • 优化删除语句：当前delete语句使用的like 'EDM-%-FOLDER'条件即使走索引，也会对匹配的索引范围加锁，建议拆分where条件缩小锁范围，或分批删除每次操作少量数据，避免长时间持有锁
  • 自动清理闲置连接：配置wait_timeout和interactive_timeout为300秒，自动断开闲置超过5分钟的连接，避免未提交事务长时间持有锁
• 死锁处理：
  开启innodb_print_all_deadlocks = 1，死锁信息会自动打印到错误日志中，根据死锁日志调整业务操作顺序，避免循环等待锁。

内容的提问来源于stack exchange，提问作者Dominique Corfa