无惧双十二Or 黑五，这些 MySQL 性能调优技巧看过来

摘要：针对购物旺季网站流量会对数据库造成的压力，作者给出了 MySQL 性能调优的一些技巧，这些技巧极具参考价值，通过这些调优，可以有效避免因为流量过大造成服务器宕机，从而给企业造成经济损失。以下是译文

万圣节已经过去很久了，该是把注意力集中在即将到来的假日季节的时候了。首先是感恩节，接着就是黑色星期五和网络星期一，最终在圣诞节 / 节礼周（从 12 月 26 日的节礼日开始，到 12 月 31 日的除夕结束为期六天或更长时间。这个词是由零售业在 2000 年代中期左右发明的，试图延长他们的节礼日销售）达到购物高潮。对于企业主来说，一年的这个时候标志着人们期待已久的年底获利了结。对于一些 DBA 来说，它会带来恐惧，不安，甚至是不眠之夜，他们要努力使系统重新上线。

值得庆幸是，情况并非如此。通过对 MySQL 性能变量做一些主动调整，可以使数据库服务器免受购物旺季带来的需求增加的冲击。

技巧 #1：确定 MySQL 的最大连接数

对于 MySQL 的最大连接数，一次最好是发送 5 个请求到 Web 服务器。对 Web 服务器的 5 个请求中的一部分将用于 CSS 样式表，图像和脚本等资源。由于诸如浏览器缓存等原因，要获得准确的 MySQL 到 Web 服务器的请求比率可能很困难; 要想得到一个确切的数字，就需要分析 Web 服务器的日志文件。例如，可以手动访问 Apache 的 “access_log” 日志文件，也可以通过 Analog 或 Webalizer 等实用程序访问日志文件。

一旦有了对特定使用情况的准确估计，请将该比率乘以 Web 服务器的最大连接数。例如，如果 Web 服务器配置为最多为 256 个客户端提供服务，MySQL 请求与 Web 请求的比率为 1/8，则最好将最大数据库连接数设置为 32。还要考虑留有安全余量，把这个数乘以 2，得到最终的数量。只有在基础设施支持的情况下，才能尝试将数据库连接数的最大数量与 Web 服务器的客户端限制相匹配。在大多数情况下，最好保持接近 32。

在 Monyog 中查看 MySQL 连接

在 MySQL 数据库中，MySQL 的最大并发连接数是存储在全局变量 max_connections 中的。Monyog 报告变量 “max_connections” 作为当前连接监控组中的 “最大允许” 指标。它还将该数字除以打开的连接数，以生成连接使用百分比：

还有一个连接历史记录监控，可以帮助计算最佳的最大并发连接数。它包括尝试，拒绝和成功连接的数量。此外，允许达到的最大指标的百分比显示为一个进度条，可以让你快速评估服务器在过去达到的最大并发连接数：

技巧 #2：为临时表分配足够的内存

在某些情况下，服务器在处理语句时会创建内部临时表。临时表用于内部操作如 GROUP BY 和 distinct，还有一些 ORDER BY 查询以及 UNION 和 FROM 子句（派生表）中的子查询。这些都是在内存中创建的内存表。内存中临时表的最大大小由 tmp_table_size 和 max_heap_table_size 中较小的值确定。如果临时表的大小超过这个阈值，则将其转换为磁盘上的 InnoDB 或 MyISAM 表。此外，如果查询涉及 BLOB 或 TEXT 列，而这些列不能存储在内存表中，临时表总是直接指向磁盘。

这种转换的代价很大，所以考虑增加 max_heap_table_size 和 tmp_table_size 变量的大小来帮助减少在磁盘上创建临时表的数量。请记住，这将需要大量内存，因为内存中临时表的大小是基于 “最坏情况” 的。例如，内存表总是使用固定长度的列，所以字符列使用 VARCHAR（255）。这可以使内存中的临时表比想象的要大得多—事实上，这比查询表的总大小要大很多倍！当增加 max_heap_table_size 和 tmp_table_sizevariables 的大小时，一定要监视服务器的内存使用情况，因为内存中的临时表可能会增加达到服务器内存容量的风险。

一般来说，32M 到 64M 是建议值，从这两个变量开始并根据需要进行调优。

在 Monyog 中的临时表监测

临时表的监测是许多预定义的 Monyog 监测之一。它提供了一些临时表使用的指标，包括：

允许的最大值：显示 tmp_table_size 服务器变量的值，它定义了在内存中创建的临时表的最大大小。与 max_heap_table_size 一起，这个值定义了可以在内存中创建的临时表的最大大小。如果内存临时表大于此大小，则将其存储在磁盘上。

内存表的最大大小：显示 max_heap_table_size 服务器变量的值，该值定义了显式创建的 MEMORY 存储引擎表的最大大小。

创建的临时表总数：显示 created_tmp_tables 服务器变量的值，它定义了在内存中创建的临时表的数量。

在磁盘上创建的临时表：显示 created_tmp_disk_tables 服务器变量的值，该变量定义了在磁盘上创建的临时表的数量。如果这个值很高，则应该考虑增加 tmp_table_size 和 max_heap_table_size 的值，以便增加创建内存临时表的数量，从而减少在磁盘上创建临时表的数量。

磁盘：总比率：基于 created_tmp_disk_tables 除以 created_tmp_tables 的计算值。由于 tmp_table_size 或 max_heap_table_size 不足而在磁盘上创建的临时表的百分比。Monyog 将这个数字显示为一个进度条和百分比，以便快速确定有多少磁盘用于临时表，而不是内存。

趋势图可用于创建的总表，磁盘上创建的表和磁盘的总比值。这些让我们看到了它们随着时间的演变：

技巧 #3：增加线程缓存大小

连接管理器线程处理服务器监听的网络接口上的客户端连接请求。连接管理器线程将每个客户端连接与专用于它的线程关联，该线程负责处理该连接的身份验证和所有请求处理。因此，线程和当前连接的客户端之间是一对一的比例。确保线程缓存足够大以容纳所有传入请求是非常重要的。

MySQL 提供了许多与连接线程相关的服务器变量：

线程缓存大小由 thread_cache_size 系统变量决定。默认值为 0（无缓存），这将导致为每个新连接设置一个线程，并在连接终止时需要处理该线程。如果希望服务器每秒接收数百个连接请求，那么应该将 thread_cache_size 设置的足够高，以便大多数新连接可以使用缓存线程。可以在服务器启动或运行时设置 max_connections 的值。

还应该监视缓存中的线程数（Threads_cached）以及创建了多少个线程，因为无法从缓存中获取线程（Threads_created）。关于后者，如果 Threads_created 继续以每分钟多于几个线程的增加，请考虑增加 thread_cache_size 的值。

使用 MySQL show status 命令显示 MySQL 的变量和状态信息。这里有几个例子：

Monyog 线程缓存监测

Monyog 提供了一个监控线程缓存的屏幕，名为 “线程”。与 MySQL 线程相关的服务器变量映射到以下 Monyog 指标：

thread_cache_size：可以缓存的线程数。

Threads_cached：缓存中的线程数。

Threads_created：创建用于处理连接的线程。

Monyog 线程屏幕还包括 “线程缓存命中率” 指标。这是一个提示线程缓存命中率的指标。如果值较低，则应该考虑增加线程缓存。在状态栏以百分比形式显示该值；它的值越接近 100％越好。

如果这些指标的值等于或超过指定值，则可以将每一个指标配置为发出警告和 / 或严重警报。

其他相关的服务器变量

除了上述指标以外，还应该监控以下内容：

InnoDB 缓冲池大小：InnoDB 缓冲池大小在使用 InnoDB 的 MySQL 数据库中起着至关重要的作用。缓冲池同时缓存数据和索引。它的值应该尽可能的大，以确保数据库使用内存而不是硬盘驱动器进行读取操作。

临时表大小：MySQL 使用 max_heap_table_size 和 tmp_table_size 中较小的一个来限制内存中临时表的大小。拥有较大的值可以帮助减少在磁盘上创建临时表的数量，但也会增加服务器内存容量的风险，因为这个指标适用于每个客户端。一般来说，32M 到 64M 是建议的值，从这两个变量开始并根据需要进行调优。

InnoDB 日志缓冲区大小：MySQL 每次写入日志文件时，它都会利用可用于处理销售数据的重要系统资源。因此，将 InnoDB 日志缓冲区大小设置为较大值才有意义。这样，服务器在大型事务中写入磁盘的次数就减少了，从而最大限度地减少了这些耗时的操作。64M 是这个变量的一个很好的起点。

结论

虽然即便是最大的公司网站也会因宕机而遭受损失，但这种影响对于处理网上销售的中小型企业尤其关键。根据最近的一份调查报告显示，一分钟的宕机导致企业平均损失约 5000 美元。不要让你的业务成为那种统计数据（因为宕机造成的损失）的一部分。在假日繁忙之前，主动调优 MySQL 数据库服务器（S）并收获回报吧！

转自：ITeye

原文地址：http://www.iteye.com/news/32796