MySQL配置参数优化指南：提升数据库性能与稳定性

为了提升MySQL服务器的性能与稳定性，调整配置参数是至关重要的。这些参数主要包括操作系统配置和MySQL数据库配置，下面是一些建议。

操作系统配置

1. 在BIOS及内核层面禁用NUMA功能。

2. 在BIOS中将CPU和内存设置为最大性能模式。

3. 在BIOS中关闭CPU节能模式。

4. 修改IO调度器为deadline或noop，机械硬盘设置为deadline，SSD设置为noop。可通过以下命令检查当前设置：

   grep deadline /sys/block/sd*/queue/scheduler
   

5. 使用XFS文件系统，并在挂载时添加选项：noatime、nodiratime、nobarrier。

6. 在内核层面设置以下参数：

   • vm.swappiness <= 5
   • vm.dirty_ratio <= 10
   • vm.dirty_background_ratio <= 5
   • fs.file_max = 65536 # 指定能够打开的文件句柄数
   • net.core.somaxconn = 65536 # 指定socket监听的TCP协议连接数的上限
   • net.core.netdev_max_backlog = 65536
   • net.ipv4.tcp_max_sync_backlog = 65536
   • net.ipv4.tcp_fin_timeout = 10
   • net.ipv4.tcp_tw_reuse = 0 # 建议关闭
   • net.ipv4.tcp_tw_recycle = 0 # 建议关闭

7. 在内核层面将用户可打开文件数和线程数设置为65535:

   vi /etc/security/limits.conf
   # 添加以下内容
   * - nofile 65535
   * - nproc  65535
   

MySQL配置

1. 调整排序、连接、读取缓冲区大小：
   • sort_buffer_size = 4M
   • join_buffer_size = 4M
   • read_buffer_size = 8M
   • read_rnd_buffer_size = 4M
2. 临时表和堆表的大小设置：
   • tmp_table_size = 32M
   • max_heap_table_size = 32M
3. 设置二进制日志同步和InnoDB日志刷新：
   • sync_binlog = 1
   • innodb_flush_log_at_trx_commit = 1
4. 设置长查询时间：
   • long_query_time = 0.1 # 建议在0.01-0.1之间
5. 记录未使用索引的查询：
   • log_queries_not_using_indexes = 1
   • log_throttle_queries_not_using_indexes = 60
6. 设置交互超时时间和等待超时时间：
   • interactive_timeout = 600
   • wait_timeout = 600
7. 设定锁等待超时时间：
   • lock_wait_timeout = 3600
8. 设置默认时区：
   • default_time_zone = "+8:00" # 固定值可降低CPU使用率
9. 线程处理设置（适用于企业版或Percona版本）。
10. InnoDB缓冲池大小：
    • innodb_buffer_pool_size = 2G
11. InnoDB最大脏页百分比：
    • innodb_max_dirty_pages_pct = 50 # 对于IO较快的情况
12. InnoDB线程并发：
    • innodb_thread_concurrency = 0 # 建议设置为0
13. 行锁等待时间：
    • innodb_lock_wait_timeout = 10
14. InnoDB日志文件大小：
    • innodb_log_file_size = 2G
    • innodb_log_files_in_group = 3
15. 根据IOPS能力调整：
    • innodb_io_capacity = 4000
    • innodb_io_capacity_max = 8000
16. InnoDB状态输出设置：
    • innodb_status_output = OFF
    • innodb_status_output_locks = ON # 监控锁信息
17. 禁用DNS解析：
    • skip_name_resolve = ON # 内网生产建议开启
18. 设置二进制日志保留时间：
    • expire_logs_days = 7 或 binlog_expire_logs_seconds = 604800
19. SQL模式设置：
    • sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
20. 最大允许数据包大小：
    • max_allowed_packet = 64M
21. 打印所有死锁信息：
    • innodb_print_all_deadlocks = 1 # 建议开启

通过合理的配置调整，可以显著提升MySQL数据库的性能与稳定性。