相关文章：高性能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服务器内核配置

如果需要更大带宽，可以使用网络链路聚合（NICs）（或叫多块网卡并行绑定）

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复制的问题和解决方案

mysql开启二进制日志记录文件简介： MySQL的二进制日志

10.7.1数据损坏或丢失的错误

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

主库上的二进制文件损坏: 除了忽略，别无选择。可以在主库上执行 FLUSH LOGS 命令，这样主库会开始一个新的日志文件，然后将备库指向该文件开始的地方。; 也可以尝试去发现损坏区域的结束位置。某些情况下可以通过 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 来忽略一个损坏的事件。如果有多个损坏的事件，就需要重复该事件。但如果有太多损坏的事件，损坏的事件头会阻止服务器去找到下一个事件。这种情况下，可能不得不手动找到下一个完好的事件
备库上的二进制文件损坏: 可以重新CHANGE MASTER TO

数据改变，但事件仍是有效SQL: 不幸的是，MySQL甚至不能察觉这种损坏。
数据改变且事件是无效SQL: 可以通过mysqlbinlog提取事件，并看到一些错乱的数据：UPDATE tbl SET col????????????????? 可以通过偏移量的方式找到下一个事件

数据遗漏并且/或者事件的长度是错误的

某些事件已经损坏或被覆盖，或者偏移量已经改变并且下一个事件的起始偏移量也是错误的

以上这两种情况 mysqlbinlog 已经起不了多少作用。

通过一些简单的分析后可用 mysqlbinlog --start-position来跨过损坏的事件。或者使用CHANGE MASTER TO 的 MASTER_LOG_POS参数。以下是找到偏移量的两个例子：

例1：$ mysqlbinlog mysql-bin.000113 | egrep '^# at '

例2：strings -n 2 -t d mysql-bin.000113

然后找到'G结尾的行，它们是固定长度事件头的一部分。(linux命令strings)

10.7.10丢失的临时表

尽量不用（在备库上使用的影响会小一点）
另一个办法是使用伪临时表：在一个temp库上创建，如表top_users_1234，其中1234是函数CONNECTION_ID()的返回值。可能通过清理线程将其移除——通过SHOW PROCESSLIST 来获得活跃的连接列表，并将其与表名中的连接ID比较

10.7.12InnoDB锁引起的锁争用

替换INSERT...SELECT: 在主库上先执行 SELECT INTO OUTFILE 再执行 LOAD DATA INFILE。这种方法更快，而且不需要加锁

以下是加锁示意图：

两个事务更新数据，使用共享锁串行化更新

10.7.13在主—主复制结构中写入两台数据库

可以使用auto_increment_increment 和 auto_increment_offset 。但自增问题只是所有问题的一小部分

10.7.14过大的复制延迟

log_slow_slave_statements: 在备库打开该选项，在标准的MySQL慢查询日志能够记录MySQL5.1及更新版本中执行的语句，这样就可以找到哪些语句执行的慢了。最好配合pt-query-digest使用

除了购买更快的磁盘和CPU，备库没有太多的优化空间

一个简单的办法是设置 innodb_flush_log_at_trx_commit = 2 来实现，这样事务会提交的快些（见8.5.1节）
还可以在备库上禁止二进制日志记录；
设置 innodb_locks_unsafe_for_binlog = 1
设置MyISAM的 delay_key_write = ALL

但这些都是牺牲安全为代价的，如果要把备库提升为主库要记得把这些选项设置回安全值。

不要重复写操作中代价较高的部分

LOAD DATA INFILE

可能先从备库执行，再回传到主库,防止在每个备库上重放。下面例子有效避免了在主库及其它备库上执行昂贵的GROUP BY操作：

先在备库上执行（summary_db是备库的特别保留的数据库）：

REPLACE INTO summary_db.summary_table (col1, col2, ...) -> SELECT col1, sum(col2, ...) FROM main_db.enormous_table GROUP BY col1;

现在就可以执行SELECT INTO OUTFILE，然后再执行LOAD DATA INFILE，将结果加载到主库中。

在复制之外并行写入

一个很好的例子：之前讨论的归档，可以禁止归档查询写到二进制日志中去
另一个例子，根据网上总结（非教材内容，需要在主备所有机器都执行，容易不一致）Mysql中load data infile主从复制注意点。

在主库、备库上执行如下语句：

time mysql -e "set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile 'infile.txt' into table load_test fields terminated by '\t' lines terminated by '\n' (col1, col2);"

注意： time命令是用于统计执行时间的。set sql_log_bin=0 等的设置仅针对本次连接有效。

为复制线程预取缓存

10.7.15来自主库过大的包

max_ allowed_packet: 如果该值在主备不匹配。主库可能会记录一个备库认为过大的包。当备库获取二进制日志时可能会碰到各种各样的问题，包括无限的报错和重试、或者中继日志损坏

10.7.16受限的复制还宽

可以开启 slave_compressed _protocol 选项。可减少三分之一的带宽

10.8复制有多快

时间插件NOW_USEC P290、P493

10.9MySQL复制的高级特性

半同步: 在某些场景下半同步能提高性能，在主库关闭sync_binlog的情况下保证更加安全。写入远程的内存（一台备库的反馈）比写入本地的磁盘（写入并刷新）要更快。有些性能测试表明性能提高了两倍！
多线程复制: MySQL5.6已提供

十一章可扩展的MySQL

10.1什么是可扩展性

可扩展性表明了，当需要增加资源以执行更多工作时系统能获得划算的同等提升。以下是可扩展性定律的三种模型：

线性扩展、Amdahl扩展及USL扩展定律

10.2扩展MySQL

10.2.4向外扩展

3.选择分区键

两个数据模型，一个易于分片，另一个则难以分片

4.多个分区键——一个例子：: 一个社交网站下的读书俱乐部站点，该站点可以显示任何书籍的任何评论，也能显示用户读过或评论过的所有书籍。假设用户数据和书籍数据都进行了分片设计。而评论同时拥有用户ID和评论ID，这样就跨越了两个分片的边界。实际上无须冗余存储两份评论数据，替代方案是：

将评论和用户数据一起存储，然后把每个评论的标题和ID与书籍数据存储在一起。这样在渲染大多数关于某本书的评论的视图时无须同时访问用户和书籍数据存储，如果需要显示完整的评论内容，可以从用户数据存储中获得。

5.跨分片查询

可以使用数据队列服务（如Gearman）来聚合查询，再用PHP等应用使用队列服务的聚合结果。

跨分片查询也可以借助汇总表来执行。可以遍历所有分片来生成汇总表并将结果在每个分片上冗余存储。也可以把汇总表放到另外一个数据存储中，这样就只需要存储一份了。

6.分配数据、分片和节点

应该让分片比节点节点容量小很多。可以假设100G的表，则可以1G/分片。

简单说，“易于管理的大小”是指保持表足够小，以便能在5或10分钟内提供日常维护操作，例如 ALTER TABLE、CHECK TABLE 或者 OPTIMIZE TABLE操作

7.在节点上部署分片

以下是一些常用的用法

每个分片使用单一数据库，并且数据库名要相同。典型的应用场景是需要每个分片都能镜像到原应用的结构。这在部署多个应用实例，并且每个实例对应一个分片时很有用。
将多个分片的表放到一个数据库中，在每个表名上包含分片号（如 bookclub.comments_23）这种配置下，单个数据库可以支持多个分片
为每个分片使用一个数据库，并在数据库中包含所有应用需要的表。在数据库名中包含分片号（如bookclub_23.comments, bookclub_23.users ）。
库和表都加分片号如 bookclub_23.comments_23
每个节点上多个MySQL实例，每个实例上有一个或多个分片。可以使用以上任意组合方式分片

如果为已有应用分片，结果往往是一个节点对应一个分片

8.静态分配

如：SELECT CRC32(111) % 100;

缺点：分片很大且数量不多时，很难平衡负载；无法处理热点数据；修改分片策略困难

9.动态分配

14.分片工具

JAVA: Hibernate Shards (http://shards.hibernate.org) 、 HiveDB (http://www.hivedb.org)
PHP

Shard-Query (http://code.google.com/p/shard-query/)
ScaleBase (http://www.scalebase.com)
ScalArc (http://www.scalarc.com)
dbShards (http://www.dbshards.com).

10.2.6通过集群扩展

1.MySQL Cluster

当使用MySQL来控制NDB时，行的主键就是键，其他的列是值。

需要高速连接设备+大量内存（服务器间需要大量内存）

缺点：

复杂查询现在支持的不是很好，如那些关联和聚合的查询
它是一个事务型的数据库，但不支持MVCC，所以需要读锁，也不做任何死锁检测

2.Clustrix: 它能直接替代MySQL。与MySQL Cluster很像；关键不同点是，Clustrix是完全的分布式执行并且缺少顶层的代理，它能很好的理解MySQL协议，无需MySQL进行协议转换。; 实验表明，Clustrix能提供高性能和扩展性
3.ScaleBase: 是一个软件代理。相似的还有 ScaleArc、dbShards

10.2.7内向扩展

高性能MySQL笔记（下）

目录