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

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

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

<!-- 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

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

我们看到，同样是拷贝大文件，下面这个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

不要太频繁的更新文件

<!-- p { margin-bottom: 0.21cm; } -->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

Filed under database Tagged with hardware, linux, mysql, 扩展, 优化

高性能mysql读书笔记之二–架构优化和索引

2010年05月5日 由 Timo 发表评论

第三章 架构优化和索引

第三章的主要是说合理使用不同的数据类型和索引。主要需要注意的内容有如下：

1. 通用原则

1.1. 数据类型更小通常更好。

1.2. 数据类型越简单越好

1.3. 尽量避免使用NULL,要是有必要用NULL，那也可考虑使用0来进行代替。 创建表的时候定义好not null default 0。

1.4. DATETIME和TIMESTAMP都能保持同样数据类型：日期和时间，精度为妙。而且TIMESTAMP使用的空间只是DATETIME的一半，TIMESTAMP使用4个字节，DATETIME使用8个字节。而且TIMESTAMP还能保存时区，拥有特殊的自动更新能力，但是TIMESTAMP的范围要比DATETIME要小的多。TIMESTAMP类型只能保存1970年1月1日零点到2038年。而DATETIME却能保存1001年到9999年。

2. VARCHAR和CHAR

2.1. 大家都知道VARCHAR是可变长度的，而CHAR是定长的。

2.2. 使用VARCHAR(5)和VARCHAR（200）保存’hello’占用的空间都是一样的。但是VARCHAR(5)只会使用较小的内存空间，因为MySQL通常会分配固定大小的内存块来保存值。

3. 索引对于查询的性能影响是非常大的。下面先介绍下索引类型。

3.1. B-TREE索引，大部分MySQL存储引擎都支持B-TREE。除了ARCHIVE直到5.1才支持。

3.1.1. 存储引擎使用了不同的方式把索引保存在磁盘上，它们会影响一定的性能。例如MyISAM使用前缀压缩以减少索引，而InnoDB不会压缩索引，因为它不能把压缩索引用于某些优化。

3.1.2. B-TREE通常意味着存储是有序的。

3.1.3. B-TREE索引能够很好的用户全键值，键值范围或者键前缀进行查找（最左前缀）。

3.1.3.1. 匹配全名，如 where name=’timo’

3.1.3.2. 匹配最左前缀， 如 where name like ‘tim%’

3.1.3.3. 匹配范围值，如 where name between ‘tim’ and ‘timo’

3.1.3.4. 精确匹配一部分并且匹配某个范围内的另一部分，如 where name=’timo’ and age between 25 and 30

3.1.3.5. B-TREE索引通常能支持至访问索引的查询，它不会访问数据行

3.1.4. B-TREE索引的局限性

3.1.4.1. 假设有如下的索引 key(first_name, last_name, age)

3.1.4.2. 如果查找没有从索引的最左边开始，它就没有什么用处。比如where first_name like ‘%mo’ 这样的查找是不走索引的。

3.1.4.3. 不能跳过索引中的列, 如查找 where first_name=’timo’ and age = 25, 如果建立的是上面这样的联合索引，又跨了last_name，那就不会走索引了。

3.1.4.4. 存储引擎不能优化访问任何在一个范围条件右边的列。如查找

where first_name=’timo’ and last_name like ‘s%’ and age=25。访问就只能使用索引的前2列，因为like是范围条件。

3.1.4.5. 一些局限并不是B-TREE固有的，而是MySQL查询优化器和存储引擎使用索引的方式造成的。

3.2. 哈希索引(hash index)

3.2.1. 它值对使用索引中的每一列的精确查找有用。所以很少用，在MySQL中是有Memory存储引擎支持显式的哈希索引。

3.2.2. 由于hash index是给每个键值建立一个哈希表，所以它的查找速度是非常快的，但是也会有很多局限性。

3.2.2.1. 因为索引只包含了哈希码和行指针，而不是指本身，MySQL不能使用索引中的值来避免读取行。

3.2.2.2. 不能进行排序

3.2.2.3. 不支持部分键匹配

3.2.2.4. 只支持使用 =, in() 和<=>的相等比较。

3.2.2.5. 发生碰撞的时候存储引擎必须访问链表中的每个指针，然后逐行进行数据比较，以确定正确的数据。

3.2.2.6. 如果有很多碰撞，一些索引维护操作就会很慢。

3.3. 空间（R-TREE）索引

3.3.1. 只有MyISAM支持，可以使用GEOMETRY这样的地理空间数据类型，必须使用MySQL GIS函数进行查找。

3.4. 全文索引

3.4.1. 全文索引只有MyISAM支持。是从文本中直接找关键字，而不是从索引中进行比较。全文索引用户MATCH AGAINST操作。

3.5. 高性能索引策略

3.5.1. 隔离列

3.5.1.1. 下面2条语句是不会使用索引的

view sourceprint?1where actor_id + 1 = 5 2??3where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10

?

3.5.1.2. 下面2条是针对上面2条进行修改使用索引的

view sourceprint?1where actor_id = 4 2??3where date_col >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY)

?

3.5.2. 前缀索引和索引选择性

3.5.2.1. 当某一列特别长的情况下，如果给全部长度建索引，那样会增加索引的大小，而只做很短的前缀索引，虽然节约了空间，但是会增加选择性。所以建前缀索引必须让选择性接近于全部长度的选择性。

3.5.2.2. 平均来说前缀的选择性能接近于0.31就可以了。

view sourceprint?1select count(distinct city)/count(*) from db_name.table_name; select count(distinct left(city, 3))/count(*) AS sel3, count(distinct left(city, 4))/count(*) AS sel4 from db_name.table_name;

?

3.5.2.3. 只看平均选择率在特殊情况是不够的，比如在数据分布非常不均的情况下。

3.5.2.4. Alter table db_name.table_name add KEY (city(7)) 这句就是只对city这一列的前7个字母进行索引。

3.5.3. 聚集索引（clustered indexes）

3.5.3.1. 在InnoDB中聚集索引实际上在同样的结构中保存了B-TREE索引和数据行。聚集的含义就是指实际的数据行和相关的键值保存在一起。每个表只能有一个聚集索引，因为不可能一次把行保存在两个地方

3.5.3.2. 在MySQL中只有SolidDB和InnoDB是支持聚集索引的。

3.5.3.3. InnoDB是按照主键（Primary Key）列进行聚集。如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。

3.5.3.4. 聚集索引有助于性能，但是也会导致严重的性能问题。总的来说它有如下的优点：由于把索引和数据都保存在一棵B-TREE中，因此查找数据会比通常的要快。

3.5.3.5. 聚集索引也有如下的缺点：会导致I/O密集，插入速度慢，更新索引列慢，插入新行会进行分页，这样导致占用更多的磁盘空间。第二索引会比预想的大，第二索引访问需要两次索引查找。

3.5.3.6. 在InnoDB中是根据主键来进行顺序插入的（这个跟InnoDB的数据布局有关），所以主键最好是一个自增的值，与应用程序无关。

3.5.4. 覆盖索引（covering indexes）

3.5.4.1. 包含（或者覆盖）了所有满足查询需要的数据的索引叫覆盖索引。

3.5.4.2. 索引记录通常远小于全行大小，因此只读取索引就能极大的减少数据访问量（这个跟聚集索引的优点一样）

3.5.4.3. 索引是按照索引值进行排序的。

3.5.4.4. 大部分存储引擎缓存索引比缓存数据更好（除了Falcon）。

3.5.4.5. 对于InnoDB覆盖了查询的第二索引在主键中避免了另外一次索引查找。

3.5.4.6. 覆盖索引必须保存它包含列的数据。

3.5.4.7. 当发起一个索引覆盖的查询，用EXPLAIN会在extra列显示Using Index

3.5.5. 为排序使用索引扫描

3.5.5.1. MySQL有两种产生排序结果的方式，使用文件排序（Filesort）和扫描有序的索引。如果EXPLAIN的输入type列的值是index。那说明MySQL会扫描索引。

3.5.5.2. 只有当索引的顺序和order by字句中的顺序完全一致，并且所有列排序的方向（升序或降序）一样才可以。如果查询连接多个表，只有在order by 字句的所有列引用的是第一个表才可以。

3.5.5.3. 假设有如下表：

view sourceprint?01CREATE TABLE rental ( 02??03………… 04??05PRIMARY KEY (rental_id), 06??07UNIQUE KEY rental_date (rental_date, inventory_id, customer_id), 08??09KEY idx_fx_inventory_id (inventory_id), 10??11KEY idx_fx_customer_id (customer_id), 12??13KEY idx_fx_staff_id (staff_id), 14??15………… 16??17)

?

3.5.5.3.1. 下面的这几个语句是使用到索引的。

view sourceprint?1WHERE rental_date='2010-05-02' ORDER BY inventory_id desc; 2??3WHERE rental_date > '2010-05-02' ORDER BY rental_date, inventory_id; 在where字句是范围的时候需要用最左前缀索引进行排序。

?

3.5.5.3.2. 而下面的都是不能使用索引进行排序的。

view sourceprint?1... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id; 2??3... WHERE rental_date = '2005-05-25' ORDER BY customer_id; 4??5... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id; 原因是第一句里用到staff_id不在索引的列中，第二句是没能形成索引的最左前缀，第三句是由于在索引的第一列有范围条件那MySQL就不会使用余下的索引。还有多个where等于条件也不会使用索引排序。

?

3.5.6. 压缩（前缀压缩）索引

3.5.6.1. 这个只在MyISAM存储引擎才有效

3.5.6.2. 压缩完成会块的占用空间会较小，但是某些操作会变慢。排序如果是顺序没有问题，但是倒序会非常的慢。

3.5.6.3. CPU的负载会比平常多几倍。

3.5.6.4. 压缩后索引大小也许只有原来的1/10

3.5.6.5. 可以在使用CREATE TABLE命令的时候用PACK_KEYS来控制索引压缩的方式。

3.5.7. 多余和重复索引

3.5.7.1. 以前我一直觉得多建索引只是多占用系统的磁盘大小，而看了这里感觉自己错了，还是需要注意不能随便建索引。

3.5.7.2. MySQL本身不会提醒你创建了重复的索引。这样MySQL不得不单独维护每一个索引，并且查询优化器在优化查询的时候会逐个考虑它们，这样就会严重影响性能。

3.5.7.3. 看下面这个例子： CREATE TABLE test (ID INT NOT NULL PRIMARY KEY, UNIQUE(ID) INDEX(ID)); 我自己以前也有过这样的写法，其实这样对于ID这一列创建了3个相同的索引。事实上MySQL利用索引实现了UNIQUE约束和PRIMARY KEY约束。所以通常不需要这样做，除非你要在同一列上有不同的索引满足不同类型的查询（比如KEY和FULLTEXT KEY）

3.5.7.4. 多余索引。有几个例子，比如建立了（A,B）2列这样的索引，那就没有必要建（A）索引了，但是还是有必要建立（B）索引。同样建立了(A,B)如果有需要还是可以建立（B,A）索引的。

3.5.7.5. 同时在大部分情况下，为了避免它，应该扩展索已有索引，而不是添加新索引。

3.5.8. 索引和锁定（Indexes and Locking）

3.5.8.1. 索引可以让查询锁定更少的行，因为在InnoDB只有在事务提交后才会给行解锁。

3.5.8.2. InnoDB进行行锁定还是有一些开销的，锁定超过需要的行会增加锁竞争和减少并发。

3.5.8.3. 下面是一个例子

view sourceprint?1Set autocommit=0 2??3SELECT actor_id FROM table_name WHERE actor_id < 5 AND actor_id <> 1 FOR UPDATE;

?

该查询实际以独占的方式锁定了1到4行，InnoDB锁定第一行的原因是“从索引的开头开始，并且提取所有行直到 actor_id < 5不成立”。如果没有索引，MySQL不管是否需要行，都会进行全表扫描并且锁定每一行（MySQL5.1中 READ COMMITTED事务隔离级别也有这个问题）。

InnoDB能在第二索引上放置共享（读）锁，但是独占（写）锁要求访问主键。这消除了使用覆盖索引的可能性，并且能导致SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定要慢很多。

3.5.9. 索引策略总结

3.5.9.1. 为任何耗时很长的查询添加索引。

3.5.9.2. 在任何可能的地方，都要试着扩展索引，而不是新增索引。如果不知道查询的分布，就要尽可能地使索引变得更有选择性。

3.6. 索引实例研究

3.6.1. 这个主要是一个婚恋网站的维护，比较简单。

3.7. 索引和表维护

3.7.1. 维护的目的是为了减少碎片

3.7.2. 查找并修复表损坏

3.7.2.1. 这个一般MyISAM引擎会由于服务器崩溃导致表损坏，可以使用myisamchk来进行修复，修复的方法有很多。至于InnoDB我自己是从来没有碰到过表损坏的问题出现。

3.7.3. 更新索引统计

3.7.3.1. MySQL查询优化器在决定如何使用索引的时候会调用两个API，以了解索引如何分布，一个是调用records_in_range(),它接受范围结束点并且返回范围内记录的数量。第二个info(),它返回不同类型的数据。

3.7.3.2. 查询优化器的开销指标是查询会访问多少数据。如果统计永远没有产生，或者过时了，优化器就会做出不好的决定。可以运行ANALYZE TABLE来解决这个问题。

3.7.3.3. InnoDB是在第一次打开表的时候利用随机索引进行估计。InnoDB上的ANALYZE TABLE命令就使用了随机索引，同样，ANALYZE TABLE在InnoDB不是阻塞性的，并且开销也不大，因此可以在不大影响服务器的情况下在线更新统计。

3.7.3.4. 我们可以使用SHOW INDEX FROM table_name命令来检查索引的基数性。需要特别注意Cardinality列，它显示了存储引擎估计的索引中唯一值的数量。在MySQL5.0以及以上版本可以通过INFORMATION_SCHEMA.STATISTICS表来得到这些数据。

3.7.4. 减少索引和数据碎片

3.7.4.1. B-TREE索引会有它的形成机制，故会形成碎片并降低了性能。

3.7.4.2. 表的数据存储也能变的碎片化。有两种碎片分别为

3.7.4.2.1. 行碎片（row fragmentation）:当行存储在多个地方的多个片段中就会产生这种碎片。

3.7.4.2.2. 内部行碎片（intra-row fragmentation）：当逻辑上顺序的页面或行在磁盘上没有被顺序的存储时就会产生这种碎片。它影响了全表扫描和聚集索引这样的操作。

3.7.4.3. 为了消除数据碎片可以运行OPTIMIZE TABLE来转储或者重新加载数据。对于不支持OPTIMIZE TABLE的存储引擎可以用ALTER TABLE重新建立表。

3.8. 范式化和非范式化（Normalization and Denormalization）

3.8.1. 这个属于数据库设计的范畴，第一范式，第二范式，第三范式，BCNF

3.8.2. 范式化架构的利弊：这个基本上就是BCNF的好处，当写入负载大的时候，使用范式化架构师比较好的。它的优缺点如下：

3.8.2.1. 范式化更新比非范式化更新快

3.8.2.2. 当数据被很少的范式化后，就很少或者非常少的重复数据，因此改动的数据会变少

3.8.2.3. 范式化表通常较小，容易被装载到内存中并且性能更好

3.8.2.4. 由于缺少冗余数据，在取得数据的时候会较少采用DISTINCT或者GROUP BY，这样有时候要查数据会连接多个表。这样就非常难建立索引策略。

3.8.3. 非范式化架构的利弊：非范式化架构由于所有数据都在一个表里面，避免了连接，所以性能不错。由于不需要连接表，对于大多数查询，甚至不使用索引，这会比连接快的多，因为它避免了随机I/0。所以非范式化架构用在查询非常多的情况是非常好的。

3.8.4. 结合范式化和非范式化：在真实环境中很少会全部范式化和非范式化，通常都是结合者2中方案的，也许是采用部分范式化，缓存表以及其它技巧。

3.8.4.1. 非范式化最常见的技巧是复制，缓存，把一个表中部分列选到另外一个表中，在MySQL5.0以及以上版本，可以使用触发器来更新缓存的值。

3.8.5. 缓存和汇总表

3.8.5.1. 有时需构造完全不同的汇总或者缓存表，为获取数据进行特别的调优。

3.8.5.2. 缓存表的含义就是能够容易的获得数据，如果这样更慢的话就会通过scheme来获取数据。缓存表对于优化搜索和获取数据的查询是有用的。

3.8.5.3. 汇总表的意思就是我们可以通过GROUP BY来汇总查询数据。

3.8.5.4. 当使用缓存和汇总表的时候，你不得不决定是否要进行实施数据维护或周期性重建。不过可以通过使用影子表来解决这个问题。

view sourceprint?1DROP TABLE IF EXISTS my_summary_new, my_summary_old; 2??3CREATE TABLE my_summary_new LIKE my_summary; 4??5RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;

?

3.9. 加速ALTER TABLE

3.9.1. 这个是有技巧的，不是所有的ALTER TABLE操作都会导致重建表。

下面这个例子是想把这一列默认值变为5，但是第一句它实际上会执行1000次读取和1000次插入，这样就会非常慢。而第二句只修改默认值，列的默认值实际保存在表的.frm文件中，所以可以不不接触表而更改它。任何MODIFY COLUMN操作都会导致表重建。

view sourceprint?1ALTER TABLE table_name MODIFY COLUMN column_name TINYINT(3) NOT NULL DEFAULT 5; 2??3ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 5；

?

3.9.2. 如果愿意承担一定风险，可以让MySQL做其它类型的修改时也不重建表。

3.9.2.1. 以下操作可以不重建表：移除（但不是添加）列的AUTO_INCREMENT属性

3.9.2.2. 添加，移除或更改ENUM和SET常量。

3.9.2.3. 官方说这是不受支持的技巧，使用后果自负。

2010-05-05

Filed under database Tagged with 索引, mysql, 架构, 优化

{译}Mysql?innodb存储引擎的性能优化

2010年04月6日 由 Timo 发表评论

Mysql?innodb?performance?optimization

Mysql?innodb存储引擎的性能优化

本文翻译自http://www.mysqlperformanceblog.com/files/presentations/UC2007-Innodb-Performance-Optimization.pdf

这里只是我对这个PDF文件进行了翻译，由于本人刚从事mysql?DBA一职，所以很多东西自己并不能很好的翻译出来，其中对于硬件部分翻译的应该还行，但是关于数据库方面的翻译的不好，大家就看看吧，翻译本文只是想更?清楚的了解mysql 优化上的一些基本原则，而国内对于这个没有完整的资料。本文的作者Heikki?Tuuri是InnoDB的创始人，在翻译过程中同样获得了Peter?Zaitsev的帮助，他是《High?Performance?MySQL》一书的作者。

作者：?Heikki?Tuuri

Email:?pz@mysqlperformanceblog.com

译者：?Timo?Seven

Email:?greycd@gmail.com

大家有什么意见可以给我邮件。

一切都从应用程序设计开始

1.?通用应用程序的设计是至关重要的

1.1.?设计你的schema，索引和查询，以及选择正确的存储引擎是常用的优化手段。

1.2.?在有些情况下存储引擎的选择会影响到schema和索引

1.3.?我们这里不会覆盖到一般的schema设计方法，但是会主要聚焦到Innodb存储引擎。

2.?每个存储引擎都是不同的

2.1.?MySQl提供多种存储引擎可供选择

2.2.?它们每个都有不同的设计和操作属性。

2.3.?一个给某个存储引擎写的应用程序可能在其它存储引擎下表现良好。

2.4.?每个存储引擎都有特定的优化方式，所以它们只对特定的设计模式有用。

2.5.?我们覆盖所有对于InnoDB存储引擎的做何不做。

3.?使用事务

3.1.?InnoDB默认就是使用事务，甚至你不知道如何使用。每句语句都将在自己的事务内（假设你运行“autocommit”模式 set?autocommit=1），在每句语句后面都会自动增加commit语句。

3.2.?把多条update语句包装在同一个事务是更有效率的方式。（set?autcommit=0;..comit;…commit;）。不能让事务过长，这样会造成死锁和等待超时。

4.?不要使用锁表(lock?tables)

4.1.?锁表（LOCK?TABLES）是设计用来给那些表级锁的存储引擎。在行级锁的存储引擎中事务是更好的选择。InnoDB的的锁表行为在不同的mysql 版本是不同的，如果你从MySQL4.0或者更新的版本升级，那你依赖于innodb_table_locks这个选项会导致很多问题。

5.?主键簇

5.1.?主键是特殊的

5.1.1.?通过主键访问数据比通过其它key访问更快。无论是在内存还是磁盘通过主键查找都是最快的。

5.1.2.?数据都由主键聚集的。连续的主键值很容易让同一页主键的数据进行排序同时首字查询也会非常有效率。可用于把所有需要访问数据集中在一起。把用户信息存储在一起可以使用（user_id,message_id）作为主键来保存所有用户的信息在很多页中。主键是一个替代索引对于任何字段。

6.?主键的开销

6.1.?主键在随机排序是开销比较大的，也会导致表产生碎片（主键的插入一般都是根据升序进行的）

6.1.1.?如果可以装载数据都通过主键来进行排序的。

6.1.2.?有时候把主键设置成自动增长（auto_increment）是一个好主意

6.2.?如果你不指定，主键默认就是一个内在的聚集key。所以最好就定义一个并且使用它。

6.3.?UPDATE?PK开销是非常大的

6.3.1.?行数据将会在索引中物理的从一个地方移动位置。

6.3.2.?通常这种需要在设计上进行避免。

7.?让主键尽量短

7.1.?因为其它索引都是通过主键来构建索引的。

7.1.1.?使主键成为其它索引的一部分。

7.2.?长主键会让你的索引变大和变慢

7.2.1.?你可以把主键变成唯一KEY，同时给主键添加自动增长。你不能简单的让InnoDB自己去创建它的内部主键，通过把主键变为唯一key因为MySQL会自动的转换一个非空的的唯一key作为主键。

7.2.2.?如果一个表中只有主键，同时所有查询都是通过主键进行，即使主键是比较长的，那通过主键进行查询也是更快的。

8.?InnoDB的索引

8.1.?让唯一索引变的简单

8.1.1.?不使用”insert?buffer”会加快索引的更新。

8.2.?索引是不能做前缀压缩的

8.2.1.?所以它会比MyISAM引擎占用更多的空间

8.2.2.?要尽量避免过多的索引

8.3.?对所有需要更新的列进行索引

8.3.1.?不然你将看到不愿看到的lock问题。 DELETE?FROM?users?WHERE?name=’peter’,?如果没有对name列进行索引的话就会锁住表中所有的行。

9.?自动增加将限制可扩展性

9.1.?自动增长的插入可能会用到表级锁（但只会在insert语句的最后部分，没有事务的情况下）。即使你指定了自动增长列的值。

9.2.?对于并发插入将限制可可扩展性。

9.3.?会导致在运行中出现困难。

9.4.?导致超出MySQL所能分配的值。要特别小心那种非常长和随机的主键。

10.?多版本

10.1.?只对需要的行进行lock将会获得更好的并发性能。

10.2.?普通的SELECT不会进行lock操作，只会去读适当的行。

10.2.1.??Lock在共享模式下，UPDATE做更新操作时会对读进行Lock。

10.3.?甚至长时间的select查询不会阻止对于表的update或者select操作。

10.4.?过度的慢查询（通常在事务内）对于性能是不好的，如导致版本的不一致性。READ?COMMITTED能够减轻这种问题。

10.4.1.?InnoDB只能隔离一个行版本当读取这行的时候没有事务在运行。

11.?在共享模式下的…FOR?UPDATE?and?LOCK

11.1.?在read?commited模式下会进行select?lock。因为不能不能lock一个不存在的行。所以这个跟普通的select是不同的。

11.2.?SELECT…FOR?UPDATE总是不得不访问行数据页进行lock，所以不能进行对这些查询进行索引，就会减慢查询的速度。

12.?减少死锁

12.1.?在事务的数据库中死锁是普遍存在的。

12.1.1.?在你InnoDB中没有锁的select语句是不会导致死锁的。

12.1.2.?在你的应用需要控制好你的死锁时间。

12.2.?如果可能的话确认在事务中锁住的数据就是你请求的那些。

12.3.?让update数据变小（分离你的事务）

12.4.?使用SELECT…FOR?UPDATE如果你想更新大部分你所选择的行。

12.5.?使用外部锁可以避免死锁这个问题—应用程序级别的锁，SELECT?GET_LOCK(‘mylock’)等等。

13.?隔离级别是如何影响性能的

13.1.?InnoDB支持很多种的隔离级别。这些隔离级别可以设置为全局有效也可以针对每个连接和每个事务。

13.1.1.??READ?UMCOMMITED（不提交读）—这个是很少使用。如果你不想有脏数据产生那就可以很好的使用这个，但是会影响性能。

13.1.2.??READ?COMMITED（提角度）—所有提交事务的结果对于下一条语句都是显而易见的。可能比其他更高的隔离级别性能更高。允许老的内容更快的更新。在mysql5.1，InnoDB会有一些间歇锁在这个级别上：使用行级复制和binlog可以避免这个问题。

13.1.2.1.??REPEATABLE?READ（可重复读）—默认的隔离级别。事务内的读都是完全可重复的，没有幽灵行的产生。

13.1.2.2.??SERIALIZABLE（串行化）– 让所有select都锁住select，尽可能避免使用这个隔离级别。

14.?外键性能

14.1.?当更新行时候InnoDB都会检查外键，而且不会进行批处理或者当事务提交时候检查延迟。外键通常都有很多性能上的开销，但是这也保证了数据库的连续性。

14.2.?外键增加了很多行级锁，这将会影响到很多其它表不光是自己直接更新的那张表。

14.3.?外键会锁住子表，当父表在更新的时候。（select …?for?update在父表上这样执行就不会锁住子表）

15.?运行中的事务中的约束数量

15.1.?在一定数量内的运行中的事务和执行查询，InnoDB性能表现良好

15.1.1.?多个运行中查询可能导致互相之间干扰。Innodb_thread_concurrency能够被用作在InnoDB内核中限制线程数量。

15.1.2.?许多运行中事务会导致更多的锁，同时造成机器负载增加。

15.1.3.?如果有可能，在同一时间内限制一定数量的查询，在应用程序端做好队列。

16.?注意不要有太多的表

16.1.?InnoDB自己的表定义（字典）缓存依赖于MySQL的table_cache变量值。

16.2.?只要打开一次，InnoDB就不会从缓存中移除这个表。

16.3.?每张表大概要消耗4KB以上的空间。MySQL?5.1 的 InnoDB已经将这个空间减少了50% 到 75%

16.4.?当重启时，每个表的统计将会被重新计算。所以第一次操作会是非常耗资源的。MySQL的table_cache将会串行执行这些操作。

17.?Insert…Select

17.1.?Insert…Select语句执行时会对select进行锁

17.2.?语句级别复制要求更新都是串行化的。在MysQL5.1 行级别更新在 READ?COMMITED已经没有问题了。

17.3.?无论什么时候你启用或者不启用log-bin，都需要保持一致性。

17.4.?Innodb_locks_unsafe_for_binlog在MySQL5.0是有帮助的，但是你的复制有可能会被中断，同时会禁止next-key的锁。

17.5.?SELECT…INTO?OUTFILE?+?LOAD?DATA?INFILE经常被用作non-blocking的安全替代。

18.?Next?key?lock（间隙锁）

18.1.?Innodb不光会锁使用到行，也会锁这些行之间的行（称为间隙行）。

18.2.?这个是为了防止幽灵行的出现。?设置?“REPEATABLE?READ”确实会让InnoDB可重复的。

18.3.?对于MySQL语句级别的复制是很有必要的。

18.4.?会让一些写负载大的机器上增加锁的情况。

18.5.?如果你没有设置和使用二进制log（用作复制和恢复的），那可以禁止这个间隙锁。

18.6.?在MySQL5.1中，如果你使用行级复制就可以安全的进行修改。

19.?Count（*）的事实和传说

19.1.?InnoDB不能很好的控制count(*)的查询–这个只是传闻。在所有引擎中大部分count(*)查询都用相同的方式进行查询。 select?count(*)?from?articles?Where?user_id=5

19.2.?在缺少where字句的情况下，InnoDB不对count(*)查询进行优化–这个是事实。如select?count(*)?from?users;?InnoDB不能简单存储行的计数，每个事务都有自己的表的视图。因为有重要的工作还要去实现。你可以使用触发器和计数器。SHOW?TABLE?STATUS?LIKE?”USERS” 可以显示表近似的行数。

20.?InnoDB和集体提交

20.1.?集体提交–提交多个事务通过单个日志写。这个可以提高非常多的性能，特别是没有做RAID的情况下。

20.2.?在MySQL5.0下，集体提交不能在有二进制log的情况下工作。由于XA（分布式事务）方法被实现，特别要小心从MysqL4.1进行的升级。

回到基本的服务器性能调优

1.?一切都从内存开始

1.1.?InnoDB_buffer_pool_size

1.1.1.?详细指定了主要InnoDB缓存–数据和索引页，插入缓存，锁都会存在这里。

1.1.2.?在大数据集的情况下对于性能非常重要

1.1.3.?比OS级别的缓存更有效的多，特别对于写操作。InnoDB不得不去绕过OS的buffer去写。

1.1.4.?最好使用70%–80%的系统内存作为InnoDB的buffer使用。

1.1.5.?默认值是8M，可用的独立内存，要好好确认如何去配置。

1.2.?InnoDB_additional_mem_pool

1.2.1.?仅仅存储字典，它会自动增长，不用设置的太大。

2.?InnoDB日志

2.1.?Innodb_log_file_size

2.1.1.?对于写性能有非常重要的影响。要保持非常大。

2.1.2.?高的数值会增加你回复的时间。检查一下你能设置的最大的大小。

2.1.3.?最大的限制是4G。

2.2.?Innodb_log_files_in_group

2.2.1.?这些文件指定了对于Log所能使用的大小。

2.2.2.?通常不需要改变其默认值。

3.?InnoDB日志

3.1.?Innodb_log_buffer_size

3.1.1.?不要设置超过2-9M，除非你使用大量的超大文件，日志文件都会被刷新在每秒执行完毕后。

3.1.2.?检查innodb_os_log_written的增长来看你的日志文件的写入。

3.1.3.?Innodb日志是物理逻辑的，不是基于页的，所以他们是非常紧凑的。

3.2.?Innodb_flush_logs_at_trx_commit

3.2.1.?默认日志被刷新到磁盘上在每次事务提交后。这个是为了保证ACID（原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）），所以开销非常大。

3.2.2.?可以设置2和0，如果你能接受丢失最后一次的事务。

4.?InnoDB日志重新设置大小

4.1.?这个不是简单修改选项和重启下能够完成的。

4.2.?首先要关闭MysQL服务器

4.3.?确认它是正常关闭的（检查有没有错误日志）

4.4.?移动所有InnoDB日志文件到其它地方

4.5.?修改配置文件然后重新启动MySQL服务器

4.6.?检查错误日志并查看是否产生新的日志文件。

5.?InnoDB_flush_method

5.1.?指定一个方法让innodb跟OS文件系统一起工作

5.2.?Windows:?总是使用没有buffer的IO方法

5.3.?Unix:可以使用fsync()或者O_SYNC/O_DSYNC进行刷新文件。

5.3.1.?Fsync()通常是更快的，允许累计多个写操作然后并发执行。

5.3.2.?一些操作系统允许关闭OS级别的缓冲针对InnoDB的数据文件。这样非常好，你总不希望数据被缓冲2次吧。

5.4.?Linux：O_DIRECT 使用直接的非缓冲IO。避免双重缓冲，可能会让写更慢。

6.?Innodb_file_per_table

6.1.?InnoDB可以存储每个表在单独的文件中。

6.2.?对于系统需要的主表空间还是需要的。

6.3.?能够帮助拆封不同的表到多个磁盘上。

6.4.?如果表被删除允许回收空间。

6.5.?有时候使用连续的fsync()写会更慢。

6.6.?当有大量的表的时候会增加启动和关闭的时间。

7.?其它文件IO设定

7.1.?Innodb_autoextend_increment–这个参数指定了对于共享表空间的增量（不是为了单独表空间的）。大的值可以有效的减少碎片。

7.2.?Innodb_file_io_thread–改变IO线程的数量，只对windows有效，所有4个线程可以做不同的事情。

7.3.?Innodb_open_file–这个值是用作指定每个表空间所允许打开文件的数量。如果你有很多表那就要增加它。

7.4.?Innodb_support_x–把这个值设置定为0的时候能够减少innodb的工作在事务提交时。Binlog 能够通过异步的方式同步。

8.?最小化重启时间

8.1.?Innodb缓存池可能有一些未写入到磁盘的数据。所以关闭的时候需要非常的时间。

8.2.?如果你想最小化关闭时间，那需要如下设定：

8.2.1.?SET?GLOBAL?innodb_max_dirty_pages_pct=0

8.2.2.?执行show?status后观察 innodb_buffer_pool_pages_dirty

8.2.3.?当这个值变为0的时候就可以关闭服务器了

8.3.?在执行这个操作时候会让性能降低，因为InnoDB将立刻要将脏数据页写入到磁盘上。

9.?排错逃离清除

9.1.?InnoDB不会通过delete来移除一行（和在更新时候旧的行），因为这些行可能会被其他事务使用。

9.2.?清除线程被用在清除这些没有用到的行。

9.3.?在一些工作负载，清除线程可能不能保持这些表空间无限的增长。通过show?innodb?status观察transactions部分。

9.4.?Innodb_max_purge_lag–这个是用来限制事务每次所能更新或者删除最大的行数。将会延迟insert/update操作，所以清除线程会被保持。

9.5.?为什么我们不用多个清除线程呢？

10.?并发控制设置

10.1.?设置可以帮助InnoDB适应于控制大量的并发事务。

10.2.?Innodb_thread_concurrency–InnoDB内核中同时可以允许最大的在线程数量（0表示没有限制），2*（CPU核数+磁盘数量）在理论上是一个合理的值，在实际应用中设置的更小一点可能会让性能更好一点。

10.3.?Innodb_commit_concurrency–允许同一时间内事务提交的最大线程数。

10.4.?Innodb_concurrency_tickets–在不得不退出内核空间和等待之前能运行线程的数量。

10.5.?Innodb_thread_sleep_delay

10.6.?Innodb_sync_spin_loops

11.?获得高性能的不安全方式

11.1.?Innodb有一些检查和方法是数据不被最小化丢失和错误。

11.2.?Innodb_doublewrite–这个值是用来保护部分页的修改，只是禁止假如OS保证它不会发生。

11.3.?Innodb_checksums–在页中的数据校验码，帮助发现文件系统错误，内存损坏和其它问题。

11.3.1.?导致一部分大工作负载的机器会过载。

11.3.2.?当性能是第一的情况下可以禁止这个参数。

12.?Innodb?SHOW?STATUS部分

12.1.?Mysql5.0有一些性能计数信息通过show?status能够显示出来。

12.1.1.?它们都是全局的，虽然大部分其它技术信息都是针对每个线程的。

12.1.2.?它们大部分都是从show?innodb?status中获取的。

12.2.?下面将显示其中的一些指标。

12.3.?Innodb_buffer_pool_pages_misc–其他缓存页需要的在innodb?buffer中所使用到的页。

12.4.?Innodb_buffer_pool_read_ahead_rnd–innodb处理的随机预读的数量。

12.5.?Innodb_buffer_pool_read_request,?innodb_buffer_pool_reads?这2个值是用来计算缓存读的命中率的。

13.?Show?innodb?status

13.1.?这个是innodb故障处理的工具。当发生问题时就输入“show?innodb?status”

13.2.?这时候就会显示一些比show?status更详细的信息。

13.3.?一些关于现在正在运行中的事务的信息（列入它们的锁等等）

13.4.?最新的一个死锁和外键的信息等等

13.5.?一些关于latches,spinlocks(自旋锁)，操作系统等待信息

13.6.?更详细的参考http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/

14.?Show?mutex(互斥量)?status

14.1.?一个用来显示在你的工作负载下哪些热门的互斥量的工具

14.2.?显示了哪些是真正发生的互斥量，自旋锁的细节，以及操作系统等待。

14.3.?Timed_mutexes?–?跟踪操作系统等待了多久

硬件和操作系统的选择

15.?硬件和操作系统选择的检查列表

15.1.?使用什么CPU，以及多少个？

15.2.?使用多大的内存？

15.3.?如何建立IO子系统（如RAID）？

15.4.?使用哪个文件系统是最好的选择？

16.?选择CPU

16.1.?不同的CPU/架构对于innodb的扩展性能是不同的。

16.2.?老的“netburst”基于intel的xeon的扩展能力是比较弱的。

16.3.?新的酷睿基于xeon和 AMD的opterons具有更好的可扩张性

16.4.?X86_64是非常必须的。

16.5.?使用多核处理器会让Innodb工作的更好

16.6.?每个系统拥有8核是一个比较合理的限制。

16.6.1.?必须根据实际的工作负载来进行调配

16.6.2.?Innodb必须要根据未来的预期负载进行

16.7.?外部扩展，使用多台性能?较低的终端服务器。

16.8.?32位的CPU马上就要被淘汰了，所以请不要再使用32位的操作系统。

17.?使用多大的内存

17.1.?内存经常是对于很好的应用程序调优的性能瓶颈。

17.2.?Innodb可以很有效的使用大量的内存。

17.3.?工作设定必须合理设置内存

17.3.1.?因为数据页是最常被访问

17.3.2.?不要通过行进行计数：100byte的行大概是1亿行，随机100万行的工作设定能够产生大部分的数据页。

17.4.?是总数据库大小的5%能够导致50%空间占用。

17.5.?确定使用64位的平台，操作系统和mysql版本。

18.?如何建立IO子系统

18.1.?Innodb可以很好的加载大部分的磁盘驱动。每个节点有6-8个是一个看上去最优的配置。

18.2.?直接能够访问到的存储通常工作的最好

18.3.?SAN–会增加恢复时间，而且价格昂贵

18.4.?NAS–可以避免很多数据错误的风险

18.5.?ISCSI–在大部分业务中都是非常适合的，也会增加恢复时间

18.6.?RAID–电池备份cache是非常重要的。一定要确认启动了在写入cache的时候有电池备份。

18.7.?硬盘自身的缓存一般都是需要关闭。或者确认使用fsync()写入数据到磁盘或者当操作系统崩溃的时候能够产生数据腐坏。

19.?本地存储的配置

19.1.?日志最好放到单独的raid1中。这个是非常有帮助的，在很多情况比放在跟数据一起更好。

19.2.?二进制日志最好放到单独的卷中–能够很好的帮助备份和恢复。

19.3.?RAID10对于表空间非常好。比预期下降的性能更多。

19.4.?RAID5对于特定的工作负载时好的。仅仅需要确认是否需要降低性能。

19.5.?大的RAID条带大小（128K+）在理论是最好的，但是很多RAID控制器不能非常好的去控制。

19.6.?软RAID也是不错的，特别是RAID1。

20.?操作的选择也有会有问题？

20.1.?需要考虑性能，工具的有效性，社区等等。

20.2.?Windows–试用于开发环境，很低的扩展性的WEB/企业项目上。

20.3.?Solaris–提供了一些非常好的工具，也能对Mysql提供很好的支持，但是缺乏社区支援。

20.4.?FreeBSD–历史上Mysql有很多问题在这个平台上，现在是好多了，但是只有很少的工具可用，很少在生产环境中使用。

20.5.?Linux–在生产环境中和开发环境中最常使用的平台。有一些像LVM,JFS这样的工具可以使用。

21.?文件系统的选择

21.1.?主要是linux环境中有很多文件系统可以选择

21.2.?EXT3–很多Linux发行版的默认文件系统，工作的非常好对于终端安装。

21.3.?ReiserFS–很多Linux支持这个迁移。通常没有打的问题在标准的Mysql工作负载下。

21.4.?XFS–被用在有RAID驱动的情况下，能够提供不错的性能提升。

21.5.?JFS–很少被使用

21.6.?Raw分区（原始分区）针对innodb表空间—很少使用

21.7.?通常非常高的性能提升预期都是通过更改文件文件系统来获得。

最近InnoDB的性能开发

22.?InnoDB可伸缩性的补丁

22.1.?减少了buffer?pool也中竞争。在5.0中直接可用，在4.1中需要自己移植。

22.2.?在MySQL5.1提升了sync_array的实现。

22.3.?基于工作负载，硬件环境，并发上的性能提升跟原来有所不同。

22.4.?从很少的百分比到很多次的进行排序。

22.5.?性能还是会在很大数量的并发线程的情况下有所下降。

22.6.?未来可扩展实现补丁的原型都是来源于社区的。

23.?其它改进

23.1.?在Mysql5.1中行级别复制减轻了间隙锁的问题。

23.2.?在没有auto_increment”talbe?locks”能够继续工作。

23.3.?数据库页中支持ZIP压缩

23.4.?更快的索引建立

23.4.1.?不再需要全表重建

23.4.2.?可以根据物理分片的索引进行排序。

Filed under database Tagged with innodb, mysql, 优化

apache安全优化

2008年05月10日 由 Timo 发表评论

作者：timo

联系方式: greycd ## gmail dot com

如何使web服务器安全（apache）

web服务器（apache）

一个像apache这样的web服务器，它最主要的功能是能让HTML页面在客户端浏览器上显现。

功能

web服务器需要能接入互联网，只需要对静态HTML页面提供支持

服务器能支持基于名字的虚拟主机

一些页面只能某些特殊的IP和用户才能访问（基本的认证）

服务器需要记录web请求的日志（包括浏览器的信息）

web服务器必须对PHP/Perl脚本语言支持

PHP/Perl组件必须能在本地MySQL数据库中读写用户信息

安全设想

1.

操作系统越坚固越好，包括低于本地和远程攻击
2.

服务器除了HTTP，最好不要提供其他任何网络服务
3.

远程访问服务器必须通过防火墙，关闭所有出去的连接，和只放过HTTP访问连接。
4.

在系统上必须只有一个有效的Apache服务
5.

只有完全必须的apache模块才被装载
6.

任何诊断web页面和自动目录索引服务必须被关闭
7.

服务器只能透露最少的关于自身的信息
8.

apache服务必须运行在一个单独的UID／GID下，不能使用其它任何的系统进程
9.

apache服务进程被续被限制访问文件系统（chrooting）
10.

不能有shell程序能在apache的chrooted环境中出现
11.

PHP配置必须内建的安全机制
12.

PHP脚本只能在chrooted环境中执行
13.

apache服务器必须拒绝所有请求（GET和POST）,含有HTML标记（跨站攻击）或者“’”和”"”(可能有SQL注入攻击)
14.

不能有PHP警告和错误信息显示给正式访问的用户
15.

记录进站的GET和PORT请求到一个日志文件。

操作系统

UNIX或者类UNIX例如Linux，FreeBSD等等对于Apache最合适，微软的windows只能提供非常有限的安全能力，对于apache并不合适

先决条件

1.

如果你需要apache和ssl安全认证支持，那OpenSSL就需要先被安装
2.

如何需要apache和postgresql数据库连接支持，那先要安装postgresql或者需要mysql数据库支持那就安装mysql
3.

如果需要LDAP目录支持那还要安装OpenLDAP
4.

如果需要IMAP和POP能力，那还要装IMAP和POP

软件准备

1.

UNIX兼容的命令
2.

含有/var/tmp目录
3.

RedHat Linux
4.

所有步骤使用root帐户
5.

apache版本为1.3
6.

Mod_SSL
7.

Mod_Perl
8.

Mod_PHP

Apache : http://www.apache.org/

Mod_Perl : http://perl.apache.org/

Mod_SSL : http://www.modssl.org/

Mod_PHP : http://www.php.net/

没有的话就去上述网站下载

安装步骤一

把所有tar包放在同一目录中

view sourceprint?1[root@localhost]/# cp apache_version.tar.gz /var/tmp 2??3[root@localhost]/# cp mod_ssl-version-version.tar.gz /var/tmp 4??5[root@localhost]/# cp mod_perl-version.tar.gz /var/tmp 6??7[root@localhost]/# cp php-version.tar.gz /var/tmp 8??9[root@localhost]/# cd /var/tmp/

解压包

view sourceprint?01[root@localhost]/tmp# tar xzpf apache_version.tar.gz 02??03[root@localhost]/tmp# tar xzpf mod_ssl-version-version.tar.gz 04??05[root@localhost]/tmp# tar xzpf mod_perl-version.tar.gz 06??07[root@localhost]/tmp# tar xzpf php-version.tar.gz 08??09Mod_SSL 10??11[root@localhost]/# cd mod_ssl-version-version 12??13[root@localhost]/# CC="egcs" CFLAGS="-O9 -funroll-loops -ffast-math -malign-double -mcpu=pentiumpro -march=pentiumpro -fomit-frame-pointer -fno-exceptions" ./configure --with-apache=../apache_1.3.37 --with-crt=/etc/ssl/certs/server.crt --with-key=/etc/ssl/private/server.key

*CC – C 编译器

**egcs – 增强GNU边一起

***CFLAGS – O2 (i386), O3 (i586), O9 (i686) 优化选项

PHP 4

view sourceprint?1[root@localhost]# cd apache_1.3.37

Configure

view sourceprint?01[root@localhost]# CC="egcs" OPTIM="-O9 -funroll-loops -ffast-math -malign-double -mcpu=pentiumpro -march=pentiumpro -fomit-frame-pointer -fno-exceptions" CFLAGS="-DDYNAMIC_MODULE_LIMIT=0"02./configure 03??04--prefix=/home/httpd 05??06--bindir=/usr/bin 07??08--sbindir=/usr/sbin 09??10--libexecdir=/usr/lib/apache 11??12--includedir=/usr/include/apache 13??14--sysconfdir=/etc/httpd/conf 15??16--localstatedir=/var 17??18--runtimedir=/var/run 19??20--logfiledir=/var/log/httpd 21??22--datadir=/home/httpd 23??24--proxycachedir=/var/cache/httpd 25??26--mandir=/usr/man 27??28--proxycachedir=/var/cache/httpd 29??30--mandir=/usr/man 31??32[root@deep ]/php-4.0# make && make install

Mod_PERL

view sourceprint?1[root@localhost] cd ../mod_perl-version.version/ 2??3[root@localhost] perl Makefile.PL EVERYTHING=1 APACHE_SRC=../apache_1.3.37/src USE_APACI=1 PREP_HTTPD=1 DO_HTTPD=14??5[root@localhost]/mod_perl-version.version# make && make install

Apache

view sourceprint?011. [root@localhost]/apache1.3.37# SSL_BASE=SYSTEM EAPI_MM=SYSTEM CC="egcs" OPTIM="-O9 -funroll-loops -ffast-math -malign-double -mcpu=pentiumpro -march=pentiumpro -fomit-frame－pointer -fno-exceptions" CFLAGS="- DDYNAMIC_MODULE_LIMIT=0"02??03./configure 04??05--prefix=/home/httpd 06??07--bindir=/usr/bin 08??09--sbindir=/usr/sbin 10??11--libexecdir=/usr/lib/apache 12??13--includedir=/usr/include/apache 14??15--sysconfdir=/etc/httpd/conf 16??17--localstatedir=/var 18??19--runtimedir=/var/run 20??21--logfiledir=/var/log/httpd 22??23--datadir=/home/httpd 24??25--proxycachedir=/var/cache/httpd 26??27--mandir=/usr/man 28??29--add-module=src/modules/experimental/mod_mmap_static.c 30??31--add-module=src/modules/standard/mod_auth_db.c 32??33--enable-module=ssl 34??35--enable-rule=SSL_SDBM 36??37--disable-rule=SSL_COMPAT 38??39--activate-module=src/modules/php4/libphp4.a 40??41--enable-module=php442??43--activate-module=src/modules/perl/libperl.a 44??45--enable-module=perl 46??47--disable-module=status 48??49--disable-module=userdir 50??51--disable-module=negotiation 52??53--disable-module=autoindex 54??55--disable-module=asis 56??57--disable-module=imap 58??59--disable-module=env 60??61--disable-module=actions 62??63[root@localhost]/apache1.3.37# make && make install

安装步骤二

Chroot Jail

chroot一个能让你改变一个系统的root权限到另外一个地方，所以从入侵者来说是一个事实的root系统将很安全。
优点

1.

apache默认是运行于一个非root帐户，作为一个拥有本地shell的普通帐户将限制一些危险的操作。
2.

主要的好处是，jail作为文件的一部分，后台程序在这个jail中将视作为根目录
3.

jail只需要支持apache，在jail中程序的有效性将被极大的限制。
4.

不再需要setuid-root程序。
5.

如果apache被侵害，攻击者将不能访问真实的文件系统
6.

允许访问你服务器的CGI脚本将不能执行
7.

有一些扩展的库你需要在chroot jail中为了使apache能够执行
8.

如果你使用任何Perl/CGI特性在apache中，你需要拷贝必须的二进制文件,Perl库和在chroot环境中适当的文件。如一些支持SSL，PHP，LDAP，PostgreSQL以及其他的第三方程序。

寻找依赖

view sourceprint?01[root@localhost]/# ldd /usr/sbin/httpd 02??03libpam.so.0 =>/lib/libpam.so.0 (0x40016000) 04??05libm.so.6 =>/lib/libm.so.6 (0x4001f000) 06??07libdl.so.2 =>/lib/libdl.so.2 (0x4003b000) 08??09libcrypt.so.1 =>/lib/libcrypt.so.1 (0x4003e000) 10??11libnsl.so.1 =>/lib/libnsl.so.1 (0x4006b000) 12??13libresolv.so.2 =>/lib/libresolv.so.2 (0x40081000) 14??15libdb.so.3 =>/lib/libdb.so.3 (0x40090000) 16??17libc.so.6 =>/lib/libc.so.6 (0x400cb000) 18??19/lib/ld-linux.so.2 =>/lib/ld-linux.so.2 (0x40000000)

创建目录结构 (像依赖的一样)

view sourceprint?01[root@localhost ]/# mkdir /chroot/httpd 02??03[root@localhost ]/# mkdir /chroot/httpd/dev 04??05[root@localhost ]/# mkdir /chroot/httpd/lib 06??07[root@localhost ]/# mkdir /chroot/httpd/etc 08??09[root@localhost ]/# mkdir -p /chroot/httpd/usr/sbin 10??11[root@localhost ]/# mkdir -p /chroot/httpd/var/run 12??13[root@localhost ]/# mkdir -p /chroot/httpd/var/log/httpd 14??15[root@localhost ]/# chmod 75016??17/chroot/httpd/var/log/httpd/ 18??19[root@localhost ]/# mkdir -p /chroot/httpd/home/httpd

拷贝所有发现的依赖文件（包括相应权限）到所创建的目录中

view sourceprint?1[root@localhost ]/# cp -r /etc/ssl /chroot/httpd/etc/ 如果你需要mod_ssl所必须的 2??3[root@localhost ]/# chmod 600 /chroot/httpd/etc/ssl/certs/ca.crt 如果你需要mod_ssl所必须的 4??5[root@localhost ]/# chmod 600 /chroot/httpd//etc/ssl/certs/server.crt 如果你需要mod_ssl所必须的 6??7[root@localhost ]/# chmod 600 /chroot/httpd/etc/ssl/private/ca.key 如果你需要mod_ssl所必须的 8??9[root@localhost ]/# chmod 600 /chroot/httpd/etc/ssl/private/server.key 如果你需要mod_ssl所必须的

使用.dbmpasswd密码文件进行用户认证

改变dbmmanage程序的权限，使用以下命令

view sourceprint?1[root@localhost ]/# chmod 750 /usr/bin/dbmmanage

创建一个用户名和密码使用以下命令

view sourceprint?1[root@deep ]/# /usr/bin/dbmmanage /etc/httpd/.dbmpasswd adduser username 2??3New password: 4??5Re-type new password: 6??7User username added with password encrypted to l4jrdAL9MH0K.

拷贝/etc/passwd /etc/group文件到/chroot/httpd/etc下，同时移出所有帐户和组出了apache运行所依赖的

view sourceprint?1[root@localhost ]/# cp /etc/passwd /chroot/httpd/etc/ 2??3[root@localhost ]/# cp /etc/group /chroot/httpd/etc/

编辑passwd文件

view sourceprint?1vi /chroot/httpd/etc/passwd 2??3www:x:80:80::/home/www:/bin/bash

编辑passwd文件

view sourceprint?1vi /chroot/httpd/etc/group 2??3www:x:80:

你还需要/etc/resolv.conf文件和/etc/nsswitch.conf以及/etc/hosts文件在你的chroot jail中相同的目录结构来解析主机名。

第三步 安全的CGI应用程序

配置PHP

增加以下行到httpd.conf中

view sourceprint?1AddModule mod_php4.c 2??3AddType application/x-httpd-php .php 4??5AddType application/x-httpd-php .inc 6??7AddType application/x-httpd-php .class

修改PHP配置文件(/chroot/httpd/usr/local/lib/php.ini)

view sourceprint?01Parameter Description 02??03safe_mode = On 04??05safe_mode_gid = Off 06??07open_basedir = directory[:...] (只有这个目录脚本文件才能使用) 08??09safe_mode_exec_dir = directory[:...] (apache拒绝执行程序目录) 10??11expose_php = Off (不要显示php info) 12??13register_globals = Off 14??15display_errors = Off 16??17log_errors = On 18??19error_log = filename

修改文件扩展名

把所有*.php文件改为*.dhtml（举例）,所以需要修改/chroot/httpd/usr/local/apache/conf/httpd.conf文件增加以下

view sourceprint?1AddType application/x-httpd-php .php 2??3AddType application/x-httpd-php .dhtml

web用户将看不到*.php扩展名在url地址中，实际已经PHP技术已经被使用在服务器上。

The last step – Defending against CSS and SQL Injection attacks

In order to perform that, we will use the mod_security module, which we enable by

adding the following line into httpd.conf:

view sourceprint?1AddModule mod_security.c

To enable logging of the GET and POST requests, it suffices to add the following section

to httpd.conf:

view sourceprint?1AddHandler application/x-httpd-php .php 2??3SecAuditEngine On 4??5SecAuditLog logs/audit_log 6??7SecFilterScanPOST On 8??9SecFilterEngine On

最后一步－－防御拒绝CSS和SQL注入攻击

为了实现这个功能，我们将使用mod_security模块，所以我们需要加入下面这行在httpd.conf中

view sourceprint?1AddModule mod_security.c

为了实现记录GET和POST请求，需要在httpd.conf增加下面这段

view sourceprint?1AddHandler application/x-httpd-php .php 2??3SecAuditEngine On 4??5SecAuditLog logs/audit_log 6??7SecFilterScanPOST On 8??9SecFilterEngine On

超出的命令需要audit引擎来记录，如登录请求；以及过滤POST引擎，它能记录POST请求到日志中。为了防止web应用程序被CSS攻击，以下这几行也要被加入到之前

view sourceprint?1SecFilterDefaultAction "deny,log,status:500"2??3SecFilter "“

第一行是用来当请求包含任何安全过滤变量的搜索断语将返回“服务器内部错误”

第二行在搜索HTML标记的GET和POST请求中建立过滤

一个标准的SQL注入攻击是在GET和POST请求中包含”‘”（单引号）和”"”（双引号）。为了句句所有包含这些字符的请求，我们标记SQL注入技术非常困难。

SecFilter “‘”

SecFilter “”"

尽管过滤像<,>,’,”这些字符让我们防护拒绝CSS和SQL注入攻击，它将导致PHP应用程序中不正确的函数。它的发生，是由于合法的用户将不能使用这些字符在HTML表单中。为了解决这个问题，JavaScript语言使得在客户端执行用来代替这些被禁止的特殊标记像< > ” 等等

小结

要在使用服务器段技术（PHP，ASP，JSP等）的服务器安全是非常困难的在实际中

理由如下：

1.

服务器自己不能反对糟糕的编程。
2.

服务器段技术（PHP，ASP，JSP）的弱点也能制造web服务器攻击倾向。
3.

为了web服务器更安全，打上所有的安全补丁和移出不必要的文件是必须的。
4.

我们不能忘记整个环境的安全不仅依赖apache和php的配置，更依赖于web应用程序的自身。
5.

程序员需要分析代码和程序在被实施到正式服务器之前。
6.

日志的合理监控和CGI行为必须在没有攻击的情况下长久运行在服务器上

查看更多 下一篇