11. 12.

我也看到别人是转载的, 但是没留下原文地址, 我就直接copy过来了, 感觉很有用的文章

mysqladmin extended (绝对值)
重点去监视的值有:
* Slave_running:如果系统有一个从复制服务器,这个值指明了从服务器的健康度
* Threads_connected:当前客户端已连接的数量。这个值会少于预设的值,但你也能监视到这个值较大,这可保证客户端是处在活跃状态。
* Threads_running:如果数据库超负荷了,你将会得到一个正在(查询的语句持续)增长的数值。这个值也可以少于预先设定的值。这个值在很短的时间内超过限定值是没问题的。当Threads_running值超过预设值时并且该值在5秒内没有回落时, 要同时监视其他的一些值。
2)mysqladmin extended(计数器)
* Aborted_clients:客户端被异常中断的数值(因为连接到mysql服务器的客户端没有正常地断开或关闭)。对于一些应用程序是没有影响的,但对于另一些应用程序可能你要跟踪该值,因为异常中断连接可能表明了一些应用程序有问题。
* Questions:每秒钟获得的查询数量。也可以是全部查询的数量(注:可以根据你输入不同的命令会得到你想要的不同的值)。

* Handler_*:如果你想监视底层(low-level)数据库负载,这些值是值得去跟踪的。如果Handler_read_rnd_next值相对于你认为是正常值相差悬殊,可能会告诉你需要优化或索引出问题了。Handler_rollback表明事务被回滚的查询数量。你可能想调查一下原因。
* Opened_tables:表缓存没有命中的数量。如果该值很大,你可能需要增加table_cache的数值。典型地,你可能想要这个值每秒打开的表数量少于1或2.
* Select_full_join: 没有主键(key)联合(Join)的执行。该值可能是零。这是捕获开发错误的好方法,因为一些这样的查询可能降低系统的性能。
* Select_scan:执行全表搜索查询的数量。在某些情况下是没问题的,但占总查询数量该比值应该是常量(注:Select_scan除以总查询数量商应该是常数)。如果你发现该值持续增长,说明需要优化,缺乏必要的索引或其他问题。
* Slow_queries:超过该值(–long-query-time)的查询数量,或没有使用索引查询数量。对于全部查询会有小的冲突。如果该值增长,表明系统有性能问题。
* Threads_created:该值应该是低的。较高的值可能意味着你需要增加thread_cache的数值,或你遇到了持续增加的连接,表明了潜在的问题。
3)mysqladmin processlist or “SHOW FULLPROCESSLIST”命令
你可以通过使用其他的统计信息得到已连接线程数量和正在运行线程的数量,检查正在运行的查询花了多长时间是一个好主意。如果有一些长时间的查询(由于很差的构思或公式),管理员可以被通知。你可能也想了解多少个查询是在”Locked”的状态——该值作为正在运行的查询不被计算在内而是作为非活跃的。一个用户正在等待一个数据库响应。
4) “SHOW INNODB STATUS”命令
该语句产生很多信息,从中你可以得到你感兴趣的。首先你要检查的就是“从最近的XX秒计算出来的每秒的平均负载”。
* Pending normal aio reads: 该值是innodb io请求查询的大小(size)。如果该值大到超过了10—20,你可能有一些瓶颈。
* reads/s, avg bytes/read, writes/s, fsyncs/s:这些值是io统计。对于reads/writes大值意味着io子系统正在被装载。适当的值取决于你系统的配置。
* Buffer pool hit rate:这个命中率非常依赖于你的应用程序。当你觉得有问题时请检查你的命中率
* inserts/s, updates/s, deletes/s, reads/s:有一些Innodb的底层操作。你可以用这些值检查你的负载情况查看是否是期待的数值范围。
4)OS数据。查看系统状态好的工具是vmstat/iostat/mpstat,这些可以看man手册
5)SQL\” ōnclick=”tagshow(event)” class=”t_tag”>MySQL错误日志—–在服务器正常完成初始化后,什么都不会写到错误日志中,因此任何在该日志中的信息都要引起管理员的注意。
6)InnoDB表空间信息。InnoDB仅有的危险情况就是表空间填满—-日志不会填满。检查的最好方式就是:show table status;你可以用任何InnoDB表来监视InnoDB表的剩余空间。—- 我最近在网上查了一些有关优化MySql的资料,并对照MySql手册对一些调优设置进行了细结,但是由于时间比较勿忙,没来得及对这些设置进行实验/测试,你可以把这些方法应用到实际中,得出具体结论。调优方法大致如下:

MySql服务器的后台管理程序,要想使用客户端程序,该程序必须运行,因为客户端通过连接服务器来访问数据库。下面让我们以服务器的系统变量和状态变量为根据,优化我们的MySql数据库服务。

在这之前,我们需要掌握以下方法:

查看MySql状态及变量的方法:

Mysql> show status ——显示状态信息(扩展show status like ‘XXX’)
Mysql> show variables ——显示系统变量(扩展show variables like ‘XXX’)
Mysql> show innodb status ——显示InnoDB存储引擎的状态
Shell> mysqladmin variables -u username -p password——显示系统变量
Shell> mysqladmin extended-status -u username -p password——显示状态信息

查看状态变量及帮助:

Shell> mysqld –verbose –help [|more #逐行显示]

首先,让我们看看有关请求连接的变量:

为了能适应更多数据库应用用户,MySql提供了连接(客户端)变量,以对不同性质的用户群体提供不同的解决方案,笔者就max_connections,back_log做了一些细结,如下:

max_connections是指MySql的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,介于MySql会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。可以过’conn%’通配符查看当前状态的连接数量,以定夺该值的大小。

back_log是要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。如果期望在一个短时间内有很多连接,你需要增加它。也就是说,如果MySql的连接数据达到 max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过 back_log,将不被授予连接资源。另外,这值(back_log)限于您的操作系统对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制(可以检查你的OS文档找出这个变量的最大值),试图设定back_log高于你的操作系统的限制将是无效的。

优化了MySql的连接后属性后,我们需要看看缓冲区变量:

使用MySql数据库存储大量数据(或使用复杂查询)时,我们应该考虑MySql的内存配置。如果配置 MySQL服务器使用太少的内存会导致性能不是最优的;如果配置了太多的内存则会导致崩溃,无法执行查询或者导致交换操作严重变慢。在现在的32位平台下,仍有可能把所有的地址空间都用完,因此需要审视。

计算内存使用的秘诀公式就能相对地解决这一部分问题。不过,如今这个公式已经很复杂了,更重要的是,通过它计算得到的值只是“理论可能”并不是真正消耗的值。事实上,有8GB内存的常规服务器经常能运行到最大的理论值(100GB甚至更高)。此外,你轻易不会使用到“超额因素”(它实际上依赖于应用以及配置)。一些应用可能需要理论内存的10%而有些仅需1%。
那么,我们可以做什么呢?

来看看那些在启动时就需要分配并且总是存在的全局缓冲吧!

全局缓冲:
key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size,innodb_log_buffer_size, query_cache_size

注:如果你大量地使用MyISAM表,那么你也可以增加操作系统的缓存空间使得MySQL也能用得着。把这些也都加到操作系统和应用程序所需的内存值之中,可能需要增加32MB甚至更多的内存给MySQL服务器代码以及各种不同的小静态缓冲。这些就是你需要考虑的在MySQL服务器启动时所需的内存。其他剩下的内存用于连接。

key_buffer_size决定索引处理的速度,尤其是索引读的速度。一般我们设为16M,通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用’key_read%’获得用来显示状态数据)。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。可以使用检查状态值’created_tmp_disk_tables’得知详情。

innodb_buffer_pool_size对于InnoDB表来说,作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器,最大可以把该值设置成物理内存的80%。

innodb_additional_mem_pool_size指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是1M。通常不用太大,只要够用就行,应该与表结构的复杂度有关系。如果不够用,MySQL会在错误日志中写入一条警告信息。

innodb_log_buffer_size指定InnoDB用来存储日志数据的缓存大小,如果您的表操作中包含大量并发事务(或大规模事务),并且在事务提交前要求记录日志文件,请尽量调高此项值,以提高日志效率。

query_cache_size是MySql的查询缓冲大小。(从4.0.1开始,MySQL提供了查询缓冲机制)使用查询缓冲,MySQL将SELECT语句和查询结果存放在缓冲区中,今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。通过检查状态值’Qcache_%’,可以知道 query_cache_size设置是否合理:如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况,如果 Qcache_hits的值也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;如果Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。此外,在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

除了全局缓冲,MySql还会为每个连接发放连接缓冲。

连接缓冲:
每个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K,甚至在线程空闲时,它们使用默认的线程堆栈,网络缓存等。事务开始之后,则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗,然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表,则需分配大约read_buffer_size,sort_buffer_size,read_rnd_buffer_size,tmp_table_size大小的内存空间。不过它们只是在需要的时候才分配,并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块。tmp_table_size 可能高达MySQL所能分配给这个操作的最大内存空间了。注意,这里需要考虑的不只有一点 —— 可能会分配多个同一种类型的缓存,例如用来处理子查询。一些特殊的查询的内存使用量可能更大——如果在MyISAM表上做成批的插入时需要分配 bulk_insert_buffer_size 大小的内存;执行 ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 命令时需要分配 myisam_sort_buffer_size 大小的内存。

read_buffer_size是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。

sort_buffer_size是MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试增加sort_buffer_size变量的大小。

read_rnd_buffer_size是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。

tmp_table_size是MySql的heap(堆积)表缓冲大小。所有联合在一个DML指令内完成,并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。如果某个内部heap(堆积)表大小超过tmp_table_size,MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说,如果调高该值,MySql同时将增加heap表的大小,可达到提高联接查询速度的效果。

当我们设置好了缓冲区大小之后,再来看看:

table_cache所有线程打开的表的数目,增大该值可以增加mysqld需要的文件描述符的数量。每当 MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值 ’Open_tables’和’Opened_tables’,可以决定是否需要增加table_cache的值。如果你发现open_tables等于 table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用 ’Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

做了以上方面的调优设置之后,MySql应该基本能满足您需求(当然是建立在调优设置适当的情况下),我们还应该了解并注意:

只有简单查询OLTP(联机事务处理)应用的内存消耗经常是使用默认缓冲的每个线程小于1MB,除非需要使用复杂的查询否则无需增加每个线程的缓冲大小。使用1MB的缓冲来对10行记录进行排序和用16MB的缓冲基本是一样快的(实际上16MB可能会更慢,不过这是其他方面的事了)。

找出MySQL服务器内存消耗的峰值。这很容易就能计算出操作系统所需的内存、文件缓存以及其他应用。在32位环境下,还需要考虑到32位的限制,限制 “mysqld” 的值大约为2.5G(实际上还要考虑到很多其他因素)。现在运行 “ps aux” 命令来查看 “VSZ” 的值(MySQL 进程分配的虚拟内存)。监视着内存变化的值,就能知道是需要增加或减少当前的内存值了。

最后来看看调优设置方法:

安装好MySql后,配制文件应该在 ./share/mysql (“./”即MySql安装目录) 目录中,配制文件有几个,有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf。win环境下即存在于MySql安装目录中的.ini文件。不同的流量的网站和不同配制的服务器环境,当然需要有不同的配制文件了。

一般的情况下,my-medium.cnf这个配制文件就能满足我们的大多需要;一般我们会把配置文件拷贝到 /etc/my.cnf ,win环境下则拷备到 my.ini 下即可,只需要修改这个配置文件就可以了。


作者: hao32 | 可以转载, 转载时务必以超链接形式标明文章原始出处和作者信息及版权声明
网址:http://www.hao32.com/webserver/299.html

Tags: ,

发表评论: