MariaDB 10.6中MyISAM查询最小执行时间恒为100ms的原因排查

问题背景 #

我有一套依赖MariaDB 10.6中MyISAM表的旧软件，使用Percona Toolkit的pt-query-digest分析查询摘要时，发现所有MyISAM查询的执行时间最小值始终为100ms，而InnoDB表的查询耗时更低。即使是针对3万条记录以内的小表执行简单count(*)查询，最小执行时间也保持100ms。已知最大耗时由并发锁导致，现咨询：为何MyISAM查询的最小执行时间恒为100ms？

使用的pt-query-digest命令：

/usr/bin/perl /usr/bin/pt-query-digest --processlist h=127.0.0.1,u=queryprofiler,p=mqueryprofiler --run-time=598s --interval 0.03

查询摘要：

# Query 5: 0.69 QPS, 0.17x concurrency, ID 0x761C0D5EAEC0B8FBB64279CF7F7B2727 at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.05
# Time range: 2024-02-07T15:00:14 to 2024-02-07T15:19:49
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2     807
# Exec time      4    200s   100ms   509ms   248ms   393ms   116ms   293ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     1  89.05k     113     113     113     113       0     113
# id             4  10.18G  12.57M  13.30M  12.92M  13.08M 278.63k  12.46M
# String:
# Databases    asterisk
# Hosts        45.77.125.169:37888 (7/0%)... 266 more
# Users        cron
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `asterisk` LIKE 'vicidial_carrier_log'\G
#    SHOW CREATE TABLE `asterisk`.`vicidial_carrier_log`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT dialstatus,count(*) from vicidial_carrier_log where call_date >= "2024-02-06 15:11:04" group by dialstatus\G

原因分析 #

1. pt-query-digest的--processlist采样机制限制 #

你使用的--processlist模式是通过周期性轮询MySQL的PROCESSLIST表获取查询数据，这种采样方式存在精度局限性：

• 虽然设置了--interval 0.03（30ms），但MySQL对PROCESSLIST的查询结果时间精度有限，加上pt-query-digest对短耗时查询存在向上取整或阈值过滤逻辑，可能导致所有小于100ms的查询被统一统计为100ms。这种偏差在采样模式下很常见，建议改用慢查询日志+pt-query-digest的组合验证，慢查询日志的时间精度可达微秒级，能反映真实耗时。

2. MyISAM表级锁的隐性调度延迟 #

即使Lock time显示为0，MyISAM的表级锁机制仍可能存在隐性延迟：

• 查询需要等待表的元数据锁（MDL）释放，即便没有显式的读写锁冲突，MySQL的元数据锁调度流程可能存在固定的最小延迟。
• 若系统存在MyISAM的key_buffer刷新、表统计信息更新等后台操作，会抢占CPU或磁盘资源，导致查询的起始执行时间被延迟到100ms的粒度。

3. MariaDB针对MyISAM的默认配置影响 #

MariaDB 10.6的部分默认参数会影响MyISAM的查询响应：

• delay_key_write：若该参数设为ALL或针对目标表开启，MyISAM会延迟索引写入磁盘，但当查询需要读取最新索引数据时，会触发强制刷新，带来固定IO延迟。
• 磁盘IO调度策略：若服务器使用CFQ调度器，会对MyISAM的随机IO请求添加固定调度延迟，而InnoDB依赖缓冲池优化，受影响更小。

4. 文件系统特性限制 #

MyISAM的表数据和索引是独立文件，部分文件系统（如默认配置的ext3）的dirsync或commit机制，会导致每次查询后产生固定的磁盘同步延迟；而InnoDB通过事务日志批量同步，可避免此类固定延迟。

验证建议 #

• 改用慢查询日志采集数据，对比pt-query-digest的统计结果，确认是否是采样机制导致的偏差。
• 备份后临时将目标MyISAM表转换为InnoDB，执行相同查询看耗时是否降低，排除业务逻辑或数据本身的问题。
• 将key_buffer_size调整为物理内存的10%-20%，观察是否能降低最小耗时。
• 临时切换磁盘IO调度器为deadline或noop，验证IO延迟是否改善。

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