MySQL服务器的linux性能优化和扩展技巧

作者：Yoshinori Matsunbu

作者现在是DeNA公司的数据库和基础设施架构师。之前在SUN公司工作。他也是HandlerSocket的作者。这个是MySQL的NoSQL插件。

本文是根据他的PPT整理而成的，如有不正确敬请指教。

本文有可以直接点击下载：linux性能优化技巧

本文主要的内容有如下：

1. 内存和SWAP空间管理
2. 同步I/O，文件系统和I/O调度
3. 有用的命令和工具：iostat, mpstat, oprofile, SystemTap, gdb

第一部分：内存和SWAP空间管理

内存也就是随机访问内存

内存是最重要的硬件部件对于RDBMS(relation database management system)。

内存的访问速度远远超过HDD（普通硬盘）/SSD（固态硬盘）

内存：60ns， 但是还没达到每秒10W

HDD：5ms

SSD：100-500us

他们之间的关系为：

1s = 1000ms

1ms = 1000us

1us = 1000ns

所以16GB-64GB对于现在是非常合适的。（好像之前在人人的时候都是72G）

热点应用的数据都需要缓存在内存中

当然最小化热点数据大小也是很重要的，主要有以下几种措施：

使用紧凑长度的数据类型(SMALLINT来替代VARCHAR/BIGINT, TIMESTAMP来替代DATETIME等等)

不要创建无用的索引

删除不必要的数据或者将这些数据移到存档表中，来保证热点的表尽量的小

下面这个测试就是针对不同内存大小服务器的一个测试，测试数据在20-25GB（200个数据仓库，运行一小时），使用的是DBT-2测试，这是一 种密集写的测试，服务器的配置为Nehalem 2.93 * 8 cores, MySQL 5.5.2, 4 RAID 1+0 HDDs

从上面这个表格中我们可以很明显看到巨大的差异当数据全部缓存到内存中。

内存大小会影响所有操作，不管是SELECT，还是INSERT/UPDATE/DELETE操作。

INSERT：当往一个随机排序的索引中插入数据的时候会造成随机的读/写

<!-- p { margin-bottom: 0.21cm; } -->UPDATE/DELETE： 当更改数据的时候会导致磁盘的读/写

还有一个提高性能的方法是使用直接I/O（Direct I/O）

从上图中我们可以看到Direct I/O就是直接跳过了文件系统的cache。

Direct I/O对于完全利用内存是非常重要的。我们可以通过设置innodb_flush_method=O_DIRECT来运行。

注：文件I/O必须是512byte为一个单位，同时O_DIRECT不能用在InnoDB日志文件，二进制日志文件，MyISAM引擎，PostgreSQL数据文件等等。

不要分配太多的内存

这个其实只要分配到足够其它应用程序使用，而不要最后导致系统没有内存可用。

上图中我们可以看到总共系统32G内存，而Mysqld已经使用了30G，而系统居然还只有150M可用，这样是非常危险。

当系统没有内存可用时会发生什么事情呢？

减少文件系统缓存来分配内存空间，这个文件系统缓存就是上图中cached部分

替换掉一些进行来分配内存空间。也就是将一些内存空间移动到SWAP

SWAP是坏的

进程空间会写入到磁盘上（swap out），而这些进程空间本应该是写入到内存中的。

当访问磁盘上的进程空间会导致磁盘读写（swap in）

同时会产生巨量的随机磁盘读写

那也许有些人会想到把swap大小设置为0，但是这样其实是非常危险的。

因为当内存和SWAP都不可用的时候的，OOM Killer（out of memory）就会被启用。OOM Killer会杀掉任何进程来分配内存空间。

最耗费内存的进程会被最先杀掉，在mysql服务器上这个一般是mysqld进程

mysqld会被中止关闭，而在重启时候会进行崩溃修复。

OOM Killer的策略是根据/proc/<pid>/oom_score来进行倒序排列，也就是oom_score最大的会被第一个干掉

通常mysqld会拥有最高的值，因为oom_score是根据内存大小，CPU时间，运行时间来判断。

OOM Killer杀死进程会花费很长的时间，而这期间我们不能干任何事情。

所以不要设置swap为0

上图中我们看到swap被设置为了0，而一旦没有内存可用OOM Killer就会被启用。

一些CPU核心会耗尽100%的系统资源。在上图中我们就看到的就是一个CPU核使用100%的CPU资源。而这个时候连接终端(SSH)就会断掉。

所以swap是不好的，但是OOM Killer更不好。

如果/proc/<PID>/oom_adj被设置为-17，OOM Killer就不会杀掉这个进程。所以给SSHD进程设置为-17是一个有效防止断线的方法。

echo -17 > /proc/<PID>/oom_adj

但是不要给mysqld设置为-17，因为如果最耗内存的进程没被杀死，linux依然没有任何可用的内存。而我们就会在很长很长很长的时间内没法干任何事情。

因此，对于一个生产环境的系统SWAP是必须的。但是我们同样不希望Mysql进行swap out。

我们就需要知道mysql中哪些东西耗费内存

RDBMS：主要的进程空间是被使用的（innodb_buffer_pool, key_buffer, sort_buffer等等），有时候文件系统的cache也会被使用(MyISAM引擎的文件等等)

管理操作：（备份等等），这个时候主要是文件系统cache会被使用

我们要让mysql在内存中，也不要分配大量的文件系统cache。

要特别注意备份操作

因为在备份的时候往往会拷贝大文件，而拷贝大文件就会使用到swap

这个时候我们可以设置/etc/sysctl.conf中vm.swappiness=o来避免这个，而默认值是60

我们看看下图就知道前后的区别了

<