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数据库的性能与稳定性。