mysql调优和服务器优化,mysql服务器优化的几种方法

　　tr valign=top TD width=8 img alt=height=1 width=8 src=//www . IBM . com/I/c . gif//TD _ fcksavedurl=//www . IBM . com/I/c . gif//TD width=16 img alt=width=16 height=16 src=//www . IBM . com/I/c . gif//TD 8 height= 1 src= http://www . IBM . com/I

　　将此页面作为电子邮件发送。

　　级别：中级

　　高级网络工程师Sean A. Walberg (sean@ertw.com)

　　2007年7月30日

　　现在的开发者都在不断的使用LAMP(Linux，Apache，MySQL和PHP/Perl)架构开发和部署应用。然而，服务器管理员通常对应用程序本身没有什么控制权，因为应用程序是由其他人编写的。这个由三部分组成的系列将讨论许多影响应用程序性能的服务器配置问题。本文是本系列的第三部分，也是最后一部分，它将重点关注数据库层的优化，以实现最高的效率。

　　关于MySQL调优

　　提高MySQL服务器运行速度的方法有3种，效率从低到高的顺序是：

　　更换有故障的硬件。

　　调整MySQL进程的设置。

　　优化查询。

　　更换故障硬件通常是我们首先考虑的，主要是因为数据库会占用大量资源。然而，这种解决方案仅限于此。事实上，通常可以将中央处理器(CPU)或磁盘的速度提高一倍，或者将内存提高4到8倍。

　　第二种方法是调优MySQL服务器(也称为mysqld)。调优这个过程意味着适当地分配内存，并让mysqld知道它将承受什么样的负载。加速磁盘操作比减少所需的磁盘访问次数要好。同样，保证MySQL进程的正确运行意味着它在服务查询上花费的时间要比处理临时磁盘表或打开关闭文件等后台任务多。调优mysqld是本文的重点。

　　最好的方法是确保查询已经过优化。这意味着对表应用了适当的索引，查询的编写方式可以充分利用MySQL的功能。虽然本文不包含查询调优的内容(这个主题在很多书中都有讨论)，但是它将配置mysqld来报告可能需要调优的查询。

　　虽然已经指定了这些任务的顺序，但是仍然应该注意硬件和mysqld的设置，以便正确地调优查询。只是机器慢了。我见过快速机器在运行设计良好的查询时因为过载而失败，因为mysqld被大量繁忙的工作占据，无法服务查询。

　　记录慢速查询

　　在SQL server中，所有数据表都存储在磁盘上。索引服务器提供了一种在表中查找特定数据行的方法，而不是搜索整个表。当必须搜索整个表时，称为表扫描。一般来说，你可能只想得到表中数据的一个子集，所以全表扫描会浪费很多磁盘I/O，所以也会浪费很多时间。当数据必须连接时，这个问题就更复杂了，因为需要比较连接两端的多行数据。

　　但是，表扫描并不总是带来问题；有时候，读取整个表比从中选择一些数据更有效(服务器进程中的查询规划器用于做出这些决定)。如果索引效率低，或者根本用不上，就会拖慢查询速度，而且随着服务器负载和表的大小增加，这个问题会更加明显。耗时超过给定时间范围的查询称为慢速查询。

　　您可以配置mysqld在一个正确命名的慢速查询日志中记录这些慢速查询。然后，管理员将检查该日志，以帮助他们确定应用程序的哪些部分需要进一步调查。清单1展示了在my.cnf中启用慢速查询日志所需的配置。

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

　　[mysqld]

　　；启用慢速查询日志，默认为10秒

　　慢速日志查询

　　；耗时超过5秒的日志查询

　　long_query_time=5

　　；不使用索引的日志查询，即使它们花费的时间少于long_query_time

　　；仅限MySQL 4.1及更高版本

　　不使用索引的日志查询

　　这三个设置可以一起用于记录执行时间超过5秒且不使用索引的查询。请注意关于日志查询不使用索引的警告：您必须使用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显示类似“qcache%”的状态；

　　- -

　　变量名称值

　　- -

　　 Qcache_free_blocks 5216

　　 Qcache_free_memory 14640664

　　 Qcache_hits 2581646882

　　 Qcache_inserts 360210964

　　 Qcache _ low mem _ prunes 281680433

　　 Qcache_not_cached 79740667

　　 q cache _ queries _ in _ cache 16927

　　 Qcache_total_blocks 47042

　　- -

　　一组8行(0.00秒)

　　Qcache _ free _ blocks

　　缓存中相邻内存块的数量。大量表示可能有碎片。刷新查询缓存将对缓存进行碎片整理，以获得空闲块。

　　Qcache_inserts

　　每次插入查询时，它都会增长。命中数除以插入数就是缺失率；从1中减去这个值得到命中率。在上面的例子中，大约87%的查询命中缓存。

　　Qcache_lowmem_prunes

　　缓存内存不足，必须清理以便为更多查询腾出空间的次数。这个图最好看久一点；如果这个数字一直增加，说明碎片化可能很严重，或者内存很小。(上面的free_blocks和free_memory可以告诉你属于哪种情况)。

　　通常，您可以通过以几秒钟的间隔显示这些变量来判断差异，这有助于确定缓存是否被有效地使用。运行刷新状态可以重置一些计数器，如果服务器已经运行了一段时间，这将非常有帮助。

　　使用非常大的查询缓存并期望缓存所有内容是很诱人的。因为mysqld必须维护缓存，比如在内存变低时进行修剪，所以服务器在试图管理缓存时可能会遇到麻烦。通常，如果刷新查询缓存需要很长时间，则说明缓存太大。

　　强制限制

　　您可以在mysqld中实施一些限制，以确保系统负载不会导致资源耗尽。清单3展示了与my.cnf中的资源相关的一些重要设置

　　清单3。MySQL资源设置

　　设置变量=最大连接数=500

　　设置变量=等待超时=10

　　最大连接错误数=100

　　第一行管理最大数量的连接。类似于Apache中的MaxClients，其思想是确保只建立服务允许的连接数。若要确定服务器上当前建立的最大连接数，请执行show status，如 max _ used _ connections 。

　　第2行告诉mysqld终止所有空闲时间超过10秒的连接。在LAMP应用程序中，连接到数据库的时间通常是Web服务器处理请求的时间。有时，如果负载太重，连接会挂起并占用连接表空间。如果有多个交互式用户或者使用到数据库的持久连接，不建议将该值设置得更低！

　　最后一条线是安全的。如果主机在连接到服务器时出现问题，并在多次重试后放弃，则主机将被锁定，并且在刷新主机之前无法运行。默认情况下，10次失败就足以导致锁定。将该值更改为100将使服务器有足够的时间从问题中恢复。如果重试100次后仍无法建立连接，使用更高的值不会有太大帮助。可能根本连不上。

　　缓冲区和缓存

　　MySQL支持100多种可调设置；不过还好，掌握几个就能满足大部分需求。要找到这些设置的正确值，可以通过SHOW STATUS命令检查状态变量，从中可以确定mysqld的运行是否符合我们的预期。分配给缓冲区和缓存的内存不能超过系统中现有的内存，因此调优通常需要一些折衷。

　　MySQL可调设置可以应用于整个mysqld进程或单个客户端会话。

　　服务器的设置

　　每个表都可以表示为磁盘上的一个文件，必须先打开它，然后再读取它。为了加快从文件中读取数据的过程，mysqld缓存这些打开的文件，其最大数量由/etc/mysqld.conf中的table_cache指定。

　　清单4。显示打开表格的活动

　　mysql显示状态，如“打开的%个表”；

　　- -

　　变量名称值

　　- -

　　 Open_tables 5000

　　打开的表格 195

　　- -

　　集合中的2行(0.00秒)

　　4清单显示当前有5，000个打开的表和195个打开的表，因为现在缓存中没有可用的文件描述符(由于统计信息已经在前面清除，5，000个打开的表中可能只有195个打开的记录)。如果Opened_tables随着SHOW STATUS命令的重新运行而快速增加，那么缓存命中率是不够的。如果Open_tables设置比table_cache设置小很多，就说明这个值太大了(不过有增长的空间总不是坏事)。例如，使用table_cache=5000可以调整表的缓存。

　　类似于表的缓存，也有线程的缓存。Mysqld在接收连接时根据需要生成线程。在连接变化很快的繁忙服务器上，缓存线程以备后用可以加快初始连接的速度。

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

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

　　mysql显示类似“线程%”的状态；

　　- -

　　变量名称值

　　- -

　　线程缓存 27

　　线程_已连接 15

　　 Threads_created 838610

　　线程运行 3

　　- -

　　集合中有4行(0.00秒)

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

　　密钥缓冲区保存MyISAM表的索引块。理想情况下，对这些块的请求应该来自内存，而不是磁盘。清单6展示了如何确定从磁盘中读取多少块，从内存中读取多少块。

　　清单6。确定关键字效率

　　mysql显示类似“%key_read%”的状态；

　　- -

　　变量名称值

　　- -

　　 Key_read_requests 163554268

　　 Key_reads 98247

　　- -

　　集合中的2行(0.00秒)

　　Key_reads表示命中磁盘的请求数，Key_read_requests是总数。命中磁盘的读取请求数除以读取请求总数，得到的未命中率为——。在本例中，每1000次请求中，大约有0.6次未命中内存。如果磁盘上的命中次数超过每1，000次请求中的1次，则应该考虑增加关键字缓冲区。例如，key_buffer=384M会将缓冲区设置为384MB。

　　临时表可以用在更高级的查询中，这些查询中的数据在进一步处理之前必须保存在临时表中(比如按句子分组)；理想情况下，在内存中创建一个临时表。但是如果临时表变得太大，就需要写入磁盘。清单7显示了与创建临时表相关的统计数据。

　　7.确定临时表的使用

　　mysql显示类似“created_tmp%”的状态；

　　- -

　　变量名称值

　　- -

　　 Created _ tmp _ disk _ tables 30660

　　创建的临时文件 2

　　 Created_tmp_tables 32912

　　- -

　　集合中的3行(0.00秒)

　　每次使用临时表，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显示类似“排序%”的状态；

　　- -

　　变量名称值

　　- -

　　 Sort_merge_passes 1

　　 Sort_range 79192

　　 Sort_rows 2066532

　　 Sort_scan 44006

　　- -

　　集合中有4行(0.00秒)

　　如果sort_merge_passes很大，说明需要注意sort_buffer_size。例如，sort_buffer_size=4M将排序缓冲区设置为4MB。

　　MySQL也会分配一些内存来读表。理想情况下，索引提供的信息足以只读取所需的行，但有时查询(糟糕的设计或数据性质)需要读取表中的大量数据。要理解这种行为，需要知道已经运行了多少条SELECT语句，需要读取表中的下一行数据多少次(而不是通过index直接访问)。实现这个函数的命令如清单9所示。

　　清单9。确定表格扫描比率

　　mysql显示类似“com_select”的状态；

　　- -

　　变量名称值

　　- -

　　 Com_select 318243

　　- -

　　集合中的1行(0.00秒)

　　mysql显示类似‘handler _ read _ rnd _ next’的状态；

　　- -

　　变量名称值

　　- -

　　 Handler _ read _ rnd _ next 165959471

　　- -

　　集合中的1行(0.00秒)

　　handler _ read _ rnd _ next/com _ select给出的表扫描比率为——，在本例中为521:1。如果值超过4000，应该检查read_buffer_size，例如read_buffer_size=4M。如果这个数字超过了800万，那么就应该和开发人员讨论如何调优这些查询了！

　　三个基本工具

　　虽然SHOW STATUS命令在理解具体设置时非常有用，但是您仍然需要一些工具来解释mysqld提供的大量数据。我发现三个工具是必不可少的；您可以在参考资料部分找到相应的链接。

　　大多数系统管理员都熟悉top命令，它提供了任务消耗的CPU和内存的不断更新的视图。Mytop模拟top它提供了所有连接的客户端及其正在运行的查询的视图。Mytop还提供了关于关键字缓冲区和查询缓存效率的实时数据和历史数据，以及关于运行查询的统计数据。这是一个非常有用的工具，它可以检查系统的状态(例如，在10秒内)。您可以查看服务器的健康信息，并显示导致问题的任何连接。

　　Mysqlard是一个连接到MySQL服务器的守护进程，负责每5分钟收集一次数据，并在后台存储到一个循环数据库中。有一个显示这些数据的网页，比如表缓存使用情况、关键字效率、连接上的客户机和临时表使用情况。虽然mytop提供了服务器健康信息的快照，但是mysqlard提供了长期的健康信息。作为回报，mysqlard利用自己收集的一些信息，给出一些如何调优服务器的建议。

　　另一个收集节目状态信息的工具是mysqlreport。它的报告远比mysqlard复杂，因为服务器的每个方面都需要分析。这是调优服务器的一个非常好的工具，因为它正确地计算了状态变量，有助于确定需要修复哪些问题。

　　本文介绍了一些MySQL调优的基础知识，并总结了这三篇调优LAMP组件的系列文章。在很大程度上，调优需要了解组件的工作原理，确定它们是否正常工作，进行一些调整，并重新评估。每个组件—— Linux，Apache，PHP或者MySQL ——都有不同的要求。分别理解每个组件有助于减少可能降低应用程序速度的瓶颈。

　　参考数据

　　研究

　　您可以在developerWorks全球网站上参考本文的英文原文。

　　“从MySQL或PostgreSQL迁移到DB2 Express-C”(developer works，2006年6月)提供了一种从MySQL迁移到DB2 Express-C的简单方法。

　　IBM还为想要迁移到DB2 Express: DB2 C的MySQL管理员提供了帮助，请参考“学习DB2 Express和MySQL技能：DB2和MySQL的管理任务和基本任务”(developerWorks，2006年2月)和本系列文章的其他部分。

　　“在联邦数据库环境中使用MySQL”(developer works，2004年12月)是关于从WebSphere访问存储在MySQL中的数据的教程。确保IBM WebSphere软件可以很好的配合MySQL使用。

　　MySQL文档中很好地定义了显示变量和显示状态。

　　如果你喜欢博客，MySQL Performance Blog，Xaprb，MySQL DBA都很值得一读。

　　在developerWorks的体系结构区域，您可以找到一些提高您在体系结构设计领域的技能所需的资源。开发正确的架构是扩展LAMP应用的关键。

　　您可以在developerWorks Linux专区找到更多针对Linux开发人员的资源，包括Linux教程和读者上个月最喜欢的Linux文章和教程。

　　关注developerWorks技术活动和网络广播。

　　获得产品和技术