查看“高性能MySQL笔记（下）”的源代码

[[category:MySQL]]
相关文章：[[高性能MySQL笔记（上）]]<br />

淘宝的MySQL博客[http://mysql.taobao.org/monthly/ 月报] [http://mysql.taobao.org 首页]
=第九章操作系统和硬件优化=
==9.1什么限制了MySQL的性能==
最常见的两个瓶颈是CPU和I/O<br />
==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、提高并发操作<br />
'''写放大'''及'''垃圾收集''' 对性能影响较大（尤其是空间快满了的时候）<br />
===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控制器。<br />
有时答案在内存/磁盘的比例，而不只是磁盘。
===9.4.7使用Flashcache===
Flashcache创建了一个块设备，并且可以被分区，也可以像其他块设备一样创建文件系统，特点是这个块设置是由闪存和磁盘共同支撑的。闪存设备用作读取和写入的告诉缓存。<br />
*尽管理论上Flashcache可能更高效，但最终性能并不如低层闪存那么好。
*从测试来看，尚不清楚对写负载有多大好处，但对读肯定有好处。于是它适合这样的情况：'''有大量的读I/O，并且工作集比内存大的多。'''
*除了实验，这里有个生产环境的实例经验：有个4T的数据库，这个数据库遇到了很大的复制延迟。我们给系统加了半个TB的Virident PCIe卡作为存储。然后安装了Flashcache，并且把PCIe作为绑定设备的闪存部分，复制速度就翻了一倍

;Flashcache的IOPS思想实验:磁盘IPOS: 3,5" 15.000 rpm FC <big>'''150 IOPS'''</big>（参考中列出的最好的一个）参考[http://elf8848.iteye.com/blog/1731301 常见硬盘IOPS参考值] [http://elf8848.iteye.com/blog/1731274 磁盘性能指标--IOPS 理论]
:假设闪存支持100000IPOS，未命中率为'''10%'''，则按磁盘150IPOS计算，则本Flashcache设备<big>'''仅可以支持 150/10% = 1500IPOS，受制于磁盘！'''</big>
===9.4.9优化固态存储上的MySQL===
关于插入缓冲：[http://www.cnblogs.com/zuoxingyu/p/3761461.html innodb insert buffer 插入缓冲区的理解][http://blog.csdn.net/dbanote/article/details/9077113 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中[http://mysql.taobao.org/monthly/2015/04/01/ MySQL · 引擎特性 · InnoDB undo log 漫游]）

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

==9.9网络配置==
;skip_name_resolve:<big>'''建议打开'''</big>，默认是关闭的 见8.9
:损坏或者变慢的的DNS解析对很多应用程序都是个问题。对MySQL尤其严重
:'''若启用，则用户账户必须在host列使用具有唯一性的IP地址，“localhost”或者IP地址通配符。'''
;back_log:默认50秒是不够的,<big>'''建议3000'''</big>
:控制MySQL传入的TCP连接队列的大小。 设置不够的症状是，在3秒超时规则下，客户会看到零星的“连接被拒绝”的错误。在繁忙的系统中这个选项通常应加大。把这个选项增加到数百甚至数千，似乎没有任何副作用。事实上你应该看的更远一些，可能还需要配置操作系统的TCP网络设置。<big>'''在GNU/Linux上需要增加somaxconn限制，并且需要检查tcp_max_syn_back_log设置'''</big>
;网络监控:[http://oss.oetiker.ch/mrtg/ MRTG] 其它的还有[http://oss.oeticker.ch/smokeping/ Smokeping] 、[http://www.cacti.net 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服务器内核调优实例：<big>'''[[高并发情况下Linux服务器内核配置]]'''</big>
==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等)是最好的选择：[http://www.2cto.com/os/201307/228389.html 浅谈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个块<br />
对交换空间利用率要做的监控和报警<br />
;以下设置告诉内核，除非虚拟内存满了，否则不要使用交换区:
: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<br />
==9.15.1如何阅读vmstat的输出==
以下是第5秒打印的一个报告：
[[文件:Vmstat5test.png|600px|缩略图|居中|结果]]
;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秒一输出：
[[文件:Iostat5test.png|600px|缩略图|居中|结果]]
;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 )
<hr/>
<big>其它工具：</big>
;mpstatl:观察CPU统计
;pt-diskstats:增强了hostat
=第十章复制=
==10.2配置复制==
主主及主从示例：
<pre >
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;
</pre>
===10.2.1创建复制账号===
 GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*  TO repl@'192.168.0.%' IDENTIFIED BY 'YourPassword';
===10.2.2配置主库和备库===
在<big>'''主库'''</big>的my.cnf文件中增加或修改如下内容：
 log_bin = mysql-bin 
 server_id = 10
不要在my.cnf中设置master_port或master_host这些选项，它们已被废弃，只会导致问题没有好处
重启后检查主库状态：
<pre>
mysql> SHOW MASTER STATUS; 
+------------------+----------+--------------+------------------+
 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+ 
| mysql-bin.000001 | 98 | | | 
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec)
</pre>


在<big>'''备库'''</big>的my.cnf文件中增加或修改如下内容（事实上只有sever_id是必须的）：
<pre>
log_bin = mysql-bin
server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates  = 1
read_only= 1
</pre>
其中
;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;'''
[[文件:ShowSlaveStatusTest.png|400px|缩略图|居中|结果]]

查看复制线程列表：'''SHOW PROCESSLIST\G'''
[[文件:ShowProcesslistReplication.png|600px|缩略图|居中|查看线程列表]]
===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章我们会讨论。
:可以从[http://mysqlsandbox.net 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个小时内赶上，说明只消耗了一半的容量。也就是说备库在一个小时内赶上了两个小时内所有的变更