欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL性能优化1 - 服务器层面优化

程序员文章站 2022-07-02 11:01:47
...

1、服务器硬件优化

提升硬件设备,例如选择尽量高频率的内存(频率不能高于主板的支持)、提升网络带宽、使用SSD高速磁盘、提升CPU性能等。对于CPU的选择:

  • 对于数据库并发比较高的场景,CPU的数量比频率重要。
  • 对于CPU密集型场景和频繁执行复杂SQL的场景,CPU的频率越高越好。

2、CentOS系统针对MySQL的参数优化

2.1、内核相关参数


可以通过在文件 sysctl.conf 中添加参数实现优化。该文件默认目录为:/etc/sysctl.conf。以下参数可以直接放到该文件的末尾。

  • 增加监听队列上限

    net.core.somaxconn = 65535
    net.core.netdev_max_backlog = 65535
    net.ipv4.tcp_max_syn_backlog = 65535
    
  • 加快TCP连接的回收

    net.ipv4.tcp_fin_timeout = 10
    net.ipv4.tcp_tw_reuse = 1
    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
    
  • 单个共享内存段的最大值

    kernel.shmmax = 4294967295
    

    这个参数应该设置的足够大,以便能在一个共享内存段下容纳整个的Innodb缓冲池的大小。其值对于64位Linux系统,最大可取为(物理内存值 - 1)byte。
    建议值为大于物理内存的一半,一般取值大于Innodb缓冲池的大小即可。

  • 控制换出运行时内存的相对权重

    vm.swappiness = 0
    

    这个参数当内存不足时会对性能产生比较明显的影响。(设置为0,表示Linux内核虚拟内存完全被占用,才会要使用交换区。)

    Linux系统内存交换区是在Linux系统安装时都会有一个特殊的磁盘分区,称之为系统交换分区。使用 free -m命令可以看到swap就是内存交换区:

    [[email protected] ~]# free -m
                  total        used        free      shared  buff/cache   available
    Mem:           3786        1233         417          25        2136        2219
    Swap:          2047          43        2004
    

    内存交换区的作用是,当操作系统没有足够的内存时,就会将部分虚拟内存写到磁盘的交换区中,这样就会发生内存交换。如果Linux系统上完全禁用交换分区,就会带来以下风险:
    (1)降低操作系统的性能。
    (2)容易造成内存溢出,崩溃,或都被操作系统kill掉。

2.2、增加资源限制


可以通过在文件 limit.conf 中添加参数实现优化。该文件默认目录为:/etc/security/limit.conf。以下参数可以直接放到该文件的末尾。以下两行配置将可打开的文件数量增加到65535个,以保证可以打开足够多的文件句柄:(注意,这个文件的修改需要重启系统才能生效)

* soft nofile 65535
* hard nofile 65535
参数 说明
* 表示对所有用户有效
soft 表示当前系统生效的设置(soft不能大于hard)
hard 表明系统中所能设定的最大值
nofile 表示所限制的资源是打开文件的最大数目
65535 限制的数量

2.3、磁盘调度策略

2.3.1、查看调度策略的方法


cat /sys/block/devname/queue/scheduler

2.3.2、修改调度策略的方法


echo > /sys/block/devname/queue/scheduler
  • cfq(完全公平队列策略,Linux2.6.18之后内核的系统默认策略)
    该模式按进程创建多个队列,各个进程发来的IO请求会被cfq以轮循方式处理,对每个IO请求都是公平的。该策略适合离散读的应用。
  • deadline(截止时间调度策略)
    deadline 包含读和写两个队列,确保在一个截止时间内服务请求(截止时间是可调整的),而默认读期限短于写期限。这样就防止了写操作因为不能被读取而“饿死”的现象,deadline对数据库类应用是最好的选择。
  • noop(电梯式调度策略)
    noop只实现一个简单的FIFO队列,倾向饿死读而利于写,因此noop对于闪存设备、RAM及嵌入式系统是最好的选择。
  • anticipatory(预料I/O调度策略)
    本质上与deadline策略一样,但在最后一次读操作之后,要等待6ms,才能继续进行对其它I/O请求进行调度。它会在每个6ms中插入新的I/O操作,合并写入流,用写入延时换取最大的写入吞吐量。anticipatory适合于写入较多的环境,比如文件服务器。该策略对数据库环境表现很差。

3、MySQL数据库配置优化


可以通过在文件 my.cnf 中添加参数实现优化。该文件默认目录为:/etc/my.cnf

  • innodb_buffer_pool_size
    表示缓冲池字节大小(InnoDB的内存越大,会减少磁盘交互)。默认128M。应该设置足够大的 innodb_buffer_pool_size ,将数据读取到内存中。建议innodb_buffer_pool_size设置为总内存大小的3/4或者4/5。那么怎样确定 innodb_buffer_pool_size 足够大?数据是从内存读取而不是硬盘?
    show global status like 'Innodb_buffer_pool_pages_%';
    +----------------------------------+--------+
    | Variable_name                    | Value  |
    +----------------------------------+--------+
    | Innodb_buffer_pool_pages_data    | 129037 |
    | Innodb_buffer_pool_pages_dirty   | 362    |
    | Innodb_buffer_pool_pages_flashed | 9998   |
    | Innodb_buffer_pool_pages_free    | 0      | # 为0则表示buffer_pool已经被用光
    | Innodb_buffer_pool_pages_misc    | 2035   |
    | Innodb_buffer_pool_pages_total   | 131072 |
    +----------------------------------+--------+
    
  • innodb_log_file_size
    适当地调大该参数以满足实际的需求:
    参数过大,实例恢复时间就会很长长;
    参数过小,会造成日志切换频繁。
    在设推荐设置为 0.25 * innodb_buffer_pool_size
  • innodb_flush_log_at_trx_commit
    用来控制redo log刷新到磁盘的策略,默认为1。
    该参数为0时性能最好,但不支持事务。在主从机制的情况下,从机器是不负责写操作,所以不牵扯事务提交,此时为了从数据库的性能提升,需要将此参数设置为0。
  • innodb_max_dirty_pages_pct
    脏页占innodb_buffer_pool_size的比例时,触发刷脏页到磁盘。推荐值为25%~50%。
  • sync_binlog
    控制数据库的binlog刷新到磁盘,默认值为0。
    sync_binlog = 0,MySQL不控制 binlog 的刷新,由文件系统自己控制它的缓存的刷新。这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。
    sync_binlog > 0,每次 sync_binlog 事务提交,MySQL调用文件系统的刷新操作将缓存刷下去。
    最安全的就是sync_binlog = 1了,表示每次事务提交,MySQL都会把 binlog 刷下去,是最安全的设置方式,即使数据库所在的主机操作系统损坏或者突然掉电的情况下,系统才有可能丢失1个事务的数据。但是 binlog 虽然是顺序IO,若设置sync_binlog = 1,多个事务同时提交,也会很大的影响 MySQL 和 IO 性能。虽然可以通过 group commit 的补丁缓解,但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
    所以很多MySQL DBA设置的sync_binlog并不是最安全的1,而是100或者是0。这样牺牲一定的一致性,可以获得更高的并发和性能。入在主从机制的情况下,从机器是不负责写操作,所以不牵扯事务提交,也不需要再重复写binlog,所以可将此处设置为0。
  • innodb_io_capacity
    后台进程最大IO性能指标。默认200,如果SSD,调整为5000~20000。
  • innodb_data_file_path
    指定innodb共享表空间文件的大小。(分磁盘,这样可以减轻磁盘的IO操作性能压力)
  • binlog_format
    mysql复制的形式,row为MySQL8.0的默认形式。(statement,row,mixed)
  • max_connections
    调高该参数则应降低interactive_timeout、wait_timeout的值。
  • general_log
    全量日志建议关闭,且默认关闭(0)。否则就会记录一切存在的记录,非常损耗性能。
  • slow_query_log
    慢查询日志开关,不要随意开启,即使开启也要合理的设置慢查询日志时间阈值(long_qurey_time,单位秒)。