mysql性能优化学习笔记

时间：2023-03-08 22:59:51
mysql性能优化学习笔记

mysql性能优化

硬件对数据库的影响

CPU资源和可用内存大小

  • 服务器硬件对mysql性能的影响
  • 我们的应用是CPU密集型？
  • 我们的应用的并发量如何？

数量比频率更好

64位使用32位的服务器版本

32位不能使用超过4G的内容，因此选择的时候需要注意。

myisam

innodb

磁盘的配置和选择

  • 机械硬盘：

    存储容量

    传输速度

    访问时间

    主轴转速

    物理尺寸

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

    RAID：一系列小磁盘组成大磁盘，数据冗余

    RAID 0 ：成本最低，但是没有数据冗余会造成数据丢失。

    RAID 1 ：磁盘镜像，一个磁盘的数据镜像到另外一个磁盘。数据冗余性好，读性能好。

    RAID 5 ：分布式奇偶校验磁盘阵列。如果单一一块磁盘损坏可以通过其余磁盘值进行恢复，如果同时两块损坏则无法恢复。写比较慢。读比较快。

    RAID 10 ：是数据库最好的选择。又称为分片镜像。

  • 使用固态存储SSD或PCIe卡

    相比传统机械硬盘有更好的随机读写性能

    有更好的并发支持

    但固态磁盘更容易损坏

    适用于大量随机IO的情况

    使用解决单线程IO负载的IO瓶颈

    如果只有一个ssd硬盘，应该使用在从服务器上确保数据的安全。

  • 使用网络存储SAN和NAS

    SAN设备通过光纤连接到服务器上，设备通过块接口访问。服务器可以当作硬盘使用。

    可以使用大量的顺序读写

    随机访问和随机写比较慢

    NAS是通过网络访问协议进行访问，有网络访问的延迟。

    使用场景：

    不适用于数据库存储

    适用于数据库备份

** CPU **

> * 64位的CPU运行64位的系统

> * 高并发的场景，cpu数量比频率重要

> * CPU密集型的场景和复杂sql则频率越高越好

** 内存 **

  • 采用主板能使用的最高频率的内存
  • 内存尽可能大

** I/O **

  • PCIe > SSD > Raid > 机械硬盘 > 网络存储（SAN）

操作系统对性能的影响-MySQL适合的操作系统

选择服务器版本，而不是桌面版本。

CentOS系统参数优化

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

  • 网络性能设置

    端口监听队列：net.core.somaxconn=65535

    接受数据的速率：net.core.netdev_max_backlog=65535

    未获得连接的请求和保存在内存中的数量，超过时会被丢弃：net.ipv4.tcp_max_sync_backlog=65535

  • 上述三个应该同时调大
tcp连接处理等待时间：net.ipv4.tcp_fin_timeout=10

net.ipv4.tcp_tw_reuse=1

net.ipv4.tcp_tw_recycle=1
  • 上述三个主要加快网络连接的回收。
net.core.wmem_default=87380

net.core.wmem_max=16777216

net.core.rmem_default=87380

net.core.rmem_max=16777216
  • 缓冲区接受和发送数据大小的最大值和默认值
探测的时间间隔（s）: net.ipv4.tcp_keepalive_time=120

消息重发时的时间间隔（s）: net.ipv4.tcp_keepalive_intvl=30

tcp连接超时前最多发送几次: net.ipv4.tcp_keepalive_probes=3
  • 内存

    kernel.shmmax = 4292967295
  • 这个参数应该设置得足够大，以便于在一个共享内存段中容纳下整个Innodb缓冲池的大小
  • 这个值的大小对于64位linux系统，可以取内存最大值-1byte，建议大于物理内存的一半。一般大于Innodb缓冲池的大小即可。
vm.swappiness = 0 ：当内存不足时会对系统性能产生比较大的影响。

Linux系统内存交换区（swap）：

vm.swapppiness = 0

	当系统内存不足时会将一些虚拟内存写入磁盘交换区（swap）中。

	由于使用swap会对mysql性能产生灾难性的影响。因此对mysql服务器是否使用swap分区存在一些争议。

	如果禁用swap分区也会带来一些影响：
  • 降低操作系统的性能
  • 容易造成内存溢出、崩溃或者被操作系统kill掉
	就上述影响而言，保留swap分区还是必要。

vm.swapppiness是告诉操作系统，除非内存完全满了，否则不使用swap。

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

打开文件数量的限制，加到/etc/security/limit.conf末尾即可：

	* soft nofile 65535

	* hard nofile 65535
  • *表示对所有用户有效
  • soft表示当前系统生效的配置
  • hard表示系统中能设置的最大值
  • nofile表示所限制的资源是打开文件的最大数量
  • 65535限制的数量

磁盘调度策略（/sys/block/devname/queue/scheduler）

cat /sys/block/devname/queue/scheduler

noop anticipatory deadline [cfq]   # 完全公平队列，一般用于桌面系统

noop：电梯式调度，饿死读满足写

deadline：截至时间策略，`数据库类型最好的选择`

anticipatory：和deadline一样，只是最后一次读操作后要等待6毫秒。合并多次写为一次写，适合于文件服务器，对数据库服务器表现很差。

将磁盘调度策略改为deadline

echo deadline > /sys/block/devname/queue/scheduler

文件系统对性能的影响

ext3、ext4、xfs（性能更高），上诉都具备日志功能，对数据安全性较好。

ext3、ext4系统挂载参数（/etcc/fstab）
  • data=writeback（Innodb最好的选择）|ordered|journal
  • noatime，访问时间
  • nodiratime，目录访问时间
/dev/sda1/ext4 noatime,nodiratime,data=writeback 1 1

相关文章