mysql 第17天 磁盘IO问题 应 用 优 化

磁盘io问题

实际上,我们前面提到的 SQL 优化、数据库对象优化、数据库参数优化,以及应用

程序优化等,大部分都是想通过减少或延缓磁盘读写来减轻磁盘 I/O 的压力及其对性能的
影响

增强磁盘 I/O 本身的性能和吞吐量也是一个重要方面

从磁盘阵列、符号链接、裸设备等更底层的方面来介绍提高磁盘 I/O 能力的一些技术和方法。

使用磁盘阵列

常见 RAID 级别及其特性

RAID 是 Redundant Array of Inexpensive Disks 的缩写,翻译成中文就是“廉价磁盘冗
余阵列”

选择 RAID 级别

 数据读写都很频繁,可靠性要求也很高,最好选择 RAID 10;
 数据读很频繁,写相对较少,对可靠性有一定要求,可以选择 RAID 5;
 数据读写都很频繁,但可靠性要求不高,可以选择 RAID 0。

虚拟文件卷或软 RAID

最初,RAID 都是由硬件实现的,要使用 RAID,至少需要有一个 RAID 卡。但现在,一些
操作系统中提供的软件包,也模拟实现了一些 RAID 的特性,虽然性能上不如硬 RAID,但相
比单个磁盘,性能和可靠性都有所改善。比如, Linux 下的逻辑卷(Logical Volume)系统 lvm2,
支持条带化(Stripe);Linux 下的 MD(Multiple Device)驱动,支持 RAID 0、RAID 1、RAID 4、
RAID 5、RAID 6 等。在不具备硬件条件的情况下,可以考虑使用上述虚拟文件卷或软 RAID
技术,具体配置方法可参见 Linux 帮助文档。

使用 Symbolic Links 分布 I/O

MySQL 的数据库名和表名是与文件系统的目录名和文件名对应的,默认情况下,创建
的数据库和表都存放在参数 datadir 定义的目录下。这样如果不使用 RAID 或逻辑卷,所有的
表都存放在一个磁盘设备上,无法发挥多磁盘并行读写的优势!在这种情况下,我们就可以
利用操作系统的符号连接(Symbolic Links)将不同的数据库或表、索引指向不同的物理磁盘,
从而达到分布磁盘 I/O 的目的

(1)将一个数据库指向其他物理磁盘

其方法是先在目标磁盘上创建目录,然后再创建从 MySQL 数据目录到目标目录的符号
连接:

shell> mkdir /otherdisk/databases/test
shell> ln -s /otherdisk/databases/test /path/to/datadir

(2)将MyISAM(其他存储引擎的表不支持)表的数据文件或索引文件指向其他物理磁盘。


对于新建的表,可以通过在CREATE TABLE语句中增加DATA DIRECTORY和INDEX DIRECTORY
选项来完成,例如:


对于已有的表,可以先将其数据文件(.MYD)或索引文件(.MYI)转移到目标磁盘,
然后再建立符号连接即可。需要说明的是表定义文件(.frm)必须位于MySQL数据文件

目录下,不能用符号连接。

(3)在Windows下使用符号连接。

以上介绍的是Linux/UNIX下符号连接的使用方法,在Windows下,是通过在MySQL数据文件
目录下创建包含目标路径并以“.sym”结尾的文本文件来实现的。例如,假设MySQL的数据
文件目录是C:\mysql\data,要把数据库foo存放到D:\data\foo,可以按以下步骤操做:

 创建目录D:\data\foo;
 创建文件C:\mysql\data\foo.sym,在其中输入D:\data\foo。

这样在数据库foo创建的表都会存储到D:\data\foo目录下。
注意:使用Symbolic Links存在一定的安全风险,如果不使用Symbolic Links,应通过启动参数
skip-symbolic-links禁用这一功能。

禁止操作系统更新文件的 atime 属性

atime 是 Linux/UNIX 系统下的一个文件属性,每当读取文件时,操作系统都会将读操作发生
的时间回写到磁盘上。对于读写频繁的数据库文件来说,记录文件的访问时间一般没有任何
用处,却会增加磁盘系统的负担,影响 I/O 的性能。因此,可以通过设置文件系统的 mount
属性,阻止操作系统写 atime 信息,以减轻磁盘 I/O 的负担。在 Linux 下的具体做法是:
修改文件系统配置文件/etc/fstab,指定 noatime 选项:

LABEL= /home    /home    ext3    noatime   1 2

然后重新 mount 文件系统:

#mount -oremount /home

完成上述操作,以后读/home 下文件就不会再写磁盘了。

用裸设备(Raw Device)存放 InnoDB 的共享表空间

MyISAM 存储引擎有自己的索引缓存机制,但数据文件的读写完全依赖于操作系统,
操作系统磁盘 I/O 缓存对 MyISAM 表的存取很重要

但 InnoDB 存储引擎与 MyISAM 不同

它采用类似 Oracle 的数据缓存机制来 Cache 索引和数据,操作系统的磁盘 I/O 缓存对其性能
不仅没有帮助,甚至还有反作用。因此,在 InnoDB 缓存充足的情况下,可以考虑使用 Raw
Device 来存放 InnoDB 共享表空间,具体操作方法如下。

(1)修改MySQL配置文件,在innodb_data_file_path参数中增加裸设备文件名并指定
newraw属性:

(2)启动MySQL,使其完成分区初始化工作,然后关闭MySQL。此时还不能创建或修
改InnoDB表。

(3)将innodb_data_file_path中的newraw改成raw:

小结

本章站在操作系统的角度介绍了如何对 MySQL 数据库进行优化,主要讨论了 I/O 的优化问
题、文件系统分布的优化问题等。在大多数的数据库系统中,磁盘 I/O 都会是影响系统性能
的瓶颈,希望通过本章,读者能够掌握一些减少磁盘 I/O 以提高系统性能的方法。

应用优化

在实际生产环境中，由于服务器本省的性能局限，就必须对前台的应用进行一些优化，

使得前台访问数据库压力最小。

下面是一些常用的方法

使用连接池

对于访问数据库来说,建立连接的代价比较昂贵,因此,我们有必要建立“连接池”以
提高访问的性能。

以前需要直接访问数据库
的地方,现在都改为从这个“池子”里面获取连接来使用。因为“池子”中的连接都已经预

先创建好,可以直接分配给应用使用,因此大大减少了创建新连接所耗费的资源。连接返回
后,本次访问将连接交还给“连接池”,以供新的访问使用。

减少对 MySQL 的访问

1  避免对同一数据做重复检索

应用中需要理清对数据库的访问逻辑。能够一次连接就能够提取出所有结果的,就不用
两次连接,这样可以大大减少对数据库无谓的重复访问

2  使用查询缓存

MySQL 的查询缓存(MySQL Query Cache)是在 4.1 版本以后新增的功能,它的作用是存
储 SELECT 查询的文本以及相应结果。如果随后收到一个相同的查询,服务器会从查询缓存
中重新得到查询结果,而不再需要解析和执行查询

查询缓存的适用对象是更新不频繁的表,当表更改(包括表结构和表数据)后,查询缓

存值的相关条目被清空。

 have_query_cache 表明服务器在安装是否已经配置了高速缓存

query_cache_size 表明缓存区大小,单位为 M。

query_cache_type 的变量值从 0 到 2,含义分别为:
0 或者 off(缓存关闭)
1 或者 on(缓存打开,使用 SQL_NO_CACHE 提示的 SELECT 除外)

2 或者 demand(只有带 SQL_CACHE 的 SELECT 语句提供高速缓存)

通过 SHOW STATUS 命令,可以监视查询缓存的使用状况

3  增加 CACHE 层

在应用中,我们可以在应用端加 CACHE 层来达到减轻数据库的负担的目的。

比如,可以把部分数据从数据库中抽取出来放到应用端以文本方式存储,然后有查询需求的
话,可以直接从这个“CACHE”中检索,由于这里的数据量小所以能够达到很高的查询效率,
而且也减轻了数据库的负担。当然这种方案还涉及很多其他问题,比如如果有数据更新怎么
办、多长时间刷新一次“CACHE”等,都需要根据具体应用环境进行相应的处理。

再比如用户可以在应用端建立一个二级数据库,把访问频度非常大的数据放到二级库上,
然后设定一个机制与主数据库进行同步,这样用户的主要操作都在二级数据库上进行,大大
地降低了主数据库的压力。

负载均衡

负载均衡(Load Balance)是实际应用中使用非常普遍的一种优化方法,它的机制就是利用
某种均衡算法,将固定的负载量分布到不同的服务器上,以此来减轻单台服务器的负载,达
到优化的目的。负载均衡可以用在系统中的各个层面中,从前台的 Web 服务器到中间层的
应用服务器,最后到数据层的数据库服务器,都可以使用。本节主要介绍 MySQL 数据库端
的一些负载均衡方法。

1   利用 MySQL 复制分流查询操作

利用 MySQL 的主从复制(具体介绍见第 29 章)可以有效地分流更新操作和查询操作

具体的实现是一个主服务器承担更新操作,而多台从服务器承担查询操作,主从之间通过复
制实现数据的同步。多台从服务器一方面用来确保可用性,一方面可以创建不同的索引以满
足不同查询的需要。

对于主从之间不需要复制全部表的情况,可以通过在主服务器上搭建一个虚拟的从服务

器,将需要复制到从服务器的表设置成 BlackHole 引擎,然后定义 replicate-do-table 参数只
复制这些表,这样就过滤出需要复制的 BINLOG,减少了传输 BINLOG 的带宽。因为搭建的虚
拟从服务器只起到过滤 BINLOG 的作用,并没有实际记录任何数据,所以对主数据库服务器
的性能影响也非常得有限。

通过复制来分流查询是减少主数据库负载的一个常用方法,但是这种办法也存在一些问
题,最主要的问题是当主数据库上更新频繁或者网络出现问题的时候,主从之间的数据可能
存在比较大的延迟更新,从而造成查询结果和主数据库上有所差异。因此应用在设计的时候
需要有所考虑。

2   采用分布式数据库架构

具体实现的时候,可以使用 MySQL 的 CLUSTER 功能(具体介绍见第 30 章)或者通
过用户自己编写的程序来实现全局事务。需要注意的是当前分布式事务只支持 InnoDB 存储
引擎,因此如果自己编写程序来实现分布式架构数据库的话,那么就必须采用 InnoDB 存储
引擎。

其他优化措施

 对于没有删除行操作的 MyISAM 表,插入操作和查询操作可以并行进行,因为没有
删除操作的表查询期间不会阻塞插入操作。对于确实需要执行删除操作的表,尽量
在空闲时间进行批量删除操作,并且在进行删除操作之后应该进行 OPTIMIZE 操作
来消除由于删除操作带来的空洞,以避免将来的更新操作阻塞其他操作。
 充分利用列有默认值的事实。只有当插入的值不同于默认值时,才明确地插入值。
这会减少 MySQL 需要做的语法分析从而提高插入速度。
 表的字段尽量不使用自增长变量,在高并发情况下该字段的自增可能对效率有比较
大的影响,推荐通过应用来实现字段的自增长。