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

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:当往一个随机排序的索引中插入数据的时候会造成随机的读/写

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

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

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

这是因为当物理内存耗尽的时候,linux内核会减少文件系统cache作为最高优先级(低优先级就增加那个值)

当文件系统cache也没有可用的时候,就会开始使用swap。而这个OOM Killer也不会被启用,因为还有足够的swap空间呢。这样才是安全的。

内存分配

mysqld使用malloc()/mmap()方法来进行内存分配

如果要使用更快更多并发的内存就要用tcmalloc()这样的方法

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

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队列大小可以减少磁盘查找的开销。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

执行完如下结果

###########################################

Best regards
Timo Seven
blog:http://www.timoseven.com
twitter: http://twitter.com/zauc
Linux System Admin & MySQL DBA

读《构建高性能web站点》后感

其实现在我这本书只看了一半,但是有些想法我觉得还是得记录下来。

我一直是做系统运维的,作者应该是开发出身。书的第二章和第三章主要是讲网络 传输和服务器并发处理能力。

虽然这2部分离非常专业还是差的比较远,但是我觉得作者分析的思路还是很不错
的。普通的系统运维人员一般处理问题就是man一下或者google一下,翻翻WIKI,
查查mail list。但是书的作者因为是开发人员,所以他的视角从一开始就是源 码,系统函数来判断。

其实这样做是非常正确的我觉得,毕竟所有服务器软件最终都是调用系统函数,而 Linux的优势不就是开放源码呀。

其实刚开始这种分析方法帮我解决了几个nginx的问题,首先是epoll,这个I/O模
型到底有哪些优势,为什么会产生epoll,它主要解决了哪些问题,而作者关于那 个面馆的比喻也非常不错,很形象。

另外一个问题是sendfile()方法,启用这个和不启用的区别在哪里? 而作者通过
strace工具分析也很不错。可以抓取到各个系统函数的调用时间和次数。而通过
strace分析后发现使用sendfile()方法后write()方法就没有了,而write()方法就 是把文件从用户空间往内核空间中进行写。

另外一个是到底开多少nginx进程好呢? 这个在apache中是不需要配置的,它会自
己根据链接情况增加和减少。nginx进程太少可能就会让其它cpu闲置着,而开启太
多还会导致频繁的上下文切换。虽然这些时间对于1秒种来说是非常微小的,但是 累计起来就大了。

看到一半为什么要写读后感主要是觉得以后排除问题,特别是性能方面的问题有了
全新的解决思路,而这个思路就导致了我必须看下linux内核的源码以及系统软件 的源码,当然wiki还是必须要参考的。

##############################

Best regards
Timo Seven
blog: http://www.timoseven.com
twitter: http://twitter.com/zauc
UNIX System Admin & MySQL DBA

{译}RedHat GFS对比NFS :提高性能和可扩展性

本文翻译自:http://www.redhat.com/magazine/008jun05/features/gfs_nfs/

原文作者: Matthew O’Keefe

译者: Timo

本文是对比Red Hat GFS(Global Filesystem)和NFS的基础文档,用来说明2种文件系统的区别。以及GFS的优势在哪里。

数据共享在今天的现代计算机世界是必须的。当收到数据请求,包括允许一个服务器群集对一个存储连接池的访问请求, RedHat GFS是简化您的数据架构,最大限度地减少存储成本,增加存储的速度,并实现最大的正常运行时间的方案。
类似于红帽GFS的集群文件系统像使用基于IP分块网络的iSCSI共享协议,以提供可扩展低成本的文件服务。网络文件系统( NFS )是一种常见的共享存储解决方案通过使用许多设备。然而,在某些情况下,这一解决方案并不可扩展。GFS和NFS如何比较? 下文讲进行解释。
红帽®企业版Linux ®客户遇到有性能问题的NFS并且只使用Linux下的NFS客户端可以使用基于iSCSI的IP分块联网Red Hat GFS来很快提高它们的性能和扩展性。

比较 :
NFS的是Linux和UNIX下一种流行的文件共享协议。 图1 显示 不同的NFS和GFS在服务器群集下数据共享硬件拓扑。 图1  显示了典型和最常见的NFS的部署:一个单一的NFS服务器有自己的本地存储连接到在网络上的客户端。 GFS的数据共享群集构建一个具有相同的硬件架拓扑iSCSI服务器,并在实践中,拥有更好的性能。此外,不同于NFS ,GFS像一个本地文件系统因为它兼容POSIX行为。这意味着,分布式Linux应用程序可以取得良好的访问性能访问共享文件通过实现兼容POSIX标准的集群。
注:
特别是, NFS不支持UNIX (和Linux )进程的所支持的同一文件同步语义:在UNIX ,如果一个进程写入一个文件,另一个进程读该文件在稍后时间保证看到前面所写的。  NFS没有这种保证,除非特别设置写入缓存,但使用可能产生负面影响。

single图1 。 Comparing NFS and Red Hat GFS/iSCSI hardware topologies

图2 显示两组NFS服务器作为故障切换和后端的存储区域网络( SAN ); 相对的数据共享群集拓扑显示,包含两个iSCSI服务器共享SAN存储。就像图1 一样物理拓扑结构是一样的,但功能可在这两个系统已经相差很远。 在NFS的服务器只是作为一个容错互备:他们不共享文件,只有物理块存储(每个NFS服务器输出本地文件系统来映射到SAN一个共享卷) 。 必须维护两个单独的文件系统,而且在同一时间只有一个NFS服务器可以提供的处理能力来处理NFS的要求特定的文件系统。 相比之下,在数据共享集群里一个单一的文件系统可以被映射到SAN存储:这两个iSCSI服务器协作,以提供共享文件系统。 如果一个iSCSI服务器出现故障,GFS的服务器节点可以通过路由绕开周围的失效的节点来访问仍在运作的iSCSI存储服务器。

paired图2 。 Paired NFS and Red Hat GFS/iSCSI servers

图3 显示系统拓扑规模扩展到四个NFS和四个iSCSI服务器。  请注意, NFS服务器不同于是iSCSI节点是它没有一个SAN的存储空间。 这意味着,每一个NFS服务器可以提供访问的只是一个单一的文件系统,它是不可能增加更多的NFS服务器的处理能力增加服务以提供部分的文件系统。 相比之下, 4个iSCSI服务器通过SAN连接到共享存储,并提供所有四个服务器处理能力的GFS服务器。事实上,更多的iSCSI服务器可以按需添加到GFS服务器数据共享集群,但是必须与SAN的处理能力想匹配。 存储容量也可逐步增加的SAN,可以提供访问一个或多个文件系统。四个的NFS服务器在图3中显示的是四个独立的存储空间,并且性能和效率含有瓶颈的NFS服务器。

multiple图3.Multiple NFS servers and a Red Hat GFS/iSCSI data sharing cluster

摘要
红帽GFS可结合iSCSI存储网络,以提供更好的性能相比单独的NFS实现。

comparing-gfs-and-nfsTable 1. Comparing GFS/iSCSI and NFS

vsftpd一些其它配置

vsftpd可以设置为允许本地用户登陆,但是通常并不是需要本地用户全部登陆,我们可以设置为哪些本地用户不能登陆。


userlist_deny = yes

userlist_enable = /etc/vsftpd/userlist

vsftp同时也是可以设置最大客户端连接数,同时也可以设置每个IP的最大连接数


max_clients = 10

max_per_ip = 1

vsftpd同样也是可以支持SSL加密传输,因为默认ftp传输默认是明文的

首先要通过openssl生成证书


openssl req -new -x509 -nodes -out vsftpd.pem -keyout vsftpd.pem

然后修改vsftpd.conf增加下列内容


ssl_enable=YES
ssl_sslv2=YES

ssl_sslv3=YES

ssl_tlsv1=YES
force_local_data_ssl=YES
force_local_logins_ssl=YES
rsa_cert_file=/etc/vsftpd/vsftpd.pem

当然ftp客户端也要选择ftp over ssl模式