Mysql监控方案

常见Mysql系统参数调整列出MySQL服务器运行各种状态值：mysqlshowglobalstatus;查询MySQL服务器配置信息：mysqlshowvariables;1.慢查询mysqlshowvariableslike'%slow%';+---------------------+-------------------------------+|Variable_name|Value|+---------------------+-------------------------------+|log_slow_queries（已废弃）|OFF||slow_launch_time|2||slow_query_log|OFF||slow_query_log_file|/var/lib/mysql/twf13-slow.log|+---------------------+-------------------------------+4rowsinset(0.00sec)mysqlshowglobalstatuslike'%slow%';+---------------------+-------+|Variable_name|Value|+---------------------+-------+|Slow_launch_threads|0||Slow_queries|0|+---------------------+-------+2rowsinset(0.00sec)配置中关闭了记录慢查询，执行时间超过2秒的即为慢查询，系统显示有0个慢查询，你可以分析慢查询日志，找出有问题的SQL语句，慢查询时间不宜设置过长，否则意义不大，最好在5秒以内，如果你需要微秒级别的慢查询，可以考虑给MySQL打补丁：:start，记得找对应的版本。打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主-从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。（消耗的主要是CPU）mysqldumpslow查询慢查询日志2.连接数经常会遇见”MySQL:ERROR1040:Toomanyconnections”的情况，一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小：max_connectionsMax_used_connectionsConnectionsmax_user_connectionsback_logmax_connect_errorsmax_connections：整个MySQL允许的最大连接数；这个参数主要影响的是整个MySQL应用的并发处理能力，当系统中实际需要的连接量大于max_connections的情况下，由于MySQL的设置限制，那么应用中必然会产生连接请求的等待，从而限制了相应的并发量。所以一般来说，只要MySQL主机性能允许，都是将该参数设置的尽可能大一点。一般来说500到800左右是一个比较合适的参考值当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小mysqlshowvariableslike'max_connections';+-----------------+-------+|Variable_name|Value|+-----------------+-------+|max_connections|151|+-----------------+-------+1rowinset(0.00sec)这台MySQL服务器最大连接数是151，然后查询一下服务器响应的最大连接数：mysqlshowglobalstatuslike'Max_used_connections';+----------------------+-------+|Variable_name|Value|+----------------------+-------+|Max_used_connections|1|+----------------------+-------+查看当前连接数mysqlshowglobalstatuslike'Connections';+---------------+-------+|Variable_name|Value|+---------------+-------+|Connections|2|MySQL服务器过去的最大连接数是1，没有达到服务器连接数上限151，应该没有出现1040错误，比较理想的设置是：Max_used_connections/max_connections*100%≈85%（好像和上面有冲突）最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。max_user_connections：每个用户允许的最大连接数；是针对于单个用户的连接限制。在一般情况下我们可能都较少使用这个限制，只有在一些专门提供MySQL数据存储服务，或者是提供虚拟主机服务的应用中可能需要用到。back_log是要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。如果期望在一个短时间内有很多连接，你需要增加它。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。另外，这值（back_log）限于您的操作系统对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制（可以检查你的OS文档找出这个变量的最大值），试图设定back_log高于你的操作系统的限制将是无效的。mysqlshowvariableslike'back_log';+---------------+-------+|Variable_name|Value|+---------------+-------+|back_log|50|+---------------+-------+max_connect_errorsmax_connect_errors默认值为10，如果受信帐号错误连接次数达到10则自动堵塞，需要flushhosts来解除。如果你得到象这样的一个错误：Host’hostname’isblockedbecauseofmanyconnectionerrors.Unblockwith’mysqladminflush-hosts’这意味着，mysqld已经得到了大量(max_connect_errors)的主机’hostname’的在中途被中断了的连接请求。在max_connect_errors次失败请求后，mysqld认定出错了(象来字一个黑客的攻击)，并且阻止该站点进一步的连接，直到某人执行命令mysqladminflush-hosts。内网连接的话，建议设置在10000以上，已避免堵塞，并定期flushhosts。connect_timeout指定MySQL服务等待应答一个连接报文的最大秒数，超出该时间，MySQL向客户端返回badhandshake。默认值是5秒，在内网高并发环境中建议设置到10-15秒，以便避免badhandshake。建议同时关注thread_cache_size并设置thread_cache_size为非0值，大小具体调整。skip-name-resolveskip-name-resolve能大大加快用户获得连接的速度，特别是在网络情况较差的情况下。MySQL在收到连接请求的时候，会根据请求包中获得的ip来反向追查请求者的主机名。然后再根据返回的主机名又一次去获取ip。如果两次获得的ip相同，那么连接就成功建立了。在DNS不稳定或者局域网内主机过多的情况下，一次成功的连接将会耗费很多不必要的时间。假如MySQL服务器的ip地址是广域网的，最好不要设置skip-name-resolve。3.key_buffer_sizekey_buffer_size，索引缓存大小；这个参数用来设置整个MySQL中的常规KeyCache大小。一般来说，如果我们的MySQL是运行在32位平台纸上，此值建议不要超过2GB大小。如果是运行在64位平台纸上则不用考虑此限制，但也最好不要超过4GB。对于key_buffer_size的设置我们一般需要通过三个指标来计算，第一个是系统索引的总大小，第二个是系统可用物理内存，第三个是根据系统当前的KeyCache命中率。对于一个完全从零开始的全新系统的话，可能出了第二点可以拿到很清楚的数据之外，其他的两个数据都比较难获取，第三点是完全没有。当然，我们可以通过MySQL官方手册中给出的一个计算公式粗略的估算一下我们系统将来的索引大小，不过前提是要知道我们会创建哪些索引，然后通过各索引估算出索引键的长度，以及表中存放数据的条数，公式如下：Key_Size=key_number*(key_length+4)/0.67Max_key_buffer_sizeMax_RAM-QCache_Usage-Threads_Usage-System_UsageThreads_Usage=max_connections*(sort_buffer_size+join_buffer_size+read_buffer_size+read_rnd_buffer_size+thread_stack)系统中记录的与KeyCache相关的性能状态参数变量：◆Key_blocks_not_flushed，已经更改但还未刷新到磁盘的DirtyCacheBlock；◆Key_blocks_unused，目前未被使用的CacheBlock数目；◆Key_blocks_used，已经使用了的CacheBlock数目；◆Key_read_requests，CacheBlock被请求读取的总次数；◆Key_reads，在CacheBlock中找不到需要读取的Key信息后到“.MYI”文件中读取的次数；◆Key_write_requests，CacheBlock被请求修改的总次数；◆Key_writes，在CacheBlock中找不到需要修改的Key信息后到“.MYI”文件中读入再修改的次数；key_buffer_size是对MyISAM表性能影响最大的一个参数，下面一台以MyISAM为主要存储引擎服务器的配置：mysqlshowvariableslike'key_buffer_size';+-----------------+-----------+|Variable_name|Value|+-----------------+-----------+|key_buffer_size|268435456|+-----------------+-----------+分配了256MB内存给key_buffer_size，我们再看一下key_buffer_size的使用情况：mysqlshowglobalstatuslike'key_read%';+-------------------+-------+|Variable_name|Value|+-------------------+-------+|Key_read_requests|470||Key_rea