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

发表于 2011-02-26 更新于 2024-04-10 分类于樱桃沟阅读次数：本文字数： 8k 阅读时长 ≈ 7 分钟

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

作者：Yoshinori Matsunbu

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

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

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

本文主要的内容有如下：

内存和SWAP空间管理
同步I/O，文件系统和I/O调度
有用的命令和工具：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：当往一个随机排序的索引中插入数据的时候会造成随机的读/写
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//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//oom_adj被设置为-17，OOM Killer就不会杀掉这个进程。
所以给SSHD进程设置为-17是一个有效防止断线的方法。

1	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 我们看看下图就知道前后的区别了

我们看到，同样是拷贝大文件，下面这个swap才之用了216K

这是因为当物理内存耗尽的时候，linux内核会减少文件系统cache作为最高优先级（低优先级就增加那个值）当文件系统cache也没有可用的时候，就会开始使用swap。

而这个OOM Killer也不会被启用，因为还有足够的swap空间呢。
这样才是安全的。

内存分配

mysqld使用malloc()/mmap()方法来进行内存分配如果要使用更快更多并发的内存就要用tcmalloc()这样的方法

安装Google Perftools(tcmalloc被包含在了里面)

1
2
3

yum install libunwind cd google-perftools-1.5 ; ./configure –enable-frame-pointers; make; make install 
export LD_PRELOAD=/usr/local/lib/tcmalloc_minimal.so
mysqld_safe &

而对于InnoDB来说它会使用它自己的内存分配器这个可以在InnoDB Plugin中进行更改

如果innodb_use_sys_malloc=1(默认为1)，InnoDB就会使用操作系统的内存分配器这样tcmalloc通过设置LD_PRELOAD就会被使用。

下面这个是对2种不同的内存分配器进行测试，从中可以看到在内存越大时候，这个差距也越明显。

平台还是Nehalem 2.93 * 8 cores, MySQL 5.5.2, 数据量也是20-25GB（200个仓库运行1个小时）

要个别注意分配给每个session的内存

不要分配超过需求过多的的内存大小（特别是针对每个session的内存）因为分配2MB内存比分配128KB内存会花更多的时间。

当分配内存小于等于512MBLinux malloc()方法内部会调用brk()方法，其它时候会调用mmap()。

在一些情况下，分配给每个session过多的内存回到反向的性能影响。

从上面我们可以很明显的看到差距。
在大部分情况都不要分配超过需要过多的内存，当然也有特别的场景(比如：MyISAM + LIMIT + FullScan)

第二部分：同步I/O，文件系统和I/O调度
文件i/O和同步写

RDBMS会经常调用fsync()方法（每一次事务提交，检查点等等）确认使用RAID卡上的电池备份写缓存（BBWC Battery Backed up Write Cache） 10000+次的fsync()每秒，而不用BBWC会减少200次左右。
这个都是在磁盘的情况下。

为了安全的原因需要关闭写缓存。

不要在文件系统中设置“写隔离”（在很多情况下默认都是打开的）就算使用了BBWC，写入到磁盘还是会非常慢。

这是因为一旦打开写隔离，那只有把所有数据写入到磁盘才会关闭隔离。

Ext3中通过mount -o barrier=0,在xfs中是mount -o nobarrier,而在drbd中是在drbd.conf文件中写入no-disk-barrier。

写隔离技术对于防止脏页是非常有作用的，但是在mysql服务器上我们可以关闭，因为都是内部通过事务来提交了。

对于其它应用的服务器我们要审慎对待。

复写还是追加写

一些文件是复写的（固定文件大小的），其它的是追加写的（增长的文件长度的）

复写：InnoDB日志文件
追加写：二进制日志文件
追加写+fsync()比复写+fsync()要慢的多，这是因为追加写每次都要分配文件需要的空间，同时元数据需要通过每个fsync()来刷新到磁盘上。

对于复写可以达到10000+每秒的fsync，而追加写只有3000次左右。

追加写的速度依赖于文件系统。

copy-on-write的文件系统如Solaris的ZFS就会对于追加写足够快，可以达到7000+次。

特别小心设置sync-binlog=1为二进制日志，设置为1的时候会每个事务写入一次就会自动同步硬盘一次。

这样效率会非常差这个时候可以考虑ZFS 检查“预分配二进制日志”的工作日志。
Http://forge.mysql.com/worklog/task.php?id=4925

不要太频繁的更新文件 innodb_autoextend_increment=20(默认为8)，

这个表示表空间文件每次扩展空间都到20M

快速文件I/O健康检测

启用BBWC，并且写隔离是关闭的。

复写+fsync()测试：运行mysqlslap插入（InnoDB，单线程， innodb_flush_log_at_trx_commit=1 log buffer每次事务提交都会写入log file，并且将数据刷新到磁盘中去）；检查的qps超过了1000.

具体使用方法可以参考http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html

缓冲区和异步写

一些文件I/O操作既不是使用Direct I/O，也不是使用同步写，如：文件复制，MyISAM, mysqldump, innodb_flush_log_at_trx_commit=2等等在文件系统缓存的脏页最终都要被刷新到磁盘上去。

pdflush用作刷新到磁盘上的，它最大可以8个线程同时进行。

这个是高度依赖于vm.dirty_background_ratio和vm.dirty_ratio这2个系统参数的。

当脏页数量达到 dirty_background_ratio（默认是10%，64GB内存的话就是当cache达到6.4GB）的时候就会开始刷新到磁盘上。

当达到dirty_ratio的时候就会强制进行刷新到磁盘，默认是40% 强制和粗鲁的脏页刷新是有问题的。

当大幅增加传输延迟时间，会导致所有的buffer的写操作都变成了同步的。

过分的刷新脏页到磁盘执行刷新，会产生大量的写操作减少vm.dirty_background_ratio的值升级内核到2.6.22或者更高版本 pdflush线程会给每个设备进行分配，刷新一个慢设备的时候不会阻碍其它设备的pdflush线程。

文件系统—EXT3

这是一种现在最广泛使用的文件系统，但是它明显不是最好的。

首先它在删除大文件的会花费很长的时间：在执行的时候内部会有很多随机的磁盘I/O（HDD）。

而对于mysql来说，当执行DROP table的时候，所有open/lock表的客户端线程都会被block掉(LOCK_open mutex)。

还有要特别注意使用MyISAM,使用innodb_file_per_table的InnoDB，以及PBXT引擎等。

写文件是串行化的串行化是通过i-mutex(互斥)，分配给每个inode 有时候它比分配单个大文件会快。

对于快速的存储设备缺少优化（如PCI-E接口的SSD）使用“dir_index”来加快搜索文件，这个需要在文件系统中增加这个属性，tune2fs -O +dir_index/dev/hda5 关闭barrier。

文件系统—XFS/EXT2/BTRFS

xfs的特点快速删除文件当使用O_DIRECT可以进行并发写入到一个文件
在RHEL中没有官方支持可以设置“nobarrier”来关闭写隔离 ext2 更快速的写，因为它不支持日志，所以出现问题不能进行恢复 fsck的时间很长在active-active的冗余环境下使用（比如MySQL的replication）在一些情况下，ext2拥有更好的性能 btrfs（开发中）这是一种跟ZFS一样的copy-on-write的文件系统支持事务（没有half-block更新） snapshot备份无需额外的开销下图就是ext3和xfs在不同的磁盘上的随机写的一个对比图。

HDD就是普通磁盘，Intel应该是普通的SATA接口的SSD，而FUSION应该是pci-e接口的SSD

上面的HDD是4块SAS RAID1。

I/O调度器
注：RDBMS（特别是InnoDB）都会调度I/O请求，所以理论上Linux I/O调度器并不是必须的。
Linux的I/O调度器可以有效的控制I/O请求，I/O调度器类型和队列大小都是要考虑的问题。
Linux I/O调度器的类型（根据RHEL5，内核2.6.10） noop:排序进入的I/O请求通过逻辑block地址，其实就是FIFO，先进先出。
Deadline:读请求(sync)的请求比写请求(async)拥有更高的优先级。
其它的就是FIFO，这样就能避免I/O请求饥饿的问题。
cfg(默认)：对于每个I/O线程公平的策略I/O，它会对所有的I/O请求进行逻辑block地址重新进行排序，这样减少了查找block地址的时间。
Anticipatory:在2.6.33内核中已经删除，所以请不要再进行使用了。
下面会并发运行2个压力测试程序多线程的随机磁盘读（默认RDBMS读）单线程的复写+fsync()（模拟redo日志写）

从上面图中我们可以很容易的看到cfq和noop的差距。
操作为RHEL5.3和SUSE11，4 HDD的RAID 1+0。
在RDBMS中，写的IOPS通常都非常高，因为HDD写cache每秒需要控制成千上万的事务提交(write+fsync) 写入的IOPS会被调整为每个线程读IOPS，所以很明显的减少总的IOPS。
下面这个是4个不同的I/O策略的测试图，使用的DBT-2测试，引擎为InnoDB

可以看到noop和deadline差距还是很少的，但是比cfq还是高出30%的样子。
下面这个图是更改了I/O策略的队列大小后的对比图，所以用的MyISAM引擎的比较结果

queue size=N, I/O调度器就会排序N个请求来优化磁盘查找。
MyISAM引擎不会在内部优化I/O请求，它高度依赖OS和存储。
当对索引进行插入会导致巨量的随机磁盘读写。
增加I/O队列大小可以减少磁盘查找的开销。

1	echo 100000 > /sys/block/sdX/queue/nr_requests

这种操作对于InnoDB没有影响，InnoDB会在内部进行排序I/O请求。
有用的命令和工具
iostat mpstat oprofile SystemTap(stap) gdp 作者讲了这5种命令和工具，但是我这边只说到前面3个命令和工具。
iostat
每个设备的详细的I/O统计数据，对于RDBMS非常重要，因为它经常成为I/O瓶颈。
Iostat -xm 10每10秒执行一次。
主要注意r/s和w/s，svctm是平均服务时间（milliseconds），而util就是（r/s+w/s）*svctm

svctm越低意味着r/s和w/s越高。
所以我们不要太相信util，我们主要关注的是r/s,w/s和svctm这几个值。
如果你的IOPS是1000，那如果svctm是1ms的话，那util就是100。
所以当svctm大于1的话就算是有问题了。
Mpstat
以前我一直用vmstat,但是vmstat是显示的所有CPU的一个平均数，后来发现mpstat是能显示每个CPU核的统计。
经常会发现某个 CPU核会占满100%的资源，而其它CPU核却还空闲着。
而如果你使用vmstat/top/iostat/sar你却无法发现难个CPU的瓶颈。
你也可以用mpstat来检查网络的瓶颈。

从上面VMSTAT的图中我们看CPU的空闲度达到了88%，但是通过MPSTAT图中发现是一个CPU满了，而其它CPU都完全空闲了，这个就是 CPU资源分配不均。
这个在之前我们nginx cache服务器上也发现了类似的问题，最终解决后发现性能提升了30%以上。
Oprofile
oprofile是可以查看运行进程的CPU使用状况的概括。
你可以很容易的确认那些方法用掉了这些CPU资源。
这个工具同时支持系统空间和用户空间。
这个工具主要是用于数据库的内部开发者。
如果发现有特别的方法占用了大部分的资源，程序员最好跳过这些方法的调用。
对于检查低cpu活动，IO限制和互斥等情况没有用处。
如何使用呢？ Opcontrol –start –no-vmlinux benchmarking opcontrol –dump opcontrol –shutdown opreport -l /usr/local/bin/mysqld 执行完如下结果

Linux System Admin & MySQL DBA