虚拟机中Docker部署的MariaDB SELECT查询极慢问题排查

基础环境说明 #

• 后端应用基于MariaDB Server开发，采用Docker容器化架构：后端服务、MariaDB服务（使用官方镜像）分别运行在独立容器内，通过Docker Compose完成服务编排
• 项目部署在物理宿主机上时运行完全正常，宿主机硬件配置为SSD存储、8核CPU、32GB内存
• 将整套容器迁移到基于Kubuntu/Lubuntu系统的虚拟机中后，部分SELECT查询出现严重性能问题：在宿主机上耗时不足1秒的查询，在虚拟机内长时间无法返回结果
• 测试阶段为虚拟机分配4核CPU、20GB内存，后续验证增加CPU核数、提升内存配额均无法改善性能，多轮调试未定位根因

问题查询语句 #

select  distinct gene1_.id as id1_6_, gene1_.defaultName as defaultN2_6_,
        gene1_.species as species3_6_
    from  gene_in_interactome geneininte0_
    inner join  gene gene1_  ON geneininte0_.gene=gene1_.id
    inner join  gene_name names2_  ON gene1_.id=names2_.geneId
    inner join  gene_name_value names3_  ON names2_.geneId=names3_.geneId
      and  names2_.source=names3_.geneSource
    where  (geneininte0_.interactome in (666))
      and  (cast(gene1_.id as char) like 'arg%'
              or  names3_.name like 'arg%'
           )
    order by  gene1_.id asc
    limit  10

初步瓶颈定位 #

• 已确认性能瓶颈来自查询条件段(cast(gene1_.id as char) like 'arg%' or names3_.name like 'arg%')
• 该条件需要关联最后一张names3_表完成内连接，是触发查询变慢的核心原因

关联表建表语句 #

CREATE TABLE `gene_in_interactome` (
  `gene` int(11) NOT NULL,
  `interactome` int(11) NOT NULL,
  `species` int(11) NOT NULL,
  PRIMARY KEY (`gene`,`interactome`,`species`),
  KEY `FKsswgs3cc7avkugqvq78sv21xg` (`interactome`),
  KEY `FKtpcnom6fs9jal4qfgao444cse` (`species`),
  CONSTRAINT `FKa02a13n65pbhq1m1ehk63f4es` FOREIGN KEY (`gene`) REFERENCES `gene` (`id`),
  CONSTRAINT `FKsswgs3cc7avkugqvq78sv21xg` FOREIGN KEY (`interactome`) REFERENCES `interactome` (`id`),
  CONSTRAINT `FKtpcnom6fs9jal4qfgao444cse` FOREIGN KEY (`species`) REFERENCES `species` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `gene` (
  `id` int(11) NOT NULL,
  `defaultName` varchar(255) NOT NULL,
  `species` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDXnkshoslla6kq08gqh38grefke` (`id`,`species`),
  KEY `FKg5uaph3wq3eu765ch9lkq6qi1` (`species`),
  CONSTRAINT `FKg5uaph3wq3eu765ch9lkq6qi1` FOREIGN KEY (`species`) REFERENCES `species` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `gene_name` (
  `geneId` int(11) NOT NULL,
  `source` varchar(255) NOT NULL,
  PRIMARY KEY (`geneId`,`source`),
  CONSTRAINT `FKltnrhrbud9fvdilc74lybdvio` FOREIGN KEY (`geneId`) REFERENCES `gene` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `gene_name_value` (
  `geneId` int(11) NOT NULL,
  `geneSource` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  KEY `FK_gene_name_gene_name_values` (`geneId`,`geneSource`),
  CONSTRAINT `FK_gene_name_gene_name_values` FOREIGN KEY (`geneId`, `geneSource`) REFERENCES `gene_name` (`geneId`, `source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

根因定位排查思路 #

• 存储IO性能差异排查
  虚拟机磁盘IO性能远低于宿主机是这类问题的最高频诱因：KVM类虚拟机如果磁盘缓存模式配置为writethrough/none、虚拟磁盘使用未预分配空间的稀疏qcow2格式、镜像文件实际存放在机械盘上，4k随机读性能会比物理SSD低1~2个数量级。当前查询涉及多表Join、字符串模糊匹配，会产生大量随机读请求，对IO性能敏感度极高。排查时直接在虚拟机内用fio测试数据所在分区的4k随机读性能，和宿主机同场景测试结果做对比；同时在查询执行前后查看MariaDB的Innodb_buffer_pool_reads状态值，计算物理读占比，和宿主机的数值做对比。
• 执行计划差异排查
  容器内MariaDB的参数、统计信息和宿主机不一致会导致优化器选择错误执行计划：MariaDB服务首次启动时会根据硬件环境自动设置innodb_buffer_pool_size、join_buffer_size等参数，虚拟机环境下检测到的硬件信息和宿主机有差异，会导致默认参数值不同；加上数据导入时的采样差异可能导致表统计信息不准，优化器很可能放弃索引选择全表扫描gene_name_value表。排查时分别在宿主机、虚拟机的MariaDB中对问题查询执行EXPLAIN，对比两者执行计划，重点看type列是否出现代表全表扫描的ALL、rows列预估扫描行数是否存在数量级差异，同时对比两边数据库参数配置是否一致。
• 字符集与排序规则隐式转换排查
  所有业务表使用utf8mb3字符集，如果虚拟机系统默认locale、MariaDB的collation_connection/collation_database参数和宿主机不一致，会导致LIKE字符串匹配时无法利用已有索引，甚至触发逐行字符集转换，带来极高CPU开销。排查时对比两边show variables like 'collation%'、show variables like 'character_set%'的输出，确认不存在字符集、排序规则配置差异。
• 文件系统挂载参数排查
  Docker在虚拟机中如果使用overlay2存储驱动存放MariaDB数据目录，且虚拟机内文件系统未开启noatime挂载参数、或者底层使用随机读写性能较差的文件系统（比如默认配置的btrfs），也会导致InnoDB随机读写性能骤降。排查时对比宿主机和虚拟机内MariaDB数据目录的文件系统类型、挂载参数，直接测试同目录下文件的随机读写性能做对比。

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