从MySQL 5.6.35迁移至MariaDB 10.6.3后查询异常问题咨询

问题1：查询慢是否由JOIN Buffer size设置过低导致？ #

是诱因之一，但不是核心原因，核心问题是存在大量无索引的JOIN，加上InnoDB缓冲池配置严重不足：

• 你的mysqltuner输出显示累计有383010次无索引JOIN，这类JOIN需要全表扫描关联表，本身性能就极差，你的查询又包含大量外连接，多表全表扫描叠加后耗时会指数级上升。JOIN Buffer过小会导致单次无索引JOIN需要多次扫描关联表，进一步放大性能问题。
• 更核心的瓶颈是InnoDB缓冲池配置只有128M，而你的InnoDB数据总共有4.5G，97%以上的查询数据都需要从磁盘读取，这才是查询极慢的主要原因。
• 你遇到的2013断连错误，大概率是因为只改了客户端的超时配置，没有调整服务端的wait_timeout、interactive_timeout、max_execution_time参数，长查询运行超过服务端超时阈值后被主动断开连接。

问题2：Thread Pooling是否能提升性能？ #

对你当前的场景没有明显提升：

• 你的数据库QPS只有不到10，最高连接占用也只有218，默认4线程的线程池完全能承载当前负载，性能瓶颈和线程调度无关，调整线程池参数不会有明显收益。

优先级最高的优化步骤 #

1. 调整核心内存配置
   • 先将max_connections从1000调低到300，你的业务峰值连接才218，1000的最大连接配置会导致最大内存占用达到物理内存的2.5倍，极易触发OOM杀掉数据库进程。
   • 将innodb_buffer_pool_size调整为5G，你的物理内存有7.6G，预留2G给系统和其他进程完全足够，缓冲池能放下全部InnoDB数据后，查询性能会有数量级提升。
2. 解决无索引JOIN问题
   • 优先给所有查询JOIN关联的字段添加对应索引，这是解决慢查询的最优方案，比调整JOIN Buffer效果好得多。
   • 如果暂时无法修改索引，可以将join_buffer_size调整为1M，不要调得过大，因为JOIN Buffer是按线程分配的，配置过高会快速耗尽内存。
3. 其他必改配置
   • 将table_definition_cache调整为1500，你总共有1224张表，当前400的配置远低于实际需求，会频繁触发磁盘读取表元数据，拖慢所有查询。
   • 在配置文件中添加skip-name-resolve=1关闭反向域名解析，减少连接建立开销。
   • 查看/var/log/mariadb/mariadb.log中的12条错误和90条警告，里面可以直接定位2013断连的具体触发原因。

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