MySQL-性能优化_影响MySQL性能的因素分析及解决方案

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL-性能优化_影响MySQL性能的因素分析及解决方案

生猛干货

带你搞定MySQL实战,轻松对应海量业务处理及高并发需求,从容应对大场面试


官方文档

https://dev.mysql.com/doc/

如果英文不好的话,可以参考 searchdoc 翻译的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html


影响性能的几个因素

  • 硬件资源(CPU 、内存、磁盘等)
  • 操作系统的区别
  • MySQL的存储引擎的选择
    MyISAM: 不支持事务,表级锁
    InnoDB: 事务级存储引擎,完美支持行级锁,事务ACID特性。
    但 比不是说 MyISAM比InnoDB差,看使用场景
  • 数据库参数设置
  • 数据库结构设计和SQL语句 ,毫无疑问 这个是最最重要的因素 —> mysql 可以开启慢查询监控耗时长的SQL

CPU

更多的CPU or 更快的CPU ?

主要从以下几个方面考虑:

  • (1)CPU密集型的应用?

分析下我们的应用的类型,是的话,要提高SQL的运行效率,那就需要更快的CPU。

还有一点需要注意 MySQL不支持多CPU对同一SQL的并发处理,也就是说一个SQL只能运行在一个CPU的核上。


  • (2) 系统的并发量如何 ?

一个SQL只能跑在一个CPU上,如果有32个CPU呢 ,是不是同时可以跑32个SQL呢? 就是我们前面说的QPS 。 一般web应用,CPU的核心数量比CPU的主频更重要。


  • (3) MySQL的版本 ?

5.6、 5.7以上的版本,对多核CPU的支持更好。


  • (4) 64位操作系统 OR 32位操作系统

32的操作系统,进程所能寻址的最大内存也就4G。

总结

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

内存

MyISAM : 索引 缓存在内存 ,数据通过OS来缓存

InnoDB : 索引和数据都是通过内存来缓存

举个例子, 100G的数据量, 内存64G,这个时候增加内存对性能有提高。 如果内存是256G,还是慢,那就是其他问题了。

当然了,内存多了,对于数据的读和写都是好处的,都可以利用缓存来提高读写性能。

如何选择内存呢? ----> 建议选择主板支持的最大内存频率, 跟CPU一个道理。

内存的型号,保持一致, 单个内存尽可能的大,都是比较好的选择。

配置的时候,要考虑数据的增长,比如100G的数据, 128G内存可能已经满足了要求,但数据可能增长较快,这个时候256的更佳。

总结

  • 选择主板所能使用的最高频率的内存
  • 内存尽可能的多

磁盘的配置和选择

主流的4种

  • 使用传统的机械硬盘
  • 使用RAID增强传统硬盘的性能
  • 使用固态存储SSD和PCIe卡
  • 使用网络存储NAS和SAN

传统的机械硬盘

传统的机械硬盘的读取数据的过程

  1. 移动磁头到磁盘表面上正确的位置
  2. 等待磁盘旋转,使所需的数据在磁头之下 ----> 访问时间
  3. 等磁盘旋转过去,所需的数据被磁头读出 ------> 传输速度

考虑的话 1. 存储容量 2.传输速度 3 访问时间 4 主轴的转速(常见的7200转、1万5千转等等) 5. 物理尺寸


RAID

RAID : 磁盘冗余队列 (Redundant Arrays of Independent Disks )

RAID是一种将多个容量较小的磁盘组成一组容量更大的磁盘,并提供数据冗余来保证数据完整性的技术 。

Linux-Raid0、Raid1、Raid5、Raid10初探


SSD 或者 PCIe卡

SSD

  • 比普通的机械盘有更好的随机读写性能
  • 支持更好的并发, I/O 性能好
  • 缺点: 长时间密集的写,容易造成损坏

特点:

  • 可以使用SATA接口,可以替换传统的磁盘而不需要任何的改动。
  • SATA接口的SSD同样支持RAID
  • SATA的SSD 3.0 接口如果放到了 2.0接口上,受2.0接口的性能影响,性能会下降

举个例子 SATA3.0接口 6Gbps , 放到SATA2.0接口上 最多只能用到2.0的 3Gbps


PCIe 卡(Fushion IO)

  • 无法使用SATA接口,需要独特的驱动和配置
  • 比SSD性能好,但价格也比SSD贵
  • PCIe 也会吃服务器的内存
  • 支持PCIe的Raid控制器比较少,而且成本相当高

固态存储使用的场景

  • 大量随机I/O的场景
  • 解决单线程负载的I/O 瓶颈

举个例子

主从节点的MYSQL, 有一个SSD, 给哪个节点用呢 ?

----> 从节点 上用。 主节点上写 ,是多线程, 从节点的复制,单线程,为了减少延迟,建议放到从服务器上。


网络存储SAN (光纤传输)和 NAS (带宽传输)

SAN: Storage Area Network

NAS: Network Attached Storage

是两种外部文件存储设备加载到服务器上的方法。

SAN设备通过光纤连接服务器,设备通过块接口访问,服务器可以将其当做硬盘使用。 可以承受大量的顺序读写,但随即读有的时候不如RAID

NAS 使用 宽带来传输,有延迟。

使用场景的话: 数据备份等 。

总结

  • PCIe > SSD > Raid10 > 本地磁盘 > NAS

网络

网络带宽 —> 低延时 、吞吐量达

网络质量—> 少抖动

建议

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

操作系统

windows 和 linux , 大小写的区别,可以通过修改MySQL的配置来调整。

以CentoOS为例来了解下系统的参数优化

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

如需增加 可以直接追加到该文件的末尾

net.core.somaxconn=65535  # Linux kernel参数,表示socket监听的backlog(监听队列)上限
net.core.netdev_max_bakclog=65535 # 允许发送到队列中的数据包数目
net.ipv4.tcp_max_syn_backlog=65535 # #表示SYN队列长度,默认1024,改成65535,可以容纳更多等待连接的网络连接数。
net.ipv4.tcp_fin_timeout = 10 #表示如果套接字由本端要求关闭,这个参数决定了它保持在FIN-WAIT-2状态的时间
net.ipv4.tcp_tw_reuse = 1 #表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接,默认为0,表示关闭;
net.ipv4.tcp_tw_recycle = 1 #表示开启TCP连接中TIME-WAIT sockets的快速回收,默认为0,表示关闭。

TCP/IP及内核参数优化调优

net.core.wmen_default = 87380 # 表示内核套接字发送缓存区默认的大小。
net.core.wmen_max = 16777216 # 表示内核套接字发送缓存区最大大小。
net.core.rmem_default = 87380 # 默认的TCP数据接收缓冲
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  # (4G)单个共享内存段的最大值,应该设置的足够大,以便能在一个共享段下容纳整个Innodb缓冲池的大小 。 可以取物理内存-1 byte 
vm.swappiness = 0 #  除非虚拟内存满了,否则不会使用交换分区

其实就是swap交换分区的设置, 当操作系统没有足够的内存时,就会将一些虚拟内存写到磁盘的交换区中,这样就会发生内存交换。

然后执行sysctl -p命令,使上述修改生效


资源限制的配置文件 /etc/security/limits.conf

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

这俩参数,重启系统才生效。


磁盘调度策略

I/O 调度算法再各个进程竞争磁盘I/O的时候担当了裁判的角色,以求得尽可能最好的整体I/O性能。

在linux下面列出4种调度算法

  • CFQ (Completely Fair Queuing 完全公平的排队)(elevator=cfq) 默认策略
  • Deadline (elevator=deadline): 试图把每次请求的延迟降至最低
  • NOOP (elevator=noop):FIFO队列形式处理
  • Anticipatory (elevator=as):对读操作优化服务时间,在提供一个I/O的时候进行短时间等待,使进程能够提交到另外的I/O。
#查看当前IO
[root@artisan ~]#  cat /sys/block/sd*/queue/scheduler
noop [deadline] cfq 
[root@artisan ~]# 

文件系统

windows -----> FAT 和 NTFS

WinServer 只有 NTFS 这种可以选择

Linux —> EXT3 EXT4 XFS

EXT3 EXT4系统的挂载参数 /etc/fstab

# data的可配置选项
data=writeback | ordered |journal 
noatime 
nodiratime 
/dev/sda1/ext4 noatime,nodiratime,data-writeback 1 1


搞定MySQL


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
399 66
|
5天前
|
缓存 关系型数据库 MySQL
ThinkPHP框架show columns引发mysql性能问题
ThinkPHP框架的show columns引发mysql性能问题,结尾有关闭方式。
30 13
|
2月前
|
SQL 关系型数据库 MySQL
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
123 7
MySQL事务日志-Undo Log工作原理分析
|
2月前
|
关系型数据库 MySQL 数据库
mysql慢查询每日汇报与分析
通过启用慢查询日志、提取和分析慢查询日志,可以有效识别和优化数据库中的性能瓶颈。结合适当的自动化工具和优化措施,可以显著提高MySQL数据库的性能和稳定性。希望本文的详解和示例能够为数据库管理人员提供有价值的参考,帮助实现高效的数据库管理。
57 11
|
2月前
|
Cloud Native 关系型数据库 MySQL
无缝集成 MySQL,解锁秒级数据分析性能极限
在数据驱动决策的时代,一款性能卓越的数据分析引擎不仅能提供高效的数据支撑,同时也解决了传统 OLTP 在数据分析时面临的查询性能瓶颈、数据不一致等挑战。本文将介绍通过 AnalyticDB MySQL + DTS 来解决 MySQL 的数据分析性能问题。
|
1月前
|
缓存 NoSQL 关系型数据库
MySQL原理简介—4.深入分析Buffer Pool
本文介绍了MySQL的Buffer Pool机制,包括其作用、配置方法及内部结构。Buffer Pool是MySQL用于缓存磁盘数据页的关键组件,能显著提升数据库读写性能。默认大小为128MB,可根据服务器配置调整(如32GB内存可设为2GB)。它通过free链表管理空闲缓存页,flush链表记录脏页,并用LRU链表区分冷热数据以优化淘汰策略。此外,还探讨了多Buffer Pool实例、chunk动态调整等优化并发性能的方法,以及如何通过`show engine innodb status`查看Buffer Pool状态。关键词:MySQL内存数据更新机制。
|
3月前
|
SQL 关系型数据库 MySQL
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
196 11
|
2月前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
510 0
|
2月前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
1月前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
175 42