mysql优化.docx
- 文档编号:24517322
- 上传时间:2023-05-28
- 格式:DOCX
- 页数:13
- 大小:24.42KB
mysql优化.docx
《mysql优化.docx》由会员分享,可在线阅读,更多相关《mysql优化.docx(13页珍藏版)》请在冰豆网上搜索。
mysql优化
关于MySQL调优
有3种方法可以加快MySQL服务器的运行速度,效率从低到高依次为:
1.替换有问题的硬件。
2.对MySQL进程的设置进行调优。
3.对查询进行优化。
迁移到DB2
您正在寻找一种干净利落、无成本的方法用来从MySQL迁移到IBM®DB2®吗?
“从MySQL或PostgreSQL迁移到DB2Express-C”介绍了如何使用文中提供的迁移工具来简单地实现这种转换。
可以下载免费的DB2Express-C并开始体验。
替换有问题的硬件通常是我们的第一考虑,主要原因是数据库会占用大量资源。
不过这种解决方案也就仅限于此了。
实际上,您通常可以让中央处理器(CPU)或磁盘速度加倍,也可以让内存增大4到8倍。
第二种方法是对MySQL服务器(也称为mysqld)进行调优。
对这个进程进行调优意味着适当地分配内存,并让mysqld了解将会承受何种类型的负载。
加快磁盘运行速度不如减少所需的磁盘访问次数。
类似地,确保MySQL进程正确操作就意味着它花费在服务查询上的时间要多于花费在处理后台任务(如处理临时磁盘表或打开和关闭文件)上的时间。
对mysqld进行调优是本文的重点。
最好的方法是确保查询已经进行了优化。
这意味着对表应用了适当的索引,查询是按照可以充分利用MySQL功能的方式来编写的。
尽管本文并没有包含查询调优方面的内容(很多著作中已经针对这个主题进行了探讨),不过它会配置mysqld来报告可能需要进行调优的查询。
虽然已经为这些任务指派了次序,但是仍然要注意硬件和mysqld的设置以利于适当地调优查询。
机器速度慢也就罢了,我曾经见过速度很快的机器在运行设计良好的查询时由于负载过重而失败,因为mysqld被大量繁忙的工作所占用而不能服务查询。
回页首
记录慢速查询
在一个SQL服务器中,数据表都是保存在磁盘上的。
索引为服务器提供了一种在表中查找特定数据行的方法,而不用搜索整个表。
当必须要搜索整个表时,就称为表扫描。
通常来说,您可能只希望获得表中数据的一个子集,因此全表扫描会浪费大量的磁盘I/O,因此也就会浪费大量时间。
当必须对数据进行连接时,这个问题就更加复杂了,因为必须要对连接两端的多行数据进行比较。
当然,表扫描并不总是会带来问题;有时读取整个表反而会比从中挑选出一部分数据更加有效(服务器进程中查询规划器用来作出这些决定)。
如果索引的使用效率很低,或者根本就不能使用索引,则会减慢查询速度,而且随着服务器上的负载和表大小的增加,这个问题会变得更加显著。
执行时间超过给定时间范围的查询就称为慢速查询。
您可以配置mysqld将这些慢速查询记录到适当命名的慢速查询日志中。
管理员然后会查看这个日志来帮助他们确定应用程序中有哪些部分需要进一步调查。
清单1给出了要启用慢速查询日志需要在f中所做的配置。
清单1.启用MySQL慢速查询日志
[mysqld]
;enabletheslowquerylog,default10seconds
log-slow-queries
;logqueriestakinglongerthan5seconds
long_query_time=5
;logqueriesthatdon'tuseindexeseveniftheytakelessthanlong_query_time
;MySQL4.1andneweronly
log-queries-not-using-indexes
这三个设置一起使用,可以记录执行时间超过5秒和没有使用索引的查询。
请注意有关log-queries-not-using-indexes的警告:
您必须使用MySQL4.1或更高版本。
慢速查询日志都保存在MySQL数据目录中,名为hostname-slow.log。
如果希望使用一个不同的名字或路径,可以在f中使用log-slow-queries=/new/path/to/file实现此目的。
阅读慢速查询日志最好是通过mysqldumpslow命令进行。
指定日志文件的路径,就可以看到一个慢速查询的排序后的列表,并且还显示了它们在日志文件中出现的次数。
一个非常有用的特性是mysqldumpslow在比较结果之前,会删除任何用户指定的数据,因此对同一个查询的不同调用被计为一次;这可以帮助找出需要工作量最多的查询。
回页首
对查询进行缓存
很多LAMP应用程序都严重依赖于数据库,但却会反复执行相同的查询。
每次执行查询时,数据库都必须要执行相同的工作——对查询进行分析,确定如何执行查询,从磁盘中加载信息,然后将结果返回给客户机。
MySQL有一个特性称为查询缓存,它将(后面会用到的)查询结果保存在内存中。
在很多情况下,这会极大地提高性能。
不过,问题是查询缓存在默认情况下是禁用的。
将query_cache_size=32M添加到/etc/my.conf中可以启用32MB的查询缓存。
监视查询缓存
在启用查询缓存之后,重要的是要理解它是否得到了有效的使用。
MySQL有几个可以查看的变量,可以用来了解缓存中的情况。
清单2给出了缓存的状态。
清单2.显示查询缓存的统计信息
mysql>SHOWSTATUSLIKE'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|
+-------------------------+------------+
8rowsinset(0.00sec)
这些项的解释如表1所示。
表1.MySQL查询缓存变量
变量名
说明
Qcache_free_blocks
缓存中相邻内存块的个数。
数目大说明可能有碎片。
FLUSHQUERYCACHE会对缓存中的碎片进行整理,从而得到一个空闲块。
Qcache_free_memory
缓存中的空闲内存。
Qcache_hits
每次查询在缓存中命中时就增大。
Qcache_inserts
每次插入一个查询时就增大。
命中次数除以插入次数就是不中比率;用1减去这个值就是命中率。
在上面这个例子中,大约有87%的查询都在缓存中命中。
Qcache_lowmem_prunes
缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。
这个数字最好长时间来看;如果这个数字在不断增长,就表示可能碎片非常严重,或者内存很少。
(上面的free_blocks和free_memory可以告诉您属于哪种情况)。
Qcache_not_cached
不适合进行缓存的查询的数量,通常是由于这些查询不是SELECT语句。
Qcache_queries_in_cache
当前缓存的查询(和响应)的数量。
Qcache_total_blocks
缓存中块的数量。
通常,间隔几秒显示这些变量就可以看出区别,这可以帮助确定缓存是否正在有效地使用。
运行FLUSHSTATUS可以重置一些计数器,如果服务器已经运行了一段时间,这会非常有帮助。
使用非常大的查询缓存,期望可以缓存所有东西,这种想法非常诱人。
由于mysqld必须要对缓存进行维护,例如当内存变得很低时执行剪除,因此服务器可能会在试图管理缓存时而陷入困境。
作为一条规则,如果FLUSHQUERYCACHE占用了很长时间,那就说明缓存太大了。
回页首
强制限制
您可以在mysqld中强制一些限制来确保系统负载不会导致资源耗尽的情况出现。
清单3给出了f中与资源有关的一些重要设置。
清单3.MySQL资源设置
set-variable=max_connections=500
set-variable=wait_timeout=10
max_connect_errors=100
连接最大个数是在第一行中进行管理的。
与Apache中的MaxClients类似,其想法是确保只建立服务允许数目的连接。
要确定服务器上目前建立过的最大连接数,请执行SHOWSTATUSLIKE'max_used_connections'。
第2行告诉mysqld终止所有空闲时间超过10秒的连接。
在LAMP应用程序中,连接数据库的时间通常就是Web服务器处理请求所花费的时间。
有时候,如果负载过重,连接会挂起,并且会占用连接表空间。
如果有多个交互用户或使用了到数据库的持久连接,那么将这个值设低一点并不可取!
最后一行是一个安全的方法。
如果一个主机在连接到服务器时有问题,并重试很多次后放弃,那么这个主机就会被锁定,直到FLUSHHOSTS之后才能运行。
默认情况下,10次失败就足以导致锁定了。
将这个值修改为100会给服务器足够的时间来从问题中恢复。
如果重试100次都无法建立连接,那么使用再高的值也不会有太多帮助,可能它根本就无法连接。
回页首
缓冲区和缓存
MySQL支持超过100个的可调节设置;但是幸运的是,掌握少数几个就可以满足大部分需要。
查找这些设置的正确值可以通过SHOWSTATUS命令查看状态变量,从中可以确定mysqld的运作情况是否符合我们的预期。
给缓冲区和缓存分配的内存不能超过系统中的现有内存,因此调优通常都需要进行一些妥协。
MySQL可调节设置可以应用于整个mysqld进程,也可以应用于单个客户机会话。
服务器端的设置
每个表都可以表示为磁盘上的一个文件,必须先打开,后读取。
为了加快从文件中读取数据的过程,mysqld对这些打开文件进行了缓存,其最大数目由/etc/mysqld.conf中的table_cache指定。
清单4给出了显示与打开表有关的活动的方式。
清单4.显示打开表的活动
mysql>SHOWSTATUSLIKE'open%tables';
+---------------+-------+
|Variable_name|Value|
+---------------+-------+
|Open_tables|5000|
|Opened_tables|195|
+---------------+-------+
2rowsinset(0.00sec)
清单4说明目前有5,000个表是打开的,有195个表需要打开,因为现在缓存中已经没有可用文件描述符了(由于统计信息在前面已经清除了,因此可能会存在5,000个打开表中只有195个打开记录的情况)。
如果Opened_tables随着重新运行SHOWSTATUS命令快速增加,就说明缓存命中率不够。
如果Open_tables比table_cache设置小很多,就说明该值太大了(不过有空间可以增长总不是什么坏事)。
例如,使用table_cache=5000可以调整表的缓存。
与表的缓存类似,对于线程来说也有一个缓存。
mysqld在接收连接时会根据需要生成线程。
在一个连接变化很快的繁忙服务器上,对线程进行缓存便于以后使用可以加快最初的连接。
清单5显示如何确定是否缓存了足够的线程。
清单5.显示线程使用统计信息
mysql>SHOWSTATUSLIKE'threads%';
+-------------------+--------+
|Variable_name|Value|
+-------------------+--------+
|Threads_cached|27|
|Threads_connected|15|
|Threads_created|838610|
|Threads_running|3|
+-------------------+--------+
4rowsinset(0.00sec)
此处重要的值是Threads_created,每次mysqld需要创建一个新线程时,这个值都会增加。
如果这个数字在连续执行SHOWSTATUS命令时快速增加,就应该尝试增大线程缓存。
例如,可以在f中使用thread_cache=40来实现此目的。
关键字缓冲区保存了MyISAM表的索引块。
理想情况下,对于这些块的请求应该来自于内存,而不是来自于磁盘。
清单6显示了如何确定有多少块是从磁盘中读取的,以及有多少块是从内存中读取的。
清单6.确定关键字效率
mysql>showstatuslike'%key_read%';
+-------------------+-----------+
|Variable_name|Value|
+-------------------+-----------+
|Key_read_requests|163554268|
|Key_reads|98247|
+-------------------+-----------+
2rowsinset(0.00sec)
Key_reads代表命中磁盘的请求个数,Key_read_requests是总数。
命中磁盘的读请求数除以读请求总数就是不中比率——在本例中每1,000个请求,大约有0.6个没有命中内存。
如果每1,000个请求中命中磁盘的数目超过1个,就应该考虑增大关键字缓冲区了。
例如,key_buffer=384M会将缓冲区设置为384MB。
临时表可以在更高级的查询中使用,其中数据在进一步进行处理(例如GROUPBY字句)之前,都必须先保存到临时表中;理想情况下,在内存中创建临时表。
但是如果临时表变得太大,就需要写入磁盘中。
清单7给出了与临时表创建有关的统计信息。
清单7.确定临时表的使用
mysql>SHOWSTATUSLIKE'created_tmp%';
+-------------------------+-------+
|Variable_name|Value|
+-------------------------+-------+
|Created_tmp_disk_tables|30660|
|Created_tmp_files|2|
|Created_tmp_tables|32912|
+-------------------------+-------+
3rowsinset(0.00sec)
每次使用临时表都会增大Created_tmp_tables;基于磁盘的表也会增大Created_tmp_disk_tables。
对于这个比率,并没有什么严格的规则,因为这依赖于所涉及的查询。
长时间观察Created_tmp_disk_tables会显示所创建的磁盘表的比率,您可以确定设置的效率。
tmp_table_size和max_heap_table_size都可以控制临时表的最大大小,因此请确保在f中对这两个值都进行了设置。
每个会话的设置
下面这些设置针对于每个会话。
在设置这些数字时要十分谨慎,因为它们在乘以可能存在的连接数时候,这些选项表示大量的内存!
您可以通过代码修改会话中的这些数字,或者在f中为所有会话修改这些设置。
当MySQL必须要进行排序时,就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。
如果要排序的数据太大,那么数据就必须保存到磁盘上的临时文件中,并再次进行排序。
如果sort_merge_passes状态变量很大,这就指示了磁盘的活动情况。
清单8给出了一些与排序相关的状态计数器信息。
清单8.显示排序统计信息
mysql>SHOWSTATUSLIKE"sort%";
+-------------------+---------+
|Variable_name|Value|
+-------------------+---------+
|Sort_merge_passes|1|
|Sort_range|79192|
|Sort_rows|2066532|
|Sort_scan|44006|
+-------------------+---------+
4rowsinset(0.00sec)
如果sort_merge_passes很大,就表示需要注意sort_buffer_size。
例如,sort_buffer_size=4M将排序缓冲区设置为4MB。
MySQL也会分配一些内存来读取表。
理想情况下,索引提供了足够多的信息,可以只读入所需要的行,但是有时候查询(设计不佳或数据本性使然)需要读取表中大量数据。
要理解这种行为,需要知道运行了多少个SELECT语句,以及需要读取表中的下一行数据的次数(而不是通过索引直接访问)。
实现这种功能的命令如清单9所示。
清单9.确定表扫描比率
mysql>SHOWSTATUSLIKE"com_select";
+---------------+--------+
|Variable_name|Value|
+---------------+--------+
|Com_select|318243|
+---------------+--------+
1rowinset(0.00sec)
mysql>SHOWSTATUSLIKE"handler_read_rnd_next";
+-----------------------+-----------+
|Variable_name|Value|
+-----------------------+-----------+
|Handler_read_rnd_next|165959471|
+-----------------------+-----------+
1rowinset(0.00sec)
Handler_read_rnd_next/Com_select得出了表扫描比率——在本例中是521:
1。
如果该值超过4000,就应该查看read_buffer_size,例如read_buffer_size=4M。
如果这个数字超过了8M,就应该与开发人员讨论一下对这些查询进行调优了!
回页首
3个必不可少的工具
尽管在了解具体设置时,SHOWSTATUS命令会非常有用,但是您还需要一些工具来解释mysqld所提供的大量数据。
我发现有3个工具是必不可少的;在参考资料一节中您可以找到相应的链接。
大部分系统管理员都非常熟悉top命令,它为任务所消耗的CPU和内存提供了一个不断更新的视图。
mytop对top进行了仿真;它为所有连接上的客户机以及它们正在运行的查询提供了一个视图。
mytop还提供了一个有关关键字缓冲区和查询缓存效率的实时数据和历史数据,以及有关正在运行的查询的统计信息。
这是一个很有用的工具,可以查看系统中(比如10秒钟之内)的状况,您可以获得有关服务器健康信息的视图,并显示导致问题的任何连接。
mysqlard是一个连接到MySQL服务器上的守护程序,负责每5分钟搜集一次数据,并将它们存储到后台的一个RoundRobinDatabase中。
有一个Web页面会显示这些数据,例如表缓存的使用情况、关键字效率、连接上的客户机以及临时表的使用情况。
尽管mytop提供了服务器健康信息的快照,但是mysqlard则提供了长期的健康信息。
作为奖励,mysqlard使用自己搜集到的一些信息针对如何对服务器进行调优给出一些建议。
搜集SHOWSTATUS信息的另外一个工具是mysqlreport。
其报告要远比mysqlard更加复杂,因为需要对服务器的每个方面都进行分析。
这是对服务器进行调优的一个非常好的工具,因为它对状态变量进行适当计算来帮助确定需要修正哪些问题。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mysql 优化
![提示](https://static.bdocx.com/images/bang_tan.gif)