MySQL 服务器调优

转载本站文章请注明，转载自：扶凯[http://www./]

看到这个文章相当不错,就转过来了,自己学习一下.并小改和总结了一下

http://hi.baidu.com/%CA%AB%D5%B9/blog/item/42dd2e344abe473d5ab5f547.html

利用服务器的几个调优技巧,让 MySQL 服务器飞速运行
如今,开发人员不断地开发和部署使用 LAMP（Linux?、Apache、MySQL 和 PHP/Perl）架构的应用程序.但是,服务器管理员常常对应用程序本身没有什么控制能力,因为应用程序是别人编写的.这份共三部分的系列文章将讨论许多服务器配置问题,这些配置会影响应用程序的性能.本文是本系列文章的第三部分,也是最后一部分,将重点讨论为实现最高效率而对数据库层进行的调优.

关于 MySQL 调优

有 3 种方法可以加快 MySQL 服务器的运行速度,效率从低到高依次为：

替换有问题的硬件.
对 MySQL 进程的设置进行调优.
对查询进行优化.

替换有问题的硬件通常是我们的第一考虑,主要原因是数据库会占用大量资源.不过这种解决方案也就仅限于此了.实际上,您通常可以让中央处理器（CPU）或磁盘速度加倍,也可以让内存增大 4 到 8 倍.

第二种方法是对 MySQL 服务器（也称为 mysqld）进行调优.对这个进程进行调优意味着适当地分配内存,并让 mysqld 了解将会承受何种类型的负载.加快磁盘运行速度不如减少所需的磁盘访问次数.类似地,确保 MySQL 进程正确操作就意味着它花费在服务查询上的时间要多于花费在处理后台任务（如处理临时磁盘表或打开和关闭文件）上的时间.对 mysqld 进行调优是本文的重点.

最好的方法是确保查询已经进行了优化.这意味着对表应用了适当的索引,查询是按照可以充分利用 MySQL 功能的方式来编写的.尽管本文并没有包含查询调优方面的内容（很多著作中已经针对这个主题进行了探讨）,不过它会配置 mysqld 来报告可能需要进行调优的查询.

虽然已经为这些任务指派了次序,但是仍然要注意硬件和 mysqld 的设置以利于适当地调优查询.机器速度慢也就罢了,我曾经见过速度很快的机器在运行设计良好的查询时由于负载过重而失败,因为 mysqld 被大量繁忙的工作所占用而不能服务查询.

记录慢速查询

在一个 SQL 服务器中,数据表都是保存在磁盘上的.索引为服务器提供了一种在表中查找特定数据行的方法,而不用搜索整个表.当必须要搜索整个表时,就称为表扫描.通常来说,您可能只希望获得表中数据的一个子集,因此全表扫描会浪费大量的磁盘 I/O,因此也就会浪费大量时间.当必须对数据进行连接时,这个问题就更加复杂了,因为必须要对连接两端的多行数据进行比较.

当然,表扫描并不总是会带来问题；有时读取整个表反而会比从中挑选出一部分数据更加有效（服务器进程中查询规划器用来作出这些决定）.如果索引的使用效率很低,或者根本就不能使用索引,则会减慢查询速度,而且随着服务器上的负载和表大小的增加,这个问题会变得更加显著.执行时间超过给定时间范围的查询就称为慢速查询.

您可以配置 mysqld 将这些慢速查询记录到适当命名的慢速查询日志中.管理员然后会查看这个日志来帮助他们确定应用程序中有哪些部分需要进一步调查.清单 1 给出了要启用慢速查询日志需要在 my.cnf 中所做的配置.

清单 1. 启用 MySQL 慢速查询日志

[mysqld] 
; enable the slow query log, default 10 seconds 
log-slow-queries 
; log queries taking longer than 5 seconds 
long_query_time = 5 
; log queries that don't use indexes even if they take less than long_query_time 
; MySQL 4.1 and newer only 
log-queries-not-using-indexes

这三个设置一起使用,可以记录执行时间超过 5 秒和没有使用索引的查询.请注意有关 log-queries-not-using-indexes 的警告：您必须使用 MySQL 4.1 或更高版本.慢速查询日志都保存在 MySQL 数据目录中,名为 hostname-slow.log.如果希望使用一个不同的名字或路径,可以在 my.cnf 中使用 log-slow-queries = /new/path/to/file 实现此目的.

阅读慢速查询日志最好是通过 mysqldumpslow 命令进行.指定日志文件的路径,就可以看到一个慢速查询的排序后的列表,并且还显示了它们在日志文件中出现的次数.一个非常有用的特性是 mysqldumpslow 在比较结果之前,会删除任何用户指定的数据,因此对同一个查询的不同调用被计为一次；这可以帮助找出需要工作量最多的查询.

对查询进行缓存

很多 LAMP 应用程序都严重依赖于数据库,但却会反复执行相同的查询.每次执行查询时,数据库都必须要执行相同的工作 —— 对查询进行分析,确定如何执行查询,从磁盘中加载信息,然后将结果返回给客户机.MySQL 有一个特性称为查询缓存,它将（后面会用到的）查询结果保存在内存中.在很多情况下,这会极大地提高性能.不过,问题是查询缓存在默认情况下是禁用的.

将 query_cache_size = 32M 添加到 /etc/my.conf 中可以启用 32MB 的查询缓存.

监视查询缓存

在启用查询缓存之后,重要的是要理解它是否得到了有效的使用.MySQL 有几个可以查看的变量,可以用来了解缓存中的情况.清单 2 给出了缓存的状态.

清单 2. 显示查询缓存的统计信息

mysql> SHOW STATUS LIKE 'qcache%'; 
+————————-+————+ 
| Variable_name           | Value      | 
+————————-+————+ 
| Qcache_free_blocks      | 5216       | 
| Qcache_free_memory      | 14640664   | 
| Qcache_hits             | 2581646882 | 
| Qcache_inserts          | 360210964 | 
| Qcache_lowmem_prunes    | 281680433 | 
| Qcache_not_cached       | 79740667   | 
| Qcache_queries_in_cache | 16927      | 
| Qcache_total_blocks     | 47042      | 
+————————-+————+ 
8 rows in set (0.00 sec)

这些项的解释如表 1 所示.

表 1. MySQL 查询缓存变量变量名说明
Qcache_free_blocks 缓存中相邻内存块的个数.数目大说明可能有碎片.FLUSH QUERY CACHE 会对缓存中的碎片进行整理,从而得到一个空闲块.
Qcache_free_memory 缓存中的空闲内存.
Qcache_hits 每次查询在缓存中命中时就增大.
Qcache_inserts 每次插入一个查询时就增大.命中次数除以插入次数与hits 的次数之和就是命中比率；用 1 减去这个值就是不命中率.在上面这个例子中,大约有 87% 的查询都在缓存中命中.Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;
Qcache_lowmem_prunes 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数.这个数字最好长时间来看；如果这个数字在不断增长,就表示可能碎片非常严重,或者内存很少.（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）.
Qcache_not_cached 不适合进行缓存的查询的数量,通常是由于这些查询不是 SELECT 语句.
Qcache_queries_in_cache 当前缓存的查询（和响应）的数量.
Qcache_total_blocks 缓存中块的数量.
通常,间隔几秒显示这些变量就可以看出区别,这可以帮助确定缓存是否正在有效地使用.运行 FLUSH STATUS 可以重置一些计数器,如果服务器已经运行了一段时间,这会非常有帮助.

使用非常大的查询缓存,期望可以缓存所有东西,这种想法非常诱人.由于 mysqld 必须要对缓存进行维护,例如当内存变得很低时执行剪除,因此服务器可能会在试图管理缓存时而陷入困境.作为一条规则,如果 FLUSH QUERY CACHE 占用了很长时间,那就说明缓存太大了.

强制限制

您可以在 mysqld 中强制一些限制来确保系统负载不会导致资源耗尽的情况出现.清单 3 给出了 my.cnf 中与资源有关的一些重要设置.

清单 3. MySQL 资源设置

set-variable=max_connections=500 
set-variable=wait_timeout=10 
max_connect_errors = 100

连接最大个数是在第一行中进行管理的.与 Apache 中的 MaxClients 类似,其想法是确保只建立服务允许数目的连接.要确定服务器上目前建立过的最大连接数,请执行

SHOW STATUS LIKE 'max_used_connections';

第 2 行告诉 mysqld 终止所有空闲时间超过 10 秒的连接.在 LAMP 应用程序中,连接数据库的时间通常就是 Web 服务器处理请求所花费的时间.有时候,如果负载过重,连接会挂起,并且会占用连接表空间.如果有多个交互用户或使用了到数据库的持久连接,那么将这个值设低一点并不可取！

最后一行是一个安全的方法.如果一个主机在连接到服务器时有问题,并重试很多次后放弃,那么这个主机就会被锁定,直到 FLUSH HOSTS 之后才能运行.默认情况下,10 次失败就足以导致锁定了.将这个值修改为 100 会给服务器足够的时间来从问题中恢复.如果重试 100 次都无法建立连接,那么使用再高的值也不会有太多帮助,可能它根本就无法连接.

缓冲区和缓存

MySQL 支持超过 100 个的可调节设置；但是幸运的是,掌握少数几个就可以满足大部分需要.查找这些设置的正确值可以通过 SHOW STATUS 命令查看状态变量,从中可以确定 mysqld 的运作情况是否符合我们的预期.给缓冲区和缓存分配的内存不能超过系统中的现有内存,因此调优通常都需要进行一些妥协.

MySQL 可调节设置可以应用于整个 mysqld 进程,也可以应用于单个客户机会话.

服务器端的设置

每个表都可以表示为磁盘上的一个文件,必须先打开,后读取.为了加快从文件中读取数据的过程,mysqld 对这些打开文件进行了缓存,其最大数目由 /etc/mysqld.conf 中的 table_cache 指定.清单 4 给出了显示与打开表有关的活动的方式.

清单 4. 显示打开表的活动

mysql> SHOW STATUS LIKE 'open%tables'; 
+—————+——-+ 
| Variable_name | Value | 
+—————+——-+ 
| Open_tables   | 5000 | 
| Opened_tables | 195   | 
+—————+——-+ 
2 rows in set (0.00 sec)

清单 4 说明目前有 5,000 个表是打开的,有 195 个表需要打开,因为现在缓存中已经没有可用文件描述符了（由于统计信息在前面已经清除了,因此可能会存在 5,000 个打开表中只有 195 个打开记录的情况）.如果 Opened_tables 随着重新运行 SHOW STATUS 命令快速增加,就说明缓存命中率不够.如果 Open_tables 比 table_cache 设置小很多,就说明该值太大了（不过有空间可以增长总不是什么坏事）.例如,使用 table_cache = 5000 可以调整表的缓存.

与表的缓存类似,对于线程来说也有一个缓存. mysqld 在接收连接时会根据需要生成线程.在一个连接变化很快的繁忙服务器上,对线程进行缓存便于以后使用可以加快最初的连接.

清单 5 显示如何确定是否缓存了足够的线程.

清单 5. 显示线程使用统计信息

mysql> SHOW STATUS LIKE 'threads%'; 
+——————-+——–+ 
| Variable_name     | Value | 
+——————-+——–+ 
| Threads_cached    | 27     | 
| Threads_connected | 15     | 
| Threads_created   | 838610 | 
| Threads_running   | 3      | 
+——————-+——–+ 
4 rows in set (0.00 sec)

此处重要的值是 Threads_created,每次 mysqld 需要创建一个新线程时,这个值都会增加.如果这个数字在连续执行 SHOW STATUS 命令时快速增加,就应该尝试增大线程缓存.例如,可以在 my.cnf 中使用 thread_cache = 40 来实现此目的.

关键字缓冲区保存了 MyISAM 表的索引块.理想情况下,对于这些块的请求应该来自于内存,而不是来自于磁盘.清单 6 显示了如何确定有多少块是从磁盘中读取的,以及有多少块是从内存中读取的.

清单 6. 确定关键字效率

mysql> show status like '%key_read%'; 
+——————-+———–+ 
| Variable_name     | Value     | 
+——————-+———–+ 
| Key_read_requests | 163554268 | 
| Key_reads         | 98247     | 
+——————-+———–+ 
2 rows in set (0.00 sec)

Key_reads 代表命中磁盘的请求个数, Key_read_requests 是总数.命中磁盘的读请求数除以读请求总数就是不中比率 —— 在本例中每 1,000 个请求,大约有 0.6 个没有命中内存.如果每 1,000 个请求中命中磁盘的数目超过 1 个,就应该考虑增大关键字缓冲区了.例如,key_buffer = 384M 会将缓冲区设置为 384MB.

临时表可以在更高级的查询中使用,其中数据在进一步进行处理（例如 GROUP BY 字句）之前,都必须先保存到临时表中；理想情况下,在内存中创建临时表.但是如果临时表变得太大,就需要写入磁盘中.清单 7 给出了与临时表创建有关的统计信息.

清单 7. 确定临时表的使用

mysql> SHOW STATUS LIKE 'created_tmp%'; 
+————————-+——-+ 
| Variable_name           | Value | 
+————————-+——-+ 
| Created_tmp_disk_tables | 30660 | 
| Created_tmp_files       | 2     | 
| Created_tmp_tables      | 32912 | 
+————————-+——-+ 
3 rows in set (0.00 sec)

每次使用临时表都会增大 Created_tmp_tables；基于磁盘的表也会增大 Created_tmp_disk_tables.对于这个比率,并没有什么严格的规则,因为这依赖于所涉及的查询.长时间观察 Created_tmp_disk_tables 会显示所创建的磁盘表的比率,您可以确定设置的效率. tmp_table_size 和 max_heap_table_size 都可以控制临时表的最大大小,因此请确保在 my.cnf 中对这两个值都进行了设置.

每个会话的设置

下面这些设置针对于每个会话.在设置这些数字时要十分谨慎,因为它们在乘以可能存在的连接数时候,这些选项表示大量的内存！您可以通过代码修改会话中的这些数字,或者在 my.cnf 中为所有会话修改这些设置.

当 MySQL 必须要进行排序时,就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行.如果要排序的数据太大,那么数据就必须保存到磁盘上的临时文件中,并再次进行排序.如果 sort_merge_passes 状态变量很大,这就指示了磁盘的活动情况.清单 8 给出了一些与排序相关的状态计数器信息.

清单 8. 显示排序统计信息

mysql> SHOW STATUS LIKE "sort%"; 
+——————-+———+ 
| Variable_name     | Value   | 
+——————-+———+ 
| Sort_merge_passes | 1       | 
| Sort_range        | 79192   | 
| Sort_rows         | 2066532 | 
| Sort_scan         | 44006   | 
+——————-+———+ 
4 rows in set (0.00 sec)

如果 sort_merge_passes 很大,就表示需要注意 sort_buffer_size.例如, sort_buffer_size = 4M 将排序缓冲区设置为 4MB.

MySQL 也会分配一些内存来读取表.理想情况下,索引提供了足够多的信息,可以只读入所需要的行,但是有时候查询（设计不佳或数据本性使然）需要读取表中大量数据.要理解这种行为,需要知道运行了多少个 SELECT 语句,以及需要读取表中的下一行数据的次数（而不是通过索引直接访问）.实现这种功能的命令如清单 9 所示.

清单 9. 确定表扫描比率

mysql> SHOW STATUS LIKE "com_select"; 
+—————+——–+ 
| Variable_name | Value | 
+—————+——–+ 
| Com_select    | 318243 | 
+—————+——–+ 
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE "handler_read_rnd_next"; 
+———————–+———–+ 
| Variable_name         | Value     | 
+———————–+———–+ 
| Handler_read_rnd_next | 165959471 | 
+———————–+———–+ 
1 row in set (0.00 sec)

Handler_read_rnd_next / Com_select 得出了表扫描比率 —— 在本例中是 521:1.如果该值超过 4000,就应该查看 read_buffer_size,例如 read_buffer_size = 4M.如果这个数字超过了 8M,就应该与开发人员讨论一下对这些查询进行调优了！

3 个必不可少的工具

尽管在了解具体设置时,SHOW STATUS 命令会非常有用,但是您还需要一些工具来解释 mysqld 所提供的大量数据.我发现有 3 个工具是必不可少的；在参考资料一节中您可以找到相应的链接.

大部分系统管理员都非常熟悉 top 命令,它为任务所消耗的 CPU 和内存提供了一个不断更新的视图. mytop 对 top 进行了仿真；它为所有连接上的客户机以及它们正在运行的查询提供了一个视图.mytop 还提供了一个有关关键字缓冲区和查询缓存效率的实时数据和历史数据,以及有关正在运行的查询的统计信息.这是一个很有用的工具,可以查看系统中（比如 10 秒钟之内）的状况,您可以获得有关服务器健康信息的视图,并显示导致问题的任何连接.

mysqlard 是一个连接到 MySQL 服务器上的守护程序,负责每 5 分钟搜集一次数据,并将它们存储到后台的一个 Round Robin Database 中.有一个 Web 页面会显示这些数据,例如表缓存的使用情况、关键字效率、连接上的客户机以及临时表的使用情况.尽管 mytop 提供了服务器健康信息的快照,但是 mysqlard 则提供了长期的健康信息.作为奖励,mysqlard 使用自己搜集到的一些信息针对如何对服务器进行调优给出一些建议.

搜集 SHOW STATUS 信息的另外一个工具是 mysqlreport.其报告要远比 mysqlard 更加复杂,因为需要对服务器的每个方面都进行分析.这是对服务器进行调优的一个非常好的工具,因为它对状态变量进行适当计算来帮助确定需要修正哪些问题.

备注：
1.mysql 内存占用的计算公式

mysql used mem = key_buffer_size + query_cache_size + tmp_table_size

+ innodb_buffer_pool_size + innodb_additional_mem_pool_size

+ innodb_log_buffer_size

+ max_connections * (

read_buffer_size + read_rnd_buffer_size

+ sort_buffer_size+ join_buffer_size

+ binlog_cache_size + thread_stack

)

在mysql 中输入如下命令，可自动计算自己的当前配置最大的内存消耗

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; 
  
SHOW VARIABLES LIKE 'innodb_additional_mem_pool_size'; 
  
SHOW VARIABLES LIKE 'innodb_log_buffer_size'; 
  
SHOW VARIABLES LIKE 'thread_stack'; 
  
SET @kilo_bytes = 1024; 
  
SET @mega_bytes = @kilo_bytes * 1024; 
  
SET @giga_bytes = @mega_bytes * 1024; 
  
SET @innodb_buffer_pool_size = 2 * @giga_bytes; 
  
SET @innodb_additional_mem_pool_size = 16 * @mega_bytes; 
  
SET @innodb_log_buffer_size = 8 * @mega_bytes; 
  
SET @thread_stack = 192 * @kilo_bytes; 
  
SELECT
  
( @@key_buffer_size + @@query_cache_size + @@tmp_table_size 
  
+ @innodb_buffer_pool_size + @innodb_additional_mem_pool_size 
  
+ @innodb_log_buffer_size 
  
+ @@max_connections * ( 
  
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size 
  
+ @@join_buffer_size + @@binlog_cache_size + @thread_stack 
  
) ) / @giga_bytes AS MAX_MEMORY_GB;

2.有关 innodb 的优化
如果是使用 innodb 的环境,记的打开 innodb_buffer_pool_size 对性能很有帮助.

The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system.

足够大的 innodb_buffer_pool_size
推荐将数据完全保存在 innodb_buffer_pool_size ，即按存储量规划 innodb_buffer_pool_size 的容量。这样你可以完全从内存中读取数据，最大限度减少磁盘操作

如何确定 innodb_buffer_pool_size 足够大，数据是从内存读取而不是硬盘？

mysql> show status like 'Innodb_buffer_pool_%'; 
+---------------------------------------+--------+ 
| Variable_name                         | Value  | 
+---------------------------------------+--------+ 
| Innodb_buffer_pool_pages_data         | 1278   |   --有数据的内存页 
| Innodb_buffer_pool_pages_dirty        | 45     | 
| Innodb_buffer_pool_pages_flushed      | 3344   | 
| Innodb_buffer_pool_pages_free         | 0      | 
| Innodb_buffer_pool_pages_misc         | 1      | 
| Innodb_buffer_pool_pages_total        | 1279   | 
| Innodb_buffer_pool_read_ahead_rnd     | 0      | --记录进行随机读的时候产生的预读次数； 
| Innodb_buffer_pool_read_ahead         | 0      | 
| Innodb_buffer_pool_read_ahead_evicted | 24153  | 
| Innodb_buffer_pool_read_requests      | 500305 | <STRONG>--请求了这么多次</STRONG> 
| Innodb_buffer_pool_reads              | 73263  | <STRONG>--物理文件中读取的次数</STRONG> 
| Innodb_buffer_pool_wait_free          | 0      | 
| Innodb_buffer_pool_write_requests     | 6485   | 
+---------------------------------------+--------+ 
13 rows in set (0.00 sec)