什么影响了Mysql的性能?

服务器硬件对MYSQL性能的影响

CPU

  • 1.64位的CPU一定要工作在64位的系统下

  • 2.对于并发比较高的场景CPU的数量比频率重要

  • 3.对CPU密集性场景和复杂的SQL则频率越高越好

内存

  • 1.选择主板所能够使用的最高频率的内存

  • 2.内存的大小,越大越好

  • 3.足够的内存虽然可以尽量减少随机I/O来增加服务器性格,但是无法避免随机I/O的发生,产生随机读写就会大大降低服务器的性能。

I/O子系统

  • 1.PCIE -> SSD(传统接口) -> Raid -> SAN(网络存储)

OS操作系统

  • 1.Windows(文件系统不明感)

  • 2.FreeBSD(老版本支持不好,新版改进)

  • 3.Solaris(SUM公司)

  • 4.Linux

常用的Centos参数优化

内核网络相关参数

  • net.core.somaxconn=65535

  • net.core.netdev_max_backlog=65535 #网络接口接受网络数据包的速率比系统处理数据包快的时候,允许被发送到队列中的数据包的最大数目

  • net.ipv4.tcp_max_syn_backlog=65535 #还未连接可以保存在数据包的最大数目

系统相关参数

  • net.ipv4.tcp_fin_timeout=10 #连接比较大的系统,有大量的连接处于等待状态,加快TCP回收的连接速度

  • 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 #在认定的TCP连接失效之前,最多发送多少个探测消息

  • 内存相关的参数

    • kernel.shmmax=4294967295 #用于定义单个Linux内存段的最大值

注意;
1.这个参数应该要设置足够大,才能在一个共享内存段下容纳整个Innodb的缓存池的大小
2.这个值的大小对于64位linux系统,可以取到的最大值为物理值-1byte,建议值为大于物理内存的一半,一般取值大于Innodb缓存池的大小即可,可取物理内存-1byte

  • vm.swappiness=0

当操作系统内存不足够的时候就将一部分暂时不使用的存储在swap中,但是这样会降低MYSQL的性能

  • 增加资源的限制(/etc/security/limit.conf)

这个文件实际上是Linux PAM也就是插入式认证模块配置文件,打卡文件的数量

* sotf nofile 65535
* hard nofile 65535 
* 表示对所有用户有效
soft 指的是当前系统生效的设置
hard 表明系统中所能设定的最大值
nofile 表示所限制的资源是打开文件的最大数目
65535 就是限制的数量
结论:可以打开的文件数量增加到65535个保证可以打开足够多的文件句柄
注意:这个文件的修改需要重启系统才能生效
  • 磁盘调度策略(/sys/block/devname/queue/scheduler)

    cat /sys/block/sda/queue/scheduler

noop(电梯式调度策略)
NOOP实现了一个FIFO队列,它像电梯的工作方式一样对I/O请求进行组织,当有一个新的请求到来的时候,它将请求合并到最近的请求后,以此来保证请求同一介质,NOOP倾向饿死读而利于写,因此NOOP对于闪存设备、RAM以及嵌入式系统是最好的选择
deadline(截止时间调度策略)
Deadline确保了在一个截止时间内服务请求,这个截止时间是可以调度的,而默认读期限短于写期限。这样就防止了写操作因为不能被读取而饿死的现象,Deadline对数据库类应用是最好的选择
anticipatory(预料I/O调度策略)
本质上与Deadline一样,但是在最后一次读操作后,要等待6毫秒,才能继续进行对其他I/O请求进行调度,它会在每6毫秒中插入新的I/O操作,而会将一些小写入流合并成一个大写的入流,用写入延迟换取最大的写入吞吐量,AS适合于写入较多的环境,比如文件服务器,AS对数据库环境表现很差

  • 文件系统对性能的影响

  • ext3/4系统的挂载参数(/etc/fstab)
规则(日志策略):
data = writeback(源数据和数据写入不同步) | ordered(只是记录源数据,提供一些数据的保证)| journal(原子日志对Innodb没必要)
noatime | nodiratime (禁止记录文件访问的时间和目录时间,加快写的操作)
完整版:
/dev/sda1/etx4 noatime,nodiratime,data=writeback 1 1

Mysql本身的性能瓶颈

采用C/S架构,当链接上Mysql的客户端中,会开启一个线程,连接的查询只会在这个线程中进行查询。

  • 第一层:客户端(通过各种协议连接上Mysql客户端)

  • 第二层:核心服务层

  • 第三层:存储引擎层

MyISAM

Mysql5.5之前的版本默认的都是Myisam存储引擎

  • 临时表:在排序、分组等操作中,当数量超过一定的数量大小后,由于查询优化器建立的磁盘临时表

  • Myisam存储引擎由MYD(数据文件)和MYI(索引文件)组成

文件名称 相关作用
MyISAM.frm 记录表的结构
MyISAM.MYD 存放表数据的文件
MyISAM.MYI 存放表索引的文件
  • Myisam引擎特性

  • 并发性与锁级别

  • 表损坏修复

    • check table myIsam; #检查的当前的存储引擎的状态

    • repair table myIsam; #修复损坏的表

    • myisamchk –help #提供命令行的修复,不能同时进行修复否则表损坏

  • 不是事务的存储引擎

  • 支持全文索引

  • 支持数据的压缩

    • myisampack –help #进行压缩

使用限制

  • 版本 < Mysql5.0时候默认表大小为4G
    • 如存储大表则要修改MAX_Rows和AVG_ROW_LENGTH
  • 版本 > Mysql5.0时默认支持256TB

适用场景

  • 非事务型应用

  • 只读类型应用(加共享锁,互不影响)

  • 空间类应用(GPS,空间函数进行运算的数据)

Innodb

Innodb存储引擎的特性

  • Innodb是一种事务性存储引擎

  • 完全支持事务的A(原子性)C(一致性)I(隔离性)D(持久性)特性

  • Redo log 和 UndoLog

  • Innodb支持行级锁

    • 行级锁可以最大程度的支持并发

    • 行级锁是由于存储引擎层实现的

  • Mysql5.5以及之后的默认版本都是Innodb

  • Innodb使用表空间进行 数据存储

    • innodb_file_per_table

    • ON:独立表空间:tablename.idb

    • OFF:系统表空间:ibdataX(X代表从1开始的任意数字)

什么是锁?

  • 1.锁对于主要作用是管理共享资源的并发访问
  • 2.锁用于实现事务的隔离性

锁的类型?

  • 1.共享锁(读锁)
  • 2.独占锁(写锁)

锁的粒度?

只要相互之间不产生阻塞多少锁都可以

  • 1.表级锁
  • 1.行级锁(锁开销大,最大提高性能)

阻塞和死锁

  • 什么是阻塞

    因为不同锁之间的兼容关系,在有些时候一个事务中的锁需要等待另外一个事务中的锁释放,它所暂用的资源就是阻塞。阻塞为了保证事务的可以并发正常运行,但是当一个系统中出现了大量的阻塞往往是出现了问题占用大量的资源。

  • 什么是死锁

    产生于多个事务之间,相互之间占用对方等待的资源,死锁会自动发现,并且在多个死锁的事务中选择一个资源占用最少的事务来进行回滚操作,这样可以使其他事务正常运行了。死锁可以系统自动处理,频繁出现大量的死锁,就需要去排错。

系统表空间和独立表空间要如何选择

比较:

  • 系统表空间无法简单的收缩文件大小

  • 独立表空间可以通过optimeize table 命令收缩系统文件

  • 系统表空间会产生I/O瓶颈

  • 独立表空间可以同时向多个文件刷新数据

建议:

  • 对innodb使用独立表空间

表的转移步骤

将原来的存在于系统表空间中的表转移到独立表空间中的方法

步骤

  • 1.使用mysqldump导出所有的数据库表数据

  • 2.停止Mysql服务,修改参数,并删除Innodb相关文件

  • 3.重启mysql服务,重建Innodb系统表空间

  • 4.重新导入数据

  • innodb数据字典信息

  • 存放Mysql源数据的信息,与数据库对象相关的信息。

系统表空件虽然被迁移出来可但是还有存在一些问题,Mysql使用Frm文件来存储,mysql服务器层产生的文件,保证事务的安全性,是简单二进制的文件,而innodb字典是通过避数来进行数据管理的。

  • Undo 回滚段

适用场景

常见的存储引擎

csv

文件系统存储特点

  • 1.以CSV格式进行数据存储

  • 2.所有列必须都是不能为NULL的

  • 3.不支持索引,不适合大表,不适合在线处理

  • 4.可以对数据文件直接编辑

    • 数据以文本方式存储在文件中
    • .csv文件存储表内容
    • .csm文件存储表的元数据如表状态和数据量
    • .frm文件存贮表结构信息

适合场景

  • 合适作为数据交换的中间表

Archive

文件系统存储特点

  • 对同一个数据级别的数据量,archive比myisam和innodb更加的节约存储空间

  • 以zlib对表数据进行压缩,磁盘I/O更少

  • 数据存储在ARZ为后缀的文件中

存储特点

  • 只支持insert和select操作

  • 只允许在自增ID列上加索引

  • 支持行级锁和专用的缓冲区,可以实现高并发的插入

使用场景

  • 日志和数据的采集应用

Memory

文件系统存储特点

  • HEAP存储引擎,使数据保存在内存中

  • 容器丢失的性的,但是表结构任能保存下来

  • I/O效率比myisam高很多

功能特点

  • 支持HASH索引(等值查找)和BTree索引(范围查找)

  • 所有字段都是为固定的长度 varchar(10)=char(10)

  • 不支持BLOG和TEXT等大字段

  • Memory存储引擎使用表级锁

  • 最大大小由max_heap_table_size参数决定,默认为16M(修改这个参数是对已经存在的存储引擎的表示无效的,如果想对已经存在的表生效则需要对表进行重建)

使用场景

  • 用于查找或者是映射表,例如邮编地区对应的表

  • 用于保存数据分析中产生的中间表

  • 用于缓存周期性聚合数据的结果表

如何选择正确的存储引擎

参考条件

  • 事务

  • 备份

  • 奔溃恢复

  • 存储引擎的特有特性

  • 不混合使用存储引擎

Mysql服务器参数调优

内存相关项

I/O相关参数

它是一种事务存储引擎,为了减少提交事务的I/O开销,它使用了预写日志的方式
解释:提交事务时候写入事务日志中,而不是每次把修改的文件刷新到文件中,这样是为了提高I/O性能事务的修改会使得数据和索引文件都会随机的映射到磁盘空间随机的位置,写入文件产生大量的随机I/O,而记录日志只需要记录顺序的I/O,所以相比于脏数据到磁盘中,记录日志的方法要快得多,一旦事务日志安全的的写入到磁盘中,事务也就持久化了,即使变更还没有写入文件,宕机了,这个时候我们任然可以通过事务日志,来恢复我们的事务。

安全相关

其他相关

数据库结构设计和SQL优化

性能优化的顺序

  • 数据库结构设计和SQL语句

  • 数据库存储引擎的选择和参数配置

  • 系统选择及其优化

  • 硬件升级

发表评论