什么影响了mysql的性能-硬件资源及系统方面优化

随着数据量的增大，数据库的性能问题也是个值得关注的问题，很多公司对mysql性能方面没有太过重视，导致服务浪费过多资源、mysql服务性能差从而直接影响用户体验，这里我们简单的先来聊聊什么影响了mysql性能，从而能关注这些问题。

影响性能的几个方面

• 服务器硬件
• 服务器系统
• 数据库存储引擎的选择(插件形式的)
  • MyISAM: 不支持事务,表级锁
  • InnoDB: 事务级存储引擎,完美支持行级锁，事务ACID特性
• 数据库参数配置
• 数据库结构设计和SQL语句
  • 慢查询(数据库表结构设计)
  • SQL语句的编写和优化

硬件资源方面的影响有哪些？

CPU资源和可用内存大小

• 我们的CPU是不是密集型的，如果是就需要更好的CPU
• 不支持多CPU对同一SQL并发
• 我们的系统并发量如何?
  • 如果是大并发场景下，CPU数量比频率更重要
  • MYSQL版本决定多核CPU支持(尽量用最新版)
    • 高版本的mysql更支持多核
• 选择32位还是64位的CPU？
  • 64位使用32位的服务器版本(不能忽视这个问题，测试/开发环境可能会遇到系统版本问题)
  • 32位对性能有限制
• 内存大小影响性能
  内存的影响不过多解释，大当然不是说内存越大性能越好，这里有一片博客介绍参数写的听全，感兴趣可以参考https://www.cnblogs.com/xuan52rock/p/4569835.html

磁盘的配置和选择

• 使用传统机器硬盘

  • 存储容量
  • 传输速度
  • 访问时间
  • 主轴转速(7200转/15000转)
  • 物理尺寸

• RAID增加传统机器硬盘的性能

  • RAID 0(不担心数据丢失)
    • 没有提供冗余或错误修复能里
    • 成本低
  • RAID 1(可利用率低，可以提高好的读操作)
    • 在写入一个磁盘同时，往另外一个镜像硬盘写入
  • RAID 5(读操作多的应用,从数据库上)
  • RAID 10（分片镜像，重要的选择）
    • 一块磁盘损坏性能会下降

• 固态存储(SSD闪存)

  • 相比机械磁盘固态磁盘有更好的随机读写
  • 有更好的支持并发
  • 相对机械磁盘固态磁盘更容易损坏(缺点)
    • SSD(固态硬盘)
      • 使用SATA接口
      • SATA接口的SSD同样支持RAID技术
      • 使用场景
        • 适合于大量随机IO
        • 解决单线程负载IO的瓶颈
        • 只有一块固态存储设备，应该放在从服务器上
          • 主服务器上多线程，从服务器单线程
    • PCI-E SSD(Fusion-io)
      • 无法使用SATA接口
      • 价格相对贵(还要占用服务器内存和CPU)

• 网络存储SAN和NAS

  • 网络存储使用的场景(并不适合)

    • 数据库备份

  • 网络性能的限制

    • 延迟

    • 带宽

    • 网络质量

      建议

      • 采用高性能和高带宽的网络接口和交换机
      • 对多个网卡进行绑定，增强可用性和带宽
      • 网络隔离

  总结:

  • CPU
    • 64位的CPU一定要工作在64位系统
    • 对于并发比较高的场景CPU的数量比频率重要
    • 对于CPU密集性场景和复杂SQL则频率越高越好
  • 内存
    • 选择主板所能使用最高频率的内存
    • 内存大小越大越好
  • I/O子系统
    • PCIe卡-》SSD-〉-》Raid10〉磁盘-》SAN

操作系统对性能的影响

• centos系统参数优化

  • 内核相关参数(/etc/sysctl.conf)

    # net.core.somaxconn是Linux中的一个kernel参数，表示socket监听（listen）的backlog上限。什么是backlog呢？backlog就是socket的监听队列，当一个请求（request）尚未被处理或建立时，他会进入backlog。而socket server可以一次性处理backlog中的所有请求，处理后的请求不再位于监听队列中。当server处理请求较慢，以至于监听队列被填满后，新来的请求会被拒绝
    net.core.somaxconn = 65535
    net.core.netdex_max_backlog = 65535
    net.ipv4.tcp_max_syn_backlog = 65535
    

    加快TCP连接,快速回收TCP连接资源
    net.ipv4.tcp_fun_timeout = 10
    net.ipv4.tcp_tw_reuse = 1
    net.ipv4.tcp_te_recycle = 1
    

    加快资源回收效率
    net.ipv4.tcp_keepalive_time = 120
    net.ipv4.tcp_keepalive_intvl = 30
    net.ipv4.tcp_keepalive_probes = 3
    

    kernel.shmmax = 4294967295
    这个参数应该设置的足够大，以便能在一个共享内存段下容纳下整个innodb缓冲池的大小
    
    vm.swappiness = 0
    MYsql上保留内存交换分区还是有必要的
    
    

    增加资源限制(/etc/security/limit.conf)
    * soft nofile 65535
    * hard nofile 65535
    * 表示对所有用户有效
    soft 指的是当前系统生效的配置
    hard 表明系统中所能设定的最大值
    nofile 表示所限制的资源是打开文件的最大数目
    65535 就是限制的数量
    

    磁盘调度策略(/sys/block/devname/queue/scheduler)
    
    cat /sys/block/sda/queue/scheduler
    noop anticipatory deadline [cfq]
    
    noop(电梯式调度策略)
    deadline(截止时间调度策略) 对数据库最好选择
    anticipatory(预料I/O)
    
    改变磁盘调度策略(/sys/block/devname/queue/scheduler)
    echo deadline > /sys/block/devname/queue/scheduler
    

• 文件系统对性能的影响

  • XFS对性能最好

    data = writeback | ordered | journal
    常用的文件系统的配置
    /dev/sda1/ext4  noatime,nodiratime,data=writeback 1 1