Mysql优化之配置

mysql优化顺序：sql语句优化 ==》 表结构优化 ==》Mysql配置优化 ==》 服务器系统优化 ==》硬件优化

Linux CentOs服务器配置优化

* 内核相关的参数（/etc/sysctl.conf）

• net.core.somaxconn 端口监听队列的长度，默认值比较小，可以改为2048或更大；
• net.core.netdev_max_backlog 接收数据包的速率高于内核处理的速率时，允许队列的数目；
• net.ipv4.tcp_max_syn_backlog 类似上一条的一种队列的数目，因为超过数目就会被抛弃，应改大一些；

    # 加速释放tcp连接相关，tcp连接太多会导致连接上不数据库

• net.ipv4.tcp_fin_timeout tcp连接处理的等待时间
  在一个高访问的服务器，有很多tcp连接是等待状态，应把此项的值改小一点，加速释放tcp连接，就不会造成阻塞
• net.ipv4.tcp_tw_reuse=1 作用同是加速释放tcp连接
• net.ipv4.tcp_tw_recycle=1 作用同是加速释放tcp连接

    # 接收和发送缓冲区的大小，对于数据库服务器这些值应该改大一些

• net.core.wmem_default=87380
• net.core.wmem_max=16777216
• net.core.rmem_default=87380
• net.core.rmem_max=16777216

    # 加速释放失效tcp连接

• net.ipv4.tcp_keepalive_time=120
• net.ipv4.tcp_keepalive_intvl=30
• net.ipv4.tcp_keepalive_probes=3

  # 单个共享内存段的最大值，mysql服务器应设置为足够大，以便能够容纳整个的Innodb缓冲池的大小

• kernel.shmmax=足够大
• vm.swappiness=0 设置除非虚拟内存完全满了，否则不要使用交换区

 

* 增加资源限制（/etc/security/limit.conf）

• * soft nofile 65535 // 添加到配置文件末尾，需要重启操作系统
• * hard nofile 65535 // 添加到配置文件末尾，需要重启操作系统

 

* 磁盘调度策略（/sys/block/devname/queue/scheduler）应使用deadline最佳

 

Mysql配置优化

查看配置：

# show variables like '{like}'

配置参数：

# set global configName=configValue 设置全局的配置
# set session configName=configValue 设置Session级的配置

* 内存配置相关的参数

    # 下面4个选项应该合理设置，过大或过小都不好

• sort_buffer_size
• join_buffer_size
• read_buffer_size
• read_rnd_buffer_size

    # Innodb 缓存池，建议系统内存75%以上

• innodb_buffer_pool_size

    # MyIsam 索引缓存池，就算系统全使用innodb存储引擎，也需要分配内存空间

• key_buffer_size

 

* Innodb I/O相关配置参数

• innodb_log_file_size
• innodb_log_files_in_group

    #

• innodb_log_buffer_size=128M
• innodb_flush_log_at_trx_commit=??
  0 => 每秒进行一次log写入cache，并flush log到磁盘
  1【默认】 => 在每次事务提交执行log写入cache，并flush log到磁盘
  2【建议】=> 每次事务提交，执行log写入cache，没秒执行一次flush log到磁盘
• innodb_flush_method=O_DIRECT
• innodb_file_per_table=1
• innodb_doublewrite=1

 

* Myisam I/O相关配置

• delay_key_write=??
  OFF => 每次写操作后刷新键缓冲中的脏块到磁盘
  ON => 只对在建表时指定了delay_key_write选项的表使用延迟刷新
  ALL => 对所有MYISAM表都使用延迟键写入

 

* 安全相关的配置参数

• expire_logs_days 指定自动清理binlog的天数
• max_allowed_packet 控制Mysql可以接收的包的大小
• skip_name_resolve 禁用DNS查找
• sysdate_is_now 确保sysdate()函数返回确定性日期
• read_only 禁止非super权限的用户写权限
• skip_slave_start 禁用Slave自动恢复，当复制故障时应检查故障在手动恢复
• sql_mode 设置Mysql所使用的SQL模式
   

* 其它常用配置参数

• sync_binlog 控制Mysql如何向磁盘刷新binlog
• tmp_table_size 和 max_heap_table_size 控制内存临时表大小
• max_connections 控制允许的最大连接数，默认100