MySQL在高内存、IO利用率上的几个优化点

以下优化都是基于CentOS系统下的一些MySQL优化整理，有不全或有争议的地方望继续补充完善。

一、mysql层面优化

1. innodb_flush_log_at_trx_commit 设置为2

设置0是事务log（ib_logfile0、ib_logfile1）每秒写入到log buffer,1是时时写，2是先写文件系统的缓存，每秒再刷进磁盘，和0的区别是选2即使mysql崩溃也不会丢数据。

2. innodb_write_io_threads=16(该参数需要在配置文件中添加，重启mysql实例起效)

脏页写的线程数，加大该参数可以提升写入性能.mysql5.5以上才有。

3. innodb_max_dirty_pages_pct 最大脏页百分数

当系统中 脏页 所占百分比超过这个值，INNODB就会进行写操作以把页中的已更新数据写入到磁盘文件中。默认75，一般现在流行的SSD硬盘很难达到这个比例。可依据实际情况在75-80之间调节

4. innodb_io_capacity=5000

从缓冲区刷新脏页时，一次刷新脏页的数量。根据磁盘IOPS的能力一般建议设置如下：

SAS 200
SSD 5000
PCI-E 10000-50000

5. innodb_flush_method=O_DIRECT(该参数需要重启mysql实例起效)

控制innodb数据文件和redo log的打开、刷写模式。有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。

• fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。
• O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成。
• O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲。

三种模式如下图： # 通过图可以看出O_DIRECT相比fdatasync的优点是避免了双缓冲，本身innodb buffer pool就是一个缓冲区，不需要再写入到系统的buffer，但是有个缺点是由于是直接写入到磁盘，所以相比fdatasync的顺序读写的效率要低些。
在大量随机写的环境中O_DIRECT要比fdatasync效率更高些，顺序写多的话，还是默认的fdatasync更高效。

6. innodb_adaptive_flushing  设置为 ON （使刷新脏页更智能）

影响每秒刷新脏页的数目。规则由原来的“大于innodb_max_dirty_pages_pct时刷新100个脏页到磁盘”变为 “通过buf_flush_get_desired_flush_reate函数判断重做日志产生速度确定需要刷新脏页的最合适数目”；即使脏页比例小于 innodb_max_dirty_pages_pct时也会刷新一定量的脏页。

7. innodb_adaptive_flushing_method  设置为 keep_average  

影响checkpoint，更平均的计算调整刷脏页的速度，进行必要的flush.（该变量为mysql衍生版本Percona Server下的一个变量，原生mysql不存在）

8. innodb_stats_on_metadata=OFF   

关掉一些访问information_schema库下表而产生的索引统计。

当重启mysql实例后，mysql会随机的io取数据遍历所有的表来取样来统计数据，这个实际使用中用的不多，建议关闭.

9. innodb_change_buffering=all  

当更新/插入的非聚集索引的数据所对应的页不在内存中时（对非聚集索引的更新操作通常会带来随机IO），会将其放到一个insert buffer中，当随后页面被读到内存中时，会将这些变化的记录merge到页中。当服务器比较空闲时，后台线程也会做merge操作。

由于主要用到merge的优势来降低io，但对于一些场景并不会对固定的数据进行多次修改，此处则并不需要把更新/插入操作开启change_buffering，如果开启只是多余占用了buffer_pool的空间和处理能力。这个参数要依据实际业务环境来配置。

10. innodb_old_blocks_time=1000

使Block在old sublist中停留时间长为1s，不会被转移到new sublist中,避免了Buffer Pool被污染BP可以被认为是一条长链表。被分成young 和 old两个部分，其中old默认占37%的大小（由innodb_old_blocks_pct 配置）。靠近顶端的Page表示最近被访问。靠近尾端的Page表示长时间未被访问。而这两个部分的交汇处成为midpoint。每当有新的Page需要加载到BP时，该page都会被插入到midpoint的位置，并声明为old-page。当old部分的page，被访问到时，该page会被提升到链表的顶端，标识为young。

由于table scan的操作是先load page，然后立即触发一次访问。所以当innodb_old_blocks_time =0 时，会导致table scan所需要的page不读的作为young page被添加到链表顶端。而一些使用较为不频繁的page就会被挤出BP，使得之后的SQL会产生磁盘IO，从而导致响应速度变慢。

这时虽然mysqldump访问的page会不断加载在LRU顶端，但是高频度的热点数据访问会以更快的速度把page再次抢占到LRU顶端。从而导致mysqldump加载入的page会被迅速刷下，并立即被evict（淘汰）。因此，time=0或1000对这种压力环境下的访问不会造成很大影响，因为dump的数据根本抢占不过热点数据。不只dump，当大数据操作的时候也是如此。

二、mysql系统层面优化

1.关闭 numa=off,或修改策略为interleave(交织分配内存)防止意外的swap  

numa策略引入了node的概念，每个物理CPU都被视为一个node，而每个node都有一个local memory，相对这个node之外的其它node都属于外部访问。

NUMA的内存分配策略有localalloc（默认）、preferred、membind、interleave。

• localalloc规定进程从当前node上请求分配内存；
• preferred比较宽松地指定了一个推荐的node来获取内存，如果被推荐的node上没有足够内存，进程可以尝试别的node。
• membind可以指定若干个node，进程只能从这些指定的node上请求分配内存。
• interleave规定进程从指定的若干个node上以Round-roll算法交织地请求分配内存。

每个进程（或线程）都会分配一个优先node,对于系统默认的localalloc策略会有一个问题，对于mysql这种几乎占满整个系统内存的应用来说，很容就把某个node的资源给占满，若Linux又把一个大的资源分配到这个已经占满资源的node时，会资源不足，造成内存数据于磁盘进行交换，或者摒弃buffer_pool里的活跃数据。在实际测试中发现比如有node0、node1 两个物理node，当系统负载很高的时候，node0资源被占满，node1虽然仍有部分空闲内存，但是系统即使进行内存到磁盘交换也不会去利用node1上的空闲资源。

因此建议对于像mysql这样的单实例的庞大复杂的进程来说，关闭numa或者设置策略为交织分配内存更合理。但对于一个机器上有多少个实例，可以每个实例绑定一个CPU核上。然后就可以充分利用numa的特性，更高效。

2.增加本地端口，以应对大量连接

echo ‘1024 65000′ > /proc/sys/net/ipv4/ip_local_port_range

该参数指定端口的分配范围，该端口是向外访问的限制。mysql默认监听的3306端口即使有多个请求链接，也不会有影响。但是由于mysql是属于高内存、高cpu、高io应用，不建议把多少应用于mysql混搭在同一台机器上。即使业务量不大，也可以通过降低单台机器的配置，多台机器共存来实现更好。

3.增加队列的链接数

echo ‘1048576’ > /proc/sys/net/ipv4/tcp_max_syn_backlog

建立链接的队列的数越大越好，但是从另一个角度想，实际环境中应该使用连接池更合适，避免重复建立链接造成的性能消耗。使用连接池，链接数会从应用层面更可控些。

4.设置链接超时时间

echo ’10’ > /proc/sys/net/ipv4/tcp_fin_timeout

该参数主要为了降低TIME_WAIT占用的资源时长。尤其针对http短链接的服务端或者mysql不采用连接池效果比较明显。

三、其它层面优化的考虑

对于高DAU的业务mysql实例来说，建议不要吝惜内存，使用128G或更高内存，innodb很好的利用了内存的优势来提高mysql的性能，我们就要给予他足够的空间来发挥他的性能。磁盘IO性能远不及内存的处理速度，这个无可厚非，所做的优化尽量的把需求IO的操作阻拦到内存直接返回给客户端。

当然内存再高也不能把所有的数据都缓存到内存中，在实际的大部分业务中还是依赖随机IO居多，更如现今比较火的手游，更是高随机写入的业务类型，各个云厂商也都默认提供SSD甚至需求更高的PCIe Flash存储设备。通过提高磁盘的IO性能也是其次的选择。

另业务如果足够大，单台机器必然无法支撑，就要考虑分库，分表，大部分产品公司所做的还是按业务划分库。如果一项业务也大到单台机器无法承受，那就需要分表和分库来操作了。其实不管业务是否会做大，当开始建立工程的时候都应该要做到支持可分库、可分表（尽量避免使用表的自增ID作为业务ID使用）、可分业务(尽量避免事务操作，甚至允许在业务上有些可接受的牺牲，否则后期很难划分业务)，否则只有面临重构的尴尬场面。这样做的好处是即使有些错误的设计预想不周，由于业务的各种原因无时间重构，也可以通过扩展、迁移的方式来降低单点造成的影响程度，然后后期再慢慢优化。

有一点一定要注意，“杀手级”sql语句的存在会让以上所有优化全部作废。比如上千万表数据的无索引搜索、排序、计算。所以必须开启慢查询日志排查所有慢查询语句。

以上为小弟的一些针对mysql实际使用的一些整理，有不足的地方还望提出补充，慢慢完善。

--------------------------------------分割线 --------------------------------------

Ubuntu 14.04下安装MySQL 2014-05/102366.htm

《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF 2014-03/98821.htm

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL 2014-05/102351.htm

Ubuntu 14.04下搭建MySQL主从服务器 2014-05/101599.htm

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群 2013-11/93019.htm

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb 2013-08/89270.htm

MySQL-5.5.38通用二进制安装 2014-07/104509.htm

--------------------------------------分割线 --------------------------------------