相关文章：高性能MySQL笔记（上）

淘宝的MySQL博客月报首页

第九章操作系统和硬件优化

9.1什么限制了MySQL的性能

最常见的两个瓶颈是CPU和I/O

9.2如何选择CPU

快速CPU还是多CPU，有两个目标：

低延时（快速响应）: 要做到这一点就需要高速CPU
高吞吐: 如果能同时运行很多查询，则可以从多CPU中受益

MySQL复制，也能在高速CPU下工作的非常好，而多CPU对复制的帮助不大。而备库的瓶颈通常是I/O子系统而不是CPU（因为在备库上已经被简化成了串行化任务）

9.3平衡内存和磁盘资源

顺序I/O比随机I/O快

内存随机访问比磁盘随机访问快了2500倍，但顺序读只比磁盘快了10倍左右

假设每次读取100字节：

磁盘随机寻道：100次/S ； 50M/S的顺序读
内存随机访问：25万次/S；顺序访问：500万次/S。内存的顺序读写速度一般都在10G/S以上

缓存命中率: 缓存命中率实际上也会决定使用了多少CPU，所以评估缓存命中率的最好方法是看CPU的使用率。; 例如，若CPU使用了99%的时间工作，用了1%时间等待I/O，那缓存命中率还是不错的。（REF: 7.12节论述了查询缓存，不要和这里的概念混了）

9.4固态存储

闪存最重要的两个特性：1、提升随机I/O；2、提高并发操作
写放大及垃圾收集 对性能影响较大（尤其是空间快满了的时候）

9.4.2闪存技术

有两种闪存类型：

单层单元（SLC）：每个单元存储一个比特（0或1）。SLC相对更昂贵，但非常快，并且擦写寿命高达100000个写周期（20年左右）缺点是存储密度相对较低
多层单元（MLC）：每个单元存储2个比特（3个比特设置也正在进入市场中）。这使得存储密度提高了，但速度和耐擦写性能降低了。一个不错的MLC设备可能被定为10000个写循环周期（2年）

9.4.3闪存的基准测试

闪存的三阶段模式，我们称为A-B-C模式：

A：开始阶段，通常运行比较快
B：过度状态状态，此时垃圾回收开始工作
C：稳定状态，此时最慢

作者的基准测试：

http://www.ssdperformanceblog.com   http://www.mysqlperformanceblog.com

9.4.4固态硬盘

SSD是否配置电容或者电池是我们必须关注的特性

9.4.5PCIe设备

没有什么能比得上PCIe设备上获得的性能。缺点是它们太贵了。

9.4.6什么时候应该使用闪存

一些事务或插入繁忙的工作负载
单线程负载是另一个闪存的潜在应用场景。MySQL复制是单线程工作的典型例子，它可以从低延迟中获得很多收益
闪存也可以为服务器整合提供巨大的帮助，尤其是PCIe方式的。我们已经看到了机会，把很多实例整合到一台物理服务器——有时高达10或15倍的整合都是有可能的（详见11章）

然而缓存也可能不一定是你要的答案。一个很好的例子是，像InnoDB日志文件这样的顺序写的工作负载，闪存不能提供多少性能优势，因为这种情形下，闪存连续写方面不比标准硬盘快多少。这样的工作负载也是高吞吐的，会耗尽缓存的寿命。在标准硬盘上存放日志文件通常是一个更好的主意，用具有电池保护的写缓存的RAID控制器。
有时答案在内存/磁盘的比例，而不只是磁盘。

9.4.7使用Flashcache

Flashcache创建了一个块设备，并且可以被分区，也可以像其他块设备一样创建文件系统，特点是这个块设置是由闪存和磁盘共同支撑的。闪存设备用作读取和写入的告诉缓存。

尽管理论上Flashcache可能更高效，但最终性能并不如低层闪存那么好。
从测试来看，尚不清楚对写负载有多大好处，但对读肯定有好处。于是它适合这样的情况：有大量的读I/O，并且工作集比内存大的多。
除了实验，这里有个生产环境的实例经验：有个4T的数据库，这个数据库遇到了很大的复制延迟。我们给系统加了半个TB的Virident PCIe卡作为存储。然后安装了Flashcache，并且把PCIe作为绑定设备的闪存部分，复制速度就翻了一倍

Flashcache的IOPS思想实验: 磁盘IPOS: 3,5" 15.000 rpm FC 150 IOPS（参考中列出的最好的一个）参考常见硬盘IOPS参考值磁盘性能指标--IOPS 理论; 假设闪存支持100000IPOS，未命中率为10%，则按磁盘150IPOS计算，则本Flashcache设备仅可以支持 150/10% = 1500IPOS，受制于磁盘！

9.4.9优化固态存储上的MySQL

关于插入缓冲：innodb insert buffer 插入缓冲区的理解 InnoDB三大特性之-插入缓冲

禁用双缓冲

在闪存上禁用双缓冲差不多能提高性能50%。

建议用innodb_file_per_table: 1、并把数据文件目录放到闪存设备。; 2、然后移动ibdata1和日志文件，以及其它所有日志文件（二进制日志、复制日志、等等）到RAID卷

这会把随机I/O集中到缓存设备上，然后把大部分顺序写入的压力尽可能的移出闪存

9.6RAID性能优化

案例：RAID10 （硬件RAID1，软件RAID0 ReiserFS）获5倍性能提升: 请求串行的发送到一个10块盘的RAID10卷中的每个设备，使用ReiserFS文件系统，InnoDB打开了inondb_file_pertable选项。尝试RAID1的基础上用RAID0做成的RAID10的方式，获得了5倍多的吞吐
基准测试: 最后，当准备上线新服务器时不要忘了做基准测试！这能让你获得期望的性能。如。若一个硬盘能每秒做200个随机读，一个有8个硬盘驱动器的RAID10卷应该接近每秒1600个随机读。
RAID配置和缓存: 除非控制器有电池备份单元（BBU）或其它非易失性存储，否则不应该启用RAID缓存。不带BBU的缓冲写，在断电时，有可能损坏数据库，甚至是事务性文件系统。

9.8使用多磁盘卷

InnoDB事务日志包含redo（ib_logfile0 ib_logfile1）日志和undo日志（存储于表空间 ibdata中MySQL · 引擎特性 · InnoDB undo log 漫游）

分离临时表目录: 最好把它们入在内存文件系统，如tmpfs

9.9网络配置

skip_name_resolve: 建议打开，默认是关闭的见8.9; 损坏或者变慢的的DNS解析对很多应用程序都是个问题。对MySQL尤其严重; 若启用，则用户账户必须在host列使用具有唯一性的IP地址，“localhost”或者IP地址通配符。
back_log: 默认50秒是不够的,建议3000; 控制MySQL传入的TCP连接队列的大小。设置不够的症状是，在3秒超时规则下，客户会看到零星的“连接被拒绝”的错误。在繁忙的系统中这个选项通常应加大。把这个选项增加到数百甚至数千，似乎没有任何副作用。事实上你应该看的更远一些，可能还需要配置操作系统的TCP网络设置。在GNU/Linux上需要增加somaxconn限制，并且需要检查tcp_max_syn_back_log设置
网络监控: MRTG 其它的还有Smokeping 、Cacti

内核调优: echo 1024 65535 > /proc/sys/net/ipv4/ip_local_port_range #改变本地端口范围; echo 4096 > /proc/sys/net/ipv4/tcp_max_syn_backlog #允许更多的连接进入队列; echo <value> > /proc/sys/net/ipv4/tcp_fin_timeout; 风行网TK服务器内核调优实例：高并发情况下Linux服务器内核配置

9.11选择文件系统

在/etc/fstab中添加noatime、nodiratime挂载选项可禁用文件的访问时间这样有时可以提高5%~10%的性能: /dev/sda2 /usr/lib/mysql ext3 noatime,nodiratime,data=writeback 0 1

使用O_DIRECT选项会自动禁用预读。例如，InnoDB有自己的预读策略

9.12选择文件调度策略

Deadline对数据库环境(ORACLE RAC,MYSQL等)是最好的选择：浅谈linux性能调优之六:IO调度算法的选择

可按如下命令查询文件调度策略

cat /sys/block/vda/queue/scheduler

输出（其中方括号表示当前使用的调度策略）： noop anticipatory deadline [cfq]

cfg: 完全公平队列（默认选项），这个用在个人电脑是最好的，但用在服务器则是有问题的
noop: 适合那么自己在后台实现了高度算法的设备。如RAID和SAN
deadline: 则对RAID和直接使用磁盘更好。我们的基准测试表明这两者差别很小（noop），重要的是别使用cfq

9.14内存交换区

用vmstat查看，我们喜欢si和so列的值为0，并且要保证它们低于每秒10个块
对交换空间利用率要做的监控和报警

以下设置告诉内核，除非虚拟内存满了，否则不要使用交换区: cat /proc/sys/vm/swappiness; echo 0 > /proc/sys/vm/swappines
另外一个选项是修改存储引擎怎么读取和写入数据: 使用innodb_flush_method=O_DIRECT ，减轻I/O压力; Direct I/O并不缓存，因此操作系统并不能把MySQL视为增加文件缓存的原因。
另一个选择是使用MySQL的memlock配置项: 可以把MySQL锁定在内存。这可以避免交换，但是也可能带来危险：如果没有足够的可锁定内存，MySQL在分配更多的内存时会崩溃。这也可能导致锁定的内存太多而没有足够的内存留给操作系统

9.15操作系统状态

安装iostat: yum install sysstat

9.15.1如何阅读vmstat的输出

以下是第5秒打印的一个报告：

结果

procs: r这一列表示多少进程正在等待CPU; b列显示多少进程正在不可中断的休眠（通常意味着正在等待I/O，如磁盘、网络、用户输入，等等）
memory: swiped列显示多少块被换出到了磁盘（页面交换）。剩下三列表示了多少块是空闲的（未被使用）、多少块正在被用作缓冲，以及多少正在用作操作系统的缓存
swap: 显示了每秒有多少块正在被换入（si 从磁盘）和换出（so 到磁盘）; 大部分时间我们喜欢看到si和so列的值为0，并且要保证它们低于每秒10个块。突发性的高峰一样很糟糕
io: 显示有多少块设备的读取（bi）和写入（bo）。这通常反映了硬盘I/O
system: 这些列显示了很秒中断（in）和上下文切换（cs）的数量
cpu: 显示了各类操作的百分比。包括执行用户代码（非内核）、执行系统代码（内核）、空闲、以及等待I/O

9.15.1如何阅读iostat的输出

以下是5秒一输出：

结果

rrq/s和wrq/s: 每秒合并的读和写的请求。“合并的”意味着操作系统从队列中拿出多个逻辑请求合并为一个请求到实际磁盘
r/s和w/s: 每秒发送到设备的读和写的请求
rsec/s和wsec/s: 每秒读和写的扇区数。
avgrq-sz: 请求的扇区数
avgqu-sz: 在设备队列中等待的请求数
await，r_await, w_await: 磁盘上排队花费的毫秒数
svctm: 服务请求花费的毫秒数，不包括排队时间
%util: 至少有一个活跃请求所占时间的百分比

可以利用以上输出服务器的并发数: concurrency = ( r/s + w/s ) * ( svctm/1000 )

其它工具：

mpstatl: 观察CPU统计
pt-diskstats: 增强了hostat

第十章复制

10.2配置复制

mysql主从复制-CHANGE MASTER TO 语法详解

主主及主从示例：

1.两主互为主从
（1）在主1上执行
grant replication slave on *.* to slave@'10.161.0.168' identified by 'lemon123';
（2）在主2上执行
CHANGE MASTER TO MASTER_HOST='10.161.0.145',MASTER_USER='slave',MASTER_PASSWORD='lemon123',MASTER_LOG_FILE='mysql-bin.000020',MASTER_LOG_POS=107;
start slave;
（3）在主2上执行
grant replication slave on *.* to slave@'10.161.0.145' identified by 'lemon123';
（4）在主1上执行
CHANGE MASTER TO MASTER_HOST='10.161.0.168',MASTER_USER='slave',MASTER_PASSWORD='lemon123',MASTER_LOG_FILE='mysql-bin.000021',MASTER_LOG_POS=107;

2.主从复制
（1）在主1上执行
grant replication slave on *.* to slave@'10.161.0.164' identified by 'lemon123';
grant replication slave on *.* to slave@'10.161.0.165' identified by 'lemon123';
grant replication slave on *.* to slave@'10.161.0.169' identified by 'lemon123';

（2）在三个从库上分别执行
CHANGE MASTER TO MASTER_HOST='10.129.150.89',MASTER_USER='slave',MASTER_PASSWORD='lemon123',MASTER_LOG_FILE='mysql-bin.000020',MASTER_LOG_POS=261;

10.2.1创建复制账号

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*  TO repl@'192.168.0.%' IDENTIFIED BY 'YourPassword';

10.2.2配置主库和备库

在主库的my.cnf文件中增加或修改如下内容：

log_bin = mysql-bin 
server_id = 10

不要在my.cnf中设置master_port或master_host这些选项，它们已被废弃，只会导致问题没有好处重启后检查主库状态：

mysql> SHOW MASTER STATUS; 
+------------------+----------+--------------+------------------+
 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+ 
| mysql-bin.000001 | 98 | | | 
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec)

在备库的my.cnf文件中增加或修改如下内容（事实上只有sever_id是必须的）：

log_bin = mysql-bin
server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates  = 1
read_only= 1

其中

relay_log: 允许备库将其重放的事件也记录到自身的二进制日志中
read_only: 如果可能的话最好使用read_only选项，该选项会阻止任何没有特权权限的线程修改数据。但不是很实用，尤其是那些需要在备库建表的应用

10.2.3启动复制

连到主：

CHANGE MASTER TO MASTER_HOST='server1', 
 MASTER_USER='repl',
 MASTER_PASSWORD='p4ssword',
 MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS=0;

启动：

START SLAVE;

查看状态：SHOW SLAVE STATUS\G;

结果

查看复制线程列表：SHOW PROCESSLIST\G

查看线程列表

10.2.5推荐的复制配置

如果用的InnoDB强烈推荐如下配置（这些都是默认配置）：

innodb_flush_logs_at_trx_commit=1	# Flush every log write
innodb_support_xa=1	# MySQL 5.0 and newer only
innodb_safe_binlog	# MySQL 4.1 only, roughly equivalent to innodb_support_xa

主库(一定要指定日志文件的绝对路径)：

log_bin=/var/lib/mysql/mysql-bin # Good; specifies a path and base name

#log_bin # Bad; base name will be server’s hostname

备库(一定要指定中继日志的绝对路径)：

relay_log=/path/to/logs/relay-bin

skip_slave_start

read_only #除了复制线程和其它超级线程之外不能修改

如果正在使用MySQL5.5并且不介意额外的fync()导致的性能开销，最好设置如下选项：

sync_master_info = 1

sync_relay_log = 1

sync_relay_log_info = 1

10.4复制拓扑

记住下面的基本原则：

一个MySQL备库只能有一个主库
每个备库必须有一个唯一的服务器ID
一个主库可以有多个备库
如果打开了log_slave_updates选项，一个备库可以把其主库上的数据变化传播到其他备库

10.4.8定制的复制方案

创建日志服务器: 日志服务器不需要任何数据，它的目的仅仅是将数据提供给别的数据服务器; 它更加容易重放且/或者过滤二进制日志事件，它对崩溃后重启恢复很有帮助。同时对基于时间点的恢复也很有帮助，在第15章我们会讨论。; 可以从mysqlsandbox 上获得一个沙箱脚本来创建日志服务器。

示例，假设有 somelog-bin.000001, somelog-bin.000002 两个日志文件。把它放在MySQL日志文件夹中，如/var/log/mysql，然后编辑my.cnf：

log_bin = /var/log/mysql/somelog-bin

log_bin_index = /var/log/mysql/somelog-bin.index

我们再来创建日志索引文件（然后启动日志服务器并通过 SHOW MASTER LOGS 来确保其找到日志文件）：

# /bin/ls −1 /var/log/mysql/somelog-bin.[0-9]* > /var/log/mysql/somelog-bin.index

10.5复制和容量规划

10.5.2备库什么时候开始延迟

监控方式: 如果有复制延迟的曲线图则要观察图上的一些短暂的延迟骤升
测试法: 如果将备库停止一个小时，然后开启并在1个小时内赶上，说明只消耗了一半的容量。也就是说备库在一个小时内赶上了两个小时内所有的变更

10.6复制的管理和维护

10.6.1监控复制

在主库上可以使用 SHOW MASTER LOGS 命令来查看当前主库的二进制日志位置和配置（该命令用于给PURGE MASTER LOGS命令决定使用哪些参数）

SHOW MASTER LOGS

此外还可以通过SHOW BINLOG EVENTS来查看复制事件：

SHOW BINLOG EVENTS IN 'mysql-bin.000223' FROM 13634\G

10.6.2测量备库延迟

Seconds_behind_master: SHOW SLAVE STA TUS中的Seconds_behind_master 可以显示延迟，但不准确。
heartbeat record: 最好的方式是使用heartbeat record; 这是一个在主库上每秒执行一次的时间戳。延时 = 备库当前时间戳 - 心跳时间翟; pt-heartbeat 脚本是复制心跳最流行的一种实现

10.6.3确定主备是否一致

可能使用工具：pt-table-checksum
利用pt-table-checksum做主从一致性校验

通常情况下可以在主库上运行该工具：

$ pt-table-checksum --replicate=test.checksum <master_host>

该命令会检查所有的表，并将结果插入到test.checksum 中。当查询在备库执行完就可以简单的比较主备之间的不同了。pt-table-checksum能够发现服务器所有的备库，在每台备库上运行查询，并自动输出结果

10.6.4从主库重新同步备库

最简单的办法是mysqldump转储受影响的数据并重新导入
但问题多多尤其在一个繁忙的数据库里；或从一百万行找出一千行的不同，是非常浪费资源的

pt-table-sync: 可以解决该问题。该工具能高效地查找及解决服务器间的不同; 它是结合pt-table-checksum生成的checksum表来工作的，所以只能同步那些已知的不同步的表的数据块

10.6.5改变主库

整个过程最困难的是获取新主库上合适的二进制日志的位置，这样才可以从和老主库相同的逻辑位置开始复制

计划内提升

RESET MASTER 和RESET SLAVE

计划外提升

确定期望的日志位置

如果有备库和新主库的位置不相同，则需要找到该备库最后一条执行的事件在新主库的二进制日志中相应的位置，然后执行CHANGE MASTER TO 。可以通过mysqlbinlog工具来找到备库执行的最后一条查询，然后在主库找到同样一条查询，进行简单的计算即可得到。示例命令如下（不需要用户名密码）：

 mysqlbinlog   mysql-bin.000011 | tail -n 30

mysqlbinlog

例子——提升server2为新主库：
假设server1是server2和server3的主库，其中server1已经崩溃。根据SHOW SLAVE STATUS获得Master_Log_File/Read_Log_Pos的值，server2已经执行完server1的所有二进制日志，但server3还不是最新的数据。如下图所示：

当server1崩溃，server2已追赶上，但server3的复制落后

根据以上结果，server2已经跟执行完主库上所有二进制日志，但server3还不是最新数据
应该在server3的CHANGE MASTER TO语句赋予一个什么样的参数呢？server3在1493停止，比server2的1582要小89字节。server2正在向偏移量为8167的二进制日志写入，8167-89=8078，因此理论上我们应将server3指向server2的日志偏移量为8087的位置。最好去确认下这个位置附近的日志事件（使用mysqlbinlog）
假设我们观察到事件是一样的，下面这条语句可以完成从server3到server2的切换：CHANGE MASTER TO MASTER_HOST="server2", MASTER_LOG_FILE="mysql-bin.000009", MASTER_LOG_POS=8078;

10.6.6在一个主主配置中交换角色

master_pos_wait函数与MySQL主从切换

10.7复制的问题和解决方案

10.7.1数据损坏或丢失的错误

主库意外关闭: 如果没有设置sync_binlog，当主库故障重启后可能在二进制日志里丢失一些事务。这时解决方案是从备库的下一个二进制日志的开头读日志。但一些事件会永久性的丢失。; 建议使用pt-table-checksum工具检查主备一致性，以便于修复。可以通过开启sync_binlog来避免事件丢失
备库意外关闭: 可能会导致同步信息没来得及写入master.info，备库可能桧重新尝试一些二进制日志事件，可能导致唯一索引错误。唯一的办法就是忽略这些错误; 可以用pt-slave-restart来帮助完成这一点

主库上的二进制文件损坏: 除了忽略，别无选择。可以在主库上执行 FLUSH LOGS 命令，这样主库会开始一个新的日志文件，然后将备库指向该文件开始的地方。

最后修改于2016年9月12日 (星期一) 03:24

高性能MySQL笔记（下）