MySQL系統調優及問題查找 性能優化相關狀態參數 SHOW STATUS LIKE 'value'; connections 連接數 uptime 啟動時間 show_queries 慢查詢次數 com_select 查詢操作次數 com_insert 插入操作次數 com_update 更新操作次數 com_delete 刪除操作次數 www.2cto.com 分析查詢語句 EXPLAIN/DESC SELECT; 禁用/啟用索引 ALTER TABLE table DISABLE/ENABLE KEYS; 禁用唯一索引 SET UNIQUE_CHECK=0/1 分析、檢查和優化表 ANALYZE TABLE table1 [,table2 ...] CHECK TABLE table1[,table2 ...] OPTIMIZE TABLE table1[,table2...] 分析SQL語句 explain select count(*), max(id), min(id) from user\G 通過explain分析SQL語句，獲知執行情況 Profiling的使用 打開profiling 參數 >set profiling=1; 執行一些SQL語句後就可以查看query的profile 信息 >show profiles; >show profiles cpu, blockio for query 6; #查看query 6所使用的CPU IO資源情況 www.2cto.com 索引中的限制 1、MyISAM引擎索引長度總和不能超過1000字節 2、BLOB和TEXT類型的列只能創建前綴索引 3、MySql不支持函數索引 4、使用不等於( != 或 <>) 的時候無法使用索引 5、過濾字段使用了函數後（如: abs(column)) 無法使用索引 6、Join語句中Join條件字段類型不一致時，無法使用索引 7、使用Like操作的時候如果條件以通配符開始 ( '%abc...')無法使用索引 8、使用非等值查詢時，無法使用hash索引 查詢效率測試工具 mysqlslap $ mysqlslap --create-schema=example --query="select * from group_message where user_id=3 AND subject like 'weiurezs%' --iterations=10000 #用於測試query的執行效率，給出平均、最大、最小執行時間。 FORCE INDEX(索引名稱) 強制使用索引 EXPLAIN select * from group_message FORCE INDEX(idx_group_message_author_subject) where user_id=3 AND author='3' AND subject like 'weiurazs%'\g 性能調優——log設置 Mysql的log項有：錯誤日志、更新日志、二進制日志、查詢日志、慢查詢日志 Binlog >show variables like '%binlog%'; 慢查詢 >show veriables like 'log_slow%'; >show variables like 'long_query%'; long_query_time最小值為1秒，如果需要進一步縮短慢查詢的時間限制，可以使用Percona提供的microslow-path(http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-path-installation-walk-through/) www.2cto.com 性能調優——Query Cache 查看Query Cache系統變量 >show variables like '%query_cache%'; 了解Query Cache的使用情況 >show status like 'Qcache%'; cache命中率= Qcache_hits / (Qcache_hits + Qcache_inserts) Qcache_hits / (Qcache_hits + Com_select) 應該更准確些 弊端： 1、Query語句的hash運算和查找資源增加CPU資源的消耗 2、Query Cache失效問題（當表的更新頻繁時會造成非常高的失效率 3、Query Cache中緩存的Result Set, 而不是頁面，可能造成內存的過度消耗，以及因內存不足造成過多的換入換出導致命中率的下降。 應對措施： 1、對那些經常更新的記錄指定SQL_NO_CACHE的SQL Hint，強制MySQL不緩存。 2、對那些大部分時候都是靜態的數據指定SQL_CACHE，使用CACHE。 3、對那些Result Set較大的的Query要麼使用SQL_NO_CACHE,強制不使用CACHE，或者通過設置query_cache_limit參數來控制query中cache的最大Result Set，系統默認為1M，大於此設定值的Result Set將不會Cache。 Query Cache的限制 1、 5.1.17 之前的版本不能 Cache 幫定變量的 Query ，但是從 5.1.17 版本開始， Query Cache 已經開始支持幫定變量的 Query 了； 2、 所有子查詢中的外部查詢 SQL 不能被 Cache ； 3、在 Procedure ， Function 以及 Trigger 中的 Query 不能被 Cache ； 4、包含其他很多每次執行可能得到不一樣結果的函數的 Query 不能被 Cache 。 www.2cto.com 性能調優——其他常用優化 max_connections(最大連接數)：一般設置為 500—800左右 max_user_connections(每個用戶允許的最大連接數)：一般不做限制 net_buffer_length(網絡傳輸緩存)：默認16KB基本夠用 thread_cache_size(Thread Cache池應該存放的連接線程數): 不應該小於應用系統對數據庫實際並發請求數，一般50-100之間。對短連接效果很好。 相關系統設置值及狀態值 >show variables like 'thread%'; >show status like 'connections'; >show status like '%thread%'; Thread Cache 命中率：（應該保持在90%以上） Thread_Cache_hit = (Connections - Threads_created) / Connections * 100%; MyISAM引擎優化 MyISAM引擎適用場景：以讀為主的非事務性數據系統，對數據的准確性要求不高時有優異的性能表現。 系統參數： key_buffer_size: 索引緩存大小 key_buffer_block_size: 索引緩存中的Cache Block Size: key_cache_division_limit: LRU鏈表中的Hot Area和Warm Area的分界值（范圍1-100），系統默認100，及只有Warm Cache。 key_cache_aeg_threshold: 控制 Cache Block從Hot Area降到 Warm Area的限制 性能參數： key_block_not_flushed 已經更改但還未刷新到磁盤的Dirty Cache Block key_blocks_unused 目前未被使用的Cache Block數目 key_read_requests Cache Block被請求讀取的總次數 key_read, 在Cache Block中找不到需要讀取的Key信息後到 .MYI 文件中讀取的次數 key_write_requests, Cache Block被請求修改的總次數 key_writes 在Cache Block中找不到需要修改的Key信息後到 .MYI 文件中讀入再修改的次數 參數合理性判斷指標： Key_buffer_UsageRatio = ( 1- Key_blocks_used / (key_blocks_used +key_blocks_unused ) ) * 100% (應該在99%以上，如果該值過低，說明key_buffer_size設置過大，MySQL根本用不完) Key_buffer_read_HitRatio = ( 1- Key_reads / key_read_requests ) * 100% (應該在99%以上，如果值過低，說明key_buffer_size設置過小，需要增加；也可能是key_cache_age_threshold 和 key_cache_division_limit 的設置不當，造成 Key Cache cache 失效太快 。一般來說，在實際應用場景中，很少有人調整 key_cache_age_threshold 和 key_cache_division_limit 這兩個參數的值，大都是使用系統的默認值) Key_buffer_write_HitRatio = ( 1- Key_writes / key_Write_requests ) * 100% 多Cache系統 MySQL 官方建議在比較繁忙的系統上一般可以設置三個 Key Cache ： 一個 Hot Cache 使用 20% 的大小用來存放使用非常頻繁且更新很少的表的索引； 一個 Cold Cache 使用 20% 的大小用來存放更新很頻繁的表的索引； 一個 Warm Cache 使用剩下的 60% 空間，作為整個系統默認的 Key Cache ； Key Cache 的 Mutex 問題：目前MySQL在Active線程數量較高時非常容易出現 Cache Block 的鎖問題 www.2cto.com Key Cache 預加載 在 MySQL 中，為了讓系統剛啟動之後不至於因為 Cache 中沒有任何數據而出現短時間的負載過高或 者 是響應不夠及時的問題。 MySQL 提供了 Key Cache 預加載功能，可以通過相關命令（ LOAD INDEX INTO CACHE tb_name_list ... ），將指定表的所有索引都加載到內存中，而且還可以通過相關參數控制是否只 Load 根結點和枝節點還是將頁節點也全部 Load 進來，主要是為 Key Cache 的容量考慮。 對於這種啟動後立即加載的操作，可以利用 MySQL 的 init_file 參數來設置相關的命令，如下： mysql@sky:~$ cat /usr/local/mysql/etc/init.sql SET GLOBAL hot_cache.key_buffer_size=16777216 SET GLOBAL cold_cache.key_buffer_size=16777216 CACHE INDEX example.top_message in hot_cache CACHE INDEX example.event in cold_cache LOAD INDEX INTO CACHE example.top_message,example.event IGNORE LEAVES 這裡我的 init file 中首先設置了兩個 Key Cache （ hot cache 和 cold cache ）各為 16M ，然後分別將 top_message 這個變動很少的表的索引 Cache 到 Hot Cache ，再將 event 這個變動非常頻繁的表的索引Cache 到了 Cold Cache 中，最後再通過 LOAD INDEX INTO CACHE 命令預加載了 top_message，groups 這兩個表所有索引的所有節點以及 event 和 user 這兩個表索引的非葉子節點數據到 Key Cache 中，以提高系統啟動之初的響應能力。 www.2cto.com 其他可以優化的地方 1. 通過 OPTIMIZE 命令來整理 MyISAM 表的文件。這就像我們使用 Windows 操作系統會每過一段時間後都會做一次磁盤碎片整理，讓系統中的文件盡量使用連續空間，提高文件的訪問速度。 MyISAM 在通過 OPTIMIZE 優化整理的時候，主要也是將因為數據刪除和更新造成的碎片空間清理，使整個文件連續在一起。一般來說，在每次做了較大的數據刪除操作之後都需要做一次 OPTIMIZE 操作。而且每個季度都應該有一次 OPTIMIZE 的維護操作。 2. 設置 myisam_max_[extra]_sort_file_size 足夠大，對 REPAIR TABLE 的效率可能會有較大改善。 3. 在執行 CREATE INDEX 或者 REPAIR TABLE 等需要大的排序操作的之前可以通過調整 session 級別的 myisam_sort_buffer_size 參數值來提高排序操作的效率。 4. 通過打開 delay_key_write 功能，減少 IO 同步的操作，提高寫入性能。 5. 通過調整 bulk_insert_buffer_size 來提高 INSERT...SELECT... 這樣的 bulk insert 操作的整體性能， LOAD DATA INFILE... 的性能也可以得到改善。當然，在設置此參數的時候，也不應該一味的追求很大，很多時候過渡追求極端反而會影響系統整體性能，畢竟系統性能是從整體來看的，而不能僅僅針對某一個或者某一類操作。 MySql MyISAM、INNODB類型表碎片優化 針對MyISAM表類型采用 OPTIMIZE TABLE table_name SQL語句清理碎片. InnoDB 使用的 Clustered Index,索引和數據綁定在一起,重排序是不現實的.所以不支持 MyISAM 式的 OPTIMIZE,而是綁定到了ALTER TABLE 命令上面.可以通過執行以下語句來整理碎片,提高索引速度: ALTER TABLE table_name ENGINE = Innodb; 這其實是一個NULL操作,表面上看什麼也不做,實際上重新整理碎片了.當執行優化操作時,實際執行的是一個空的 ALTER 命令,但是這個命令也會起到優化的作用,它會重建整個表,刪掉未使用的空白空間. innodb 存儲引擎優化 Innodb 存儲引擎和 MyISAM 存儲引擎最大區別主要有四點，第一點是緩存機制，第二點是事務支持 ，第三點是鎖定實現，最後一點就是數據存儲方式的差異。在整體性能表現方面， Innodb 和 MyISAM 兩個存儲引擎在不同的場景下差異比較大，主要原因也正是因為上面這四個主要區別所造成的。 www.2cto.com Innodb_buffer_pool_size 假設是一台單獨給 MySQL 使用的主機，物理內存總大小為 8G ， MySQL 最大連接數為 500 ，同時還使用了 MyISAM 存儲引擎，這時候我們的整體內存該如何分配呢？ 內存分配為如下幾大部分： 1、系統使用，假設預留 800M ； 2、線程獨享，約 2GB = 500 * (1MB + 1MB + 1MB + 512KB + 512KB) ，組成大概如下 : sort_buffer_size ： 1MB join_buffer_size ： 1MB read_buffer_size ： 1MB read_rnd_buffer_size ： 512KB thread_statck ： 512KB 3、MyISAM Key Cache ，假設大概為 1.5GB ； 4、Innodb Buffer Pool 最大可用量： 8GB - 800MB - 2GB - 1.5GB = 3.7GB 通過Buffer Pool 的實時狀態信息來確定InnoDB的Buffer Pool的使用是否高效： >show status like 'Innodb_buffer_pool_%'; Innodb_Buffer_pool_HitRatio = ( Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads ) / Innodb_buffer_pool_read_requests * 100% buffer pool 使用率 = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100% innodb_log_buffer_size 參數的使用 顧名思義，這個參數就是用來設置 Innodb 的 Log Buffer 大小的，系統默認值為 1MB 。 Log Buffer的主要作用就是緩沖 Log 數據，提高寫 Log 的 IO 性能。一般來說，如果你的系統不是寫負載非常高且以大事務居多的話， 8MB 以內的大小就完全足夠了。 >show status like 'innodb_log%'; (查看innodb_log_buffer_size 設置是否合理) Innodb 存儲引擎的物理存儲結構： 最小單位： page（默認16KB）——＞extent（64連續的page）——>segment（一個或多個extent）——>tablespace（最大的物理結構單位，由多個segment組成） InnoDB 性能監控 >show innodb status\G 持續獲取狀態信息的方法： create table innodb_monitor(a int) engine=innodb; 創建一個innodb_monitor空表後，InnoDB就會每隔15秒輸出一次信息並記錄到Error Log中，通過刪除該表停止監控 除此之外，我們還可以通過相同的方式打開和關閉 innodb_tablespace_monitor, innodb_lock_monitor, innodb_table_monitor 這三種監控功能 MySQL 高可用性方案 1、MySQL Replication 2、MySQL Cluster 3、DRDB
