分类 笔记 下的文章

MySQL技术内幕(InnoDB存储引擎索引)学习笔记02-索引

概述

InnoDB存储引擎支持以下几种常见的索引 :

  • B+树索引
  • 全文索引
  • 哈希索引

InnoDB存储引擎支持的哈希索引是自适应的,InnoDB会跟根据表的使用情况自动为表生成哈希索引,不能人为干预是否在一张表中生成哈希索引。

B+树索引就是传统意义上的索引。类似于二叉树。
B+树索引中的B不是代表的二叉(binary),而是代表的平衡(balance),因为B+树是从最早的平衡二叉树演化而来,但是B+树不是一个二叉树。

数据算法与结构

介绍B+树之前先介绍下相关的数据结构

二分查找

将记录有序化排列,在查找过程中采用跳跃式的方式查找,先以有序数列的重点位置为比较对象,如果要找的元素值小于该重点元素,则将待查序列缩小为左半部分,否则为右半部分。通过一次查找将查找区间缩小一半

二叉查找树和平衡二叉树

二叉查找树 :左子树的键值总是小于节点的键值,右子树的键值总是大于节点的键值
平衡二叉树 :首先要符合二叉查找树的条件,然后必须满足任何子节点的两个子树的高度最大差为1
平衡二叉树在插入,更新和删除节点时,需要做旋转操作,会有额外的开销

B+树

B+树是为磁盘或其他存取辅助设备设计的一种平衡查找树。在B+树中,所有记录节点都是按照键值的大小顺序存放在同一层的子节点上,由各子节点指针进行连接。

B+树的插入操作

为了保持B+树的平衡,对于新插入的键值可能需要做大量的拆分页(split)操作。因为B+树结构主要用于磁盘,页的拆分意味着磁盘操作,所以应该在尽可能的情况下尽量减少页的拆分操作。因此B+树同样提供了类似于平衡二叉树的旋转功能。

B+树的删除

B+树使用填充因子(fill factor)来控制树的删除变化,50%是填充因子的最小值。B+树的删除操作必须保证删除后叶子节点中的记录依然排序。

B+树索引

前面讨论的都是B+树的数据结构及其一般操作,B+树索引的本质就是B+树在数据库中的实现。在数据库中,B+树的高度一般都是在2~4层,这也就是说查找某一键值的行记录时最多只需要2到4次IO。一般的机械硬盘每秒至少可以做100次IO,2~4次的IO意味着查询时间只需要0.02~0.04秒。

数据库中的B+树可以分为聚集索引(clustered index)和辅助索引(secondary index),但是不管是聚集索引还是辅助索引,其内部都是B+树的,即高度平衡的,叶子节点存放所有的数据。聚集索引与辅助索引不同的是,叶子节点存放的是否是一整行的数据。

聚集索引

聚集索引就是按照每张表的的主键构建一颗B+树,同时叶子节点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中的数据也是索引的一部分。同B+树数据结构一样,每个数据页都通过一个双向链表来进行链接。

由于实际的数据页只能按照一颗B+树进行排序,因此每张表只能拥有一个聚集索引。在多数情况下,查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外,由于定义了数据的逻辑顺序,聚集索引能特别快的访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

数据页上存放的是完整的每行的记录,而在非数据页的索引页中,存放的是键值和指向数据页的偏移量,而不是一个完整的行的记录。

如果聚集索引必须按照特定的顺序存放物理记录,则维护成本会相当高,所以聚集索引的存储并不是物理上连续的,而是逻辑上连续的。这其中有两点:一是页通过双向链表链接,页按照主键的顺序排序;另一点是每个页中的记录也是通过双向链表进行维护的,物理存储上可以同样不按照主键存储。

聚集索引的另一个好处是,他对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户索要查询的数据

辅助索引

辅助索引(Secondary Index,也称为非聚集索引),叶子节点并不包含行记录的全部数据。叶子节点除了包含键值外,每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到索引相应的行数据。由于InnoDB存储引擎表是索引组织表,因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引并不影响数据在聚集索引中的组织,因此在每张表上可以有多个辅助索引。当通过辅助索引来寻找数据是,InnoDB存储引擎会遍历索引并通过叶级别的指针获得指向主键索引的主键,然后再通过主键索引来找到一个完整的行记录。举例来说,如果在一颗高度为3的辅助索引树中查找数据,那需要对这棵索引树遍历3次找到指定主键,如果聚集索引树的高度同样为3,那么还需要对聚集索引树进行3次查找,最终找到一个完整的行数据所在的页,因此一共需要6次逻辑IO访问以得到最终的数据页。

B+树索引的管理

添加删除索引

不赘述了,可以百度

可以通过show index from观察表上的索引,show index展现结果中每列的含义
Table : 索引所在的表名
Non_unique : 非唯一的索引,可以看到primary key是0,因此必须是唯一的
Key_name : 索引的名字,用户可以通过这个名字来执行drop index
Seq_in_index : 索引中该列的位置
Column_name : 索引列的名称
Collation : 列以什么方式存储在索引中,可以是A或NULL。B+树索引总是A,即排序的。如果使用了Heap存储引擎,并建立了Hash索引,这里就会显示NULL了。因为Hash是根据Hash桶存放索引数据,而不是对数据进行排序。
Cardinality : 非常关键的值,标识索引中唯一值的数目的估计值。Cardinality表的行数应尽可能接近1,如果非常小,如果非常小,那么可以考虑删除此索引
Sub_part : 是否是列的部分被索引。字段的类型为字符串时,只会以字符串的前面一部分字符作为索引
Packed : 关键词如何被压缩
Null : 是否索引的列含有NULL值。
Index_type : 索引的类型,InnoDB存储引擎只支持B+树索引,所以这里显示的都是BTREE。
Comment : 注释

Cardinality值非常关键,优化器会根据这个值来判断是否使用该索引。但是这个值并不是实时更新的,即并非每次索引的更新都会更新该值,因为这样代价太大了。因此这个值是不太准确的,只是一个大概的值。如果需要更新Cardinality的值,可以使用ANALYZE TABLE

Fast Index Creation

MySQL5.5版本之前存在的一个普遍被人诟病的问题是MySQL数据库对于索引的添加或删除的这类DDL操作,MySQL的操作过程为

  1. 创建一张临时表,表结果通过alter table新定义结果
  2. 把原表中数据导入到临时表中
  3. 删除原表
  4. 把临时表重命名为原来的表名

如果是一张很大的表,会需要很长的时间,并且在这段时间内,是不能对这张表进行操作的。

InnoDB从1.0.x版本开始支持一种称为Fast Index Creation(快速索引创建)的索引创建方式--检查FIC。

Cardinality值

什么是Cardinality

并不是所有的查询条件中出现的列都需要添加索引。对于什么时候添加B+树索引,一般经验是,在访问表中很少一部分时使用B+树索引才有意义。对于性别字段,地区字段,类型字段,他们可取值的范围很小,称为低选择性,这种时候添加索引其实是没有必要的。高选择性的字段,比如说用户邮箱,比如用户有邮箱。

InnoDB的Cardinality统计

InnoDB内部更新Cardinality的策略为

  • 表中1/16的数据已发生变化
  • stat_modified_counter > 2000000000

B+树索引的使用

联合索引

从本质上说,联合索引也是一颗B+树,不同的是联合索引的键值数量不是1,而是大于等于2。
和单个键值的B+树一样,键值都是排序的,通过叶子节点可以逻辑上顺序地读出所有数据。比如一个索引idx(a,b)(1,1),(1,2),(2,1),(2,4),(3,1),(3,2)。数据按(a,b)的顺序进行了存放

因此对于查询select * from t where a = 1 and b = 2,显示是可以使用这个联合索引的。对于单个的a列的查询select * from t where a = 1,也可以使用这个索引。但是对于单个b列的查询select * from t where b = 2则是不能使用这个索引的。可以看到b的顺序为1,2,1,4,1,2,显然是不排序的,因此对于b列的查询使用不到(a,b)的索引。

联合索引的第二个好处是已经对第二个键值进行了排序处理,这样可以避免一次排序操作。

联合索引(a,b)其实是根据列a,b进行排序,因此下列语句可以直接使用联合索引得到结果

然而对于联合索引(a,b,c)来说,下列语句同样可以直接通过连接索引拿到结果
select ... from t where a = 1 order by b
select ... from t where a = 1 and b = 2 order by c
但是对于下面的语句,联合索引不能直接得到结果,其还需要执行一次filesort操作,因为(a,c)并未排序
select ... from t where a = 1 order by c

覆盖索引

InnoDB支持覆盖索引(covering index,或称覆盖索引),则从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息,故其大小远小于聚集索引,因此可以减少大量的IO操作。InnoDB版本小于1.0或MySQL版本为5.0或以下的,不支持此特性。

对于辅助索引而言,由于其包含了主键信息,因此其叶子节点存放的数据为(primary key1,primary key2,...,key1,key2,key3...)。例如,下面的语句都可仅适用一次辅助联合索引完成查询
select key2 from t where key1 = xxx
select primary key2, key2 from t where key1 = xxx
select primary key1, key2 from t where key1 = xxx
select primary key1, primary key2, key2 from t where key1 = xxx

覆盖索引还有一个好处是对某些统计问题而言的。当InnoDB选择适用辅助索引来进行统计时,由于辅助索引远小于聚集索引,选择辅助索引可以减少IO操作。如下面的SQL
select COUNT(*) from t
通常情况下InnoDB引擎不会查询聚集索引来进行统计。但是当表上有辅助索引时,辅助索引远小于聚集索引,选择辅助索引可以减少IO操作,故优化器会选择辅助索引进行统计。

表bug_log有(user_id, buy_date)的联合索引,这里只根据列b进行条件查询,一般情况下是不可以选择b中所谓的查询条件。但是如果是统计操作,并且是覆盖索引,则优化器会进行选择。
select count(*) from buy_log where buy_date >= '2011-01-01'
表buy_log有联合索引,这里只根据列b进行条件查询,一般情况下是不能使用该联合索引的,但是这句SQL查询是统计操作,并且可以利用到覆盖索引的信息,因此优化器会选择改联合索引。

优化器选择不使用索引的情况

当用户要查询的数据是整行信息,而辅助索引不能覆盖到我们要查询的信息,因此在对辅助索引查询到指定的数据后,还需要进行一次书签访问来查找到整行的信息。虽然辅助索引数据是顺序存放的,但是再一次进行书签查找的数据则是无序的,因此变成了磁盘上的离散读操作。如果要求访问的数据量很小,则优化器还是会选择辅助索引,但是当访问量的数据占整个表的蛮大一部分时(一般是20%左右),优化器会选择通过聚集索引来查找数据。因为之前提到过,顺序读要远远快于离散读。

因此对于不能进行索引覆盖的情况,优化器选择辅助索引的情况是,通过辅助索引查找的数据是少量的。这是由于当前传统机械硬盘的特性决定的,即利用顺序读来替代离散读。若使用的磁盘是固态硬盘,随机操作足够快,同时有足够的自信确认使用辅助索引可以带来更好的性能,那么使用关键词force index来强制使用某个索引

哈希表和全文索引就不记录了。。。

MySQL技术内幕(InnoDB存储引擎概述)学习笔记01-MySQL基本概念

MySQL组成部分

  • 连接池组件
  • 管理服务和工具组件
  • SQL接口组件
  • 查询分析器组件
  • 优化器组件
  • 缓冲(Cache)组件
  • 插件式存储引擎
  • 物理文件

MySQL是单进程多线程

插件式存储引擎是MySQL的一大特点

InnoDB体系架构

  • 内存块
  • 后台线程

内存块

  • 维护所有进程/线程需要访问的多个内部数据结果
  • 缓存磁盘上的数据,方便快速的读取,同时对磁盘文件的数据修改之前在这里花村
  • 重做日志(redo log)缓冲

后台线程

作用

  • 刷新内存池中的数据,保证缓冲池中的内存缓存的是最近的数据。
  • 将已修改的数据文件刷新到磁盘文件
  • 保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态

不同的线程

InnoDB是多线程的模型,其后台有多个不同的线程,负责处理不同的任务

  • Master Thread
    是一个核心线程,主要将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括脏页的刷新,合并插入缓冲(INSERT BUFFER),UNDO页的回收
  • IO Thread
    在InnoDB中大量使用了AIO(Async IO)来处理写请求,这样可以极大的提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调(call back)处理。InnoDB 1.0版本之前共有4个IO Thread,分别是write,read,insert buffer和log IO Thread。在Linux平台下,IO Thread的数量不能进行调整,但是在Windows下可以通过innodb_file_io_threads来增大IO Thread。从InnoDB 1.0.x版本开始,read thread和write thread分别增大到4个,并且不再使用innodb_file_io_threads参数,而是分别使用innodb_read_io_threads和innodb_write_io_threads参数进行设置。
  • Purge Thread
    事务被提交后,其所使用的undolog可能不再需要,因此需要Purge Thread来回收已经使用并分配的undo页。在InnoDB 1.1版本之前,purge操作仅在InnoDB存储引擎的Master Thread中完成。而从1.1版本开始,purge操作独立到单独的线程中进行,以此来减轻Master Thread的工作,从而提高CPU的使用率以及提升存储引擎的性能
  • Page Cleaner Thread
    在1.2.x版本引入。作用是将之前版本中脏页的刷新操作都放到单独的线程来完成。其目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞,进一步提高InnoDB存储引擎的性能。

内存

  • 缓冲池

InnoDB存储引擎是基于磁盘存储的,并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统。在数据库系统中,由于CPU速度与磁盘速度之间的鸿沟,基于磁盘的数据库系统通常使用缓冲池极速来提高数据库的整体性能。
缓冲池简单说就是一块内存区域,通过内存的速度来弥补磁盘速度相对较慢的速度。在数据库中进行读取页的操作,首先将从磁盘读取到的页放在缓冲池中,这个过程称为将页FIX在缓冲池中。下一次再读相同的页时,首先判断该页是否在缓冲池中。如在缓冲池中,称该页在缓冲池中被命中,直接读取该页。否则,读取磁盘上的页。
对于数据库中页的修改操作,则首先修改在缓冲池中的页,然后再以移动的频率刷新到磁盘上。这里需要注意的是,页从缓冲池刷新回磁盘的操作并不是在每次更新时触发,而是通过一种称为Checkpoint的机制刷新回磁盘。同样,这也是为了提高数据库的整体性能。
综上所述,缓冲池的大小直接影响着数据库的整体性能。由于32位操作系统的限制(最多将该值设置成3G,也可以通过操作系统的PAE选项来获得最大64GB的支持),建议数据库服务器采用64位的系统。

对于InnoDB而言,其缓冲池的配置通过参数innodb_buffer_pool_size来设置。

具体来看,缓冲池重的数据页类型有 : 索引页,数据页,undo页,插入缓冲(insert buffer),自适应哈希索引(adaptive hash index),InnoDB存储的锁信息(lock info),数据字典信息(data dictionary)等。不能简单的认为缓冲池只是缓存索引页和数据页,他们只是占缓冲池的很大一部分而已。

从InnoDB 1.0.x开始,允许有多个缓冲池实例。每个页根据hash值平均分配带不同缓冲池实例中,这样做的好处是减少数据库内存的资源竞争,增加数据库的并发处理能力。可以通过innodb_buffer_pool_instances来进行设置,该值默认为1。

在配置文件中将innodb_buffer_pool_instances来进行配置,该值默认为1。将该值设置为大于1就能得到多个缓冲池实例。再通过SHOW ENGINE INNODB STATUS可以查看

  • LRU List,Free List和Flush List

通常来说,数据库中的缓冲池是通过LRU(Last Recent Used,最近最少使用)算法来进行管理的。即最频繁使用的在LRU列表前面,最少使用的在LRU列表的尾端。当缓冲池不能存放新读取到的页的时候,将首先释放LRU列表尾端的页。
InnoDB中对传统的LRU算法做了优化。在InnoDB中加入了midpoint位置。新读取到的页,虽然是最新访问的页,但是不是直接放到LRU列表的首部,而是加入到LRU列表的midpoint位置。这个算法在InnoDB存储引擎下成为midpoint insertion strategy。默认配置下,该位置在列表长度的5/8处。midpoint可由参数innodb_old_blocks_pct控制。

那为什么不采用朴素的LRU算法,直接将读取的页放入到LRU列表的首部呢?这是因为若直接将读取到的页放入到LRU的首部,那么某些SQL操作可能会使3缓冲池中的页被刷新出,从而影响缓冲池的效率。常见的这类操作为索引或者数据的扫描操作。这类操作需要访问表中的很多页,甚至是全部的页,而这些页通常来说又仅在这次查询操作中需要,并不是活跃的热点数据。如果页被放入LRU列表的首部,那么非常可能将所需要的热点数据页从LRU列表中移除,在下一次需要该页时,InnoDB存储引擎需要再次访问磁盘。

为了解决这个问题,InnoDB存储引擎引入了另一个参数来进一步管理LRU列表,这个参数是innodb_old_block_time,用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。因此当需要执行上说所说的SQL操作时,可以通过下面的方法尽可能使LRU列表中的热点数据不被刷出。

set global innodb_old_blocks_time = 1000;
# data or index scan operation
set global innodb_old_blocks_time = 0;

如果预估到活跃的热点数据不止63%,那么在执行SQL语句之前,还可以通过下面的语句来减少热点页可能被刷出的概率。

set global innodb_old_blocks_pct = 20;

LRU列表用来管理以及读取的页,但当数据库启动时,LRU列表是空的,即没有任何页,这时页都存放在Free列表中。当需要从缓存池中分页时,首先从Free列中查找是否有可用的空闲页,若有则将该页从Free列表中删除,放入到LRU列表中。否则,根据LRU算法,淘汰LRU列表末尾的页,将该内存空间分配给新的页。当页从LRU列表的old部分加入到new部分时,称此时发生的操作为page made young,而因为innodb_old_blocks_time的设置导致页没有从old部分移动到new部分的操作称为page not made young。可以通过命令show engine innodb status来观察LRU列表及Free列表的使用情况及运行状态。注意这个命令显示的不是当前的状态,而是过去的某个时间范围内的InnoDB存储引擎的状态,具体的时间范围可以在命令执行后的结果中看到。

InnoDB从1.0版本开始支持压缩叶的功能,即将原本16KB的页压缩为1KB,2KB,4KB和8KB。而由于页的大小发生了变化,LRU列表也有了些许改变。对于非16KB的页,是通过unzip_LRU列表进行管理的。通过命令SHOW ENGINE INNODB STATUS可以观察到如下内容

...
LRU len: 266, unzip_LRU len: 0
...

可以看到LRU列表一共有266个页,而unzip_LRU列表中有0个页。这里需要注意的是,LRU中包含了unzip_LRU列表中的页。

对于压缩页的表,每个表的压缩比率可能各有不同。可能存在有的表页大小为8KB,有表页大小为2KB的情况。unzip_LRU是怎么样从缓冲池中分配内存的呢?

首先,在unzip_LRU列表中对不同压缩页大小的页进行分别管理。其次,通过伙伴算法进行内存分配。例如需要从缓冲池中申请页为4KB的大小,其过程为

  1. 检查4KB的unzip_LRU列表,检查是否有可用的空闲页
  2. 若有则直接使用
  3. 否则,检查8KB的unzip_LRU列表
  4. 如果能得到空闲页,将页分为2个4KB页,存放到4KB的unzip_LRU列表
  5. 若不能得到空闲页,从LRU列表中申请一个16KB的页,将页分为1个8KB的页,2个4KB的页,分别存放到对应的unzip_LRU列表中

同样可以使用infomation_schema架构下的表 INNODB_BUFFER_PAGE_LRU 来观察unzip_LRU列表中的页

SELECT TABLE_NAME,SPACE,PAGE_NUMBER,COMPRESSED_SIZE FROM INNODB_BUFFER_PAGE_LRU WHERE COMPRESSED_SIZE <> 0;

LRU列表中的页被修改后,该页称为脏页(dirty page),即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘,而Flush列表中的页即为脏页。需要注意的是,脏页既存在于LRU列表中,也存在于Flush列表中国。LRU列表用来管理缓冲池中页的可用性,Flush列表用来管理将页刷新会磁盘,二者互不影响。

同LRU列表一样,Flush列表也可以通过命令SHOW ENGINE INNODB STATUS来查看,Modify db pages显示的就是脏页的数量。information_schema中可以用下面的语句查看

SELECT TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE FROM INNODB_BUFFER_PAGE_LRU WHERE OLDEST_MODIFICATION > 0;
  • 重做日志缓冲

InnoDB存储引擎会首先将重做日志信息放入到这个缓冲区,然后按一定频率将其刷新到重做日志文件。重做日志缓冲一版不需要设置的很大,因为一般情况下每一秒钟会将重做日志刷新到日志文件,因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。该值可由配置参数innodb_log_buffer_size控制,默认为8MB

在通常情况下,8MB的重做日志缓冲池足以满足绝大部分的应用,因为重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中。

  1. Master Thread每一秒将重做日志刷新到重做日志文件
  2. 每个事务提交时会将重做日志刷新到重做日志文件
  3. 当重做日志缓冲池空间小于1/2时,重做日志缓冲刷新到重做日志文件
  • 额外的内存池

在InnoDB存储引擎中,对内存的管理是通过一种称为内存堆(heap)的方式进行的。在对一些数据结构本身的内存进行分配时,需要从额外的内存池中进行申请,当该区域的内存不够时,会从缓冲池中进行申请。例如分配了缓冲池(innodb_buffer_pool),但是每个缓冲池中的帧缓冲(frame buffer)还有对应的缓冲控制对象(buffer control block),这些对象记录了一些诸如LRU,锁,等待等信息,而这个对象的内存则需要从额外内存池中申请。因此在申请了很大的InnoDB缓冲池时,也应该考虑相应的增加这个值。

Checkpoint

Checkpoint(检查点)技术的目的主要是解决以下几个问题

  • 缩短数据库的回复时间
  • 缓冲池不够用时,将脏页刷新到磁盘
  • 重做日志不可用时,刷新脏页

在InnoDB存储引擎中,Checkpoint发生的时间,条件及脏页的选择等都非常复杂。而Checkpoint所做的事情无外乎是将缓冲池中的脏页刷新到磁盘。不同之处在于每次刷新多少页到磁盘,每次从哪里取脏页,以及什么时间出发Checkpoint。在InnoDB存储引擎内部,有两种Checkpoint,分别为

  • Sharp Checkpoint
  • Fuzzy Checkpoint

Sharp Checkpoint发生在数据库关闭时将所有的脏页都刷新回磁盘,这是默认的工作方式,即参数innodb_fast_shutdown=1

但是数据库如果再运行时也使用Sharp Checkpoint,那么数据库的可用性就会收到很大的影响。故在InnoDB引擎内部视同Fuzzy Checkpoint进行页的刷新,即只刷新一部分脏页,而不是刷新所有的脏页回磁盘。

Innodb存储引擎可能发生如下几种情况的Fuzzy Checkpoint

  • Master Thread Checkpoint
  • FLUSH_LRU_LIST Checkpoint
  • Async/Sync Flush Checkpoint
  • Dirty Page too much Checkpoint

对于Master Thread中发生的Checkpoint,差不多以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的,即此时InnoDB存储引擎可以进行其他操作,用户查询线程不会阻塞。

FLUSH_LRU_LIST Checkpoint是因为InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用。在InnoDB 1.0.x版本之前,需要检查LRU列表中是否有足够的可用空间操作发生在用户查询线程中,显然这会阻塞用户的查询操作。倘若没有100个可用空闲页,那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页,那么需要进行Checkpoint,而这些页是来自LRU列表的,因此成为FLUSH_LRU_LIST Checkpoint。

而从MySQL 5.6版本,也就是InnoDB1.2.x版本开始,这个检查被放在了一个单独的Page Cleaner线程中进行,并且用户可以通过参数innodb_lru_scan_depth控制LRU列中可用页的数量,该值默认为1024。

Async/Sync Flush Checkpoint指的是重做日志不可用的情况,这时需要强制将一些页刷新回磁盘,而此时脏页是从脏页列表选取的。

Dirty page too much即脏页数量太多。导致InnoDB存储引擎强制进行Checkpoint。其目的总得来说还是为了保证缓冲池中有足够可用的页。其可由参数innodb_max_dirty_pages_pct控制。
innodb_max_dirty_pages_pct的值为百分比,如果是90则代表当缓冲池脏页数量超过90%时,强制进行Checkpoint,刷新一部分脏页到磁盘。

InnoDB关键特性

  • 插入缓冲(Insert Buffer)
  • 两次写(Double Write)
  • 自适应哈希索引(Adaptive Hash Index)
  • 异步IO(Async IO)
  • 刷新邻接页(Flush Neighbor Page)

插入缓冲

Insert Buffer可能是InnoDB存储引擎关键特性中最令人激动与兴奋的一个功能。不过这个名字可能会让人认为插入缓冲是缓冲池的一个组成部分。其实不然,InnoDB缓冲池中有Insert Buffer信息固然不错,但是Insert Buffer和数据页一样,也是物理页的一个组成部分。

在InnoDB存储引擎中,主键是行的唯一标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此,插入聚集索引(Primary Key)一般是顺序的,不需要磁盘的随机读取。比如按照下列SQL定义表:

CREATE TABLE T(
a INT AUTO_INCREMENT,
b VARCHAR(30),
PRIMARY KEY(a)
)

其中a列是自增长的,若对a列插入NULL值,则由于其具有AUTO_INCREMENT属性,其值会自动增长。同时页中的行记录按照a的值进行顺序存放。在一般情况下,不需要随机读取另一个页中的记录。因此对于这类插入操作,速度是非常快的。

注意并不是所有的主键插入都是顺序的。若主键是类似UUID这样的,那么插入和辅助索引一样,同样是随机的。即使主键是自增类型,但是插入的是指定的值,而不是NULL值,那么同样可能导致插入并非连续的情况。

但是不可能每张表上只有一个聚集索引,更多的情况下,一张表上有多个非聚集的辅助索引(secondary index)。比如,用户需要按照b这个字段进行查找,并且b这个字段不是唯一的,即表是按如下的SQL语句定义的:

CREATE TABLE T(
a INT AUTO_INCREMENT,
b VARCHAR(30),
PRIMARY KEY(a),
KEY(b)
)

在这样的情况下产生了一个非聚集的且不是唯一的索引。在进行插入操作时,数据页的存放还是按主键a进行顺序存放的,但是对于非聚集索引叶子节点的插入不再是顺序的了,这时就需要离散的访问非聚集索引页,由于随机读取的存在而导致了插入操作性能下降。当然这并不是这个b字段上索引的错误,而是因为B+树的特性决定了非聚集索引插入的离散型。

需要注意的是,在某些情况下,辅助索引的插入依然是顺序的,或者说是比较顺序的,比如用户购买表中的时间字段。在通常情况下,用户购买时间是一个辅助索引,用来根据时间进行查询。但是在插入时却是根据时间的递增而插入的,因此插入也是较为顺序的。

InnoDB存储引擎开创性的设计了Insert Buffer,对于非聚集索引的插入或更新操作,不是每一次直接插入到索引页中,而是先判断插入的非聚集索引页是否在缓冲池中,若在则直接插入;若不在则先放入到一个Insert Buffer对象中,好似欺骗。数据库这个非聚集的索引已经插到叶子节点,而实际没有,只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge(合并)操作,这时通常能将多个插入合并到一个操作中(因为在一个索引页),这就大大提高了对于非聚集索引插入的性能。

然后Insert Buffer的使用需要同时满足两个条件

  • 索引是辅助索引(secondary index)
  • 索引不是唯一的(unique)

当满足以上两个条件时,InnoDB会使用Insert Buffer,这样就能提高插入操作的性能了,不过考虑到这样一种情况:应用程序进行大量的插入操作,这些都涉及了不唯一的非聚集索引,也就是使用了Insert Buffer。若此时MySQL数据库发生了宕机,这时势必有大量的Insert Buffer并没有合并到实际的非聚集索引中去。因此这时恢复可能需要花很长的时间,在极端条件下甚至需要几个小时。

辅助索引不能是唯一的,因为在插入缓冲时,数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生,从而导致Insert Buffer失去了意义。

目前Insert Buffer存在一个问题 :在写密集的情况下,插入缓冲会占用过多的缓冲池内存(innodb_buffer_pool),默认最大可以占用到1/2的缓冲池内存。

这对于其他的操作可能会带来一定的影响。

Change Buffer

InnoDB从1.0.x版本开始引入了Change Buffer,可将其视为Insert Buffer的升级。从这个版本开始,InnoDB存储引擎可以对DML操作--INSERT,DELETE,UPDATE都进行缓冲,他们分别是:Insert Buffer,Delete Buffer,Purge Buffer。

当然和之前Insert Buffer一样,Change Buffer使用的对象依然是非唯一的辅助索引。
对一条记录进行UPDATE操作可能分为两个过程

  • 将记录标记为已删除。
  • 真正将记录删除。

因此Delete Buffer对应UPDATE操作的第一个过程,即将记录标记为已删除。Purge Buffer对应UPDATE操作的第二个过程,即将记录真正的删除。同时,InnoDB存储引擎提供了参数innodb_change_buffering,用来开启各种Buffer的选项。该参数可选的值为 :inserts,deletes,purges,changes,all,none。inserts,deletes,purges就是前面讨论过的三种情况。changes表示启用inserts和deletes,all表示启用所有,none表示都不启用。该参数默认值为all。

从InnoDB 1.2.x版本开始,可以通过参数innodb_change_buffer_max_size来控制Change Buffer最大内存使用量

mysql> show variables like "%innodb_change_buffer_max_size%";
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

该值默认为25,表示最多使用1/4的缓冲池内存空间。需要注意的是改值的最大有效值为50.

...
Ibuf: size 1, free list len 0, seg size 2, 470 merges
merged operations:
 insert 585, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
...

seg size :显示了当前Change Buffer的大小为1 * 16KB = 16KB
free list len :代表了空闲页的数量
size : 代表已经合并记录页的数量。seg size - (1 + free list len)。原文如下 :The number of pages used within the change buffer. Change buffer size is equal to seg size - (1 + free list len). The 1 + value represents the change buffer header page.
merges :代表合并的次数,也就是实际读取页的次数
merged operations - insert: 表示操作Insert Buffer的次数(插入的次数)
merged operations - delete mark: 表示操作Delete Buffer的次数
merged operations - delete: 表示操作Purge Buffer的次数;
discarded operations :表示当Change Buffer发生merge时,表已经被删除,此时就无需再将记录合并到辅助索引中了。

merged operations - insert/merges = 585/470 与等于 1.3 / 1
从这个值就可以看出性能的提升

Insert Buffer的内部实现http://jinblog.com/admin/write-post.php?cid=905#wmd-preview

Insert Buffer的使用场景,即非唯一辅助索引的插入操作。但是对于Insert Buffer具体是什么,以及内部是怎么实现可能依然模糊。

Insert Buffer的数据结构是一颗B+树。在MySQL 4.1之前的版本中每张表都有一颗Insert Buffer B+树。而在现今的版本中,全局只有一颗Insert Buffer B+树,负责对所有表的辅助索引进行Insert Buffer。而这棵B+树存放在共享表空间中,默认也就是ibdata1中。因此,试图通过独立表空间ibd文件恢复表中数据时,往往会导致CHECK TABLE失败。这是因为ibd文件进行恢复后,还需要进行REPAIR TABLE操作来重建表上所有的辅助索引。

Insert Buffer是一颗B+树,因此其也由叶节点和非叶节点组成。非叶节点存放的是查询的search key(键值)

当一个辅助索引要插入到页时,如果这个页不在缓冲池中,那么InnoDB引起会构造一个search key,接下来查询Insert Buffer这棵B+树,然后将这条记录插入到Insert Buffer B+树的页子节点中。

Merge Insert Buffer

Merge Insert Buffer的操作可能发生在以下几种情况

  • 辅助索引页被读取到缓冲池时
  • Insert Buffer Bitmap页追踪到改辅助索引已无可用空间时
  • Master Thread

两次写

如果写失效,可以通过重做日志进行恢复。但是需要认识到,重做日志中记录的是对页的物理操作,如偏移量800,写“aaa”记录。如果这个页本身就已经损坏了,再对其进行重做是没有意义的。这就是说,在应用重做日志前,需要一个页的副本,放写入失效时,先通过页的副本还原该页,再进行重做,这就是doublewrite。

doublewrite由两部分组成,一部分是内存中的doublewrite buffer,大小为2MB,另一部分是物理磁盘上共享表空间中连续的128个页,即2个区(extent),大小同样为2MB。在对缓冲池的脏页进行刷新时,并不直接写磁盘,而是通过memcpy函数将脏页先复制到内存中的doublewrite buffer,之后通过doublewrite buffer再分两次,每次1MB顺序的写入共享表空间的物理磁盘上,然后马上调用fsync函数同步磁盘,避免缓冲写带来的问题。在这个过程中,因为doublewrite页是连续的,因此这个过程是顺序写的,开启并不是很大。在完成doublewrite页的写入后,再将doublewrite buffer中的页写入各个表空间文件中,此时的写入是离散的。

可以用show global status like "innodb_dblwr%";观察到doublewrite的运行情况,如果Innodb_dblwr_pages_written(doublewrite一共写的次数):Innodb_dblwr_writes(实际写入的次数)远小于64:1,那么可以说明系统写入压力并不是很高。

如果操作系统在将页写入到磁盘的过程中发生了崩溃,在恢复过程中,InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本,将其复制到表空间文件,再应用重做日志。

有些文件系统本身就提供了部分写失效的防范机制,在这种情况下,可以使用skip_innodb_doublewrite禁用doublewrite功能以提高性能。

自适应哈希索引

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来,因此构建速度很快,而且不需要对整张表建立索引。

AHI有一个要求,即对这个页的连续访问模式必须是一样的。例如对于(a,b)这样的联合索引页,其访问模式可以是以下情况

  • where a=xxx
  • where a=xxx and b=xxx

访问模式一样是指的查询的条件是一样的,若交替进行上述两种查询,那么InnoDB存储引擎不会对该页构造AHI,此外还有如下要求

  • 以该模式访问了100次
  • 页通过该模式访问了N次,其中N=页中记录*1/16

根据InnoDB存储引擎官方的文档显示,启用AHI后,读取和写入速度可以提高2倍,辅助索引的链接操作性能可以提升5倍。毫无疑问,AHI是非常好的优化模式,其设计思想是数据库自优化(self-runing),则无需DBA对数据库进行人为调整。

可以使用show engine innodb status;查看哈希索引的使用情况

show engine innodb status;

......

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 125156, seg size 125158, 122194 merges
merged operations:
 insert 367572, delete mark 25630980, delete 1001186
discarded operations:
 insert 9209, delete mark 0, delete 0
0.00 hash searches/s, 0.56 non-hash searches/s
---
LOG
---
...

可以使用innodb_adaptive_hash_index来禁用或启用此特性,默认为开启

异步IO

异步IO的优势

  1. 提高数据库的性能
  2. 可以进行IO Merge操作

异步IO需要系统支持才能开启,可以用innodb_use_native_aio开控制。

官方的测试,开启Native IO,恢复速度可以提升75%

在InnoDB存储引擎中,read ahead方式的读取都是通过AIO完成,脏页的刷新,即磁盘的写入操作则全部由AIO完成。

刷新邻接页

InnoDB提供了Flush Neigbor Page(刷新邻接页特性)。其工作的原理为:在刷新一个脏页时,InnoDB存储引擎会检测该页所在区(entent)的所有页,如果是脏页,那么一起刷新。这样做可以通过AIO将多个IO写入操作合并为一个IO操作。改工作机制在传统的机械磁盘下有显著优势。但是需要考虑两个问题

  • 是不是可能将不怎么脏的页进行了写入,则该页之后又会很快变成脏页?
  • 固态硬盘有较高的IOPS,是否还需要这个特性?

为此。InnoDB在1.2.x版本之后提供了innodb_flush_neighbors,用来控制是否启用该特性,对于固态硬盘有着超高IOPS的磁盘,可以将该参数设置为0,即关闭此特性。

启动,关闭与恢复

可以使用innodb_fast_shutdown控制MySQL数据库关闭时的行为(刷新脏页和merge数据到磁盘),innodb_force_recovery控制启动时的行为(启动时有数据需要恢复的情况下,恢复的行为)

数据库索引设计与优化-01

磁盘与CPU时间的基础假设

I/O时间
随机读 10ms (4KB或8KB的页)
顺序读 40MB/s

顺序扫描的CPU时间
检查一行记录 5µs
FETCH 100µs

不合适的索引

对于下面这个简单的SQL,仅有两个合理的访问路径

  1. 索引扫描(LNAME, FNAME)
  2. 全表扫描
select cno,fname from cust where lname = :lname and city = :city order by fname

假设表中总共有 1000000 条数据

对于第一种选择来说,数据库会根据谓词条件lname = :lname扫描索引片。对于索引片中的每一个索引行,数据库系统都必须回到表中校验city字段的值。由于表中的行是根据cno字段而不是lname字段来聚簇的,所以校验操作需要做一次磁盘的随机读。对于最普遍的姓氏来说,在不考虑city字段的过滤因子的情况下,获取完整的结果意味着,需比对10000个索引行和10000个表行。那么,这个过程会持续多久?

假设索引(lname,fname)的大小是1000000 100 byte ≈ 100MB,包括数据及分散的空闲空间。另外,再假设顺序读的速度是40MB/s。读取一个宽度为1%的索引片的,即1MB,需花费10ms + 1MB / 40MB/s,这显然是没有问题的,但是10000次随机读将花费10000 10ms,这使得这种方式太慢了。

对于第二种选择来说,只有第一个页需要随机读。如果表的大小为1000000 * 600 byte ≈ 600MB,包括分散的空闲空间,那么花费的I/O时间将会是10ms + 600MB / 40M/s ≈ 15s,仍旧很慢

第二种方案的CPU时间将会比第一种方案的CPU时间长的多,因为数据库管理系统必须对比1000000行而不是20000行,并且还需要对这些行进行排序。从另一个方面来说,由于是顺序读,CPU的时间可以与I/O时间交叠。在这个场景下,全表扫描比在不合适的索引上扫描要快,但是这还不够,需要一个更好的索引。

三星索引

前面讨论了一个非常不合适的索引,这里来讨论另一个极端,三星索引,即对于一个查询语句可能是最好的索引。如果使用了三星索引,一次查询通常只需要进行磁盘随机读及一次窄索引片的扫描。因此,其响应时间通常会比使用一个普通索引的响应时间少几个数量级。

  • 如果与一个查询相关的的索引行是相邻的,或者至少相距足够靠近的话,那么这个索引就可以被标记上第一颗星。这最小化了必须扫描的索引片的宽度
  • 如果索引行的顺序与查询语句的需求一致,则索引可以被标记上第二颗星。这排除了排序操作
  • 如果索引行包含查询语句的所有列,那么索引就可以被标记上第三颗星。这避免了访问表的操作:仅访问索引就可以了。

对于这三颗星,第三颗通常是最重要的。将一个列排除在索引之外可能会导致许多速度较慢的随机读。我们把包含第三颗星的索引名为对应查询语句的宽索引。

示例

DECLARE CURSOR41 CURSOR FOR
SELECT CNO,FNAME
FROM CUST
WHERE LNAME = :LNAME AND CITY = :CITY
ORDER BY FNAME

为了满足第一颗星(减少索引片的大小以减少需要扫描的数据行)

取出所有等值为此的列(WHERE COL = ...)。把这些列作为索引最开头的列,以任意顺序都可以。对于CURSOR41来说,三星索引可以以LNAME,CITY或者以CITY,LNAME开头。在这两种情况下,必须扫描的索引片宽度将被缩减至最窄。

为了满足第二颗星(避免排序,较少磁盘IO和内存使用)

将ORDER BY列加入到索引中。不要改变这些列的顺序,但是忽略那些在第一步中已经加入索引的列。例如如果CURSOR41在ORDER BY中有重复的列,如ORDER BY LNAME, FNAME或者是ORDER BY FNAME, CITY,只有FNAME需要在这步中被加入到索引中去。当FNAME时索引的第三列时,结果集中的记录无需排序就已经是以正确的顺序排列了。第一次读取操作将返回FNAME值最小的那一行。

为了满足第三颗星(避免没个索引对于的数据行都需要进行一次随机IO从聚集索引中读取剩余数据)

将查询语句中剩余的列加到索引中去,列在索引中添加的顺序对查询语句的性能没有影响,但是将易变的列放在最后能够降低更新的成本。现在索引中已包含了满足无需回表的访问路径所需的所有列。
最终三星索引将会是:
(LNAME,CITY,FNAME,CNO)或(CITY,LNAME,FNAME,CNO)
CURSOR41在以下三个方面是较为挑剔的:

  • WHERE条件不包含范围谓词(BETWEEN,>,>=等)
  • FROM语句只涉及单表
  • 所有谓词对于优化器来说都足够简单

范围谓词和三星索引

下面的SQL与之前的相同,只是显现顾客是在一个范围内

DECLARE CURSOR43 CURSOR FOR
SELECT CNO,FNAME
FROM CUST
WHERE LNAME BETWEEN :LNAME1 AND :LNAME2
AND
CITY = :CITY
ORDER BY FNAME

尝试为这个CURSOR设计一个三星索引。大部分的推论与CURSOR41相同,但是BETWEEN谓词=谓词替代后将会有很大的影响。我们将会以相反的顺序依次考虑三颗星。
首先最简单的行(虽然非常中烟),第三颗星。按照之前的描述,确保查询的所有列都在索引中就能满足第三颗星。这样就不需要访问表,那么同步读就不会造成问题。
添加ORDER BY列能使索引满足第二颗星,但是这个仅在将其放在BETWEEN谓词列LNAME之前才成立,如索引(CITY,FNAME,LNAME)。由于CITY的值只有1个(=谓词),所以使用这个索引可以使结果集以FNAME的顺序排序,而不需要额外的排序。但是如果ORDER BY字段加在BETWEEN谓词列LNAME后,如索引(CITY,LNAME,FNAME),那么索引行不是按FNAME顺序排列的,因而就需要进行排序操作。因此为了满足第二颗星,FNAME必须在BETWEEN谓词列LNAME的前面,若索引(FNAME)或索引(CITY,FNAME,...)。
再考虑第一颗星,如果CITY是索引的第一列,那么我们将会有一个相对窄的索引片需要扫描(MC=1),这取决于CITY的过滤因子。但是如果用索引(CITY,LNAME,...)的话,索引片会更窄,这样在有两个匹配列的情况下我们只需要访问真正需要的索引行。但是,为了做到这样,并从一个很窄的索引片中获益,其他列(如LNAME)就不能放在这两列之间。
所以我们的理想索引会有几颗星呢?首先他一定能有第三颗星,但是,正如我们刚才所说,我们只能有第一颗星或者第二颗星,而不能同时拥有两颗星。换句话说,我们只能二选一

  • 避免排序--拥有第二颗星
  • 拥有可能的最窄索引片,不仅将需要处理的索引行数降至最低,而且将后续处理量,特别是表中数据行的同步读较小到最少--拥有第一颗星

在这个例子中,BETWEEN谓词或者任何其他范围谓词的出现,意味着我们不能同时拥有第一颗星和第二颗星。也就是说我们不能拥有一个三星索引。
这就意味着需要在第一颗星和第二颗星中作出选择。通常这不是一个困难的选择,第一颗星一版比第二颗星重要,虽然并不总是这样。

让我们考虑一下索引(LNAME,CITY,...),LNAME是范围谓词,如前面看到的,这意味着LNAME是参与索引匹配过程的最后一个列。等值谓词CITY不会在匹配过程中被使用。这样做将会导致只有一个匹配列---索引片将会比使用索引(CITY,LNAME,...)更宽。

为查询语句设计最佳索引的算法

根据以上的讨论,理想的索引是一个三星索引。正如我们看到的,当存在范围谓词时,这是不可能实现的。我们不得不牺牲第二颗星来满足一个更窄的索引片(第一颗星),这样,最佳索引就只拥有两颗星。在这个例子中理想索引是不可实现的。将这层因素考虑在内,我们可以对所有情况创建最佳索引(也许不是理想索引)的过程公式化。创建出的索引将拥有三颗星或者两颗星。

首先设计一个索引片尽可能窄(第一颗星)的宽索引(第三颗星)。如果查询使用这个索引时不需要排序(第二颗星),那么这个索引就是三星索引。否则这个索引只能是二星索引,牺牲第二颗星。或者采用另一种选择,避免排序,牺牲第一颗星保留第二颗星。这两种二星索引中的一个将会是相应查询语句的最佳索引。

下面的内容阐述了为查询语句创建最佳索引的算法

候选A

  1. 取出对于优化器来说不过分复杂的等值谓词列。将这些列作为索引的前导列--以任意顺序指定皆可。
  2. 将选择性最好的范围谓词作为索引的下一个列,如果存在的话。最好的选择性是指对于最差的输入值有最低的过滤因子。只考虑对于优化器来说不过分复杂的范围谓词即可
  3. 以正确的顺序添加ORDER BY列(如果ORDER BY列有DESC的话,加上DESC)。忽略在第一步或第二步中已经添加的列。
  4. 以任意顺序将SELECT语句中其余的列添加至索引中(但是需要以不易变的列开始)

举例 : CURSOR43
候选A为(CITY,LNAME,FNAME,CNO)
由于FNAME在范围谓词列的后面,候选A引起了CURSOR43的一次排序操作。

候选B

如果候选A引起了所给查询语句的一次排序操作,那么还可以设计候选B。根据定义,对于候选B来说第二颗星比第一颗星更重要。

  1. 取出对于优化器来说不过分复杂的等值谓词列。将这些列作为索引的前导列--以任意顺序指定皆可。
  2. 以正确的顺序添加ORDER BY列(如果ORDER BY列有DESC的话,加上DESC)。忽略在第一步或第二步中已经添加的列。
  3. 以任意顺序将SELECT语句中其余的列添加至索引中(但是需要以不易变的列开始)

举例 : CURSOR43
候选B为(CITY,FNAME,LNAME,CNO)
现在我们有两个最佳索引的候选对象,一个有第一颗星,一个有第二颗星。为了判断哪一个是最佳索引

需要注意,到目前为止,我们所做的只是设计理想索引或是最佳索引。但是这是否是实际可行的,我们在这个阶段还不好说

SQL 4.4

DECLARE CURSOR44 CURSOR FOR
SELECT CNO,FNAME
FROM CUST
WHERE LNAME = :LNAME AND CITY = :CITY
ORDER BY FNAME
WE WANT 20 ROWS PLEASE

OPEN CURSOR CURSOR4
FETCH CURSOR CURSOR4 --- 最多20次
CLOSE CURSOR CURSOR4

现今排序速度很快--我们为什么还需要候选B

近几年来,排序速度已经提升了很多。现在大多数的排序过程都在内存中进行,用当前最快的处理器每排序50000行记录所耗费的时间只有0.5s,这对于一次事务操作来说也许是可接受的,但这对于CPU时间来说已经是一个比较大的开销了。
由于现在硬件条件下排序速度很快,所以如果一个程序取出结果集的所有行,那么候选A可能和候选B一样快,甚至比候选B更快。
然而,如果一个程序只需获取能够填满一个屏幕的数据量,如CURSOR44,那么候选B可能会比候选A快很多。正如第三章中讨论的,如果访问路径中没有排序,数据库管理系统只要一次一次地读取数据行就能对结果集进行物化。这也是为什么有些时候避免排序非常重要(通过采用候选B)。如果结果集很大,为了产生第一屏数据,二星索引候选A(需要进行排序)可能会话费非常长的时间。我们需要时刻记着,终端用户的一次错误输入可能会使结果集变得非常大。
如果访问路径中没有排序,使用CURSOR44的程序将会非常快(假设LNAME和CITY两列是索引中的前两列---不管顺序如何),即使结果集包含数以百万级的数据行。每个事务永远都不会使数据库管理系统物化大于20行的数据。

需要为所有查询语句都设计理想索引吗?

为每一个查询设计最佳索引的过程是简单的。这个设计过程就是上文所说的两种候选方案算法,是机械式的,只要给出下面这些内容就可以自动完成整个过程

  1. 查询语句
  2. 数据库统计信息(行数,页数,列值分布等)
  3. 对于一个简单谓词或组合谓词最差情况下的过滤因子
  4. 已经存在的索引

在这种简单的过程中,当前存在的索引信息只是用来避免生成重复的索引。有时一个表的索引可以删除掉一些多余的索引以提高插入速度,前提是删除后不会使查询速度明显下降。

在为一个查询语句设计了一个最佳索引后,去看一下已存在的索引是很有必要的。有可能某一个已经存在的索引几乎和理想索引差不多好用,特别是打算在这个已有索引的最后添加一些列的情况下。

当分析一个已经存在的索引对一个新查询语句由多大用处时,需要记住,多余的索引分为三种:完全多余索引,近乎多余索引,以及可能多余的索引。

完全多余索引

如果一个查询包含WHERE A= :A AND B= :B而另一个查询包含WHERE B = :B AND A = :A,数据库管理系统就会创建两个索引:(A,B)和(B,A)。如果没有查询包含A列或者B列上的范围谓词的话,那么这两个索引中的一个就是完全多余的。我们不需要两本电话簿,一个根据LNAME,FNAME排序而另一本根据FNAME,LNAME排序(如果姓氏和名字都已知)。这个时候需要规范SQL语句。

近乎多余的索引

假设索引(LNAME,CITY,FNAME,CNO)已经存在。为一个新的查询语句设计的理想索引包含了以这个索引的4列开头的14个列。那么,在创建了新的索引之后,原来的索引是不是应该删除呢?一些DBA可能会犹豫要不要这样做,因为这个已经存在的索引是唯一索引。但是,这个索引并不是主键索引也不是候选索引,只是恰好这个索引包含了主键列CNO。把其他列加到这个索引上不会有完整性问题。如果数据库管理系统支持非键值索引列,或者有约束来保证唯一性,数据列甚至可以加到主键索引或者任何键值必须唯一的索引上。这样一来,问题就成了一个纯粹的性能问题:一个原本使用4列索引的查询现在使用新的14列索引,速度是否会明显变慢?

假设索引行的大小从原先的50字节增长为200字节,那么扫描10000行索引片并从中取出1000个索引项会花费多少时间?CPU时间增长不多,但是I/O时间是和需要访问的页数成比例的。
CPU时间 = 1000 0.1ms + 10000 0.005ms = 150ms (两种情况下都是1000次FETCH调用和10000个索引行)
4KB大小的叶子页的数量(4列) 1.5 10000 50 / 4000 约等于 200
4KB大小的叶子页的数量(14列) 1.5 10000 200 / 4000 约等于 800
1.5位空闲空间系数

顺序读时间(4列) = 200 * 0.1ms = 20ms
顺序读时间(14列) = 800 * 0.1ms = 80ms

由于顺序读的处理过程是的响应时间还是受CPU时间的限制,所以查询语句使用这两个索引的响应时间没有明显不同。在新的14列索引创建之后,现存的4列索引就变成多余的了

可能多余的索引

一个普遍的常客是这样的 : 一个新的查询语句的理想索引是(A,B,C,D,E,F),而表上已经存在的索引是(A,B,F,C)。那么如果把已经存在的索引替换成(A,B,F,C,D,E),新的索引是不是就多余了?换句话说如果把D和E两列加到现有的索引上是的访问路径仅限于索引,这样对于新的查询语句是否就已经足够了?
理想索引可能在两方面比索引(A,B,F,C,D,E)要好

  1. 可能使得查询有更多的匹配列
  2. 可能可以避免排序

这两个优势都受需要在索引片上扫描的行数的影响。两个索引的差异可以入本章所述转换成毫秒值进行比较,或者更简单一些,通过后面讨论的快速上线估算法(QUBE)进行估算。估算结果往往会显示,新的索引是不需要的,在现有的索引后面加上新的列对于新的SELECT语句就已经足够了

新增一个索引的代价

如果表上有100个不同的查询,且为每一个查询语句都设计了最佳索引的话,那么即使没有重复的索引,该表上最终也可能有非常多的索引,这样一来表的插入,更新和删除操作就会变得很慢。

响应时间

当数据库管理系统向表中添加一行时,他必须在每一个索引上都添加响应的行。在当前的硬件条件下,在一个索引上添加一行,插入操作所花费的时间就增加10ms,因为必须从磁盘上读取一个叶子页。当一个事务向一张有10个索引的表里插入1行数据时,索引的维护就会使响应时间增加10*10ms = 100ms,这可能是可以接受的。然而,如果一个事务向一张有10个索引的表里插入20行数据的话,索引的维护就会需要181次随机读,即耗费1.8s。这个估算基于的前提假设是,新的索引行会把表上其中一个索引(一直增大的键值上的索引)添加到同一个叶子页上,而会把其余9个索引添加到20个不同的叶子页上。从响应时间的角度来看,在一个有10个索引的大表上进行大的事务操作(每个事务中有许多插入或删除操作)可能是无法忍受的。另外,从磁盘负载的角度来看,要在一个大表上进行每秒多余于10行的插入操作可能不容许表上有10个索引。

磁盘负载

被修改过的叶子页是迟早会被写到磁盘上去的。由于数据库的写是异步的,所以这些写不会影响到事务的响应时间。但是,这些会在增加磁盘负载。RAID 5会放大这种影响,因为每一次页的随机更新都会引来两个磁盘的访问。每一次访问都会耗费12ms,因为整个Raid条带都需要被读取和写回:一次寻道(4ms)和两次旋转(24ms)。因此,向磁盘写一个被修改的也带来的整体磁盘繁忙度的增加为24ms。RAID 10(条带化和镜像)相应的增量为26ms = 12ms。

如果一张表的插入频率较高的话,磁盘负载可能会变成主要的问题,限制了表上索引的数量。由于删除操作和插入操作锁带来的磁盘负载是相同的,所以大量的删除任务是另外一个重要的考虑事项。更新操作只会影响到列值被修改了的索引。

假设一个RAID 5磁盘服务器有128块盘,16块空闲盘。数据库(表和索引或者他们的分区)被条带化到这些活动盘上。读缓存为64GB,写缓存为2GB。

在TRANS表中,新插入的行保存在表及其聚簇索引的末尾。在页被写到磁盘之前,许多行会被写到这个页上,所以这些操作不会造成大量的磁盘读和写。会带来问题的是4个索引上的随机插入操作,每一个新的索引行可能都会导致一次磁盘读和磁盘写,每秒插入20行,即每秒一共80次随机写(4*20)。

先忽略读缓存和写缓存。在最差的情况下,4个索引造成的磁盘繁忙度为80*(6ms + 24ms)=2400ms,相应的次磁盘负载为2400ms/s=2.4=240%,如果这4个索引是在112块磁盘上做条带,他们对于平均磁盘负载的贡献是240%/120≈2%。根据平均排队时间将会是3ms。由此,增加2%的磁盘复杂度是可以忍受且不明显的。

使用读缓存和写缓存能多大程度上减轻磁盘负载?
64GB的读缓存和4个索引的大小(1.2GB)相比较似乎很大,但是如果访问模式是完全随机的,当插入频率是20行每秒时,对于每一个索引上75000个叶子页中的任何一个叶子页,对同一个叶子页的相邻两次访问的时间间隔是基本平均的,间隔时间为75000*50ms=3750s≈1h,如果读缓存的的平均时间是30分钟,那么就不会有很多的读命中缓存。如果写缓存的保留时间比读缓存的保留时间短,比如10分钟,那读缓存的作用会更小:只有当一个叶子页在10分钟内被更新1次以上,才能通过写缓存节省一次磁盘写。因此这个4个索引导致的平均磁盘繁忙度几乎依然是2%。如果访问模式不是随机的,缓存将会节省更多磁盘负载上的开销。每小时更新次数多余10次的叶子页能在读缓存和写缓存中长时间保留。

RAID 10,镜像并条带但是没有校验位,能将每个被修改也锁带来的次破案繁忙度从24ms降至12ms,但是这样需要增加磁盘数量。有256块盘的RAID 5基本上也能带来相同的效果。

从这个例子中可以引申出一个经验法则,其中指示符L表示一个表上的索引对RAID 5下磁盘平均负载平均负载的贡献 : L = N * I / D
其中 N = 随机插入设计的索引数量
I = 插入频率(表每秒插入的行数)
D = 磁盘数量(空闲盘除外)

如果L < 1,那么磁盘负载的增加不构成问题;负载增量很可能低于2%
如果L再1和10之间,那么负载的增加可能会比较明显
如果L>10,那么磁盘负载很可能会构成问题,除非缓存命中率很高。

在上面例子中, L = 4 * 20 / 112 = 0.7

如果磁盘负载是一个问题,较明显的解决办法是尝试合并索引。一个有10个列的索引比两个各有6个列的索引锁引起的磁盘负载要小。

磁盘空间

如果表中有一千万行以上的数据,索引磁盘空间成本可能会成为一个问题。外购硬件的价格主要取决于两个因素 : 花费的CPU时间和分配的磁盘空间。

举个例子,有人建议表上创建一个每行包含400字节用户数据的索引,我们是否需要考虑磁盘空间?
这个索引(去除RAID带来的额外开销)需要1.5 10000000 400byte ≈ 6GB 左右的磁盘空间。不过如果将这些列添加到现有索引上并不能提供可接受的响应时间,磁盘空间可能不是问题的关键。

随着索引变大,缓冲池或磁盘缓存也应该随之增大,否则非子叶的I/O量会增加,这一点也会增加开销。

一些建议

即使在目前磁盘空间成本较低的情况下,机械式的为每一个查询设计最佳索引也是不明智的,因为索引的维护可能会使得一些程序太慢或者使磁盘负载超负荷(这会影响所有程序)。最佳索引(根据两个候选索引的方法设计或者使用索引工具设计)是一个好的开端,但是,在决定为一个新的查询创建理想索引前需要先考虑下三种多余索引。

即使可能为每一个查询设计最佳索引,但在实际中更常见的情况是,只对那些由于不合适的索引而导致速度太慢(通过估算或通过测量)的查询语句进行索引设计。

练习

为4.5中的查询语句设计候选索引A和候选索引B。

SELECT A,B,D,E
FROM ORDERITEM
WHERE B BETWEEN :B1 AND :B2 (FF = 1...10%)
AND
C = 1 (FF = 2%)
AND
E > 0 (FF = 50%)
AND 
F = :F (FF = 0.1...1%)
ORDER BY A,B,C,F
WE WANT 20 ROWS PLEASE

候选索引A

对每一个候选索引,计算在最差情况下一个事务必须访问的索引行数。ORDERITEM表有100000000行。
候选索引A

  1. 提取出简单谓词(C, F)
  2. 从范围谓词中提取出过滤因子最低的(C, F, B)
  3. 把select中的字段放到索引中(C, F, B, A, D, E)
    扫描的索引行 100000000 (2% 1% * 10) = 2000

候选索引B
没有想到...

mysql 学习记录8-高可用

Slave + LVS + Keepalived实现高可用

环境

LVS服务器IP : 192.168.23.8
MySQL服务器IP : 192.168.23.9,192.168.23.10
LVS虚拟IP : 192.168.23.11

配置LVS

先安装ipvsadm命令,yum install ipvsadm -y

1. 配置LVS服务器

LVS服务器上创建文件并执行

#! /bin/bash
# DR Model
echo 1 > /proc/sys/net/ipv4/ip_forward
ipv=/sbin/ipvsadm
vip=192.168.23.11
rs1=192.168.23.9
rs2=192.168.23.10
gt=192.168.23.2
ifconfig eth0:0 down
ifconfig eth0:0 $vip up
#ifconfig eth0:0 $vip broadcast $vip netmask 255.255.255.255 up
route add -host $vip dev eth0:0
$ipv -C
$ipv -A -t $vip:3307 -s rr
$ipv -a -t $vip:3307 -r $rs1:3307 -g
$ipv -a -t $vip:3307 -r $rs2:3307 -g

2. 配置RealServer服务器(MySQL)

LVS服务器上创建文件并执行

#! /bin/bash
vip=192.168.23.11
ifconfig lo:0 $vip broadcast $vip netmask 255.255.255.255 up
route add -host $vip lo:0
echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce

3. 测试连接

mysql -h 192.168.23.11 -u root -pxxxxx

配置Keepalived

LVS并不支持故障自动切换,需要通过Keepalived实现

1. 安装Keepalived

yum install -y keepalived

2. 清除ipvsadm已经设置的规则

ipvsadm -C

3.配置keepalived

修改/etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   # 发送提醒邮件地址
   notification_email {
     jin@jin.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   # smtp服务器地址
   smtp_server 192.168.23.1
   smtp_connect_timeout 30 #smtp的超时时间
   router_id LVS_DEVEL #物理服务器的主机名
}

vrrp_instance VI_MYSQL { #定义一个虚拟路由
    state Master # #当前节点在此虚拟路由器上的初始状态;只能有一个是MASTER,余下的都应该为BACKUP;
    interface eth0:0 #绑定为当前虚拟路由器使用的物理接口;
    virtual_router_id 1 #当前虚拟路由器的惟一标识,范围是0-255;
    priority 100 #当前主机在此虚拟路径器中的优先级;范围1-254;
    advert_int 1 #通告发送间隔,包含主机优先级、心跳等。
    authentication { #认证配置,keepalived的Master和Backup服务器之间需要验证这个
        auth_type PASS #认证类型,PASS表示简单字符串认证
        auth_pass 3307 #密码,PASS密码最长为8位
    }
    virtual_ipaddress {
        192.168.23.11 #虚拟路由IP地址,以辅助地址方式设置
    }
}

virtual_server 192.168.23.11 3307 { #LVS配置段 ,设置LVS的VIP地址和端口
    delay_loop 6 #服务轮询的时间间隔;检测RS服务器的状态。
    lb_algo rr #调度算法,可选rr|wrr|lc|wlc|lblc|sh|dh。
    lb_kind DR #集群类型。
    nat_mask 255.255.255.0 #子网掩码,可选项。
    persistence_timeout 50 #是否启用持久连接,连接保存时长
    protocol TCP #协议,只支持TCP
    
    real_server 192.168.23.9 3307 { #配置RS服务器的地址和端口
        weight 1 #权重
        TCP_CHECK {
            connect_timeout 3 #连接请求的超时时长;
            nb_get_retry 3 #超时重试次数
            delay_before_retry 3 #每次超时过后多久再进行连接
            connect_port 3307 #连接端口
        }   
    }   
    
    real_server 192.168.23.10 3307 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3307
        }   
    }   
}

4. 设置keepalived虚拟IP

添加bash文件并执行

#! /bin/bash
# DR Model
echo 1 > /proc/sys/net/ipv4/ip_forward
ipv=/sbin/ipvsadm
vip=192.168.23.11
gt=192.168.23.2
ifconfig eth0:0 down
ifconfig eth0:0 $vip up
#ifconfig eth0:0 $vip broadcast $vip netmask 255.255.255.255 up
route add -host $vip dev eth0:0

4. 设置RS服务器IP

添加bash文件并执行

#! /bin/bash
vip=192.168.23.11
ifconfig lo:0 $vip broadcast $vip netmask 255.255.255.255 up
route add -host $vip lo:0
echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce

Dual-Master高可用

使用keepalived和双master实现故障自动切来保证高可用

双节点读写的隐患

自增主键导致同步线程报错,导致同步进程报错,进而同步失败,如下
# 停止slave
stop slave
# 插入记录,新纪录会自动生成主键ID(自增)
insert into test_db.test (name) value ('jin');
# 重新开始同步
start slave
# 查看报错信息
show slave status;
处理报错

两种处理方式

  1. 手动修复数据,后面还是会出现错误
stop slave;
delete from test_db.test where name = 'jin';
start slave;
  1. 跳过错误(sql_slave_skip_counter)后面还是会出现错误
set global sql_slave_skip_counter=1;
start slave;
  1. 避免自增列值冲突
    使用下列值来避免出现自增主键冲突
  • auto_increment_increment : 指定递增值,默认为1(即每次增长1)
  • auto_increment_offset : 自增偏移量,自增初始值

例如设置从8开始自增,每次增长88888

set auto_increment_increment = 6;
set auto_increment_offset = 88888;

IP自动漂移

修改/etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   # 发送提醒邮件地址
   notification_email {
     jin@jin.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   # smtp服务器地址
   smtp_server 192.168.23.1
   smtp_connect_timeout 30 #smtp的超时时间
   router_id LVS_DEVEL #物理服务器的主机名
}

vrrp_script check_run {
    script "/root/check_mysql.php"
    interval 10
}

vrrp_instance VI_MYSQL { #定义一个虚拟路由
    state Backup # # 初始指定两台服务器均为备份状态,以避免服务器重启时可能造成的震荡(master角色争夺)
    interface eth0:0 #绑定为当前虚拟路由器使用的物理接口;
    virtual_router_id 1 #当前虚拟路由器的惟一标识,范围是0-255;
    priority 100 #当前主机在此虚拟路径器中的优先级,另一节点中本参数的值可以设置得稍小一些;
    advert_int 1 #通告发送间隔,包含主机优先级、心跳等。
    nopreempt # 不抢占,只在优先级搞的机器上设置即可,优先级低的机器不设置
    authentication { #认证配置,keepalived的Master和Backup服务器之间需要验证这个
        auth_type PASS #认证类型,PASS表示简单字符串认证
        auth_pass 3307 #密码,PASS密码最长为8位
    }
    virtual_ipaddress {
        192.168.23.11 #虚拟路由IP地址,以辅助地址方式设置
    }
}

virtual_server 192.168.23.11 3307 { #LVS配置段 ,设置LVS的VIP地址和端口
    delay_loop 6 #服务轮询的时间间隔;检测RS服务器的状态。
    lb_algo rr #调度算法,可选rr|wrr|lc|wlc|lblc|sh|dh。
    lb_kind DR #集群类型。
    nat_mask 255.255.255.0 #子网掩码,可选项。
    persistence_timeout 50 #是否启用持久连接,连接保存时长
    protocol TCP #协议,只支持TCP
    
    real_server 192.168.23.9 3307 { #配置RS服务器的地址和端口
        weight 1 #权重
        TCP_CHECK {
            connect_timeout 3 #连接请求的超时时长;
            nb_get_retry 3 #超时重试次数
            delay_before_retry 3 #每次超时过后多久再进行连接
            connect_port 3307 #连接端口
        }   
    }   
    
    real_server 192.168.23.10 3307 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3307
        }   
    }   
}

mysql 学习记录07-性能调优与诊断

测试方法

关键性指标

1. IOPS(Input/Output operations Per Second,每秒处理的I/O请求次数)

传统的机械磁盘的I/O能力(不管是吞吐量还是IOPS)进展缓慢,严重滞后于系统中的其他组件,因此磁盘I/O能力往往都称为整套系统中的瓶颈,这也是目前的现状,而判断磁盘的I/O能力的指标之一,就是IOPS。

需要说明一点,通常提到的磁盘读写能力,比如形容他每秒读300M,写200M,这个说的数据吞吐量(I/O)能力的另一个关键指标,但是IOPS指的可不是读写的数据吞吐量,IOPS指的是每秒能够处理的I/O请求次数。

什么是一次I/O请求呢?举个例子来说,读写100MB的文件,就是一次I/O请求,写入1B的数据,这也是一次I/O请求,反应快的朋友看到这肯定明白了,IOPS指标越高,那么单位时间能够响应的请求自然就越多。从理论上来讲,只要系统实际的请求数低于IOPS的能力,就相当于每一个请求都能即时得到响应,那么I/O就不会是瓶颈了。

不过,往深处想一想,旋即又产生了疑问,这个IOPS只是请求次数,可请求与请求不一样,就像前面说的,同样一个请求,读取100MB的文件所需要耗费的时间,肯定比写入1B的时间要长上不少,这中间除了寻道时间耗时,差异除妖体现在数据传输的时间上,而数据传输时间实际上就是最开始提到的吞吐量了。

如果想I/O系统的响应足够快,那么IOPS越高越好,或者换种说法,IOPS指标比较高的话,就更适合要快速响应的系统,尤其是对于短连接,小事务,轻量数据作为操作特点的OLTP系统,当然吞吐量也很重要,但是吞吐量对于磁盘来说基本是个确定的值,没什么讨论空间。

我们怎么衡量服务器的IOPS呢,在Linux下和Windows下都有很多性能测试工具,可以获得IOPS指标,比如Linux平台上较为流行的IOmeter/FIO,Windows平台下最常用的磁盘性能测试工具HD tune,输出的测试结果中均包含IOPS,不过对于传输的机械式磁盘,这个指标其实不必专门测试。

一方面是因为磁盘读写性能测试环境太多,只通过一个难有代表性,如果要搞多重全面测试的话,偏离了本章的注意;再其次,传统磁盘完成一个I/O请求所花费的时间受到3个方面因素的影响

  • 寻道时间(Tseek) : 将磁头移动到数据所在的磁道上所需要的时间,通常都在3~15ms。
  • 旋转延迟时间(Trotation) : 将盘头旋转,使所请求的数据所在扇区移动至磁头下方所需要的时间,这个时间跟磁盘的转速密切相关,转速越快,延迟越短,一般15000转的磁盘平均旋转延迟时间为2ms。
  • 数据传输时间(Transfer) : 完成传输所请求的数据所需要的时间

考虑到数据传输时间跟要传输的数据量关系密切,而传输数据量又与吞吐量密切相关,这个变数太多,不过为了方便推算,我们假定要传输的数据很小,或者吞吐量极高,数据能在瞬间完成。也就是说,在计算时我们先忽略数据传输的时间,那么根据现有信息就可以计算出磁盘理论上的最大IOPS,计算公式为 IOPS = 1000ms/(寻道时间 + 旋转延迟时间)。

基于这一公式计算的话,单块SAS 15K转的硬盘,其中最大的IOPS=1000/(3+2),约为200个每秒。这是理论上的最大值,实际表现一定超不过这个值,如果是万转的磁盘或更低速的磁盘,那么这个指标还会更低。而且在实际场景中,数据顺序读个随机读时,寻道或旋转延迟肯定不同,因此单位时间内的请求响应能力肯定也都不一样,当然,相比前面的公式计算出的值,只会更低,不会更高。

要提高IOPS,目前来看就是拼硬件,传统方案是使用多块磁盘通过RAID调带后,使I/O读写能力获得提升。比如我们希望IOPS达到5000,那么理论上就需要5000/200=25块磁盘,组成RAID0来实现。

考虑到上面提供的IOPS指标只是理论值,实际表现往往低于这个值,因此应该留足富余,这里提到的数据都只是理论参考值,现实中需要根据实际情况操作。另外,不同RAID类型,计算公式也需要有所调整。

举例来说,RAID5上每个写的I/O操作,分别需要读写数据和校验位,计算后再写入数据和校验位,也就是说,对于RAID5,每个写I/O操作实际将产生4次I/O,若视同RAID5条带后的存储系统,写入时IOPS能达到5000/s,那就至少需要4 * 5000 / 200 = 100块磁盘

当然这也只是理论值,实际上极少会存在纯写入而不读取的系统,更多都是读写平均,或多读少写。假如仍然是套RAID5条带的存储系统,但平均下来系统有1/3时间在做写入操作,2/3在做读取操作,那么实际需要的磁盘数就可能变为(2/35000+41/3*5000)/200。

必须再次强调,这只是理论,实际情况需要考虑到各种细节,除了完全依赖于硬件的阵列算法,缓存命中率等,还有实际应用时数据访问特点都有可能对性能造成波动,总之一句话,不管是吞吐量还是IOPS,组RAID时磁盘数一定要留足富裕。

还有一个关键的现实因素,前面动辄就需要几十块盘起,在现实场景中极有可能不具备可操作性,因为通常MySQL数据库的数据文件都保存在本地磁盘,在显示场景中既有可能不具备可操作性,因为通常MySQL数据库的数据文件都保存在本地磁盘,而不会像Oracle数据库使用专有的独立存储,普通的2U x86服务器,磁盘挂满也就能插几十块,所以很多情况下不是买不起次破案,而是买来也挂不上去。就像前面计算单块盘的IOPS,普通x86服务器,整体磁盘的IOPS最大值也能计算出来。

基于现实背景考虑,I/O的处理能力尽管非常重要,但也只能是整体架构设计中的环节之一,单机性能再强也无法满足所有场景需要,因此集群方案在架构设计时就得考虑在内。

固态硬盘越发火热,由于SSD磁盘通过电子信息来工作,天生无机械构件,没有马达和磁片,即使是运行状态也完全静音,关键是避免了传统机械式磁盘在寻道的盘片旋转上的时间开销,这使得寻址时间超短,IOPS可以做到很高(相比传统磁盘而言),不同厂商的不同产品差距巨大。单块SSD磁盘IOPS达到几千只是刚起步,几万甚至几十万都有可能,像Fusion-IO这种甚至能突破百万IOPS。

固态硬盘SSD相比传统硬盘,主要是在IOPS方面有巨大优势,I/O延迟较低,不过在吞吐量指标上,相比传统磁盘的优势就不那么明显了(还是有优势)。由于自身实现原理,存在数据擦除操作,尽管可以通过一定的几千/算法尽量避免,但还是有可能在用来一阵时间之后响应变慢的情况。

同时SSD的IOPS数值受较多因素的影响,比如数据读写的特征(和传统磁盘一样),使用时间,系统配置,甚至驱动程序都有关系。此外,在使用寿命方面,相比传统磁盘也有一定差距,至于容量/性价比更是搞的离谱。

像我们之前的制定的5000 IOPS目标,如果对容量没有要求,那直接上一块SSD可能就满足了。听起来SSD相当不错,那是否往后就用SSD了?这个在可能的前提下,能选择SSD磁盘绝对应该选用SSD,只不过就目前来看,还不可能所有场景均使用SSD磁盘。制约固态磁盘普及的因素主要有两个,一方面是容量(已有较大改善),另一方面是价格(也在稳步下降)。不过,只要银子足够充足,多块SSD磁盘再做条带,性能嗷嗷叫,I/O不为瓶颈都不是梦。这种思路简单粗暴,但着实有效,只是这种确实耗费银两,多数企业不肯接受这个成本投入,再加上规模也没到这个级别,在有别的选择缓解瓶颈的前提下,还是通过别的方案来处理性能问题吧,这也是性能调优依然被视作较有技术含量的因素之一。

2. QPS(Query Per Second,每秒请求(查询)次数)

要注意这个参数,尽管所有数据库都有这个指标,单对于MySQL数据库来说,这个指标尤其重要,因为MySQL数据库中,尽管能拿来衡量性能的指标众多,可整租称得上实用又好用的,就是QPS了。顾名思义,就是每秒查询次数,还有比这更指标更直观反映系统(查询)性能的吗,这就像用IOPS衡量磁盘每秒钟能接受多少次请求,明确且直观。

这个指标的获取也比较简单,MySQL数据库原生就提供有QPS指标值,DBA可以在mysql命令行模式下执行status命令,返回的最后一行输出信息中就包含QPS指标,此外,通过mysqladmin命令附加status参数,也能查询到QPS指标。

不过MySQL原生提供的QPS指标,是该MySQL实例生命周期内的全局指标,这个平均值具备移动参考意思,可是我们都知道系统有忙有闲,闲时就不用说了,QPS完全可以为0,那么系统繁忙时变现如何,峰值承载的QPS能达到多少,这就得通过其他方式获取了。

MySQL提供了一系列的状态变量,其中有一项就是用来记录当前的请求次数,即Questions状态变量的值。尽管这也是MySQL实例生命周期内的全局指标,不过我们只要每隔一秒查询下这个变量值,并将相邻的两值相减,得到的就是精确的每一秒的实际请求数了,如果当前MySQL数据库恰处于,那么我们获取的值就可以视为该MySQL实例的QPS峰值响应能力。

MySQL数据库原生提供的QPS指标,就是通过Questions状态变量除以Uptime状态变量的值所得到的结果,即QPS=Questions/Uptime。我们自己计算QPS时,工时大致也是如此,只是把Uptime换成我们自己定义的时间单位就好了。

3. TPS(Transation Per Second,每秒事务数)

按说TPS指标应该更加重要,可是考虑到MySQL数据库这种开创式的插件式存储引擎的设计,必然存在着并非所有存储引擎都支持事务,这也导致TPS在MySQL数据库体系内,使用范围并不是100%,不过他依然很重要,因为最为流行的存储引擎之一InnoDB是支持事务的,因此这个参数还是需要我们重点关注的。

TPS参数MySQL原生没有提供,如果需要的话我们得自己算,计算方法去QPS同理,任然是基于MySQL数据库提供的一系列变量,计算公式为 :

TPS = (Com_commit + Com_rollback) / Seconds

这个公式里面又出现了两个状态变量,分别代表提交次数和回滚次数,Seconds就是我们定义的时间间隔,如果把这个换成Uptime,那么该公式就能计算该MySQL实例在本次生命周期中的瓶颈TPS。

获取关键性指标

前面提到的3个指标,IOPS先跳过,这个纯硬件指标,尽管对DB性能影响很大,不过硬件因素通常不是由DBA所能左右的,所以我们这里重点关注OPS和TPS,在系统资源层面,我们重点关注CPU占用情况。也就是说,我们会重点统计CPU和QPS,如果能统计到TPS(对象使用了支持事务的存储引擎),那就顺道将TPS指标也手机以下

1. 手动获取关键性指标

mysql> show global status like "%Questions%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 138   |
+---------------+-------+

值是取到了,但是现在面临两个问题
1、QPS统计的是每秒产生的请求数,怎么让其按秒输出Questions状态变量的值呢?卡着秒取值吗...
2、当前的数据没有访问量,怎么让数据库繁忙起来?

2. 自动获取性能指标

先解决第一个问题,自动统计请求

# 每隔一秒查询一次,并返回差值
[root@jin mysql3307]# mysqladmin -h 127.0.0.1 -P 3307 -u root -pZj123$%^ extended-status -r -i 1 | grep Questions
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Questions                                     | 140                                              |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |
| Questions                                     | 1                                                |

通过灵活的使用mysqladmin命令,咱们还可以用来获取MySQL实例每秒查询,更新,删除数据等指标,如每秒SELECT,UPDATE数量

mysqladmin -h 127.0.0.1 -P 3307 -u root -pZj123$%^ extended-status -r -i 1 | grep -E "Com_select|Com_update"

再解决第二问题,使用mysqlslap,一款mysql自带的专用清理压测工具

在执行mysqlslap命令期间,mysqladmin命令仍需在运行,否则就看不到它的输出了。

mysqlslap -h 127.0.0.1 -P 3307 -u root -pZj123$%^ --query="select * from mysql.user;" --number-of-queries=100000 -c 30 -i 10 --create-schema=jssdb

参数解释

  • --number-of-queries : 指定测试要执行的查询语句。
  • -c, -concurrency : 指定测试执行请求的并行度
  • -i,-iterations : 指定测试运行的次数
  • --create-schema : 指定此次测试在哪个schema下执行

上面的命令翻译过来就是 : 在jssdb数据库下,模拟30个用户连接,共执行100万次请求。

mysqlslap命令功还支持其他的参数

  • -a,--auto-generate-sql : 自动生成测试所需的SQL语句。
  • -x,--number-char-cols : 指定生成的表对象中VARCHAR类型列的数量,默认只有一个
  • -y,--number-int-cols : 指定生成的表对象中INT类型列的数量,默认只有一个
  • --auto-generate-sql-add-autoincrement : 自动生成测试用的表对象时,在表中增加AUTO_INCREMENT列
  • --auto-generate-sql-execute-number : 指定此次测试要执行的查询次数
  • --auto-generate-sql-guid-primary : 自动生成基于GUID为主键的表,本参数与--auto-generate-sql-add-autoincrement参数互斥
  • --auto-generate-sql-load-type : 指定测试类型,可选值如下

    • read : 读
    • write : 写
    • key : 通过主键读
    • update : 更新操作
    • mixed : 既有读也有写,默认就是这个
  • --auto-generate-sql-secondary-indexes : 指定自动生成的表中辅助索引(或者说是非主键索引)的数量
  • --auto-generate-sql-unique-query-number : 指定生成的查询语句个数。举例来说生成100个查询语句,如果设置此次测试共执行100次,那就代表每次测试执行的都是不同的SQL语句,如果设置此次测试共执行1万次,那就代表每个查询语句会被执行100次(前面的相当于同一条语句被执行了100万次)。默认值为10。
  • --auto-generate-sql-unique-write-number : 指定生成的插入语句个数,与上同理,只是这个参数专用于指定参入语句,默认值是10,通常与- --auto-generate-sql-write-number联用
  • --auto-generate-sql-write-number : 指定每个线程执行时插入的记录数,默认是100个。

总结

# 获取OPS信息
mysqlslap -h 127.0.0.1 -P 3307 -u root -pZj123$%^ --number-of-queries=100000 -c 30 -i 10 --create-schema=my_test -a 
mysqladmin -h 127.0.0.1 -P 3307 -u root -pZj123$%^ extended-status -r -i 1 | grep Questions
# 获取TPS信息
mysqlslap -h 127.0.0.1 -P 3307 -u root -pZj123$%^ --number-of-queries=100000 -c 30 -i 10 --create-schema=my_test -a 
mysqladmin -h 127.0.0.1 -P 3307 -u root -pZj123$%^ extended-status -r -i 1 | grep -E "Com_select|Com_update"

3. 自定义脚本获取性能指标

这里就不延伸了...

TPCC测试

专业名词

  • TPC : 全称[T]ransaction [Processing] Performance [C]ouncil,是一家非盈利组织,该组织制定各种商业应用的基准测试规范,任意厂商或个人,都可以按照其规范来开发自己的应用程序
  • TPC-C : 由TPC退出的一套基准测试程序,主要用于联机事务类应用的测试,最后那个字母C仅是序号,在他之后还有TPC-D,TPC-R,TPC-W,不过也废弃了。TPC-C这个关键不是完全因为他没有被废弃,这只是一部分原因,最主要的原因是TPCC是套基准,不管是有了新硬件,还是软件出了新版本,为显示出新产品的优势,总得拿出些数值做对比,TPCC就是数值之一,各大厂商都着力在吹。TPC-C针对联机事务类应用和决策支持类应用(或称数据仓库)另有一套标准,就是TPC-H。
  • TPCC-MYSQL : 由Percona基于TPCC规范开发的一套mysql基准测试程序,也就是本书的主角,这里先不多作介绍,因为本节剩下的篇幅全是介绍他。

"高性能MySQL"的作者之一Vadim Tkachenko,在该书的第二章提到过一款名为dbt2的TPC-C测试工具,书中也提供了使用该工具的例子,可能是作者自己都觉得该工具不大好使,因此后台作者就开发了我们将要提到的主角 : tpcc-mysql

tpcc-mysql

安装

代码可以从github下载

cd tpcc-mysql-master/src
make

如果没有报错的话,就是安装好了,安装好了之后,tpcc-mysql-master下会生成两个可执行文件

  • tpcc_load : 用于初始化数据
  • tpcc_start : 用于执行基准测试

使用

1. 创建测试数据库
mysqladmin -h 127.0.0.1 -P 3306 -u root -pZj123$%^ create tpcc 
2. 导入数据
mysql -h 127.0.0.1 -P 3306 -u root -pZj123$%^ tpcc < create_table.sql
3. 倒入索引
mysql -h 127.0.0.1 -P 3306 -u root -pZj123$%^ tpcc < add_fkey_idx.sql
4. 初始化数据

语法 : pcc_load -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -l part -m min_wh -n max_wh * [part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS

tpcc_load -h 127.0.0.1 -P 3306 -u root -pZj123$%^ -d tpcc -w 10
5. 测试

语法 : tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -c connections -r warmup_time -l running_time -i report_interval -f report_file -t trx_file
与连接相关的参数都比较好懂,这里介绍下从-r开始的的参数

  • -r warmup_time : 指定预热时间,以秒为单位,默认是10秒,主要母的是为了将数据加载到内存
  • -l running_time : 指定测试执行的时间,以秒为单位,默认是20秒
  • -i report_interval : 指定生成报告的间隔时间
  • -f report_file : 将测试中各项操作的记录输出到指定文件内保存
  • -t trx_file : 输出更详细的操作信息到指定文件内保存

命令如下

# 开始压测指定仓库10个,并发10个,预热10s,持续300s,每隔10s生成报告,并生成日志文件tpcc_mysql.log:
./tpcc_start -h 127.0.0.1 -P 3306 -u root -pZj123$%^ -d tpcc -w 10 -c 10 -r 100 -l 300 -f tpcc_mysql.log -t tpcc_mysql.rtx

返回结果

***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '127.0.0.1'
option P with value '3306'
option u with value 'root'
option p with value 'Zj123$%^'
option d with value 'tpcc'
option w with value '10'
option c with value '10'
option r with value '100'
option l with value '300'
option f with value 'tpcc_mysql.log'
option t with value 'tpcc_mysql.rtx'
<Parameters>
     [server]: 127.0.0.1
     [port]: 3306
     [DBname]: tpcc
       [user]: root
       [pass]: Zj123$%^
  [warehouse]: 10
 [connection]: 10
     [rampup]: 100 (sec.) --预热时长
    [measure]: 300 (sec.) --测试时长

RAMP-UP TIME.(100 sec.) --预热结束

MEASURING START. --测试开始

  10, trx: 28, 95%: 3776.729, 99%: 5182.355, max_rt: 7222.578, 28|4839.867, 2|2649.252, 2|8888.263, 4|28777.471
  20, trx: 21, 95%: 4728.754, 99%: 5173.055, max_rt: 5173.142, 19|2625.231, 3|768.530, 1|5638.004, 1|13718.509
...

STOPPING THREADS.......... --结束压测

<Raw Results> --第一次统计结果
  [0] sc:0 lt:1557  rt:0  fl:0 avg_rt: 1063.3 (5) --new-order,新订单业务成功次数(success),延迟次数(late),重试次数(retry),失败次数(failure),平均重试次数
  [1] sc:36 lt:1521  rt:0  fl:0 avg_rt: 433.4 (5) --payment,支付业务统计
  [2] sc:11 lt:145  rt:0  fl:0 avg_rt: 532.8 (5) --order-status,订单状态业务统计
  [3] sc:0 lt:153  rt:0  fl:0 avg_rt: 3633.0 (80) --delivery 发货业务统计
  [4] sc:0 lt:158  rt:0  fl:0 avg_rt: 6656.3 (20) --stock-level库存业务统计
 in 300 sec.

<Raw Results2(sum ver.)>    --第二次测试结果
  [0] sc:0  lt:1557  rt:0  fl:0 
  [1] sc:36  lt:1521  rt:0  fl:0 
  [2] sc:11  lt:145  rt:0  fl:0 
  [3] sc:0  lt:153  rt:0  fl:0 
  [4] sc:0  lt:158  rt:0  fl:0 

<Constraint Check> (all must be [OK])  --所有的都必须OK才行
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK] --支付成功次数,结果大于43%,为OK,结果小于43%,为NG;
   Order-Status: 4.36% (>= 4.0%) [OK] --订单状态 
       Delivery: 4.27% (>= 4.0%) [OK] --发货状态
    Stock-Level: 4.41% (>= 4.0%) [OK] --库存状态
 [response time (at least 90% passed)] --响应耗时指标必须超过90%通过才行
      New-Order: 0.00%  [NG] * --表示not good
        Payment: 2.31%  [NG] *
   Order-Status: 7.05%  [NG] *
       Delivery: 0.00%  [NG] *
    Stock-Level: 0.00%  [NG] *

<TpmC>
                 311.400 TpmC --TpmC结果值(每分钟事务数,该值是第一次统计结果中的新订单事务数除以总耗时分钟数,例如本例中是:1557/5=311.4),TpmC/60=tps

解释

10, trx: 28, 95%: 3776.729, 99%: 5182.355, max_rt: 7222.578, 28|4839.867, 2|2649.252, 2|8888.263, 4|28777.471
20, trx: 21, 95%: 4728.754, 99%: 5173.055, max_rt: 5173.142, 19|2625.231, 3|768.530, 1|5638.004, 1|13718.509

第一项为我们定义的任务执行时间,他是按照每10秒为一个区间进行输出。后面分别为在10s内新订单事物数,每个给定区间的新订单事务的95%响应时间,每个给定区间的新订单事务的99%响应时间,新订单事物最大响应的时间,其他事物数和最大响应时间。

所以上面10的一列为
164 10秒内执行了28次事务
95% 事务平均响应时间为3776.729秒
99% 事务平均响应时间为5182.355秒
max_rt 事务最长响应时间为7222.578秒

TPCC没有收集系统性能指标,需要通过其他途径获取期间的系统性能指标,重点关注CPU和系统平均负载。

数据库参数配置优化

连接相关参数

1. max_connections : 指定MySQL服务端最大并发连接数,范围为1~10万,默认值为151。

这个参数非常重要,因为它决定了同时最多能有多少个会话链接到MySQL服务;其次这个参数很有意思,改参数的默认值随着版本的不同一致在变来变去,单总体值的范围都比较保守。我们可以忽略默认值,直接手动指定参数值。设定该参数时,根据数据库服务器的配置和性能,一般设置在500~2000之间都没有太大的问题。

2. max_connect_error : 指定允许连接不成功的最大尝试次数,值的范围从1~2^64(32位的平台最大为2^32),在5.6.6版本默认值是100,在之前的版本中,默认值为10。

这个参数很重要,如果尝试连接的错误数量超过该参数指定值,则服务器就不再允许新的连接,实际表现就是拒绝,尽管MySQL仍在提供服务,但无法创建新连接了。如果出现这种情况怎么处理呢?在已经连接的会话中执行FLUSH HOSTS,使状态清零,或者重新启动数据库服务,这个代价就太高了,一版不会选择。这个参数的默认值较小,建议加大,一般设置在10万以上的量级。

3. interactive_timeout和wait_timeout : 这两个参数都与连接会话的自动超时断开有关,前者用于指定关闭交互连接前等待的时间,后者用于指定关闭非交互连接前的等待时间,单位均是秒,默认值为28800,即8个小时。

是否是交互模式,取决于用户端创建连接,调用mysql_real_connect()函数时指定的CLIENT_INTERACTIVE选项,如果指定了这个选项,那么wait_timeout的值就会被interactive_timeout值覆盖,这是基于MySQL的实现机制进行的设置,因此这两个参数必须被同时设置(如果不能确定所使用的客户端到低使用交互还是非交互模式)。这个参数的参数值设置有些讲究,不能太小,否则可能会出现连上去后,一会会话就被断开;可是又不能太大,否则可能存在长时间不操作,但占据着连接资源(如果前端应用层没有主动断开的话)。建议设置的时长不要超过24小时,即参数值小于86400,应能满足绝大多数的需求。

4. skip-name-resolve : 可以将之简单理解为禁用DNS解析,注意,这个是服务端的行为,连接时不检查客户端主机名,而只使用IP。如果指定了这个参数,那么在创建用户及授予权限时,HOST列必须是IP而不能是主机名。建议启用本参数,对于加快网络连接速度有移动帮助(相当于跳过了域名解析)。

5. back_log : 指定MySQL连接请求队列中存放的的最大连接请求数量,在5.6.6版本之前,默认是50,最大值不超过65535。进入5.6.6版本之后,默认值为-1,表示由MySQL自行调节,所谓自行调节其实也是有规则,即50 + (max_connections / 5)。

该参数用来应对这种场景--短时间内有大量的连接请求,MySQL主线程无法及时为每一个连接请求分配(或创建)连接的线程,MySQL也不会直接拒绝,他会将一部分请求放到等待队列中待处理,这个等待队列的长度就是back_log的参数值,若等待队列也被放满了,那么后续的连接请求才会被拒绝。

文件相关参数

有一些参数在前面已经说过了,这里就不重复了

1. sync_binlog : 指定同步二进制文件的频率,默认是0

简单来说,要性能就设置该参数值为0,为了安全则指定该参数值为1。
sync_binlog从字面上看就是为了同步二进制日志文件,二进制日志文件在物理上看就是一堆保存在磁盘上的文件。我们都知道磁盘的性能不理想,因此对于频繁写入的场景,所做的操作可能无法立刻写到磁盘,对于二进制日志来说,他也有自己的缓存区(这部分参数由binlog_cache_size管理),一般会先写到缓存中,而sync_binlog的作用在于,何时刷新缓存中的日志到磁盘中,当设置为0(默认值)时,就标识MySQL不关注二进制文件何时刷新,完全交由MySQL来管理何时刷新到磁盘,这种情况性能最好,但是存在风险,如果MySQL服务宕机,那这部分未刷新到磁盘的数据就丢失了。
当sync_binlog的值设置为n(大于0的整数)时,就表示MySQL每进行n次事务后,就同步binary log到磁盘(使用fdatasync())。如果 将sync_binlog的参数设置为1,这种情况下就算数据库服务崩溃,最多丢失一条语句的数据或者一个事务的数据,当然,每个事务同步肯定会对性能有影响,因此,大家希望在性能和安全性方面取得平衡的话,可以考虑适当增大sync_binlog的参数值,2/4/6/8/16都行,sync_binlog最大能支持无符号的bigint数据类型所能支持的最大值(2^64)。

2. expire_logs_days : 指定设置二进制日志文件的生命周期,超出将被自动删除,参数值以天为单位,值的范围从0~99,默认值为0。

当该参数设置为0时,表示不自动删除二进制文件,需要DBA手动清理。一般将该参数值设置为7~14之间。自动清除操作一般会在启动或二进制日志被flushed时,手动删除二进制日志可以通过PURGE BINARY LOGS语句进行,不建议直接在操作系统层删除物理文件。

3. max_binlog_size : 指定二进制日志文件的大小,值的范围从4KB~1GB,默认为1GB。

单个二进制日志文件不可能无限增长,就算写入了,操作系统也不一定支持。因此需要指定单个文件的最大可用空间,这正是由系统参数max_binlog_size进行控制
对于比较繁忙的系统,数百MB很正常,可以以设置为512M。当日志文件大小达到max_binlog_size指定的大小时,就会创建新的二进制文件。不过max_binlog_size并不能严格的控制日志文件的大小,生成的文件可能会超过参数指定的值。这通常会出现在二进制日志快被写满时,又执行了一个超大的事务,由于事务特性决定相关事件必须连续,因此该事件会被写到同一日志文件,这就可能造成日志文件的实际大小超出参数值的现象,但是没关系,不会有其他的影响。

4. local_file : 指定是否允许从客户端本地加载数据,该参数值为布尔型,默认为允许。这个参数可以说是LOAD DATA INFILE的专用参数,用户可以根据实际需求设置为ON或OFF。

5. open_files_limit : 指定操作系统允许mysql进程使用的文件描述符数量,改参数的参数值受较多因素影响,正常情况下,MySQL会按照规则从下列3个条件中,选择最大的一项作为参数值。

  • 10 + max_connections + (table_open_cache * 2)
  • mac_connections * 5
  • 启动时指定的open_files_limit参数值大小,如果未指定默认为500

缓冲控制参数

1. binlog_cache_size : 指定二进制日志事务缓存区的大小,默认值为32KB,最大可以支持到2^64

为事务指定缓存区,用于缓存二进制日志SQL语句,注意这里提到了"事务",也就是说,这个缓存区仅用于事务。只有当MySQL服务中有支持事务的存储引擎,并且启用了二进制日志记录,每个客户端在操作事务时,才会被分配二进制日志缓存。一般来说,改参数设置为8MB或16MB即可满足大多数场景,如果经常使用大量含有多条SQL语句的事务,可以通过调高该参数来获得性能的提升,不过最大建议不要超过64MB,这主要是考虑到这个参数是为每一个连接(支持事务)客户端分配内存,如果链接的客户端较多,每个会话二进制日志缓存占用过多,也不利于系统的整体性能

2. max_binlog_cache_size : 本参数功能与binlog_cache_size参数密切相关,主要用来指定binlog能够使用的最大内存去。如果单个事务中执行多个语句所需内存超过该参数设置的话,则服务器会抛出Multi-statement required more than 'max_binlog_cache_size' bytes of storage的错误。一般建议改参数为binlog_cache_size的两倍大小即可。

3. binlog_stmt_cache_size : 可以理解为非事务语句的binlog_cache_size

4. table_open_cache : 指定MySQL同时能打开的表对象的数量。

在5.6.8版本之前,默认值为400,5.6.8之后提升为2000;table_open_cache是在5.1.3版本引入的,之前名为table_cache

5. thread_cache_size : 指定MySQL为快速重用缓存的进程数量。范围从0~16384,默认值为0

一般当客户端终端连接后,为了后续再有连接创建时,能够快速创建成功,MySQL会将客户端终端的链接放入缓存区,而不是马上终端并释放资源。这样当有新的客户端请求连接时,就可以快速创建成功。因此本参数最好保持一定数量,建议在300~500之间。线程的缓存命中率也是一项比较重要的监控指标,计算规则为(1 - Thread_created/Connections) * 100%。可以通过计算该项指标的值,来优化和调整thread_cache_size参数。

6. query_cache_size : 指定用于缓存查询结果集的内存区大小,改参数的值应为1024的整倍数。

这个参数的设定很讲究,既不能太小,查询缓存至少会需要40KB的空间分配给其自身结构(具体大小要看操作系统结构),太小时缓存的结果集就没有意义,热点数据保存不了多少,而且总是很快被刷新出去;但是也不能太大,否则可能占据过多的内存资源,影响整机性能,再说太大也没有意义,但只要数据源发生变更,缓存中的数据也就自动失效了,这种情况下分配多大都没有意义。因此需要综合考虑,查询缓存不是万能的,应用不当不仅无助于性能的提升,还有可能降低系统性能。这个不仅仅适用于query_cache_size,不过内存类参数就是如此,讲究一个平衡,过犹不及。最好不要超过256MB

7. query_cache_limit : 用来控制查询缓存,能够缓存的单条SQL生成的最大结果集,默认是1MB,超出的就不要进入查询缓存。这个大小对很多场景都足够了,缩小可以考虑,加大就不用了

8. query_cache_min_res_unit : 指定查询缓存最下分配的块大小,默认为4KB,最大能支持2^64。

当查询能够被被缓存时,那么其查询的结果(发送到客户端)就会被保存在查询缓存区中,知道该查询过期,基于OLTP的特点,查询结果集通常不会太大,查询缓存一经需要就会为其分配空间(块)保存数据,当一个块被写满,则新的块又会被分配。考虑到频繁操作代价比较昂贵(时间成本),因此查询缓存再为结果集分配空间时,默认会按照本参数的值进行分配,并视情况可增大到query_cache_limit指定的值。
本参数值的大小同样需要认真考量。如果查询多数是小结果集,那么当指定的块比较大时,就可能会导致内存分裂,这种情况下降低query_cache_min_res_unit可能就更适合。如果查询都是大结果集的话,那么增长改参数值可能就更适合。

9. query_cache_type : 设置查询缓存的类型。支持全局或会话级设置,可选类型有3个

  • 0(OFF) : 不使用查询缓存,注意该选项并不会关闭查询缓存区,如果不想分配查询缓存区的内存空间,还是需要抢query_cache_size参数值设置为0
  • 1(ON) : 缓存除SELECT SQL_NO_CACHE之外的查询结果,也是本参数的默认选项
  • 2(DEMAND) : 只缓存SELECT SQL_CACHE的查询结果

若要了解查询缓存的实际使用应用情况,有一系列以Qcache开头的状态变量可供灿开,可以通过show global variables获取这些状态变量的值,辅助分析查询缓存的设置是否合适。比如说,通过状态变量计算查询缓存的命中率,公式如下 : Qcache_hits * 100 / (Qcache_hits + Qcache_insets)

10. sort_buffer_size : 指定单个会话能够使用的排序区大小,默认值为256KB,最大支持2^64

一般单个会话执行的语句进行排序操作时,会使用这部分空间,如果要排序的数据再sort_buffer_size指定的区域内就可以完成排序,那么所有操作都是在内存中进行,性能自然很好,否则MySQL就不得不使用临时表来交换排序,要知道临时表可是创建在磁盘上的文件,这个性能相比内存中的运行效率,相差不知道多少倍。

通常当发现状态变量sort_merge_passes值比较大时,可以考虑增加sort_buffer_size参数值的代销,应该能够有效提升查询效率。不过需要注意,这部分空间一经设置,所有会话将按此分配排序区,哪怕并非所有会话都需要这么大的排序区,因此,如果设置过大,也有可能对系统的性能造成影响。考虑到他是基于会话的,如果分配的空间过大,同时MySQL服务的会话数很多,那么仅这一块内存的占用就不容忽视,基于这一因素,这个参数一版设置在1~4MB之间即可。

11. read_buffer_size : 指定顺序读取时的数据缓存区大小,默认是128KB,最大能够支持到2GB。

从表中读取数据时也会应用缓存,从表中读取数据其实有两种方式,一是顺序读取(通常是全表扫描),另一种是随机读取(通常是索引扫描)。当采用顺序方式读取时,数据就会保存在read_buffer_size指定的缓存区中。该参数的参数值在设置时应为4KB的整倍数,实际上,当设置为一个非4KB整倍数时,MySQL也会强制将其降为最接近的4KB整倍数。改参数最大不超过2GB,一版来说,适当加大本参数,对于提升全表扫描速率会有帮助。

12. read_rnd_buffer_size : 指定随机读取时的数据缓存区大小,默认是256KB,最大能够支持到4GB。

当以随机方式读取数据时,数据就会保存在本参数的数据缓存区中。为该参数指定一个较大的值,能够有效提高ORDER BY语句的执行效率,不过需要注意,read_buffer_size参数和read_rnd_buffer_size参数所指定的值都是针对单个会话,因此不建议指定太大的值,如有需要,可以在session级别单独进行设置。

13. join_buffer_size : 指定表join操作时的缓存区大小,默认为256KB(5.6.6版本之前为128KB),最大支持2^64。

不管是索引扫描,索引范围扫描,还是不使用索引的全表扫描的JOIN操作,数据所使用的内存都是由join_buffer_size参数指定。通常来说,最好,最快的连接方式仍是使用索引,只有当创建的所有索引不生效时,才会使用这部分空间来存储表连接时的数据。参与FULL JOIN的每一个表都需要有自己独立的Join Buffer,所以这个参数分配的话,至少是两个。对于比较复杂的多表连接查询,还可能使用到多个join缓存区,对于这个缓存的设置,建议参考sort_buffer_size的方式,全局值设置的保守些,对于特殊的查询可以单独设置session级别的更适合的参数值

14. net_buffer_length : 指定单个客户端与MySQL服务端交互时,相关信息的缓存区大小,默认是16KB,最大能够支持到1MB。

每个客户端的链接都需要与服务端进行交互,交互的信息也需要缓存,以保存连接信息,语句的结果集等,这类缓存池的起始大小就是由net_buffer_length参数决定,而后会动态增长,直到达到max_allowed_packet参数指定值。该参数默认值即可满足大多数场景需求,不建议进行修改,如果内存着实有限,可以考虑适当减小。

15. max_allowed_packet : 指定网络传输时,单次传输的数据包大小。在5.6.6版本之前默认为1MB,进入5.6.6版本之后,默认是4MB大小。

这个参数与前面提到的net_buffer_length相关联,数据包初始化时被置为net_buffer_length参数指定的值,不过最大可以增长到max_allowed_packet指定的参数值大小。该参数默认情况下较小,建议增加,特别是当使用了大字段类型(如BLOB)时,该参数值最大不超过1GB,应该设置成1024的倍数。

16. bulk_insert_buffer_size : 指定批量插入时的缓存区大小,默认是8MB,最大可以支持到2^64。

该参数用于加速像INSERT...SELECT,INSERT...VALUES,...以及LOAD DATA INFILE这类语句。这是个会话级的参数,可以动态调整,实际使用过程中,可以无视需求加大(比如恢复mysqldump导出的数据),如果批量插入的机会不多的话,也就无所谓了,保持默认值即可。

17. max_heap_table_size : 指定内存表(Memory引擎表对象)的最大可用空间,默认为16MB,最大可支持到2^64。

该值可用来计算内存表的max_rows值。需要注意的是,修改该参数的值,不会影响当前已经存在的内存表,除非又通过CREATE/ALTER/TRUNCATE重建内存表。默认值16MB确实比较小,可以适当增加本参数的值。

18. tmp_table_size : 指定内部内存临时表的最大可用空间(实际大小将取决于tmp_table_size和max_heap_table_size两参数的最小值)。

当内存临时表达到最大值时,MySQL自动将其转换成保存在磁盘上的MyISAM类型的表对象。如果内存超大,并且临时表需要执行复杂的GROUP BY查询,那么可以适当增加tmp_table_size(以及max_heap_table_size)参数的值。可以通过show global variables查看

MyISAM专用参数

1. key_buffer_size : 指定MyISAM表索引的缓存区的大小,该缓存区为所有线程共用,注意这里说的是共享使用,而不是针对单个会话。默认缓存区大小是8MB,对于32位平台来说,该参数的最大值为4GB,64位平台则无此限制。

该参数不是越大越好,即使数据库服务器中只有MyISAM引擎的表对象,建议此设置值最大也不要超过物理内存的25%,更何况现在已经是InnoDB引擎的天下了,本参数最多指定个128MB就顶天了

对于纯MyISAM引擎对象的数据库服务,key_buffer_size的可配置性还是很强的,尽管缓存区只有一个,但是他也能配置成多个键值缓存区,分别对应较热/较冷等不同热点的缓存数据,此外还有专门的CACHE INDEX/LOAD INDEX INTO CACHE等语句,用于加载或处理缓存中的数据等。

要检查MyISAM索引缓存区应用的性能怎么样,也可以通过一些状态变量中给出的指标值。
计算缓存命中率可以使用 : 1 - (key_reads / key_read_requests) * 100,这个值最好能无限接近于1。
计算缓存写的比例可使用公式 : key_writes / key_write_requests,如果有大量的更新或删除,那么这个值也应该趋近于1。

2. key_cache_block_size : 指定索引缓存的块大小,值的范围从512B到16KB,默认是1KB。

注意这个参数不仅仅是在缓存区中保存键值的大小,而且也决定了从磁盘*.MYI文件中读取索引键值时,一次读取的块大小,因此本参数的设置最好能与磁盘的IO能力综合考虑,使之相匹配,已获得好的I/O性能

3. myisam_sort_buffer_size : 指定MyISAM引起排序时的缓存区大小,默认是8MB,最大能支持到2^64B。

与sort_buffer_size的功能差不多,只不过这个参数是MyISAM引擎专用,当执行REPAIR TABLE或CREATE/ALTER INDEX重建索引时会用到,为MyISAM引擎表对象的索引排序分配缓存区。

4. myisam_max_sort_file_size : 当重建MyISAM索引(REPAIR TABLE/ALTER TABLE/LOAD DATA INFILE)时,MySQL运行操作的临时文件最大空间,32位平台默认为2GB,64位平台可以理解为无限制(已达EB量级)。如果文件大小超过了该参数值,则索引创建时会把key_cache_size用上,但是那个缓存区太小,缓存中的频繁数据交换将导致速度慢很多。因此如果索引文件超出该参数值,并且在磁盘空间由空闲的情况下,提高该参数值能提高性能。

InnoDB专用参数

1. innodb_buffer_pool_size : 指定InnoDB引擎专用的缓存区大小,用来缓存表对象的数据及索引信息,默认值为128MB,最大能支持(2^64 - 1)B

innodb_buffer_pool_size是个全局参数,其所分配的缓存区将供所有被访问到的InnoDB表对象使用,如果数据库中是以InnoDB为主,那么本参数值就越大越好,官方文档中也是这么建议,可以将这个值设置为服务器物理内存的80%。不过,这说的是理想情况,实际设置时还是需要考虑各种因素,以避免内存分配超量,造成操作系统级别的换页操作。此外,MySQL服务启动时,初始化内存区时所花费的时间也会与该内存区大小成正比,因此设置超大内存时,还需要考虑对MySQL数据库服务启动速度的影响。InnoDB缓存命中率也可以通过状态变量的值进行计算,公式为1 - (innodb_buffer_pool_reads/innodb_buffer_pool_read_request) * 100,命中率越接近100%越好,说明几乎所有要请求的数据,都能从内存中获取,效率自然刚刚的。

2. innodb_buffer_pool_instances : 指定InnoDB缓存池分为多少个区域来使用,值的范围从1~64,默认值为-1,表示由InnoDB自行调整

当InnoDB缓存池较大(如今动辄以GB计算)时,就需要考虑如何高效利用这个空间,内存资源很宝贵,尽管内存中操作数据的效率相比磁盘要高上许多,但也不是说数据放到内存就一定会快,能不能用好可是很有讲究的,尤其是当InnoDB缓存池能够使用数GB甚至数十GB内存空间时,将之当做整块区域操作M管理成本显然太高。基于这一点,InnoDB缓存池能够被分块处理,innodb_buffer_pool_instances参数就是用来指定InnoDB缓存池的个数。每个区块有自己的LRU列表及相关的数据结构,这就相当于把一块大的缓存池换分成多个小的缓存池来管理,不同链接的读写操作的是不同的缓存也,以提高并发性能。只有当innodb_buffer_pool_size参数值大于1GB时本参数才有效,默认是8(32位平台默认是1)。那么本参数怎么设置合适呢?可以参照InnoDB缓存池的大小,以GB为单位,每GB指定一个instances。例如当innodb_buffer_pool_size设置为16GB时,则指定innodb_buffer_pool_instances设置为16即可。

3. innodb_max_dirty_pages_pct : 指定InnoDB缓存池中的脏页(即已经被修改,但未同步到数据文件)比例,本参数值的范围时0~99,默认值是75。

InnoDB更新innodb_buffer_pool_size中的数据时,并不会实时将数据写回到磁盘,而是等待相关的触发时间,本参数就是指定缓存数据中被改动数据未刷新到磁盘的最大百分比。如果数据库的写操作比较频繁,建议适当降低这个比率值,意见上MySQL宕机后的恢复时间,当然这样也会带来更多的I/O操作。

4. innodb_thread_concurrency : 指定InnoDB内部最大线程数,值的范围为0~1000,默认值为0。

当线程数达到该参数指定数量时,后面的线程将被置入FIFO队列中进入等待状态,不过当参数值设置为0时,就表示没有限制,完全交由InnoDB自己去管理可创建线程数量。这个参数争议较大,而且从MySQL版本演进过程中,改参数默认值的变化也可以看出这一点。在5.1.12版本之前这个默认曾数次变更,先是8,然后是20,后又变成8,再之后又改成20,从5.5开始默认值就是0。这个值可以直接设置成0,由InnoDB自己管理。

5. innodb_flush_method : 用来控制InnoDB刷新数据文件及日志文件的方式,仅作用于Linux,UNIX操作系统,与I/O吞吐量密切相关。InnoDB默认使用fsync()系统调用刷新数据文件和日志文件,此外还有O_DSYNC,O_DIRECT,O_DIRECT_NO_FSYNC几个选项。

当指定为O_DSYNC选项时,InnoDB将使用O_SYNC方法打开并刷新日志文件,使用fsync()刷新数据文件;若指定为O_DIRECT,则会使用O_DIRECT(在Sloaris系统中则使用directio()调用)打开数据文件,使用fsync刷新数据文件和日志文件;若指定为O_DIRECT_NO_FSYCN选项,在刷新I/O时他会使用O_DIRECT,不过之后会跳过fsync()系统调用,这种选项不使用与XFS文件系统。

根据硬件配置的不同,指定O_DIRECT或O_DIRECT_NO_FSYNC在性能方面的表现可能正好相反。例如,对于使用支持写保护的硬件的RAID卡,使用O_DIRECT选项可以避免InnoDB缓存和操作系统层缓存的双重缓存写,而对于将数据文件及日志文件保存在基于SAN存储系统,在对应大量的SELECT语句时,使用O_DSYNC选项可能更快一些。

6. innodb_data_home_dir : 指定InnoDB数据文件保存的路径,默认保存在MySQL的datadir参数指定的路径下

7. innodb_data_file_path : 指定InnoDB数据文件名及文件大小

8. innodb_file_per_table : 指定是否将每个InnoDB表对象存储到独立的数据文件。

9. innodb_undo_directory : 指定InnoDB引擎的UNDO表空间数据文件存储路径。

10. innodb_undo_logs : 指定回滚段的数量,默认值是0,值的范围从0~128

11. innodb_undo_tablespaces : 指定InnoDB回滚段表空间(其实也是数据文件)的数量,这些文件就会创建到innodb_undo_directory参数指定的路径下

12. innodb_log_files_in_group : 指定InnoDB日志文件组中日志文件的数量

13. innodb_log_group_home_dir : 指定InnoDB日志文件的保存位置

14. innodb_log_file_size : 指定InnoDB单个日志文件的大小

15. innodb_log_buffer_size : 指定InnoDB日志缓存区的大小,最小256KB,最大则不超过4GB,默认为8MB。

为本参数指定一个适当的值,能够延缓未提交事务向磁盘日志文件的写操作频率,因此对于较大事务的应用,可以考虑加大该缓存池以节省次磁盘I/O,通常4~8MB都是合适的,除非事务量极多,写入量极高,否则再大恐怕也没有意思。

16. innodb_flush_log_at_trx_commit : 指定InnoDB刷新log buffer中的数据到日志文件的方式,默认值为1,可选值为0/1/2,可选的参数值尽管不多,不过这个参数在设置时还是很讲究的,同时他还跟sync_binlog参数有所关联。

当参数为0时,则log buffer每秒像日志文件写入一次,并写入次磁盘,但是在事务提交前不做任何操作(不要同步数据文件),在此期间,MySQL进程崩溃,则会导致丢失最后一秒的事务;当参数的值为1时,只要事务提交或回滚,就会将缓存中的数据写入日志文件,并且明确触发文件系统同步数据;当为2时,log buffer在遇到事务提交时,会将缓存写向日志文件,但是并不会触发文件系统层的同步写入。这里稍稍有些不保险,他只是条用了文件系统的文件写入操作,在这种情况下,若MySQL进程崩溃,那么数据还是安全的(不影响操作系统层的缓存刷新),不过若操作系统崩溃或主机掉电,那么可能导致丢失最后一秒钟的事务。

所以,设置为1时可以达到比较高的安全性,设置成不为1则可以提高性能(但是当数据库崩溃时可能会丢失数据)。

17. innodb_flush_log_at_timeout : 指定每隔n秒属性日志,默认值为0,值的范围为0~27000,这是5.6版本新引入的参数,只有当innodb_flush_log_at_trx_commit指定为2时才有效。

18. innodb_lock_wait_timeout : 指定InnoDB事务等待行锁的超时时间,以秒为单位,默认为50秒。

单个事务尝试获得一行数据时,如果该行数据被InnoDB的其他事务所锁定,那么该事务会先进入等待状态,等待本参数指定的时间后若仍未成功获得,则抛出下列错误:ERROR 1205 (HY000) : Lock wait timeout exceeded;try restarting transaction。

当发生锁超时,那么当前语句并没能被成功执行,当前事务也不会显示回滚(如果自动回滚,可以在启动服务器时指定innodb_rollback_on_timeout选项加以控制)。该参数仅作用于InnoDB行锁,对表锁无效,对死锁也无效,因为InnoDB能够自动检测到死锁,并自动回滚一个事务,这种情况下该参数也不起作用。

19. innodb_fast_shutdown : 指定InnoDB引擎的关闭模式,有0/1/2三种选择,默认为1。

当为0时,InnoDB的关闭时间最长,他要完成所有数据清除以及插入缓存区的合并;参数值为1时是快速关闭模式,InnoDB会跳过上述操作,直接关闭;当参数值为2时,InnoDB首先刷新其日志文件到磁盘,而后执行冷关闭,如果MySQL崩溃了,那么未提交的事务中的数据就丢失了,下次启动会执行故障恢复。一版保持默认值即可,除非遇到紧急情况,需要立刻关闭数据库,否则不要设置为2。

参数优化实例

当前用来测试的服务器拥有1GB的物理内存,假定峰值最大连接数为10个,表对象均使用MyISAM或InnoDB两种存储引擎,其中以后者为主,可以按照下面的思路配置

1. 为操作系统预留20%的内存,约为200M

2. 与现场相关的几个关键参数如下

sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M
join_buffer_size=2M

预计链接数到达峰值时,线程最大可能占用10 * (2 + 2 + 2 + 2) = 80M

3. MyISAM引擎的表对象不多,主要是系统的对象,记录条数有限,因此与之相关的几个缓存区就没必要分配太多内存,相关设置参数如下

key_buffer_size=16M
key_cache_block_size=64K
myisam_sort_buffer_size=64M

4. 剩下的空间 1000M - 200 - 80 = 720M, 就可以全部分配给InnoDB的缓存池,设定相关参数如下

innodb_buffer_pool_size=720M
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=2

其他的查询缓存池,二进制日志缓存等配置相对来说占用的空间有限,这里就不一一提及了。MySQL的初始文件内容如下

sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M
join_buffer_size=2M
key_buffer_size=16M
key_cache_block_size=64K
myisam_sort_buffer_size=64M
innodb_buffer_pool_size=720M
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=2

5. 测试提升

还是使用tpcc命令,./tpcc_start -h 127.0.0.1 -P 3306 -u root -pZj123$%^ -d tpcc -w 10 -c 10 -r 100 -l 300 -f tpcc_mysql.log -t tpcc_mysql.rtx,结果如下,看结果提升还是挺大的,看<TpmC>部分的每分钟处理订单量有311.400变成了817.800

...
10, trx: 136, 95%: 1218.908, 99%: 1831.904, max_rt: 2236.249, 134|1867.656, 13|1527.122, 12|4758.197, 15|7153.962
20, trx: 176, 95%: 1663.076, 99%: 1845.112, max_rt: 2769.179, 178|1855.024, 18|397.819, 18|2977.147, 17|2180.252
...

<Raw Results>
  [0] sc:0 lt:4089  rt:0  fl:0 avg_rt: 490.3 (5)
  [1] sc:48 lt:4042  rt:0  fl:0 avg_rt: 211.0 (5)
  [2] sc:28 lt:381  rt:0  fl:0 avg_rt: 238.3 (5)
  [3] sc:0 lt:408  rt:0  fl:0 avg_rt: 1926.3 (80)
  [4] sc:4 lt:406  rt:0  fl:0 avg_rt: 592.6 (20)
 in 300 sec.

<Raw Results2(sum ver.)>
  [0] sc:0  lt:4089  rt:0  fl:0 
  [1] sc:48  lt:4042  rt:0  fl:0 
  [2] sc:28  lt:381  rt:0  fl:0 
  [3] sc:0  lt:408  rt:0  fl:0 
  [4] sc:4  lt:406  rt:0  fl:0 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.34% (>= 4.0%) [OK]
    Stock-Level: 4.36% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 0.00%  [NG] *
        Payment: 1.17%  [NG] *
   Order-Status: 6.85%  [NG] *
       Delivery: 0.00%  [NG] *
    Stock-Level: 0.98%  [NG] *

<TpmC>
                 817.800 TpmC
...

分析慢查询日志

慢查询日志会有以下的问题

  1. 慢查询日志变得越来越大,记录越来越多,逐行查看已不现实。
  2. 大量的慢查询日志,可能只是由某几条SQL语句触发的,这几条SQL语句有可能正是导致瓶颈的最大嫌疑,怎么快速找出他们呢?

针对这两个问题,可以使用mysqldumpslow和mysqlsla两个命令解决

mysqldumpslow

这个命令是mysql自带的,专门用来分析慢查询日志的工具,这是一段用perl语言编写的脚本。他能将类似的SQL语句(即语句抽象,将SQL语句相同但语句的值不同)归为一组显示。
就像下面的源文件

/usr/sbin/mysqld, Version: 5.7.23-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2018-11-25T08:19:18.067587Z
# User@Host: root[root] @  [192.168.23.1]  Id:     3
# Query_time: 0.471453  Lock_time: 0.101151 Rows_sent: 5002  Rows_examined: 7508
use chinabrands_db;
SET timestamp=1543133958;
/* ApplicationName=DataGrip 2018.2.5 */ select *
from goods where goods_title like "%c%c%";
# Time: 2018-11-25T08:19:55.087293Z
# User@Host: root[root] @  [192.168.23.1]  Id:     3
# Query_time: 0.485758  Lock_time: 0.000112 Rows_sent: 5002  Rows_examined: 19584
SET timestamp=1543133995;
/* ApplicationName=DataGrip 2018.2.5 */ select *
from goods where goods_title like "%b%b%";
# Time: 2018-11-25T08:19:59.257479Z
# User@Host: root[root] @  [192.168.23.1]  Id:     3
# Query_time: 0.317772  Lock_time: 0.000129 Rows_sent: 5002  Rows_examined: 6916
SET timestamp=1543133999;
/* ApplicationName=DataGrip 2018.2.5 */ select *
from goods where goods_title like "%a%a%";

使用该命令分析之后的结果为

[root@jin mysql]# mysqldumpslow jin-slow.log 

Reading mysql slow query log from jin-slow.log
Count: 3  Time=0.39s (1s)  Lock=0.03s (0s)  Rows=5002.0 (15006), root[root]@[192.168.23.1]
  /* ApplicationName=DataGrip N.N.N */ select *
  from goods where goods_title like "S"

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts

这个命令本身的参数比较少,常用的就更少了(mysqldumpslow本身使用就比较少),介绍一些比较有用的参数

  • -s t : 按照总的查询时间排序
  • -s at : 按照平均查询时间排序
  • -s l : 按照总的锁定时间排序
  • -s al : 按照平均锁定时间排序
  • -s s : 按照总的记录行数排序
  • -s as : 按照平均记录行数排序
  • -s c : 按照语句执行次数排序
  • -r : 按照排序规则倒序输出,也就是说先执行-s参数指定规则,而后将数据倒序输出
  • -t : 控制输出的SQL语句的数量。比如慢查询中有共有10000条记录,愁向后产生100条不同的SQL语句,但是实际上后面的90条,每个只执行了一次,前面的10
    条SQL语句产生的9910次慢查询,对于DBA来说,重点关注前面10条就行了。这个参数就是做这个的

mysqlsla

安装
  1. 先安装依赖
yum -y install perl-CPAN perl-Time-HiRes perl-DBI perl-DBD-MySQL
perl -MCPAN -e shell
  1. 安装mysqlsla
perl Makefile.PL
make && make install
使用
mysqlsla -lt slow SlowLogPath

使用mysqlsla分析上面的错误日志

[root@jin mysqlsla]# mysqlsla -lt slow /var/lib/mysql/jin-slow.log 
Report for slow logs: /var/lib/mysql/jin-slow.log
4 queries total, 1 unique
Sorted by 't_sum'
Grand Totals: Time 4 s, Lock 0 s, Rows sent 135.53k, Rows Examined 199.97k


______________________________________________________________________ 001 ___
Count         : 4  (100.00%)
Time          : 3.968118 s total, 992.029 ms avg, 317.772 ms to 2.693135 s max  (100.00%)
Lock Time (s) : 101.969 ms total, 25.492 ms avg, 112 ?s to 101.151 ms max  (100.00%)
Rows sent     : 33.88k avg, 5.00k to 120.52k max  (100.00%)
Rows examined : 49.99k avg, 6.92k to 165.96k max  (100.00%)
Database      : chinabrands_db
Users         : 
  root@ 192.168.23.1 : 75.00% (3) of query, 75.00% (3) of all users
  root@localhost  : 25.00% (1) of query, 25.00% (1) of all users

Query abstract:
SET timestamp=N; SELECT * FROM goods WHERE goods_title LIKE "S";

Query sample:
SET timestamp=1543133958;
select *
from goods where goods_title like "%c%c%";

mysqlsla支持分析二进制日志,普通查询日志,慢查询日志等
在命令行指定参数时,参数的格式一般为--option,不过-option也能被支持,甚至能够支持简写形式,比如--top可以简写成-to,只要简写后的值是唯一的就行。有些选项为了方便使用还指定了别名,比如--database可以用--db或-D。

mysqlsla常用参数

1. --log-type(-lt) : TYPE LOGS

用来指定分析的日志文件类型,作为重要也是最常用的选项。这个参数目前有3种选项。

  • slow : 慢查询日志
  • general : 普通查询日志
  • binary : 二进制日志,注意需要先通过mysqlbinlog处理
2. --abstract-in(-Ai) N

用于抽象处理in(...)语句,默认不启用
该参数主要用来抽象in语句的表现方式,后面指定的N参数值用于定义in值(数据)的范围,是指是个语句的分组统计方法,比如当指定-Ai 10时,如果in中的值的数量在0~10之间的抽象成一条语句,10~20之间的又抽象成一条语句,依次类推,抽象后的in语句就会变成in(S0-9),in(S10-19),in(S20-29)...的形式,每个in数量的语句都被抽象成一条。

例如,分析指定的慢查询日志,对于in语句按照值的个数每100个做分级。

mysqlsla -lt slow -Ai slowlog.log
3. --abstruct-values(-Av)

抽象values语句,按照values值的个数显示,默认不启用
默认情况下,比如像VALUES ('FOO', 'BAR'...)抽象后就变成与VALUES ('S','S'...)类似的形式。如果指定了-Av选项,那么上述形式就变成了VALUES ('S')2。跟--Ai参数功能类似,只是处理的策略不同。

4. --explain(-ex)

显示每条查询的执行计划,默认不启用,不过某些场景下还是有用处的。

5. --databases(-db)(-D) DATABASES

当指定了explain选项时需要指定本参数,以便于能够到正确的数据库中获取语句执行的执行计划,可以一次指定多个数据库名,相互间以逗号分隔。

由于使用explain语句时需要知道该语句查询的对象属于哪个数据库,因此-D选项是必要的。注意UDL日志不支持执行计划。执行explain时,mysqlsla会根据-D执行的db名逐个尝试,知道成功(失败也不会报错)。

6. --microsecond-symbol(-us) STRING

已字符串方式显示毫秒值,默认显示为us,某些字符集下可能让人看起来感觉像乱码一样。

7. --statement-filter (-sf) CONDITIONS

过滤SQL语句类型,默认不启用。指定的CONDITIONS格式为 : +-,[TYPE]
[+-]只出现一次,用来表示包含(+)或不包含(-)。
[TYPE]可以指定多个,相互间以逗号分隔即可,用来指定具体的过滤关键字,比如:SELECT/CREATE/DROP/UPDATE/INSERT。

8. --top N

只显示topN的查询,默认值为10.
例如,分析指定的慢查询日志,并列出前50条:

关注系统状态

MySQL服务在做什么

SHOW [FULL] PROCESSLIST

这个命令会将每一个连接的线程,作为一条独立的记录输出,每一条记录都包括下列值

  • Id : 当前链接的标识ID号,自增序列
  • User : 当前链接所使用的用户
  • Host : 来访的服务器
  • Db : 当前访问的数据库,如果未选择任何数;据,则本列会显示为NULL
  • Command : 标识当前所执行的操作类型。具体信息可参考https://dev.mysql.com/doc/refman/5.6/en/thread-commands.html
  • Time : 该会话保持在当前这个状态的时间,以秒为单位,每次更换状态时,时间即被充值。
  • State : 显示当前会话的状态,比如是在检查表还是发送数据,是在注册master还是在等待接收日志等,这个状态的数量就更多了,MySQL官方文档从8.12.5.2到8.12.5.10小节均是在描述各种状态,有兴趣的可以看看
  • Info : 当前连接正在执行的操作,通常是SQL语句。默认情况下,Info列只会显示当前所执行的前100个字符,如果希望查看完整的内容,可以在执行show processlist时加上full关键字。如果当前什么也没做(Command中标记当前操作类型为Sleep),则本列会显示为NULL。

除了通过show peocesslist命令获取线程的信息外,执行mysqladmin命令附加processlist选项,又或者查询information_schema.processlist表对象,都能够获取相对应的信息。在执行show processlist时,如果用户拥有super权限,那就能看到所有的线程信息,否则只能看到自有的(当前用户创建的线程)

对于有用过show processlist操作经验的朋友,可能会发现,某些时间刷新processlist,会发现用户列为"unauthenticated user",看到未认证的用户这类字眼,不用担心,不是被攻击了,这个状态其实是那些已经与服务端建立了连接但是还没有完成验证的用户。

MySQL中的每个连接,都是独立的线程,对于异常的可以使用KILL命令清除。
例如,要清除48号进程

kill 48;

对于KILL命令来说,实际上有两种清除策略。

  • 直接杀掉线程,即kill thread_id
  • 终止线程当前正在执行的操作,即kill query thread_id

注意,kill命令执行后,MySQL可能不是马上就干掉指定的线程,如果该线程正在执行操作,那么MySQL需要先中止该操作,涉及数据读写的话,那就该提交的提交,该回滚的回滚(相当于执行kill query thread_id),等到确认无误后才正在杀掉线程

MySQL语句在做什么

show processlist可以查看当前系统的线程信息,但如果需要查看SQL语句在执行时具体做了什么,可以用show profiles和show profile命令

show profiles

show profiles用于显示最近执行的语句(以及语句执行的时间开销)。show profiles显示执行语句相关的信息时,受制于两方面的因素:

  • 首先,资源统计是一个名为profiling的状态变量控制,因此得先确定当前系统是否启用了资源统计,若未启用,那么show profiles命令会返回空
  • 所显示的最近执行语句条数,由系统变量profiling_history_size控制,默认为15,最大值不超过100。

show profile命令用于显示(单个)语句执行时的详细资源信息。默认将显示最近(show profiles中记录的)执行过的语句所使用的总和信息,不过他支持for query子句,当指定for query n,就可以查询具体的某条语句执行时所使用的资源信息,同时show profile还支持limit子句,这样就可以用来限制输出的记录。

语法 :

show profile [type[,type]...]
 [for query n]
 [limit row_count [OFFSET offset]]
type : 
ALL|BLOCK IO|CONTEXT SWITCHES|CPU|IPC|MEMORY|PAGE FAULTS|SOURCE|SWAPS

默认情况下,show profile只显示Status和Duration列,其中Status列中内容,与show processlist命令中的State内容相同。另外show profile还有一个type关键字,对于可选的type关键字来说,他可以控制show profile命令输出下列附加信息

  • ALL : 显示所有信息
  • BLOCK IO : 显示输入/输出的块数量
  • CONTEXT SWITCHED : 显示换页操作的

后面的都丢失了,我去...