面试题：MySQL在SAN上的并发性能优化及故障处理

Question

当MySQL运行在SAN存储架构下，面对高并发场景，如何进行性能调优？若出现因SAN相关故障导致MySQL性能急剧下降，你会从哪些方面排查和解决问题？

Answer 1

高并发场景下MySQL在SAN存储架构下的性能调优

1. MySQL配置优化
   • 缓冲池：适当增大 innodb_buffer_pool_size，让更多的数据能缓存于内存，减少磁盘I/O。比如对于有大量内存的服务器，可设置为物理内存的60% - 80%。
   • 日志设置：合理配置 innodb_log_file_size 和 innodb_log_files_in_group。增大日志文件大小可减少日志切换频率，降低I/O开销，但要注意崩溃恢复时间可能变长。
   • 连接池：使用连接池（如 mysql - connection - pool）来管理数据库连接，减少连接创建和销毁的开销，提高连接复用率。
2. 查询优化
   • 索引优化：分析查询语句，确保在经常用于 WHERE、JOIN 条件的列上建立合适的索引。避免索引冗余和过度索引。
   • 查询重写：将复杂查询分解为多个简单查询，优化 JOIN 操作，确保 JOIN 条件正确，避免笛卡尔积。
3. SAN存储优化
   • 存储网络：确保SAN网络带宽足够，使用多路径I/O（MPIO）技术提高网络可靠性和带宽利用率。
   • 存储缓存：合理配置SAN存储设备的缓存，让热点数据能快速从缓存中读取，减少磁盘访问。
   • LUN配置：根据MySQL数据文件（如数据文件、日志文件）的I/O特性，合理分配LUN，比如将日志文件和数据文件分置于不同的LUN以减少I/O竞争。

SAN相关故障导致MySQL性能急剧下降的排查与解决

1. SAN网络排查
   • 网络连接：检查光纤通道或iSCSI连接，查看是否有物理线路损坏、接口松动等问题。可通过设备管理界面查看链路状态。
   • 网络拥堵：使用网络监控工具监测SAN网络带宽使用情况，判断是否存在网络拥堵。若拥堵，考虑增加网络带宽或优化网络拓扑。
2. 存储设备排查
   • 存储硬件：检查存储设备的硬件状态，如磁盘阵列中的磁盘是否有故障、控制器是否正常工作。通过存储设备的管理界面查看硬件报警信息。
   • 存储缓存：查看存储缓存命中率是否异常降低。若命中率低，可能需要调整缓存配置或检查是否有大量无效数据占用缓存。
   • LUN映射：确认LUN映射关系是否正确，是否存在LUN映射中断或错误映射的情况。
3. MySQL与SAN交互排查
   • I/O性能：使用 iostat 等工具监测MySQL服务器的磁盘I/O性能，查看是否存在高等待时间、低吞吐量等问题。若I/O性能低，结合SAN存储情况分析是存储问题还是MySQL配置问题。
   • 日志检查：查看MySQL错误日志和SAN存储设备日志，寻找与故障相关的线索，如存储设备不可达、I/O错误等信息。根据日志提示进行针对性解决。