MySQL之优化服务器设置(二)

优化服务器设置

InnoDB事务日志(包含:Redo log 重做日志和Undo log回滚日志)

了解清楚"把日志缓冲写到日中文件"和"把日志刷新到持久化存储"之间的不同是很重要的。在大部分操作系统中，把缓冲写到日志只是简单地把数据从InnoDB的内存缓冲转移到了操作系统的缓存，也是在内存里，并没有真的把数据写到持久化存储。因此，如果MySQL崩溃了或者电源断电了，设置0和2通常会导致最多一秒的丢失，因为数据可能只存在于操作系统的缓存。我们说"通常"，因为不论如何InnoDB会每秒尝试刷新日志文件到磁盘，但是在一些场景下也可能丢失超过1秒的事务，例如当刷新被推迟了。
与此相反，把日志刷新到持久化存储意味着InnoDB请求操作系统把数据刷出缓存，并且确认写到磁盘了。这是一个阻塞IO的调用，直到数据被完全写回才会完成。因为写数据到磁盘比较满，当innodb_flush_log_at_trx_commit被设置为1时，可能明显地降低InnoDB每秒可以提交的事务数。今天的高速驱动器可能每秒只能执行一两百个磁盘事务，受限于磁盘旋转速度和寻道时间。有时硬盘控制器或者操作系统假装做了刷新，其实只是把数据放到了另一个缓存，例如磁盘自己的缓存。这更快但是很危险，因为u如果驱动器断电，数据依然可能丢失。者甚至比设置innodb_flush_log_at_trx_commit为不为1的值更糟糕，因为这可能导致数据损坏，不仅仅时丢失事务。
设置innodb_flush_log_at_trx为不为1的值可能导致丢失事务。然而，如果不在意持久性(ACID中的D),那么设置为其他的值也是有用的。也许你只是想拥有InnoDB的其他一些功能，例如聚簇索引、防止数据损坏，以及行锁。但仅仅因为性能原因用InnoDB替换MyISAM的情况也并不少见。
高性能事务处理需要的最佳配置是把innodb_flush_log_at_trx_commit设置为1且把日志i文件放到一个有电池保护的写缓存的RAID卷中。这兼顾了安全和速度。事实上，哦我们敢说任何希望能扛过高负荷工作负载的产品数据库服务器，都需要有这种类型的硬件。

Percona Server扩展了innodb_flush_log_at_trx_commit变量，使得它成为一个会话级变量，而不是一个全局变量。这允许有不同的性能和持久化要求的应用，可以使用同样的数据库，同时又避免了标准MySQL提供的一刀切的解决方案。

InnoDB 怎样打开和刷新日志以及数据文件

使用innodb_flush_method选项可以配置InnoDB如何跟文件系统相互作用。从名字来看，会以为只能影响InnoDB怎么写数据，实际上还影响了InnoDB怎么读数据。Windows和非Windows的操作系统对这个选项的值是互斥的:async_unbuffered、unbuffered和norm只能在Windows下使用，并且Windows下不能使用其他的值。在Windows下默认值是unbuffered，其他操作系统都是fdatasync(如果SHOW GLOBAL VARIABLES 显式这个变量为空，意味着它被设置为默认值了)。
改变Innodb执行IO操作的方式可以显著地影响性能，所以请确认你明白了在做什么后再去做改动。
这是个有点难以理解的选项，因为它既影响日志文件，也应该想数据文件，而且有时候对不同类型的文件的处理也不一样。如果有一个选项来配置日志，另一个选项来配置数据为念，这样最好了，但实际上它们混合在同一个配置项中。下面是一些可能的值:

• 1.fdatasync
  这在非Windows系统上是默认值:InnoDB用fsync()来刷新数据和日志文件。InnoDB通常用fsync()代替fdatasync()，即使这个值似乎表达的是相反的意思。fdatasync()跟fsync()相似，但是只刷新文件的数据，而不包括元数据(最后修改时间，等等)。因此,fsync()会导致更多的IO,然而InnoDB的开发者都很保守，它们发现某些场景下fdatasync()会导致数据损坏。InnoDB决定了哪些方法可以安全地使用，有一些是编译时设置的，也有一些是运行时设置的。它使用尽可能最快的安全方法。使用fsync()的缺点是操作系统至少会在自己的缓存中缓冲一些苏剧。理论上，这种双重缓冲是浪费的，因为InnoDB管理自己的缓冲比操作系统能做的更加智能。然而，最后的影响跟操作系统和文件系统非常相关。如果能让文件系统做更智能的IO调度和批量操作，双重缓冲可能并不是坏事。有的文件系统和操作系统可以积累写操作后合并执行，通过对IO重新排序来提升效率，或者并发写入多个设备。它们也可能做预读优化，例如，若连续请求了几个顺序的块，它会通知硬盘预读下一个块。有时这些优化有帮助，有时没有。如果你好奇你的系统中的fsync()会做哪些具体的事，可以阅读系统的帮助手册，看下fsync(2).innodb_file_per_table选项会导致每个文件独立地做fsync()，这意味着些多个表不能合并到一个IO操作。这可能导致InnoDB执行更多的fsync()操作
• 2.0_DIRECT
  InnoDB对数据文件使用0_DIRECT标记或directio()函数，这依赖于操作系统。这个设置并不影响文件并且不是在所有的类UNIX系统上都有效。但至少GNU/Linux、FreeBSD。以及Solaris(5.0以后的新版本)是支持的。不像0_DSYNC标记，它会同时影响读和写。这个设置依然使用fsync()来刷新文件到磁盘，但是会通知操作系统不要缓存数据，也不要使用预读。这个选项完全关闭了操作系统缓存，并且使所有的读和写都直接通过存储设备，避免了双重缓冲。在大部分系统上，这个实现用fcntl()调用来设置文件描述符的0_DIRECT标记，所以可以阅读fcntl(2)的手册页来了解系统上这个函数的细节。在Solaris系统，这个选项用directio().如果RAID卡支持预读，这个设置不会关闭RAID卡的预读。这个设置只能关闭操作系统和文件系统的预读(RAID卡的预读控制必须在RAID卡的设置中调整)。如果使用0_DIRECT选项，通常需要带有写缓存的RAID卡，并且设置为Write-Back策略(就是写入会在RAID卡缓存上进行缓冲，不直接写道硬盘)因为这是典型的唯一能保持好性能的方法。当InnoDB和实际存储设备之间没有缓冲时使用0_DIRECT，例如当RAID卡没有写缓存时，可能导致严重的性能下降。现在有了多个写线程，这个问题稍微小一点(并且MySQL5.5提供了原生异步IO)但是通常还是有问题。这个选项可能导致服务器预热时间变长，特别时草走系统的缓存很大的时候，也可能导致小容量的缓存池(例如，默认大小的缓冲池)比缓冲IO(Buffered IO)方式操作要慢的多。这是因为操作系统不会通过保持更多数据在自己的缓存中来"帮助"提升性能.如果需要的数据不在缓冲池，InnoDB将不得不直接从磁盘读取。这个选项不会对innodb_file_per_table产生任何额外的损失。相反，如果不用innodb_file_per_table，当使用0_DIRECT时，可能由于一些顺序IO而遭受性能损失。这种情况的发生是因为一些文件系统(包括Linux所有的ext文件系统)每个inode有一个Mutex。当在这些文件系统上使用0_DIRECT时，确实需要打开innodb_file_per_table
• 3.0_DSYNC
  这个选项使用日志文件调用open()函数时设置0_SYNC标记。它使得所有的写同步——换个说法，只有数据写道磁盘后写操作才返回。这个选项不影响数据文件。0_SYNC标记和0_DIRECT标记的不同之处在于0_SYNC没有禁用操作系统层的缓存。因此，它没有避免双重缓冲，并且它没有使写操作直接操作到磁盘。用了0_SYNC标记，在缓存中写数据，然后发送到磁盘。使用0_SYNC标记做同步写操作，听起来可能跟fsync()作得事情非常相似，但是它们两个的实现无论在操作系统层还是在硬件层都非常不同。用了0_SYNC标记后，操作系统可能把"使用同步IO"标记下传给硬件层，告诉设备不要使用缓存，另一方面，fsync()告诉操作系统把修改过的缓冲数据刷写到设备上，如果设备支持，紧接着会传递一个指令给设备刷新它自己的缓存，所以，毫无疑问，数据肯定记录在了物理媒介上。另一个不同是，用了0_SYNC的话，每个write()或pwrite()操作都会在函数完成之前把数据同步到磁盘，完成前函数调用是阻塞的。相对来看，不用0_SYNC标记的写入调用fsync()允许写操作积累在缓存(使得每个写更快)，然后一次性刷新所有的数据。再一次吐槽下这个名称，这个选项设置0_SYNC标记，不是0_DSYNC标记，因为InnoDB开发者发现了0_DSYNC的Bug。0_SYNC和0_DSYNC类似于fsync()和fdatasync()，0_SYNC同时同步数据和元数据，但是0_DSYNC只同步数据。
• 4.async_unbuffered
  这是Windows下的默认值。这个选项让InnoDB对大部分写使用没有缓冲的IO;例外是当innodb_flush_log_at_trx_commit设置为2的时候，对日志文件使用缓冲IO。这个选项使得InnoDB在Windows 2000 、XP，以及更新版本中对数据读写都是用操作系统的原生(重叠的)IO.在更老的Windows版本哪种，InnoDB使用自己用多线程模拟的异步IO
• 5.unbuffered
  只对Windows有效。这个选项于async_unbuffered类似，但是不适用原生异步IO
• 6.normal
  只对Windows有效。这个选项让InnoDB不要使用原生异步IO或者无缓冲IO
• 7.Nosync和littersync
  只为开发使用。这个两个选项在文档中没有并且对生产环境来说不安全，不应该使用这个。

如果这些看起来像是一堆不带建议的说明，那么下面是一些建议:如果使用类UNIX操作系统并且RAID控制器带有电池保护的写缓存，建议使用0_DIRECT。如果不是这样，默认值或者0_DIRECT都可能是最好的选择