作者自己根据实际工作经验，撰写了这篇文章。根据status信息对MySQL服务器进行优化，希望大家能够根据本文，将自己的MySQL服务器优化得更加理想。

网上有许多的文章教怎样装备MySQL服务器，但考虑到服务器硬件装备的不同，详细运用的不同，那些文章的做法只能作为开始设置参阅，咱们需求依据自己的状况进行装备优化，好的做法是MySQL服务器安稳运转了一段时刻后运转，依据服务器的”状况”进行优化。

依据status信息对MySQL服务器进行优化（mysql status） MySQL 优化 status 第1张

mysql> show global status;

能够列出MySQL服务器运转各种状况值，别的，查询MySQL服务器装备信息句子：

mysql> show variables;

一、慢查询

mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| log_slow_queries | ON  |
| slow_launch_time | 2  |
+------------------+-------+

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name  | Value |
+---------------------+-------+
| Slow_launch_threads | 0  |
| Slow_queries  | 4148 |
+---------------------+-------+
装备中翻开了记载慢查询，执行时刻超越2秒的即为慢查询，体系显现有4148个慢查询，你能够剖析慢查询日志，找出有问题的SQL句子，慢查询时刻不宜设置过长，不然含义不大，***在5秒以内，假如你需求微秒等级的慢查询，能够考虑给MySQL打补丁：http://www.percona.com/docs/wiki/release:start，记住找对应的版别。


翻开慢查询日志或许会对体系功能有一点点影响，假如你的MySQL是主－从结构，能够考虑翻开其间一台从服务器的慢查询日志，这样既能够监控慢查询，对体系功能影响又小。


二、连接数


经常会遇见”MySQL: ERROR 1040: Too many connections”的状况，一种是访问量的确很高，MySQL服务器抗不住，这个时分就要考虑添加从服务器涣散读压力，别的一种状况是MySQL装备文件中max_connections值过小：


mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 256   |
+-----------------+-------+


这台MySQL服务器***连接数是256，然后查询一下服务器呼应的***连接数：


mysql> show global status like 'Max_used_connections';


MySQL服务器曩昔的***连接数是245，没有到达服务器连接数上限256，应该没有呈现1040过错，比较抱负的设置是：




Max_used_connections / max_connections * 100% ≈ 85%
***连接数占上限连接数的85％左右，假如发现份额在10%以下，MySQL服务器连接数上限设置的过高了。


三、Key_buffer_size


key_buffer_size是对MyISAM表功能影响***的一个参数，下面一台以MyISAM为首要存储引擎服务器的装备：


mysql> show variables like 'key_buffer_size';
mysql> show variables;0


分配了512MB内存给key_buffer_size，咱们再看一下key_buffer_size的运用状况：


mysql> show variables;2


一共有27813678764个索引读取恳求，有6798830个恳求在内存中没有找到直接从硬盘读取索引，核算索引未射中缓存的概率：




mysql> show variables;4
比方上面的数据，key_cache_miss_rate为0.0244%，4000个索引读取恳求才有一个直接读硬盘，现已很BT了，key_cache_miss_rate在0.1%以下都很好（每1000个恳求有一个直接读硬盘），假如key_cache_miss_rate在0.01%以下的话，key_buffer_size分配的过多，能够恰当削减。


MySQL服务器还供给了key_blocks_*参数：


mysql> show variables;5


Key_blocks_unused标明未运用的缓存簇(blocks)数，Key_blocks_used标明从前用到的***的blocks数，比方这台服务器，一切的缓存都用到了，要么添加key_buffer_size，要么便是过渡索引了，把缓存占满了。比较抱负的设置：


Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%


四、暂时表


mysql> show variables;7
每次创立暂时表，Created_tmp_tables添加，假如是在磁盘上创立暂时表，Created_tmp_disk_tables也添加,Created_tmp_files标明MySQL服务创立的暂时文件文件数，比较抱负的装备是：
mysql> show variables;8


比方上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 1.20%，应该恰当好了。咱们再看一下MySQL服务器对暂时表的装备：


mysql> show variables;9
只要256MB以下的暂时表才干悉数放内存，超越的就会用到硬盘暂时表。


五、Open Table状况


0
Open_tables标明翻开表的数量，Opened_tables标明翻开过的表数量，假如Opened_tables数量过大，阐明装备中table_cache(5.1.3之后这个值叫做table_open_cache)值或许太小，咱们查询一下服务器table_cache值：


1
比较适宜的值为：


Open_tables / Opened_tables * 100% >= 85%


Open_tables / table_cache * 100% <= 95%


六、进程运用状况


2
假如咱们在MySQL服务器装备文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以呼应下一个客户而不是毁掉（条件是缓存数未达上限）。Threads_created标明创立过的线程数，假如发现Threads_created值过大的话，标明MySQL服务器一直在创立线程，这也是比较耗资源，能够恰当添加装备文件中thread_cache_size值，查询服务器thread_cache_size装备：


3
示例中的服务器仍是挺健康的。


七、查询缓存(query cache)


4
MySQL查询缓存变量解说：


Qcache_free_blocks：缓存中相邻内存块的个数。数目大阐明或许有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行收拾，然后得到一个闲暇块。


Qcache_free_memory：缓存中的闲暇内存。


Qcache_hits：每次查询在缓存中射中时就增大


Qcache_inserts：每次刺进一个查询时就增大。射中次数除以刺进次数便是不中比率。


Qcache_lowmem_prunes：缓存呈现内存不足并且必需求进行收拾以便为更多查询供给空间的次数。这个数字***长时刻来看；假如这个数字在不断添加，就标明或许碎片十分严峻，或许内存很少。（上面的 free_blocks和free_memory能够告诉您归于哪种状况）


Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 句子或许用了now()之类的函数。


Qcache_queries_in_cache：当时缓存的查询（和呼应）的数量。


Qcache_total_blocks：缓存中块的数量。


咱们再查询一下服务器关于query_cache的装备：


5
各字段的解说：


query_cache_limit：超越此巨细的查询将不缓存


query_cache_min_res_unit：缓存块的最小巨细


query_cache_size：查询缓存巨细


query_cache_type：缓存类型，决议缓存什么样的查询，示例中标明不缓存 select sql_no_cache 查询


query_cache_wlock_invalidate：当有其他客户规矩在对MyISAM表进行写操作时，假如查询在query cache中，是否回来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%


示例服务器 查询缓存碎片率 ＝ 20.46％，查询缓存利用率 ＝ 62.26％，查询缓存射中率 ＝ 1.94％，射中率很差，或许写操作比较频频吧，并且或许有些碎片。


八、排序运用状况


6
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 并不一定能进步速度，见 How fast can you sort data with MySQL?（引自http://qroom.blogspot.com/2007/09/MySQL-select-sort.html，形似被墙）


别的，添加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值对排序的操作也有一点的优点，拜见：http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/


九、文件翻开数(open_files)


7
比较适宜的设置：Open_files / open_files_limit * 100% <= 75％


十、表锁状况


8
Table_locks_immediate标明当即开释表锁数，Table_locks_waited标明需求等候的表锁数，假如Table_locks_immediate / Table_locks_waited > 5000，***选用InnoDB引擎，由于InnoDB是行锁而MyISAM是表锁，关于高并发写入的运用InnoDB作用会好些。示例中的服务器Table_locks_immediate / Table_locks_waited ＝ 235，MyISAM就足够了。


十一、表扫描状况


9
各字段解说拜见http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html，调出服务器完结的查询恳求次数：


mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| log_slow_queries | ON  |
| slow_launch_time | 2  |
+------------------+-------+

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name  | Value |
+---------------------+-------+
| Slow_launch_threads | 0  |
| Slow_queries  | 4148 |
+---------------------+-------+0
核算表扫描率：


表扫描率 ＝ Handler_read_rnd_next / Com_select


假如表扫描率超越4000，阐明进行了太多表扫描，很有或许索引没有建好，添加read_buffer_size值会有一些优点，但***不要超越8MB。


跋文：


文中说到一些数字都是参阅值，了解基本原理就能够，除了MySQL供给的各种status值外，操作体系的一些功能指标也很重要，比方常用的top,iostat等，尤其是iostat，现在的体系瓶颈一般都在磁盘IO上，关于iostat的运用，能够参阅：http://www.php-oa.com/2009/02/03/iostat.html


【修改引荐】


  1. 浅谈SQL Server数据库并发测验办法
    2. 

  2. SQL Server 2005常见问题浅析
    3. 

  3. 影响SQL Server功能的三个要害点 
                                        

                                        

                                                                                

                                                                                        
                                                                                        
                                                                                        
                                                                                    

                                                                                
转载请说明出处
知优网 » 依据status信息对MySQL服务器进行优化（mysql status）
                                        

                                        
                                        

                                            

                                                

                                                                                                        站长资讯网友投稿帖
                                                                                                    

                                                

                                                    
站长资讯网友投稿帖

                                                

                                            

                                            

                                                分享到：
                                                
                                                
                                                                                            

                                        

                                    

                                

                                

                                    
                                

                                                                

                                     
相关推荐

                                    

                                                                                

                                            
                                        

                                                                                

                                            
                                        

                                                                                

                                            
                                        

                                                                                

                                            
                                        

                                                                                

                                            
                                        

                                                                                

                                            
                                        

                                                                                

                                            
                                        

                                                                                

                                            
                                        

                                                                                

                                            
                                        

                                                                                

                                            
                                        

                                                                            

                                

                                                                


    

                
    
            
                                                
            
        

        


    
发表评论 

    

                
您需要后才能发表评论