MySQL 配置文件优化
MySQL 配置文件優化
查看MySQL服務器配置信息：
show variables;
查看MySQL服務器運行的各種狀態值：
show global status;
1. 慢查詢
show variables like '%slow%';
show global status like '%slow%';
mysqldumpslow -s c -t 20 host -slow.log
分析慢查詢日志，找出有問題的SQL語句，慢查詢時間不宜設置過長，否則意義不大，最好在5秒以內，如果你需要微秒級別的慢查詢，可以考慮給MySQL打補丁：http://www.percona.com/docs/wiki/release:start，記得找對應的版本。
2. 連接數
經常會遇見”MySQL: ERROR 1040: Too manyconnections” 的情況：
一種是訪問量確實很高，MySQL服務器抗不住，這個時候就要考慮增加從服務器分散讀壓力。
一種情況是MySQL配置文件中max_connections值過小。
查看最大連接數：
show variables like 'max_connections';
查看mysql服務器過去的最大連接數：
show global status like 'max_used_connections';
理想值：max_used_connections / max_connections * 100% ≈ 85%
最大連接數占上限連接數的85%左右，如果發現比例在10%以下，MySQL服務器連接數上限設置的過高了。
3. key_buffer_size
key_buffer_size是對MyISAM表性能影響最大的一個參數，不過數據庫中多為Innodb
查看key_buffer_size設置大小：
show variables like 'key_buffer_size';
查看key_buffer_size使用情況：
show global status like 'key_read%';
計算索引未命中緩存的概率：key_cache_miss_rate = Key_reads / Key_read_requests * 100%
key_cache_miss_rate在0.1%以下都很好(每1000個請求有一個直接讀硬盤)，如果key_cache_miss_rate在0.01%以下的話，key_buffer_size分配的過多，可以適當減少。
4. key_blocks_*參數
show global status like 'key_blocks_u%';
Key_blocks_unused：表示未使用的緩存簇(blocks)數
Key_blocks_used：表示曾經用到的最大的blocks數
理想值：Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
5. 臨時表
當執行語句時，關于已經被創造了的隱含臨時表的數量，查看命令：
show global status like 'created_tmp%';
每次創建表時Created_tmp_tables 都會增加，如果在磁盤上創建，Created_tmp_disk_tables也會增加，Created_tmp_files表示服務器創建的臨時文件數
理想值：Created_tmp_disk_tables / Created_tmp_tables * 100% ≤25%
查看服務器對臨時表的配置：
show variables where variable_name in('tmp_table_size','max_heap_table_size');
6.打開表的情況
show global status like 'open%tables%';
open_tables 表示打開表的數量，opened_tables表示打開過的表數量，如果opened_tables數量過大，說明配置中 table_cache(5.1.3之后這個值叫做table_open_cache)值可能太小
查詢服務器table_cache值：
show variables like 'table_open_cache';
理想值：open_tables / opened_tables * 100% ≥ 85%
理想值：open_tables / table_cache * 100% ≤95%
7. 進程使用情況
如果我們在MySQL服務器配置文件中設置了thread_cache_size，當客戶端斷開之后，服務器處理此客戶的線程將會緩存起來以響應下一個客戶而不是銷毀（前提是緩存數未達上限）。Threads_created表示創建過的線程數：
show global status like 'thread%';
如果發現Threads_created值過大的話，表明 MySQL服務器一直在創建線程，這也是比較耗資源，可以適當增加配置文件中thread_cache_size值，查詢服務器 thread_cache_size配置：
show variables like 'thread_cache_size';
8. 查詢緩存(query cache)
查看服務器query_cache配置情況：
show variables like 'query_cache%';
參數解釋：
query_cache_limit：超過此大小的查詢將不緩存。
query_cache_min_res_unit：緩存塊的最小值。
query_cache_size：查詢緩存大小。
query_cache_type：緩存類型，決定緩存什么樣的查詢。
query_cache_wlock_invalidate：表示當前客戶端，正在對MyISAM表進行寫操作時，讀請求是要等SRITE LOCK釋放資源后再查詢，還是允許直接從Query Cache中讀取結果，默認為OFF（可以直接從Query Cache 中讀取結果）。
查看服務器query_cache使用情況：
show global status like 'qcache%';
參數解釋：
Qcache_free_blocks：緩存中相鄰內存塊的個數。數目大說明可能有碎片。FLUSH QUERY CACHE會對緩存中的碎片進行整理，從而得到一個空閑塊。
Qcache_free_memory：緩存中的空閑內存。
Qcache_hits：每次查詢在緩存中命中時就增大
Qcache_inserts：每次插入一個查詢時就增大。命中次數除以插入次數就是不中比率。
Qcache_lowmem_prunes：緩存出現內存不足并且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看；如果這個數字不斷增長，表示可能碎片非常嚴重，或內存很少。
Qcache_not_cached：不適合進行緩存的查詢的數量，通常是由于這些查詢不是 SELECT 語句或者用了now()之類的函數。
Qcache_queries_in_cache：當前緩存的查詢（和響應）的數量。
Qcache_total_blocks：緩存中塊的數量。
9. 排序使用情況
它表示系統中對數據進行排序時所使用Buffer，查看命令：
show global status like 'sort%';
增大sort_buffer_size 會減少Sort_merge_passes和創建臨時文件的次數，但盲目增加并不一定能提高速度。
10. 文件打開數
當open_files大于open_files_limit值時，mysql數據庫就會發生卡住的現象，導致web服務器打開不響應的頁面。
查看open_files命令：
show global status like 'open_files';
查看open_files_limit命令：
show variables like 'open_files_limit';
理想值：open_files / open_files_limit *100% ≤ 75%
11.Innodb_buffer_pool_size 的合理設置
show status like 'Innodb_bufferpool_%';
根據實際運行場景進行調整
read 命中率:
(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / ?Innodb_buffer_pool_read_requests = ?
write 命中率:
Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total?
本文轉自 dengaosky 51CTO博客，原文鏈接：http://blog.51cto.com/dengaosky/1957327，如需轉載請自行聯系原作者
總結
以上是生活随笔為你收集整理的MySQL 配置文件优化的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Maven生命周期详解
- 下一篇： @interface CTDataFac