高性能 MySQL（一）优化服务器设置.pdfVIP

高性能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_writte