關鍵字:?mysql
*1, 查看?MySQL?服務器配置信息
*1. mysql>?show?variables;?
?
*通過?show?status 來優化?MySQL?數據庫
?
?
?
*2, 查看?MySQL?服務器運行的各種狀態值
*1. mysql>?show?global?status;??
?
?
*3, 慢查詢
**1. mysql>?show?variables?like '%slow%';??
*2. +------------------+-------+??
*3. |?Variable_name????|?Value?|??
*4. +------------------+-------+??
*5. |?log_slow_queries?|?OFF???|??
**6. |?slow_launch_time?| 2?????|??
*7. +------------------+-------+??
**8. mysql>?show?global?status?like '%slow%';?
*9. +---------------------+-------+??
10.?|?Variable_name????????|?Value?|?
11.?+---------------------+-------+??
12.?|?Slow_launch_threads??|?0?????|?
13.?|?Slow_queries?????????|?279???|?
14.?+---------------------+-------+??
?
?
配置中關閉了記錄慢查詢（最好是打開，方便優化），超過?2?秒即為慢查詢，一共有?279?條慢查詢
?
*4, 連接數
?
**1. mysql>?show?variables?like 'max_connections';?
*2. +-----------------+-------+?
*3. |?Variable_name???|?Value?|?
*4. +-----------------+-------+?
*5. |?max_connections?|?500???|?
*6. +-----------------+-------+?
7.
**8. mysql>?show?global?status?like 'max_used_connections';??
*9. +----------------------+-------+?
10.?|?Variable_name????????|?Value?|?
11.?+----------------------+-------+?
12.?|?Max_used_connections?|?498???|?
13.?+----------------------+-------+?
?
?
設置的最大連接數是?500，而響應的連接數是?498??
*max_used_connections?/?max_connections?*?100%?=?99.6% （理想值≈?85%）
MySQL設置默認值。
5,?key_buffer_size?
key_buffer_size?是對?MyISAM?表性能影響最大的一個參數,??不過數據庫中多為?Innodb??
?
**1. mysql>?show?variables?like 'key_buffer_size';?
*2. +-----------------+----------+??
*3. |?Variable_name???|?Value????|??
*4. +-----------------+----------+??
*5. |?key_buffer_size?|?67108864?|??
*6. +-----------------+----------+??
7.
**8. mysql>?show?global?status?like 'key_read%';??
*9. +-------------------+----------+?
10.?|?Variable_name?????|?Value????|?
11.?+-------------------+----------+?
*12.?|?Key_read_requests?| 25629497?|?
*13.?|?Key_reads?????????| 66071????|?
14.?+-------------------+----------+?
?
?
一共有?25629497?個索引讀取請求，有?66071?個請求在內存中沒有找到直接從硬盤讀取索引，計算索引未命中緩存的概率：
key_cache_miss_rate??＝??Key_reads?/?Key_read_requests?*?100%?=0.27%??
需要適當加大?key_buffer_size?
?
**1. mysql>?show?global?status?like 'key_blocks_u%';?
*2. +-------------------+-------+?
*3. |?Variable_name?????|?Value?|?
*4. +-------------------+-------+?
**5. |?Key_blocks_unused?| 10285?|?
**6. |?Key_blocks_used???| 47705?|?
*7. +-------------------+-------+?
?
?
Key_blocks_unused?表示未使用的緩存簇(blocks)數，Key_blocks_used?表示曾經用到的最大的?blocks?數
*Key_blocks_used?/?(Key_blocks_unused?+?Key_blocks_used)?*?100%?≈?18% （理想值≈?80%）
?
?
6,臨時表
?
**1. mysql>?show?global?status?like 'created_tmp%';??
*2. +-------------------------+---------+?
*3. |?Variable_name???????????|?Value???|?
*4. +-------------------------+---------+?
*5. |?Created_tmp_disk_tables?|?4184337?|?
*6. |?Created_tmp_files???????|?4124????|?
*7. |?Created_tmp_tables??????|?4215028?|?
*8. +-------------------------+---------+?
?
?
每次創建臨時表，Created_tmp_tables?增加，如果是在磁盤上創建臨時表，Created_tmp_disk_tables?也增加,Created_tmp_files?表示
MySQL?服務創建的臨時文件文件數：
**Created_tmp_disk_tables?/?Created_tmp_tables?*?100% ＝??99% （理想值<=?25%）
?
*1. mysql>?show?variables?where?Variable_name?in?('tmp_table_size',?'max_heap_table_size');??
?
*2. +---------------------+-----------+?
*3. |?Variable_name???????|?Value?????|?
*4. +---------------------+-----------+?
**5. |?max_heap_table_size?| 134217728?|?
**6. |?tmp_table_size??????| 134217728?|?
*7. +---------------------+-----------+?
?
?
需要增加?tmp_table_size?
?
?
7,open?table??的情況
**1. mysql>?show?global?status?like 'open%tables%';??
*2. +---------------+-------+??
*3. |?Variable_name?|?Value?|??
*4. +---------------+-------+??
**5. |?Open_tables???| 1024??|??
**6. |?Opened_tables?| 1465??|??
*7. +---------------+-------+??
?
?
*Open_tables 表示打開表的數量，Opened_tables?表示打開過的表數量，如果?Opened_tables?數量過大，說明配置中??table_cache(5.1.3
之后這個值叫做?table_open_cache)值可能太小，我們查詢一下服務器?table_cache?值
**1. mysql>?show?variables?like 'table_cache';??
*2. +---------------+-------+??
*3. |?Variable_name?|?Value?|??
*4. +---------------+-------+??
**5. |?table_cache???| 1024??|??
*6. +---------------+-------+??
?
?
**Open_tables?/?Opened_tables?*?100%?=69% 理想值 （>=?85%）
*Open_tables?/?table_cache?*?100%?=?100% 理想值??(<=?95%)??
?
?
*8, 進程使用情況
**1. mysql>?show?global?status?like 'Thread%';??
*2. +-------------------+-------+?
*3. |?Variable_name?????|?Value?|?
*4. +-------------------+-------+?
**5. |?Threads_cached????| 31????|?
**6. |?Threads_connected?| 239???|?
**7. |?Threads_created???| 2914??|?
**8. |?Threads_running???| 4?????|?
*9. +-------------------+-------+?
?
?
?
如果我們在?MySQL?服務器配置文件中設置了?thread_cache_size，當客戶端斷開之后，服務器處理此客戶的線程將會緩存起來以響應
下一個客戶而不是銷毀（前提是緩存數未達上限）。Threads_created?表示創建過的線程數，如果發現?Threads_created?值過大的話，
表明??MySQL?服務器一直在創建線程，這也是比較耗資源，可以適當增加配置文件中?thread_cache_size?值，查詢服務器
thread_cache_size?配置：
**1. mysql>?show?variables?like 'thread_cache_size';?
?
*2. +-------------------+-------+?
*3. |?Variable_name?????|?Value?|?
*4. +-------------------+-------+?
**5. |?thread_cache_size?| 32????|?
*6. +-------------------+-------+?
?
?
*9, 查詢緩存(query?cache)
**1. mysql>?show?global?status?like 'qcache%';??
*2. +-------------------------+----------+??
*3. |?Variable_name???????????|?Value????|??
*4. +-------------------------+----------+??
*5. |?Qcache_free_blocks??????|?2226?????|??
*6. |?Qcache_free_memory??????|?10794944?|??
*7. |?Qcache_hits?????????????|?5385458??|??
*8. |?Qcache_inserts??????????|?1806301??|??
*9. |?Qcache_lowmem_prunes????|?433101???|??
10.?|?Qcache_not_cached???????|?4429464??|??
11.?|?Qcache_queries_in_cache?|?7168?????|??
12.?|?Qcache_total_blocks?????|?16820????|??
13.?+-------------------------+----------+??
?
?
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?的配置：
**1. mysql>?show?variables?like 'query_cache%';?
*2. +------------------------------+----------+??
*3. |?Variable_name????????????????|?Value????|??
*4. +------------------------------+----------+??
*5. |?query_cache_limit????????????|?33554432?|??
*6. |?query_cache_min_res_unit?????|?4096?????|??
*7. |?query_cache_size?????????????|?33554432?|??
*8. |?query_cache_type?????????????|?ON???????|??
*9. |?query_cache_wlock_invalidate?|?OFF??????|??
10.?+------------------------------+----------+??
?
?
各字段的解釋：
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％，命中率很差，可能寫操作比較
頻繁吧，而且可能有些碎片。
?
?
?
10,排序使用情況
?
**1. mysql>?show?global?status?like 'sort%';??
*2. +-------------------+----------+?
*3. |?Variable_name?????|?Value????|?
*4. +-------------------+----------+?
**5. |?Sort_merge_passes?| 2136?????|?
**6. |?Sort_range????????| 81888????|?
**7. |?Sort_rows?????????| 35918141?|?
**8. |?Sort_scan?????????| 55269????|?
*9. +-------------------+----------+?
?
?
*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/??
?
?
?
?
?
?
?
11.文件打開數(open_files)
?
**1. mysql>?show?global?status?like 'open_files';??
*2. +---------------+-------+??
*3. |?Variable_name?|?Value?|??
*4. +---------------+-------+??
**5. |?Open_files????| 821???|??
*6. +---------------+-------+??
7.
**8. mysql>?show?variables?like 'open_files_limit';??
*9. +------------------+-------+??
10.?|?Variable_name????|?Value?|??
11.?+------------------+-------+??
*12.?|?open_files_limit?| 65535?|??
13.?+------------------+-------+??
?
?
比較合適的設置：Open_files?/?open_files_limit?*?100%?<=?75％
正常
?
?
*12, 表鎖情況
**1. mysql>?show?global?status?like 'table_locks%';??
*2. +-----------------------+---------+?
*3. |?Variable_name?????????|?Value???|?
*4. +-----------------------+---------+?
**5. |?Table_locks_immediate?| 4257944?|?
**6. |?Table_locks_waited????| 25182???|?
*7. +-----------------------+---------+?
?
?
Table_locks_immediate??表示立即釋放表鎖數，Table_locks_waited?表示需要等待的表鎖數，如果??Table_locks_immediate?/
Table_locks_waited?>?5000，最好采用?InnoDB?引擎，因為?InnoDB?是行鎖而?MyISAM?是表鎖，對于高并發寫入的應用?InnoDB?效果
會好些.?
?
*13. 表掃描情況
**1. mysql>?show?global?status?like 'handler_read%';?
*2. +-----------------------+-----------+?
*3. |?Variable_name?????????|?Value?????|?
*4. +-----------------------+-----------+?
**5. |?Handler_read_first????| 108763????|?
**6. |?Handler_read_key??????| 92813521??|?
**7. |?Handler_read_next?????| 486650793?|?
**8. |?Handler_read_prev?????| 688726????|?
**9. |?Handler_read_rnd??????| 9321362???|?
*10.?|?Handler_read_rnd_next?| 153086384?|?
11.?+-----------------------+-----------+?
?
?
?
?
?
各字段解釋參見?http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html，調出服務器完成的查詢請求次數：
**1. mysql>?show?global?status?like 'com_select';??
*2. +---------------+---------+?
?
*3. |?Variable_name?|?Value???|?
*4. +---------------+---------+?
**5. |?Com_select????| 2693147?|?
*6. +---------------+---------+?
?
?
計算表掃描率：
*表掃描率 ＝??Handler_read_rnd_next?/?Com_select??
?
?
如果表掃描率超過?4000，說明進行了太多表掃描，很有可能索引沒有建好，增加?read_buffer_size?值會有一些好處，但最好不要超過
8MB。
?
