MySQL优化服务器设置（优化mysql服务器硬件包括）

MySQL有大量可以修改的参数——但不应该随便去修改。通常只需要把基本的配置项配置正确（大部分情况下只有很少一些参数是真正重要的），应该将更多的时间花在schema的优化、索引，以及查询设计上。

确保基本的配置是正确的，如果碰到了问题，并且问题是由于服务器的某部分导致的，而这恰好可以通过某个配置项解决，那么需要做的就是更改配置。

MySQL基本配置

InnoDB在大多数情况下如果要运行的很好，配置大小合适的缓冲池(Buffer Pool)和日志文件(Log File)是必须的。默认值都太小了。其他所有的InnoDB设置都是可选的。

我们建议，当配置内存缓冲区的时候，宁可谨慎，而不是把它们配置的过大。如果把缓冲池配置得比它可设的值少了20%，很可能只会对性能产生小的影响，也许就只影响几个百分点。如果设置得大了20%，则可能会造成更严重的问题：内存交换、磁盘抖动，甚至内存耗尽和硬件死机。

配置内存使用

可以按下面的步骤来配置内存：

1、确定可以使用的内存上限。

2、确定每个连接MySQL需要使用多少内存，例如排序缓冲和临时表。

3、确定操作系统需要多少内存。包括同一台机器上其他程序使用的内存。

4、把剩下的内存全部给MySQL的缓存，例如InnoDB缓冲池，这样做很有意义。

为缓存分配内存

下面是我们认为大部分情况来说最重要的缓冲：

InnoDB缓冲池(innodb_buffer_pool_size)

InnoDB日志文件(innodb_log_buffer_size)和MyISAM数据的操作系统缓存(key_buffer_size)

MyISAM键缓存(myisam_block_size)

线程缓存(thread_cache_size)

查询缓存(query_cache_size)

无法手工配置的缓存，例如二进制日志和表定义文件的操作系统缓存

InnoDB缓冲池

如果大部分都是InnoDB表，InnobDB缓冲池或许比其他任何东西更需要内存。

如果数据量不大，并且不会快速增长，就没必要为缓冲池分配过多的内存。

很大的缓冲池也会带来一些挑战，例如，预热和关闭都会花费很长的时间。如果有很多脏页在缓冲池里，InnoDB关闭时可能会花费较长的时间，因为关闭之前需要把脏页写回数据文件。也可以强制快速关闭，但是重启时就必须多做更多的恢复工作，也就是说无法同时加速关闭和重启两个动作。如果事先知道什么时候需要关闭InnoDB，可以在运行时修改innodb_max_dirty_pages_pct变量，将值改小，等待刷新线程清理缓冲池，然后在脏页数量较少时关闭。可以监控innodb_buffer_pool_pages_dirty状态变量或者SHOW INNODB STATUS来观察脏页的刷新量。

更小的innodb_max_dirty_pages_pct变量值并不保证InnoDB将在缓冲池中保持更少的脏页。他只是控制InnoDB是否可以“偷懒（Lazy）”的阈值。InnoDB默认通过一个后台线程来刷新脏页，并且会合并写入，更高效地顺序写出磁盘。当脏页的百分比超过了这个阈值，InnoDB将快速地刷写脏页，尝试让脏页的数量更低。当事务日志没有足够的空间剩余时，InnoDB也将进入“激烈刷写(Furious Flushing)”模式，这就是大日志可以提升性能的一个原因。

InnoDB事务日志

InnoDB使用多个文件作为一组循环日志。通常不需要修改默认的日志数量，只修改每个日志文件的大小即可。

要确定理想的日志文件大小，必须权衡正常数据变更的开销和崩溃需要的时间。如果日志太小，InnoDB将必须做更多的检查点，导致更多的日志写。在极个别情况下，写语句可能被拖累，在日志没有空间继续写入前，必须等待变更应用到数据文件。另一方面，如果日志太大了，在崩溃恢复时InnoDB可能不得不做大量的工作。这可能极大地增加恢复时间。

当InnoDB变更任何数据时，会写一条变更记录到内存日志缓冲区。在缓冲满的时候、事务提交的时候，或者每一秒钟，InnoDB都会刷写缓冲区的内容到磁盘日志文件。如果有大事务，增加日志缓冲区（默认1MB）大小可以帮助减少I/O。变量innodb_log_buffer_size可以控制日志缓冲区的大小。

可以通过检查SHOW INNODB STATUS的输出中LOG部分来监控InnoDB的日志和日志缓冲区的I/O性能，通过观察Innodb_os_log_written状态变量来查看InnoDB对日志文件写出了多少数据。一个好的经验法则是，查看10~100秒间隔的数字，然后记录峰值。

日志缓冲必须被刷新到持久化存储，以确保提交的事务完全被持久化了。如果和持久化相比更在乎性能，可以修改innodb_flush_log_at _trx_commit变量来控制日志缓冲刷新的频繁程度。可能的设置如下：

0

把日志缓冲写到日志文件，并且每秒刷新一次，但是事务提交时不做任何事。

1

将日志缓冲写到日志文件，并且每次事务提交都刷新到持久化存储。

2

每次提交时把日志缓冲写到日志文件，但是并不刷新，InnoDB每秒做一次刷新。

0与2最重重要的不同是，如果MySQL进程“挂了”，2不会丢失任何事务。如果整个服务器“挂了”或者断电了，则还是可能会丢失一些事务。

高性能事务处理需要的最佳配置是把innodb_flush_log_at_trx_commit设置为1且把日志文件放到一个有电池保护的写缓存的RAID券中。这兼顾了安全和速度。事实上，我们敢说任何希望能扛过高负载的产品数据库服务器，都需要这种类型的硬件。

（注：在测试环境可以将innodb_flush_log_at_trx_commit设为0，这个速度同上面=1时的RAID缓存的情况差不多）

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

使用innodb_flush_method选项可以配置InnoDB如何跟文件系统相互作用。它既影响日志文件，也影响数据文件，而且有时候对不同类型的文件的处理也不一样。

下面是一些可能的值：

fdatasync

这是非Windows系统上的默认值：InnoDB用fsync()来刷新数据文件和日志文件。使用fsync()的缺点是操作系统至少会在自己的缓存中缓冲一些数据。理论上，这种双重缓冲是浪费的。因为InnoDB管理自己的缓冲比操作系统能做的更加智能。

O_DIRECT

这个设置并不影响日志文件，它主要是影响数据文件的写操作，不象O_DSYNC标记同时影响读和写。

这个设置依然使用fsync()来刷新文件到磁盘，但是会通知操作系统不要缓存数据，也不要预读。这个选项完全关闭了操作系统缓存，并且使所有的读和写都直接通过存储设备，避免了双重缓冲。

如果使用O_DIRECT选项，通常需要带有写缓存的RAID卡，并且设置为Write-Back策略，因为这是典型的唯一能保持好性能的方法。当InnoDB和实际存储设备之间没有缓冲时使用O_DIRECT，例如当RAID卡没有写缓存时，可能导致严重的性能下降。

O_DSYNC

它使得所有写同步，就是只有数据写到磁盘后写操作才返回，这个选项不影响数据文件。

O_DSYNC和O_DIRECT的不同之处在于O_DSYNC没有禁用操作系统层的缓存。因此，它没有避免双重缓冲，并且它没有使写操作直接操作到磁盘。用了O_DSYNC标记，也还是要在缓冲中写数据，然后发送到磁盘。

InnoDB表空间

Innodb_file_per_table选项让InnoDB为每张表使用一个文件，它在数据字典存储为“表名.idb”的数据。

即使打开Innodb_file_per_table选项，依然需要为回滚日志和其他系统数据创建共享表空间。

我们建议使用Innodb_file_per_table并且为共享表空间设置大小范围，这样可以过得舒服点。

如果有个很大的回滚日志并且表空间因此增长很快，可以强制MySQL减速来使InnoDB的清理线程可以跟得上。可以设置innodb_max_purge_lag变量为一个大于0的值。这个值表示InnoDB开始延迟后面的语句更新数据之前，可以等待被清除的最大的事务数量。例如：事务平均影响1KB的行，并且可以容许表空间里有100M的未清理，那么可以设置这个值为100000。设置innodb_max_purge_lag也会降低性能，但是它的伤害较少。

双写缓冲（Doublewrite Buffer）

双写缓冲是表空间一个特殊的保留区域，在一些连续的块中足够保存100个页。本质上是一个最近写回的页面的备份拷贝。当InnoDB从缓冲池刷新页面到磁盘时，首先把它们写（或者刷新）到双写缓冲，然后再把它们写到其所属的数据区域中。这可以保证每个页面的写入都是原子并且持久的。

有些场景下，双写缓冲确实没必要，例如，你也许想在备库上禁止双写缓冲。此外一些文件系统（例如ZES）做了同样的事，所以没必要再让InnoDB做一遍。可以通过设置innodb_doublewrite为0来关闭双写缓冲。

二进制日志

sync_binlog选项控制MySQL怎么刷新二进制日志到磁盘。默认值是0，意味着MySQL并不刷新，由操作系统自己决定什么时候刷新缓存到持久化设备。如果这个值比0大，它指定了两次刷新到磁盘的动作之间间隔多少次二进制日志写操作（如果autocommit被设置了，每个独立的语句都是一次写，否则就是一个事务一次写）。把它设置为0和1以外的值是很罕见的。

像InnoDB日志文件一样，设置sync_binlog=1并把二进制日志放到一个带有电池保护的写缓存的RAID券，可以极大地提升性能并保证日志的持久化。事实上写和刷新二进制日志缓存其实比InnoDB事务日志要昂贵多了，因为不像InnoDB事务日志（事务日志文件大小是预分配的，而且是group commit），每次写二进制日志都会增加他们的大小，而且在sync_binlog=1的情况下是每个事务都要写一次。这需要每次写入文件系统都更新元信息。所以，设置sync_binlog=1可能比innodb_flush_log_at_trx_commit=1对性能的损害要大的多。

把带有电池保护写缓存的高质量RAID控制器设置为使用写回（Writeback）策略，可以支持每秒数千的写入，并且依然会保证写到持久化存储。数据写到了带有电池的高速缓存，所以即使系统断电它也能存在。但电源恢复时，RAID控制器会在磁盘被设置为可用前，把数据从缓存中写到磁盘。因此，一个带有电池保护写缓存的RAID控制器可以显著地提升性能，这是非常值得投资的。

MyISAM的I/O配置

MyISAM通常每次写操作之后把索引变更刷新到磁盘。如你打算在一张表上做很多修改，那么毫无疑问，批量操作会更块一些。一种办法是用LOCK_TABLES延迟写入，直到解锁这个表。这是个提升性能的很有价值的技巧。

InnoDB并发配置

在高并发场景下，InnoDB的某些方面的性能可能会降低，唯一的办法是限制并发。如果是高版本的MySQL，在大部分场景都不再需要限制并发。

最基本的限制并发的方式是使用innodb_thread_concurrency变量，它会限制一次性可以有多少线程进入内核，0表示不限制，理论上，下面的公式可以给出一个这样的值：并发值 = CPU数量 * 磁盘数量 * 2。