MySqL线上status做出适当优化

ms133

　　优化的一些命令：
mysql> show global staus;   //这个是显示所有的状态的命令； 1、慢查询mysql> show variables like '%slow%';+---------------------+-----------------------------------+| Variable_name       | Value                             |+---------------------+-----------------------------------+| log_slow_queries    | ON                                || slow_launch_time    | 2                                 || slow_query_log      | ON                                || slow_query_log_file | /var/lib/mysql/localhost-slow.log |+---------------------+-----------------------------------+4 rows in set (0.00 sec)显示出慢查询限制时间为2秒，慢查询日志文件所在的目录 mysql> show global status like '%slow%';+---------------------+-------+| Variable_name       | Value |+---------------------+-------+| Slow_launch_threads | 0     || Slow_queries        | 38    |+---------------------+-------+2 rows in set (0.00 sec) 打开慢查询日志可能会对系统有一点点的影响，如果你的MySQL是主从复制结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能的影响也会很小。另外，可用MySQL自带的命令mysqldumpslow进行查询。比如，下面的命令可以查询出访问次数最多的20个SQL语句mysqldumpslow -s c -t 20 host-slow.log 2、连接数如果经常出现MySQL：ERROR 1040:Too manyconnections 的情况，一种情况是访问量确实很高，MySQL服务器扛不住了，这个时候要考虑增加从服务器分散读压力。另外一种情况是MySQL配置文件中max_connections的值过小。所用命令如下：mysql> show variables like 'max_connections';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 800   |+-----------------+-------+1 row in set (0.00 sec)这台MySQL服务器的最大连接数是800，然后再查询一下该服务器响应的最大连数：mysql> show global status like 'Max_used_connections';+----------------------+-------+| Variable_name        | Value |+----------------------+-------+| Max_used_connections | 88    |+----------------------+-------+1 row in set (0.00 sec) MySQL服务器的最大连接数是88，没有达到服务器连接数的上限800，应该不会出现1040错误。比较理想的设置是：Max_used_connections / max_connections * 100% 这个数值在85%左右最大连接数占上限连接数和85%左右，如果发现比例在10%以下，则说明MySQL服务器连接数的上限设置的过高了。 3、key_buffer_sizekey_buffer_size是设置MyISAM表缓存空间的大小，此参数对MyISAM表性能影响最大。 mysql> show variables like 'key_buffer_size'; +-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| key_buffer_size | 268435456 |+-----------------+-----------+1 row in set (0.00 sec)从上面的配置可以看出，分配了256MB内存给key_buffer_size.下面再来看一下它的使用情况：mysql> show global status like 'key_read%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Key_read_requests | 15319 || Key_reads         | 3     |+-------------------+-------+2 rows in set (0.00 sec)一共有15319个索引读取请求，有3个请求在内存中没找到，直接从硬盘读取索引，计算索引未命中缓存的的概率：Key_cache_miss_rate = Key_reads / Key_read_requests * 100%比如上面的数据，Key_cache_miss_rate 为0.01958%，这个未命中的概率很小，效果上已经很好了，Key_cache_miss_rate在0.1%（即每1000个请求有一个直接读硬盘）以下都很好，如果Key_cache_miss_rate在0.01%以下的话，则说明key_buffer_size分配得过多，可以适当减少。MySQL服务器还提供了key_blocks_*参数，如下所示： mysql> show global status like 'key_blocks_u%';+-------------------+--------+| Variable_name     | Value  |+-------------------+--------+| Key_blocks_unused | 6      || Key_blocks_used   | 231957 |+-------------------+--------+2 rows in set (0.00 sec)Key_blocks_unused表示未使用的缓存簇（blocks)数，Key_blocks_used表示曾经用到的最大的blocks数。比如这台服务器，所有的缓存都用到了，要么增加key_buffer_size，要么就是过度索引，把缓存占满了。比较理想的设置是：Key_blocks_used / (key_blocks_unused + Key_blocks_used) * 100% ==80% 4、临时表 当执行语句时，关于已经被创造了的隐含临时表的数量，我们可以用如下命令查询其具体情况：mysql> show global status like 'created_tmp%';+-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Created_tmp_disk_tables | 135      || Created_tmp_files       | 5        || Created_tmp_tables      | 37526111 |+-------------------------+----------+3 rows in set (0.00 sec) 每次创建临时表时，Created_tmp_tables都会增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也会增加。Created_tmp_files表示MySQL服务创建的临时文件数，比较理想的配置是：Created_tmp_disk_tables / created_tmp_tables  * 100%  show variables where Variable_name in ('tmp_table_size','max_heap_table_size');+---------------------+-----------+| Variable_name       | Value     |+---------------------+-----------+| max_heap_table_size | 67108864  || tmp_table_size      | 268435456 |+---------------------+-----------+2 rows in set (0.00 sec)只有64MB以下的临时表才能放在内存中，超过的就会用到硬盘临时表。 5、打开表的情况Open_tables 表示打开表的数量，Open_tables表示打开过的表数量，我们可以用如下命令查看其具体情况：mysql> show global status like 'open%tables%';+---------------+-------+| Variable_name | Value |+---------------+-------+| Open_tables   | 646   || Opened_tables | 653   |+---------------+-------+如果Opened_tables数量过大，说明配置中table_cache（MySQL5.1.3之后这个值叫做table_open_cache)的值可能太小。我们查询一下服务器table_cache值：mysql> show variables like 'table_open_cache';+---------------+-------+| Variable_name | Value |+---------------+-------+| table_cache   | 1024  |+---------------+-------+比较合适的值为：Open_tables / Opened_tables * 100% >=85%Open_tables / table_cache * 100%  show variables like 'table_open_cache';+------------------+-------+| Variable_name    | Value |+------------------+-------+| table_open_cache | 1024  |+------------------+-------+1 row in set (0.00 sec) 6 进程使用情况如果我们在MySQL服务器的配置文件中设置了thread_cache_size，当客户端断开之时，服务器处理此客户请求的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存未达到上限）。Threads_created表示创建过的线程数，我们可以用如下命令查看：mysql> show global status like 'Thread%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Threads_cached    | 59    || Threads_connected | 44    || Threads_created   | 138   || Threads_running   | 1     |+-------------------+-------+如果发现Threads_created的值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗费资源的，可以适当的增大配置文件中thread_cache_size的值。查询服务器thread_cache_size配置，如下所示：mysql> show variables like 'thread_cache_size';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| thread_cache_size | 64    |+-------------------+-------+示例中的MySQL服务器还是挺健康的。如果运用命令：mysql> show full processlist;显示出大量的sending data 而且时间很长那就有可能是数据库一直在创建进程，此时要增大thread_cache_size的值。 7、查询缓存（query cache)它主要涉及两个参数，query_cache_size是设置MySQL的Query Cache大小，query_cache_type是设置使用查询缓存的类型，我们可以用如下命令查看其具体情况：mysql>show global status like 'qcache%';+-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Qcache_free_blocks      | 452      || Qcache_free_memory      | 83214448 || Qcache_hits             | 52902869 || Qcache_inserts          | 1856039  || Qcache_lowmem_prunes    | 305804   || Qcache_not_cached       | 42944    || Qcache_queries_in_cache | 80812    || Qcache_total_blocks     | 162634   |+-------------------------+----------+8 rows in set (0.01 sec)MySQL查询缓存变量的相关解释如下。Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行处理，从而得到一个空闲块。Qcache_free_memory：缓存中的空闲内存。Qcache_hits：多少次命中。通过这个参数可以查看到Query Cache的基本效果。Qcache_inserts:插入次数，每次插入一个查询时就增加1.命中次数除以插入次数就是命中比率。Qcache_lowmem_prunes:多少条Query因为内存不足而被清除出Query Cache 通过Qcache_lowmem_prunes和Qcache_free_memory相互结合，能够更清楚地了解到系统中Query Cache的内存大小是否真的足够，是否非常频繁地出现因为内存不足而有Query被换出的情况。Qcache_not_cached:不适合进行缓存的查询数量，通常是由于这些查询不是SELECT语句或者用了now()之类的函数。Qcache_queries_in_cache:当前缓存的查询（和响应）数量。Qcache_total_blocks:缓存中块的数量。我们再查询一下服务器上关于query_cache的配置命令如下：mysql>show variables like 'query_cache%';+------------------------------+-----------+| Variable_name                | Value     |+------------------------------+-----------+| query_cache_limit            | 3145728   || query_cache_min_res_unit     | 4096      || query_cache_size             | 268435456 || query_cache_type             | ON        || query_cache_wlock_invalidate | OFF       |+------------------------------+-----------+5 rows in set (0.00 sec)各字段的解释如下：query_cache_limit:超过此大小的查询将不缓存。query_cache_min_res_unit:缓存块的最小值。query_cache_type:缓存类型，决定缓存什么样的查询，示例中表示不缓存select sql_no_cache查询 query_cache_wlock_invalidate:表示当有其他客户端正在对MyISAM表示进行写操作时，读请求是要等WRITE LOCK释放资源后再查询还是允许直接从Query Cache中读取结果，默认为OFF（可以直接从Query Cache中取得结果）。 query_cache_min_res_unit 的配置是一柄“双刃剑”，默认是4KB，设置的值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。查询缓存碎片率=Qcache_free_blocks / Qcache_total_blocks * 100%如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。查询缓存利用率=（query_cache_size - Qcache_free_memory)/query_cache_size * 100%查询缓存利用率在25%以下的话说明query_cache_size 设置得过大，可适当减小；查询缓存利用率在80%以上的而且Qcache_lowmem_prunes>50的话则说明query_cache_size可能有点小，要不就是碎片太多。查询缓存命中率=（Qcache_hits - Qcache_inserts）/ Qcache_hits * 100%示例服务器中的查询缓存碎片率等于0.2779%，查询缓存利用率等于61.5%，查询缓存命中率等于96.49%，说明命中率还是挺高的，而且碎片很少。 8、排序使用情况它表示系统中对数据进行排序时所使用的Buffer，我们可以用如下命令查看：mysql> show global status like 'sort%';+-------------------+----------+| Variable_name     | Value    |+-------------------+----------+| Sort_merge_passes | 23       || Sort_range        | 35536    || Sort_rows         | 19732031 || Sort_scan         | 46755    |+-------------------+----------+4 rows in set (0.06 sec)Sort_merge_passes包括如下步骤：MySQL首先会尝试在内存中做排序，使用的内存大小由系统变量sort_buffer_size来决定，如果它不够大则把所有的记录都读到内存中，而MySQL则会把每次在内存中排序的结果存到临时文件中，等MySQL找到所有记录之后，再把临时文件中的记录做一次排序。这次再排序就会增加sort_merge_passes。实际上，MySQL会用另一个临时文件来存储再次排序的结果，所以我们通常会看到sort_merge_passes增加的数值是建立临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增加sort_buffer_size会减少sort_merge_passes和创建临时文件的次数，但是盲目地增大sort_buffer_size并不一定能提高速度。 9、文件打开数(open_files)我们在处理MySQL故障时，发现当open_files大于open_files_limit值时，MySQL数据库就会发生卡住的现象，导致Apache服务器打不开相应的页面，这个问题大家在工作中注意，我们可以利用如下命令查看其具体情况：mysql> show global status like 'open_files';+---------------+-------+| Variable_name | Value |+---------------+-------+| Open_files    | 25    |+---------------+-------+1 row in set (0.00 sec) mysql> show variables like 'open_files_limit';+------------------+-------+| Variable_name    | Value |+------------------+-------+| open_files_limit | 8192  |+------------------+-------+1 row in set (0.00 sec)比较合适的配置：Open_files / open_files_limit * 100%  show status like 'Innodb_buffer_pool_%';+-----------------------------------+------------+| Variable_name                     | Value      |+-----------------------------------+------------+| Innodb_buffer_pool_pages_data     | 63285      || Innodb_buffer_pool_pages_dirty    | 3          || Innodb_buffer_pool_pages_flushed  | 37081      || Innodb_buffer_pool_pages_free     | 0          || Innodb_buffer_pool_pages_misc     | 2251       || Innodb_buffer_pool_pages_total    | 65536      || Innodb_buffer_pool_read_ahead_rnd | 19214      || Innodb_buffer_pool_read_ahead_seq | 16193      || Innodb_buffer_pool_read_requests  | 3274048071 || Innodb_buffer_pool_reads          | 562959     || Innodb_buffer_pool_wait_free      | 0          || Innodb_buffer_pool_write_requests | 1159654    |+-----------------------------------+------------+12 rows in set (0.00 sec)通过此命令得出的结果可以计算出InnoDB buffer pool的read命中率大约为：（3274048071 - 63285） / 3274048071 = 99.99%write命中率大约为：63285 / 65536 * 100% = 96.56%我们发现这个值设置得过小，后期考虑将其增加到3072MB左右，另外需要注意的是，32位系统因为系统方面的制约，此值只能设置为2.2GB~2.7GB，所以建议大家的数据库系统为64位。  另外，等MySQL在线上稳定运行一段时间后，可以使用MySQL调优脚本tuning-primer.sh来检查参数设置的否全理。下载地址：http://launchpad.net/mysql-tuning-primer/trunk/1.5-r5/+download/tuning-primer.sh。该脚本使用“SHOW STATUS LIKE...”和“SHOW VARIABLES LIKE...”命令获得MySQL相关变量和运行状态。然后根据推荐的调优参数对当前的MySQL数据库进行测试。最后根据不同颜色的标识来提醒用户需要注意的各个参数设置。该版本目前兼容MySQL3.23和更高的版本（包含5.1），但是尚不支持MySQL5.5版本。当前版本会处理如下这些推荐的参数：Slow Query Log （慢查询日志）Max Connections （最大连接数）Worker Threads （工作线程）Key Buffer （Key 缓冲）Query Cache （查询缓存）Sort Buffer (排序缓存）Joins （连接）Temp Tables（临时表）Table （Open &amp；Definition）Cache（表缓存）Table Locking（表锁定）Tables Scans(read_buffer)(表扫描，读缓冲）InnoDB Status（InnoDB 状态） 整个mysql的简单优化就这样，这些参数设置只是作为参考，实际需要还要看自己的服务器。还有sql查询语句的优化应该放在重中之重。