MySQL MyISAM只读节点性能低下调优咨询

架构与业务概况 #

• 采用一主两备数据库架构，应用通过负载均衡连接至只读备节点
• 两个只读节点任一时刻均有约600个连接执行SELECT操作
• 数据库总大小为3GB，业务表采用MyISAM引擎

只读节点配置 #

MyISAM相关配置 #

key_buffer_size                     = 24G
myisam_sort_buffer_size             = 16M
sort_buffer_size                    = 16M
read_buffer_size                    = 32M

网络与连接配置 #

max_allowed_packet                  = 256M # default 16M
wait_timeout                        = 28800
skip_name_resolve                   = ON
max_connections                     = 1200
max_user_connections                = 1000
max_allowed_packet                  = 256M # default 16M

问题 #

尽管已配置如上，只读节点仍存在严重性能问题，恳请提供调优建议。

1. 内存参数精准调优 #

• key_buffer_size：当前设置24G，但数据库总大小仅3GB，MyISAM的key_buffer用于缓存索引，完全不需要这么大。建议调整为1G-2G，避免占用过多内存导致操作系统页缓存不足，反而影响数据读取性能。
• read_buffer_size：该参数为每个会话顺序读取时分配的内存，32M的配置下600个连接总内存会达到19.2G，极易引发内存耗尽和频繁swap。建议下调至1M-2M，同时开启read_rnd_buffer_size（建议4M-8M）优化随机读取场景。
• sort_buffer_size：同样为每个会话分配，16M配置下600连接总内存达9.6G，建议调整为2M-4M，避免内存过度占用。

2. MyISAM引擎特性优化 #

• 开启concurrent_insert：设置concurrent_insert=2，允许在MyISAM表末尾并发插入数据（即使表处于读锁定状态），减少读锁阻塞概率。
• 定期整理表碎片：在业务低峰期执行OPTIMIZE TABLE命令，整理MyISAM表的碎片，提升读取效率（注意执行时会锁表）。
• 监控表锁状态：通过SHOW OPEN TABLES WHERE In_use > 0查看锁表情况，确认是否有主库同步过来的写操作阻塞读请求。

3. 连接与会话优化 #

• wait_timeout：当前设置为8小时，过长的超时会导致大量空闲连接占用资源。建议调整为300秒（5分钟），配合应用侧连接池配置，及时回收空闲连接。
• 排查慢查询：通过SHOW PROCESSLIST或慢查询日志，定位长时间运行的SELECT语句，针对性进行索引优化或SQL改写。

4. 索引与SQL优化 #

• 检查索引覆盖率：用EXPLAIN分析高频查询，确保所有核心SELECT语句都能命中索引，避免全表扫描。
• 避免SELECT *：仅查询业务需要的字段，减少数据传输量和内存占用。
• 拆分复杂查询：将大查询拆分为多个小查询，缩短单查询的锁持有时间。

5. 系统层面优化 #

• 关闭swap分区：数据库服务器应避免使用swap，确保内存操作都在物理内存中进行。可通过swapoff -a临时关闭，修改/etc/fstab永久关闭。
• 调整文件句柄限制：设置系统fs.file-max=65535，同时将MySQL的open_files_limit调整为4096以上，避免因文件句柄不足引发异常。

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