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

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

操作系统配置

在BIOS及内核层面禁用NUMA功能。
在BIOS中将CPU和内存设置为最大性能模式。
在BIOS中关闭CPU节能模式。
修改IO调度器为deadline或noop，机械硬盘设置为deadline，SSD设置为noop。可通过以下命令检查当前设置：
```
grep deadline /sys/block/sd*/queue/scheduler
```
使用XFS文件系统，并在挂载时添加选项：noatime、nodiratime、nobarrier。
在内核层面设置以下参数：
- 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 # 建议关闭

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

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

MySQL配置

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

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