標籤：flush sts 緩衝 dex room 開啟 sort 日誌 檔案的
之前一位童鞋發的：
5版郵件,在使用者量很大的情況下,如果做了分布式,如果在後端mysql上執行: mysql> show global status like ‘Thread%‘;
Threads_cached 0
Threads_connected 793
Threads_created 2397086
Threads_running 260 看到 Threads_created 的值特別大,Threads_cached為0,而且此時mysql串連數和負載特別高的情況下,一定在my.cnf中加上如下參數: thread_cache_size 128 先停掉各個前端,然後再重啟後端mysql.會發現各個前端機mysql的串連數總和會遠小於後端mysql的串連數,因為串連都被cached掉了. Mysql最佳化筆記： 從慢查詢、串連數、Key_buffer_size、暫存資料表、Open Table情況、進程使用方式、查詢快取、排序使用方式、檔案開啟數、表鎖情況、表掃描情況 這些方面去最佳化
==================================================================================== 1. 慢查詢：
慢查詢個數: mysql> show global status like ‘%slow_queries‘;
慢查詢日誌開關： mysql> show global variables like ‘%log_slow_queries‘; (1) 開啟慢查詢日誌： my.cnf
long_query_time = 5 // 執行超過多久的sql會被log下來，這裡是5秒
log-slow-queries = /var/log/slowquery.log //設定把日誌寫在那裡
log-queries-not-using-indexes //紀錄沒使用索引的sql
配置後重啟 mysql。 (2) 分析日誌： mysqldumpslow -s，是order的順序，主要有c,t,l,r和ac,at,al,ar，分別是按照query次數，時間，lock的時間和返回的記錄數來排序，前面加了a的時倒序。
-t，是top n的意思，即為返回前面多少條的資料
-g，後邊可以寫一個正則匹配模式，大小寫不敏感的 mysqldumpslow -s c -t 20 /var/log/slowquery.log //訪問次數最多的20個sql語句
mysqldumpslow -s t -t 20 /var/log/slowquery.log //執行時間最多的20個sql語句
mysqldumpslow -s r -t 20 /var/log/slowquery.log //返回記錄集最多的20個sql
mysqldumpslow -t 10 -s t -g "left join" /var/log/slowquery.log //這按照時間返回前10條裡面含有左串連的sql語句。 ================================================================================= 2. 串連數
最大串連數配置： show global variables like ‘max_connections‘;
曾用最大串連數： show global status like ‘max_used_connections‘;
當前串連數： show global status like ‘Threads_connected‘;
曾用來處理串連的線程數： show global status like ‘Threads_created‘; max_used_connections / max_connections * 100% （理想值 ≈ 85%） 修改最大串連數配置：
mysql> set GLOBAL max_connections=1000;
mysql> flush privileges
也可以修改/etc/my.cnf中的max_connections：
max_connections = 1000 max_used_connections： The maximum number of connections that have been in use simultaneously since the server started. 從MYSQL本次開機以來，曆史資料中的最大同時的並發聯結數。
Threads_created： The number of threads created to handle connections. ======================================================================================== 3. key_buffer_size key_buffer_size是對MyISAM表效能影響最大的一個參數,Innodb表不適用。 mysql> show global variables like ‘key_buffer_size‘;
mysql> show global status where Variable_name in (‘Key_read_requests‘,‘Key_reads‘) ;
Key_read_requests 737517278
Key_reads 276013
mysql> select 276013/737517278*100;
0.0374 一共有737517278個索引讀取請求，有276013個請求在記憶體中沒有找到直接從硬碟讀取索引，計算索引未命中緩衝的機率：
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% = 0.0374%
如果未命中機率過大，可以適當加大key_buffer_size
mysql> show global status where Variable_name in (‘key_blocks_used‘,‘key_blocks_unused‘);
Key_blocks_unused 131673
Key_blocks_used 217227
mysql> select 217227/(131673+217227)*100;
62.2605 Key_blocks_unused表示未使用的緩衝簇(blocks)數，Key_blocks_used表示曾經用到的最大的blocks數
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 62.2605% （理想值 ≈ 80%） ======================================================================================== 暫存資料表
mysql> show global status like ‘created_tmp%‘;
Created_tmp_disk_tables 1
Created_tmp_files 16
Created_tmp_tables 1145 每次建立暫存資料表，Created_tmp_tables增加;如果是在磁碟上建立暫存資料表，Created_tmp_disk_tables 也增加;Created_tmp_files表示MySQL服務建立的臨時檔案檔案數： Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 0.0873% （理想值<= 25%）
mysql> show variables where Variable_name in (‘tmp_table_size‘, ‘max_heap_table_size‘);
max_heap_table_size 16777216
tmp_table_size 33554432 可適當增加 tmp_table_size
======================================================================================== open table 的情況
mysql> show global status like ‘open%tables%‘;
Open_tables 512
Opened_tables 2008
mysql> show variables like ‘%table_cache%‘;
table_cache 512 mysql> select 512/2008*100;
25.4980
mysql> select 512/512*100;
100.0000 Open_tables 表示開啟表的數量，Opened_tables表示開啟過的表數量。
如果Opened_tables數量過大，說明配置中 table_cache(5.1.3之後這個值叫做table_open_cache)值可能太小. Open_tables / Opened_tables * 100% = 38.5196% 理想值 （>= 85%）
Open_tables / table_cache * 100% = 99.6094% 理想值 (<= 95%) ======================================================================================== 進程使用方式 mysql> show global status like ‘Thread%‘;
Threads_cached 7
Threads_connected 112
Threads_created 2397
Threads_running 24 mysql> show variables like ‘thread_cache_size‘;
thread_cache_size 64 如果我們在MySQL伺服器設定檔中設定了thread_cache_size，當用戶端斷開之後，伺服器處理此客戶的線程將會緩衝起來以響應下一個客戶而不是銷毀（前提是緩衝數未達上限）。
Threads_created表示建立過的線程數，如果發現Threads_created值過大的話，表明 MySQL伺服器一直在建立線程，這也是比較耗資源，可以適當增加設定檔中thread_cache_size值。 ======================================================================================== 查詢快取(query cache)
mysql> show global status like ‘qcache%‘;
Qcache_free_blocks 4119
Qcache_free_memory 11911976
Qcache_hits 873527
Qcache_inserts 1267423
Qcache_lowmem_prunes 713698
Qcache_not_cached 895888
Qcache_queries_in_cache 11301
Qcache_total_blocks 27096 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的配置： mysql> show variables like ‘query_cache%‘;
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 33554432
query_cache_type ON
query_cache_wlock_invalidate OFF 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，設定值大對大資料查詢有好處，但如果你的查詢都是小資料查詢，就容易造成記憶體片段和浪費。
mysql> select 4119 / 27096 *100；
15.2015
查詢快取片段率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查詢快取片段率超過20%，可以用FLUSH QUERY CACHE整理緩衝片段，或者試試減小query_cache_min_res_unit，如果你的查詢都是小資料量的話。
mysql> select (33554432-11911976) / 33554432 *100；
64.4995
查詢快取利用率 = (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％，命中率很差，可能寫操作比較頻繁吧，而且可能有些片段。
======================================================================================== 排序使用方式 mysql> show global status like ‘sort%‘;
Sort_merge_passes 2
Sort_range 18528
Sort_rows 390838
Sort_scan 3439 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/
mysql調優參考筆記
