面试题：MariaDB 高可用架构的性能优化及故障排查

Question

在一个复杂的 MariaDB 高可用架构（如包含多个主从节点、Galera Cluster 等混合架构）中，系统出现了性能瓶颈，读写操作响应时间明显变长。请描述你进行性能优化和故障排查的思路及具体步骤。从架构设计、数据库参数调优、硬件资源分析等多个方面进行阐述。

Answer 1

架构设计方面

1. 检查主从复制延迟
   • 使用 SHOW STATUS LIKE 'Seconds_Behind_Master' 命令在从节点查看复制延迟情况。若延迟较高，可能是网络问题、从节点负载过重或主从配置不当。
   • 优化网络，确保主从节点间网络稳定且带宽充足。可以通过 ping 命令检查网络连通性，使用 iperf 测试带宽。
   • 检查从节点负载，若负载过高，可考虑分担负载到其他从节点，或升级硬件资源。
2. Galera Cluster 检查
   • 查看节点间同步状态，使用 SHOW STATUS LIKE 'wsrep_cluster_status' 确保集群状态为 Primary 且所有节点同步正常。
   • 检查 wsrep_provider_options 配置，如 gmcast.listen_addr 是否配置正确，避免节点间通信异常。
   • 分析写入集冲突，可通过 SHOW STATUS LIKE 'wsrep_local_send_queue_avg' 等状态变量，若队列平均值过高，可能存在写入集冲突，优化业务逻辑减少并发写入冲突。
3. 读写分离优化
   • 确认读写分离策略是否合理，是否存在读请求被错误路由到主节点的情况。检查中间件（如 ProxySQL、MaxScale 等）的配置。
   • 评估读负载在各从节点的分布，若不均衡，调整负载均衡策略，确保各从节点读负载相对均匀。

数据库参数调优

1. 内存相关参数
   • innodb_buffer_pool_size：这是 InnoDB 存储引擎最重要的参数之一，用于缓存数据和索引。根据服务器内存大小合理调整，一般建议设置为物理内存的 60% - 80%。可通过修改 my.cnf 文件并重启 MariaDB 服务生效。
   • innodb_log_buffer_size：用于缓存 InnoDB 重做日志，适当增大可减少日志写入磁盘的频率。默认一般为 8MB，对于写入量较大的系统可调整至 16MB 或 32MB。
2. 并发相关参数
   • innodb_thread_concurrency：控制 InnoDB 允许同时进入内核的线程数。默认值为 0，表示不限制，根据服务器 CPU 核心数合理设置，一般为 CPU 核心数的 2 倍。
   • max_connections：设置 MariaDB 允许的最大连接数。过高可能导致系统资源耗尽，需根据服务器性能和业务需求调整。可通过查看 SHOW STATUS LIKE 'Threads_connected' 了解当前连接数，以评估是否需要调整。
3. 查询优化参数
   • query_cache_type 和 query_cache_size：查询缓存可缓存查询结果，提高重复查询性能。但在高并发写入环境下可能影响性能，需谨慎使用。若业务读多写少，可适当设置 query_cache_type 为 1（开启），并设置合适的 query_cache_size。
   • join_buffer_size：对于多表连接查询，增大此参数可提高连接操作性能。根据查询复杂度和数据量合理调整，默认一般为 256KB，可适当增大。

硬件资源分析

1. CPU 分析
   • 使用 top 或 htop 命令查看 CPU 使用率和负载情况。若 CPU 使用率长期过高，可能是查询过于复杂，未使用索引等原因。
   • 分析 SHOW PROCESSLIST 中运行时间较长的查询，使用 EXPLAIN 关键字分析查询执行计划，优化查询语句，添加合适索引。
2. 内存分析
   • 通过 free -h 查看内存使用情况，确保 MariaDB 有足够内存可用。若内存不足，可能导致频繁磁盘 I/O，影响性能。
   • 检查 swap 使用情况，若 swap 使用率较高，说明物理内存不足，可考虑增加物理内存或优化 MariaDB 内存使用参数。
3. 磁盘 I/O 分析
   • 使用 iostat 命令查看磁盘 I/O 性能，如 await 值过高表示磁盘 I/O 等待时间长，可能是磁盘性能瓶颈。
   • 对于机械磁盘，可考虑升级为 SSD 磁盘，提高磁盘读写速度。同时优化数据库文件存储位置，避免与其他 I/O 密集型应用共用磁盘。
4. 网络分析
   • 使用 ping 检查节点间网络连通性，确保无丢包或高延迟情况。
   • 使用 iperf 测试网络带宽，保证主从节点间、应用与数据库节点间网络带宽满足业务需求。若带宽不足，可考虑升级网络设备或网络带宽。

其他方面

1. 慢查询日志分析
   • 开启慢查询日志，在 my.cnf 中设置 slow_query_log = 1 和 long_query_time（如设置为 2，表示查询执行时间超过 2 秒的记录到慢查询日志）。
   • 分析慢查询日志，使用 mysqldumpslow 工具对日志进行统计分析，找出执行时间长、扫描行数多的查询，优化这些查询语句。
2. 监控与预警
   • 部署监控工具（如 Prometheus + Grafana），实时监控数据库性能指标，如 CPU、内存、磁盘 I/O、查询响应时间等。
   • 设置合理的预警阈值，当性能指标超出阈值时及时通知运维人员，以便及时发现和处理潜在问题。