标签 mysql 下的文章

MySQL索引优化

三星索引

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

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

示例

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

响应时间

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 : 显示换页操作的

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

mysql 学习记录06-MySQL命令行工具

MySQL的命令行工具不少,从大体上可以分为服务端和客户端两类。服务端工具包括mysql_install_db,mysqld_safe,mysqld以及mysqld_mutli,数量不多,但是非常重要。客户端工具有五花八门了。

mysql_install_db--MySQL建库工具

这个命令用来初始化与MySQL数据库系统表相关的物理文件。所创建的文件如果没有特别别指定,都会保存在data目录下,所以对改命令而言,指定data路径的--datadir就是最重要的参数之一。

需要注意这个命令不是在bin目录下,而是在script目录下

# datadir用于指定生成的文件的存放路径
# basedir用于指定mysql的目录,有可能会用mysql相关的命令
mysql_install_db --datadir=/data/mysqldata/3306/data --basedir=/usr/local/mysql

mysqld_safe--MySQL启动工具

mysqld_safe用于启动MySQL服务,在启动前会做安全检查,如果发现目前已经有MySQL服务在运行会抛出错误信息。
可以使用--defaults-file指定配置文件

mysqld_safe --defaults-file=/etc/my.cnf &

注意在命令行最后加了&符号,改符号的功能是将执行的命令行进程放到后台执行。

mysqld--MySQL主进程

真正控制MySQL服务的进程就是mysqld了
可以使用mysqld --verbose --help查看配置
如果想要知道怎么用mysqld启动数据库,可以执行ps -ef| grep mysql

[root@jin plugin]# ps -ef| grep mysql
root       4257      1  0 Nov18 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql      4523   4257  0 Nov18 pts/0    00:00:26 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

每个MySQL服务对应一个端口,每个端口对应一个mysqld进程

作为MySQL数据库中最重要的关键进程之一,除了拥有众多系统参数(MySQL服务的启动时参数),还拥有相当多的系统变量(MySQL服务的运行时参数),以及一系列的系统变量(记录MySQL服务的运行时状态)。

简单来说系统参数就是命令行选项,在执行命令时指定,用以控制时间不同功能或设定。

系统变量可以使用show [global] variables;查看,绝大多数的系统参数都有对应的同名系统变量,只不过系统参数是在服务启动时指定,系统变量则可以在服务运行时修改。

注意系统变量有作用域的概念,分为全局(GLOBAL)和会话(SESSION)两类。可以用set global variables_name = 1;

需要注意不管是全局还是局部变量,在服务重启后都会失效,所以记得要把修改的配置加到配置文件中。

状态变量就是记录MySQL服务运行时的系统状态。状态变量也有作用域,分为全局(GLOBAL)和会话(SESSION)两类,前者是记录整个MySQL服务的状态,而后者只代表当前会话的状态。可以使用show [global] status;查看。

mysqld_multi--MySQL多实例管理工具

这个命令可以同时管理多个mysqld实例,改命令的格式如下

mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR,GNR,GNR...]
or
mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR-GNR,GNR,GNR-GNR,...]

mysqld_multi会扫描my.cnf文件中的区块名(即组名),即[mysqldN]。我们知道默认配置文件中mysqld区块的配置是没有后面的N的,但是如果希望通过mysqd_multi来管理MySQL,就需要配置个N了。这个N既起到唯一的作用,又能标识mysqld进程。具体怎么配置可以使用mysqld_multi --example查看

配置步骤

1. 初始化数据

注意执行之后会随机生成一个root@locahost的密码,记得保存下来

# 5.7后用mysql_install_db看不到随机生成的密码
mysqld --initialize --user=mysql --datadir=/var/lib/mysql3307

2. 修改配置文件my.cnf

[mysqld1]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
federated
# 设置server id
server_id = 888888
# # 设置binlog存放路径
log-bin = mysql-bin
#log-bin-index = master-bin.index

[mysqld2]
mysqld     = /usr/sbin/mysqld
#ledir      = /path/to/mysqld-binary/
mysqladmin = /usr/bin//mysqladmin
socket=/var/lib/mysql/mysql3307.sock
port       = 3307
datadir=/var/lib/mysql3307
symbolic-links=0
log-error=/var/log/mysqld3307.log
pid-file=/var/run/mysqld/mysqld.3307pid
character-set-server=utf8
collation-server=utf8_general_ci
user=mysql
federated

4. 服务管理相关命令

# 开启mysql2服务
mysqld_multi start 2
# 开启mysql6,mysql7,mysql8服务
mysqld_multi start 6-8
# 查看服务是否启动
mysqld_multi report 2
Reporting MySQL servers
MySQL server from group: mysqld2 is running
# 关闭服务,注意关闭命令必须输入用户和密码才能生效
mysqld_multi stop 2 -user=root -password=Zj123$%^

mysql--专业命令行工具

相关参数

1. --auto-rehash

自动补全功能,类似linux命令行下的tab键自动补全命令。注意,注意启用此参数后,登录mysql的时间可能会有一定的延长,因为需要加载相关的字典信息,才能实现自动补全

2. --default-character-set

用于指定连接会话的字符集,相当于在连接到MySQL后,执行SET NAMES设置当前会话字符集

3. -e,--execute

mysql命令支持两种操作方式:交互模式和非交互模式。常规应用都是进入到交互模式下,而后执行MySQL的各种DML/DDL/DCL语句,或其他语句。有时我们不需要进入命令行模式,而是在执行mysql命令时直接执行要执行的语句,这种就可以用-e实现,如下

[root@jin mysql3307]# mysql -h 127.0.0.1 -P 3307 -u root -p123456 -e "show global variables like '%master%';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| master_info_repository | FILE  |
| master_verify_checksum | OFF   |
| sync_master_info       | 10000 |
+------------------------+-------+
4. -f,--force

通过mysql命令批量执行sql语句(或者执行一个包括SQL语句的文件,总之是在非交互模式)时,如果要执行的某条sql有错误,那么默认情况下该条语句后面的语句就都不会执行。这个参数的作用在于,即使前面有sql语句由错误,也不会影响后面语句的执行。

5. --show-warnings

执行完语句之后,马上显示警告信息,相当于执行完SQL语句之后再自动执行SHOW WARNING语句

相关技巧

mysql是个命令行工具,进入到交互模式的命令行后,它不仅具有执行SQL语句(各类DML/DDL/DCL)的本领,自己提供了若干命令,这些命令主旨是帮助DBA更好的执行SQL语句。
可以使用help命令查看相关的命令,说几个比较常用的

1. pager

可以视作mysql命令行模式中的管道符,如下

# 设置,pager后面接的是系统命令
pager more
# 查询看看效果吧
select * from member limit 100
# 取消
nopager
2. prompt

可以用于指定mysql命令行的提示符,可以显示当前的数据库,用户等信息,具体的可以百度,可以配置直接叫到配置文件中

3. rehash

自动补全

4. Tee

指定内容输出到外部文件,如下

# 开始
tee /tmp/mysql_tee.txt
# 执行sql
select * from mysql.user;
#结束
notee

mysqladmin--管理工具

通过该工具可以完成检查服务器配置,当前状态,创建/删除数据库等操作。
命令格式如下

mysqladmin [OPTIONS] command command ...

参数

参数很多,记录几个比较特别的参数

-i,--sleep

间隔指定时间后,再次重复调用本mysqladmin命令

-r,--relative

当与-i参数联合使用并且指定了extends-status命令时,显示本次与上次之间个状态值的差异

命令

create [dbname]

创建数据库

drop [dbname]

删除数据库

extended-status

查看服务器状态信息,跟show global variables一样

flush-hosts

刷新缓存信息

flush-logs

刷新日志

flush-status

充值状态变量

flush-tables

刷新所有表

flush-thread

刷新线程缓存

flush-privileges

重新加载授权表,与reload完全相同

reload

与上面的命令相同

refesh

刷新所有表,并切换日志文件

password [new-password]

修改指定用户的密码,只是按照旧的格式修改

ping

通过ping的方式,检查当前MySQL服务是否仍能正常提供服务

debug

输出当前MySQL服务的调试信息到error_log文件中,某些情况下性能分析或故障排查非常实用

kill id,id,...

杀掉连接至MySQL服务的进程,与KILL id功能相同

processlist

查看当前MySQL服务所有的链接线程信息,功能完全等同于SHOW PROCESSLIST语句

shutdown

关闭服务

status

查看当前MySQL的状态,功能与mysql命令行模式下的status类型,这个命令相当于只显示mysql命令行模式下status命令的最后一行信息,能够获取较为关键的几项指标。
status命令返回的信息包含

Uptime : MySQL服务的启动时间
Thread : 当前链接的会话数
Questions : 自MySQL服务为启动后,执行的查询语句数量
Slow Queries : 慢查询语句的数量
Opens : 当前处于打开状态的表对象的数量
Flush tables : 执行过flush,refresh和reload命令的数量
Open tables : 当前会话打开的表对象的数量
Queries per second avg : 查询的执行频率
start-slave

启动slave服务

stop-slave

停止slave服务

variable

显示系统变量,与show global variables相同

version

查看版本信息,同时还包含status命令的信息

其他常用命令

myisamchk

用于MyISAM表对象的检查和修复

myisam_ftdump

输出MyISAM全文索引

innochecksum

计算InnoDB数据文的checksum

mysqldumpslow

分析慢查询日志

mysqlbug

向MySQL官方输出bug

其他工具

就不说了...
phpMyAdmin,MySQL Workbench,Navcat之类。。。

mysql 学习记录05-复制特性

概述

MySQL的复制特性很灵活,可以实现整个服务(all database)级别,单个数据库级别,数据库中某些表对象的复制,即能从A复制到B(主从单项复制),B再复制到C,也可以实现A直接复制到B和C(单主多从复制),甚至A的数据库复制给B,B的数据也复制会A(双主复制)。

一般来说MySQL的复制特性一版用于以下场景

  • 提高性能。通过一主多从(甚至多主多从)的部署方案,将涉及数据写的操作放在Master端操作,而将数据读的操作分散到众多的slave端。这样一方面能够降低Master负载,提高数据写入请求的响应效率;另一方面众多的Slave节点同时提供读操作,有一个负载均衡的效果
  • 数据安全。由于数据被复制到Slave节点,即使Master节点宕机,Slave节点还保存着一份数据,这相当于实现了数据的冗余;在日常工作中可以将备份任务放在Slave端执行,以避免执行备份操作时对Master产生影响
  • 数据分析。将数据分析和挖掘等较占资源的操作放在Slave节点执行,这样就可以降低对Master节点可能造成的性能影响
  • 数据分布。基于MySQL复制特性的实现原理,Master和Slave并不需要实时链接,因此可以将Slave和Master放在不同的物理位置,而基本不用担心网络中断等因素对同步造成影响

MySQ复制的过程默认是异步的,也就是说Master端的修改不会马上同步到Slave端,另一方面,Slave不需要时刻都链接Master,只有需要同步数据时,才连接到Master。只要Master节点依然保留了自上次同步后所生成的二进制日志,就可以继续同步。

对于某些场景,这种方式就不一定适用了。如果说对于读写并发和实时性要求都很高的场景,用户可能希望插入的数据库,能够立刻就被查询出来,如果查询是从Slave端获取数据,那么异步的数据同步方案就满足不了需求。针对这种情况,MySQL复制在处理数据时,有两种不同的模式:

  • 基于语句复制([S]tatement [B]ased [R]eplication) : 基于实际执行的SQL语句的模式方案,简称SBR
  • 基于记录复制([R]wo [B]ased [R]eplication) : 基于修改的列的复制模式,简称RBR
  • 基于上述两种方式的混合的复制模式(Mix Based Replication, MBR)

数据复制模式跟日志文件记录格式强相关

创建复制环境

1. 禁止主库写入

两种方式,如下

  1. 可以关闭MySQL服务
/etc/init.d/mysqld stop
  1. 锁定表
flush tables with read lock;

2. 复制数据文件

随便找方式复制即可

3. 设置Master Server ID并启动Master(如果已经设置过可以跳过这一步)

  1. 修改Master配置文件
[mysqld]
# 设置server id
server_id = 888888
# 设置binlog存放路径
log-bin = mysql-bin

4. 获得Master当前的binlog文件和Position(用户后面设置Slave)

# 查看Master信息用于之后配置Slave
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

5. 恢复Master的正常服务

如果Master服务停止了,要启动Master

/etc/init.d/mysqld start

如果Master是锁定的,需要释放锁

unlock tables;

6. 创建复制专用用户

在MySQL中,Slave如果想要获取二进制日志,需要主动链接Master节点,创建一个用户专门用于Slave从Master同步数据,更安全和方便管理。

# 创建一个用户运行从192.168.23网段链接服务器,拥有读取二进制日志的权限(如果已经设置过可以跳过这一步)
grant replication slave on *.* to repl@'192.168.23.%' identified by 'Zj123!@#';

7. 配置Slave

  1. 修改Slave配置文件
[mysqld]
# 设置server id
server_id = 666666
# 设置binlog存放路径,Slave可以不配置这一项
# log-bin = mysql-bin
  1. 删除auto.cnf文件
    这个文件里保存了server-uuid的参数,他拥有唯一标识MySQL服务,如果Slave端是直接复制的Master端的文件,需要把这个文件删除掉,避免和Master相同,MySQL在启动时会自动生成一个
/etc/init.d/mysqld start

8. 启动Slave

/etc/init.d/mysqld start

9. 在Slave上配置Master的连接信息

# 配置连接信息,在第四步中用`show master status`
change master to master_host = '192.168.23.8', master_port = 3306, master_user = 'repl', master_password = 'Zj123!@#', master_log_file = 'mysql-bin.000002', master_log_pos = 154;
# 启动slave
start slave

10. 测试复制是否配置成功

  1. 做变更数据的操作看数据是否能同步
  2. 如果没有同步,用show slave status查看错误信息

复制环境常用管理命令

1. 检查Slave节点的状态

show slave status

参数

下面罗列一些比较重要的参数

  • Slave_IO_State : 显示Slave的当前状态
  • Slave_IO_Running : 显示I/O线程是否在运行,正常情况下应该在运行,除非DBA手动将其停止,或者出现错误
  • Slave_IO_Running : 显示SQL线程是否在运行,正常情况下应该在运行,除非DBA手动将其停止,或者出现错误
  • Last_IO_Erroe/Last_SQL_Error : 正常情况下应该是控制,如果遇到了错误,那么这里会输出错误信息,根据错误信息可以找到相应的错误
  • Seconds_Behind_Master : 显示房钱Slave节点与Matser节点的同步延迟。注意,这个参数有一定的迷惑性,表现在两方面。第一,看起来像是时间,而且确实有时间因素,但其实不是时间。其实是Slave节点接收到的Master的日志文件和已经应用的日志文件之间的差距。比如I/O线程接收到的日志文件写入位置为34560,而SQL线程才应用到34000,这两个位置之间时间上的差距是一小时,那么Seconds_Behind_Master就会显示3600秒。而有可能此时Master已经写到34660的位置了,只是还没有被I/O线程读取到Slave几点。这个参数显示的值不是完全精确的主从之间的延迟时间,而只是Slave节点本地日志接收和应用上的差异。
  • Master_Log_File/Read_Master_Log_Pos : 显示当前读取的Matser节点二进制日志文件和文件位置。
  • Relay_Master_Log_File/Exec_Master_Log_Pos : 显示当前Slave节点正在应用的日志文件位置。
  • Relay_Log_File/Relay_Log_Pos : 显示当前Slave节点正在处理的中继日志文件和位置

show slave status是最为直接简单全面的查询Slave节点状态的方法,此外show processlist也能在一定程度上起到复制了解复制状态的作用

2. 启动停止slave线程

出现意外情况,可以手动控制Slave界定啊的启动和停止,主要通过stop slavestart slave实现
Slave服务由两个线程组成

  • IO_THREAD : 负责读取Master端的二进制日志,并写入到本地的中继日志(relay-log)中
  • SQL_THREAD : 负责从本地中继日志中读取时间并执行

这是两个独立的线程,他们的启动和停止,也可以分开进行,相互之间没有依赖性。
灵活的利用这个两个线程的启停,可以使复制的应用场景更加灵活。如果在备份时,为了保持备份数据的一致性,很多人往往选择停止整个Slave服务,单仔细一想,IO_THREAD并不需要停止,特可以继续从Master读取二进制日志保存在在本地的中继日志中,这样还可以起到对Master节点数据冗余保护的作用。我们只需要停止SQL_THREAD,记录下当前应用到的日志文件名和位置,就可以开始备份任务。因为此时Slave不会有数据更新,相当于处于只读状态,这样创建出来的就是一致性的备份了。当然数据再备份期间与Master是不同步的(如果这期间Master有数据写入的话)。

怎么单独启停IO_THREAD/SQL_THREAD呢?只需要在语句后面机上要操作的线程名就行了,如下

start slave sql_thread;
stop slave sql_thread

复制特性的实施原理和关键因素

复制特性的核心就是基于Master节点对数据库中各项变更的处理机制,MySQL数据库启用二进制日志后,该文件中就会记录MySQL服务自启动以来,所有对数据库结构或内容(数据)的变更事件(即增删改操作),当然select语句由于不会触发结构上变更所以select行为不会被记录。

Slave节点只要连接到Master节点,请求这些二进制日志。拿到二进制日志后,Slave节点就能解析这些二进制日志,并在本地执行,这样就相当于将修改操作在Slave节点重演。日志应用完之后,Slave就和Master一样了

复制特性就是基于二进制日志

复制格式

二进制日志在记录事件时,支持多种格式,由binlog_format参数控制

  • 基于语句记录(Statement-Based Logging, SBL),对应的参数值为statement
  • 基于行格式记录(Row-Based Logging,RBL),对应的参数为row
  • 混合模式记录(Mixed-Based Logging,MBL),对应的参数为mixed

复制特性中,也有一个被称为复制格式的(Replication Formats)的概念,复制格式和二进制日志格式其实是同一个东西,不过是有两个名字,也就是不同场景下的称谓是不同的。

考虑到复制格式和二进制日志格式之间的关系,其格式的种类和逻辑上,目前也分为3中

  • 基于语句复制(Statement-Based Replication,SBR)。在MySQL中的复制功能,最初就是靠传播SQL语句到目标端执行的,这就是所谓的基于语句复制,也就是说二进制日志中保存的就是SQL语句。
  • 基于行复制(Row-Based Replication,RBR)。后来(5.1.4),MySQL又引入了全新的基于行粒度的日志格式,这种模式下,二进制日志文件写入事件时,记录的是变更的记录行的信息
  • 混合记录模式(Mixed-Based Replication)。再后来(5.1.8),MySQL又进了一步,在记录事件到二进制日志时,MySQL服务根据需要,动态修改日志格式,就是所谓的混合模式。注意,混合记录是中模式,而不是一种格式。在这种模式下,默认还是选择基于行的格式记录。具体选择的是什么格式,要看当前执行的语句,以及操作对象所使用的存储引擎而定。

在服务运行期间可以对服务进行修改,但是注意最好不要随便改,因为有可能会导致复制环境报错

SBR与RBR的特点及优势

SBR的优点
  • 技术成熟,自3.23版本即开始提供对这种记录格式的支持
  • 生成日志少,特备是对于大量更新及删除的操作
  • 由于能够记录下数据库做过的所有变更操作,日志可用于行为审计
SBR的缺点
  • 存在安全隐患。这个安全并不是说会被攻击,而是说数据安全。Master节点中产生的修改操作(INSERT,DELETE,UPDATE,REPLACE),并不是都能通过基于语句方式完整的复制到Slave节点,对于不去确定的行为在基于语句复制时,很难确保Slave节点会执行并获得正确的数据,这点从逻辑上证明了主从出现不一致的合理性。比如说Master节点和Slave节点分别执行FOUND_ROWS(),SYSDATE(),UUID()这类函数,可能返回不同的结果;如果使用了这些函数,那么语句执行时会抛出下列警告信息(客户端使用show warnings查看)
Statement is not safe to log in statement format
  • 执行INSERT ... SELECT语句时需要更多的行锁(相对RBR而言)
  • UPDATE要扫表(无索引可用的情况下)时需要持有更多的行锁(相对RBR而言)
  • 对于InnoDB引擎,INSERT语句使用AUTO_INCREMENT会阻塞其他INSERT语句
  • 对于复杂的语句,Slave节点执行时语句必须先被评估,而对于基于row格式复制,则Slave节点只需要修改具体的记录即可(不必执行跟Master端相同的SQL语句,这既是有点也是缺点)
  • 如果语句在Slave节点执行时操作失败,基于statement格式复制会增加主从不一致的概率
  • 单条语句中执行的函数中调用NOW()返回日志相同,但是存储过程就不一定了
  • 对象定义必须拥有唯一键,主要是为了避免冲突
RBR的优点
  • 所有修改都能被安全的复制到Slave节点
  • 5.1.14版本开始,mysql数据库不再被复制,RDB方式的复制不能支持mysql库中的表对象。对于像GRANT/REVOKE这类操作,以及trigger/stored procedures/views等对象的维护操作,会被使用SBR方式复制到Slave
  • 对于CREATE TABLE ... SELECT这类DDL+DML的操作,CREATE创建对象部分使用SBR模式复制,其他部分则使用RBR模式复制
  • 与RDBMS实现的技术类似,其他数据库软件管理和维护方面的经验也可以继承使用
  • Master端执行修改操作时,仅需持有极少的锁,因此可以获得更高的并发性能
  • Slave节点执行INSERT/UPDATE/DELETE时也仅需持有少量锁
RBR的缺点
  • RBR可能会生成更多的日志。比如DML语句,基于statement格式记录日志的话,只需记录所执行的SQL语句,相比之下,基于row格式记录日志,会记录所有变化了的行到二进制日志文件,如果语句触发的记录变更特别多,那么生成的二进制日志自然非常多,即使执行的操作随后被回滚。这也意味着创建备份和回复需要更多时间,以及二进制日志会被更长时间加锁以写数据,也可能带来额外的并发性能上的文件。
  • UDFS生成BLOB值需要花费比基于statement格式日志更长的时间,这是因为BLOB值是被记录的,而不是语句生成的
  • 不能通过分析日志来获取曾经执行过的语句。不过还是可以通过mysqlbinlog命令看有哪些数据被修改了
  • 对于非事务引擎,比如MyISAM表对象,Slave节点应用INSERT操作时,使用RBR模式要比使用SBR模式持有更强的锁定,也就是说使用RBR模式在Slave上并没有并行插入的概念

中继日志文件和状态文件

IO_THREAD将接受到二进制日志文件保存在中继日志文件中。中继日志用来保存其他及诶点的事件(也是二进制格式),可以使用如下参数配置

relay-log = relay-bin
relay-log-index = relay-bin-index

Slave节点会在满足下列条件时,触发生成新的中继文件,并更新相关的索引文件

  • 启动Slaves节点I/O线程时
  • 执行日志刷新命令,如FLUSH LOGS或mysqladmin flush-logs等
  • 中继文件达到指定的最大值有如下两种情况

    1. 如果max_relay_log_size参数值大于0,则日志文件超过该值后即会重建。
    2. 如果max_relay_log_size参数值为0,则通过max_binlog_size确定单个Relay日志的最大值

中继日志文件的维护可以交由SQL_THREAD来处理,他会自动删除无用的中继日志文件

除了中继日志文件外,复制环境中Slave节点还会创建两个复制环境的状态文件,即master.info和relay-log.info,这两个文件都默认保存在mysql的data目录下,也可以通过--master-info-file和--relay-log-info-file参数来自定义文件的名称和路径

  • master.info : 用于保存复制环境中连接Master节点的配置信息,比如用户名,端口,IP,密码等,5.6之后也可以选择将这些信息保存到mysql.slave_master_info表对象
  • relay-log-info : 保存处理进度及中继日志文件的位置,与前面的配置类型5.6之前保存在data路径下的relay-log-info文件中,5.6之后可以将这些信息保存到mysql.slave_relay_log_info表对象中

注意不要手动去修改这两个文件,不然可能会出现无法预知的错误。

复制过滤规则

注意,就算设置了过滤规则,Slave也会接受所有的二进制日志,等到应用时再过滤。

MySQL提供了一系列以--replicate-*开头的参数,用于复制环境中Slave节点定义过滤规则

replicate-rewrite-db=from_name->to_name用于将A库的对象保存到B库中

1. 库级过滤规则

--replicate-do-db--replicate-ignore-db用于控制是否应用指定数据库的变更事件,注意这两个参数执行,并不是过滤指定数据的操作,而是过滤当前默认数据(USE)所做的操作,如下
Slave设置了--replicate-ignore-db = a
在master上执行

use a;
drop table table1;
drop table b.table2;

上面的操作,Salve设置了不引用数据库a的事件,SQL操作中因为默认数据库是a,所以对table2的操作会被过滤掉,因为默认数据库为a(使用use a;设置了默认数据库)

--replicate-do-db某些情况也可以用于忽略其他数据库。例如,当指定了--replicate-do-db=sales,那么所有不在默认数据(use sales)下发生的变更,都不会记录日志

2. 表级复制选项

跟传统粒度越细,优化级别越高的规则不同,MySQL复制特性中的过滤规则,是先检查数据库级别的设置,当数据库级参数无有效匹配时,Slave节点才检查并评估表级过滤参数。

首先,作为一个准备工序,Slave节点要检查是否是基于语句复制(SBR),如果是的话,并且语句是在存储过程中触发,那么Slaves节点执行语句并退出,如果基于RBR的话,Slave节点并不知道Master端执行的语句做了什么,因此这种情况下什么都不会应用。

基于SBR场景,复制事件以语句形式体现(所有的修改时间都可以定位为SQL语句),而基于RBR,所有的事件均以具体修改的表的列的数据体现(即使一条简单的update tbl set col = 1也可能触发大量的row修改事件)

如果没有指定任何表级过滤规则,那么检查到这一步时,Slave节点就直接执行所有修改事件了,这是默认场景时的表现。这是默认场景时的表现。如果指定了--replicate-do-table--replicate-wild-do-table参数,则只执行参数中指定对象的修改事件;如果指定了--replicate-ignore-table--replicate-wild-ignore-table参数,则除了参数中指定对象的修改事件不执行外,其他变更事件均要在本地执行。

对于--replicate-*这类参数,如果需要指定多个对象。

不同场景下复制过滤规则的应用

条件结果
没有任何--replicate-*参数Slave执行所有接收到的时间
指定了--replicate-*-db参数,未指定表级参数只执行(或忽略)指定数据库的事件
指定了--replicate-*-table参数,未指定库级参数只执行(或忽略)指定表对象的事件
既有库级参数,也有表级参数Slave节点首先执行(或忽略)指定数据的事件,而后再处理表级过滤过滤选项。需要注意日志记录格式对复制的影响

使用XtraBackup

。。。

半同步复制环境

在半同步机制下,Master节点只要确认有至少一个Slave节点接收到了事务,即可向发起请求的客户端返回操作成功信息,Master节点甚至不需要等待Slave节点也成功执行完这个事务,只要至少有一个Slave节点接收到这个事务,并且将之成功写入到本地中继日志文件,就算成功。应该是因为相比同步机制,工作只完成了一半左右,所以就叫半同步。

相比异步复制,半同步数据在数据完整性方面有显著提升。每个成功提交的事务,都代表这份数据至少存在两个节点上。

1. 配置半同步复制环境

找到插件目录

1. 找到插件目录
mysql> show variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
2. 查看路径
[root@jin plugin]# ls | grep sem
semisync_master.so
semisync_slave.so
3. 安装插件
# 在master安装
install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
# 在slave安装
install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
4. 插件设置

设置master

set global rpl_semi_sync_master_enabled = 1;
set global rpl_semi_sync_master_timeout = 3000;

设置slave

set global rpl_semi_sync_slave_enabled = 1;
配置项说明
  • rpl_semi_sync_master_enabled : 用于控制是否在Master节点启用半同步复制,默认值为1即启用状态
  • rpl_semi_sync_master_timeout : 用于指定Master节点等待Slave响应的时间,单位是毫秒,默认是10000即10秒钟,这里设置为3秒。若超出指定时间Slave节点仍无响应,那么当前复制环境就临时被转换为异步复制。
  • rpl_semi_sync_slave_enabled : 跟第一个参数看起来很像,唯一的区别,他是用来控制Slave节点是否启用半同步复制
  • 前面配置的3个变量尽管可以动态修改,但强烈建议将所有配置的变量,都保存在初始化参数文件中,否则需要在每次启动MySQL服务时再手动进行配置
  • rpl_semi_sync_*开头的变量还有几个,这里只配置了最重要的3项,更多参数可以参考官方文档中的内容。
5. 重启Slave节点的IO_THREAD线程

这一步时为了让Slave节点重新连接Master节点,注册成半同步Slave身份,如果不重启IO_THREAD,那么Slave就会一直保持异步复制模式。

stop SLAVE IO_THREAD;
start SLAVE IO_THREAD;

监控半同步环境

可以使用show status查看状态变量,安装半同步插件后,MySQL中就会多出若干个以Rpl_semi_sync_*开头的状态变量,其中在Slave节点,与半同步复制相关的状态变量只有一项:Rpl_semi_sync_slave_status : 标识当前Slave是否启用了半同步模式。
Master节点中与半同步复制相关的状态变量要多一些,其中最值得关注的有下列几项:

  • Rol_semi_sync_master_clients : 显示当前处理半同步模式的Slave节点数量
  • Rol_semi_sync_master_status : 标识当前Master节点是否启用了半同步模式
  • Rol_semi_sync_master_no_tx : 当前未成功发送到Slave的事务数量
  • Rol_semi_sync_master_yes_tx : 当前成功发送到Slave的事务数量

语句如下

show variables like 'rpl_semi_sync%'

复制环境中的故障切换

...

延迟复制

MySQL数据库的复制环境,可以通过配置,实现Slave节点的延迟复制,就是说,将Slave节点与Master节点保持指定时间的间隔。这个实现起来比较简单,只需要指定Slave节点中的MASTER_DELAY,通过CHANGE MASTER TO语句即可进行设定,语法如下:

CHANGE MASTER TO MASTER_DELAY = n;

单位为秒

设置完之后,执行START SLAVE就可以使之生效,而无需重启MySQL。这样设置之后,Slave节点接收到Master的节点生成的日志之后,不会马上应用,而是等待,知道时间符合设定的延迟条件后才开始使用。

延迟复制一版用于以下场景

  • 若Master节点出现误改,误删等操作,造成数据都是的情况,由于Slaves节点有延迟因素的存在,那么DBA可以通过Slave节点仍然保存的数据,快速地将数据恢复回去。不过延迟时间不会太长如果发现误删操作,而且Slave节点恰好尚未应用这些事件,那就必须争分夺秒恢复才行
  • 测试复制环境出现延迟时,对系统可能造成的影响。
  • 无需通过恢复M就可以查看之前版本的数据库,某些场景下,这也能简化DBA的工作

可以用SHOW SLAVE STATUS查看Slaves节点复制信息时,有3个列值与此有关

  • SQL_Delay : 显示当前设定的延迟时间,以秒为单位。
  • SQL_Remaining_Delay : 当Slave_SQL_Running_State列的状态是"Waiting until MASTER_DELAY seconds after master executed event"时,本列显示的值就是距离延迟阈值的时间,换个说法就是还有多长时间才能开始应用,否则的话本列值应该是NULL
  • SQL_SQL_Running_State : 显示当前SQL_THREAD的状态

当SQL_THREAD处于延迟等待阶段,SHOW PROCESSLIST显示该进程的状态时,将会显示为"Waiting until MASTER_DELAY seconds after master exexuted event"。这些信息都说明,当前环境配置了延迟复制