mysql組態變數詳解介紹

key_buffer_size

設定這個變數給鍵緩衝區(或者說金鑰快取)分配指定大小的空間。但是作業系統只有在實際用到這些空間的時候才會進行分配。例如，將鍵緩衝區大小設定為1GB，並不意味著伺服器就會真正地給它分配1GB空間。

對一個已有的緩衝設定非零值將會沖洗緩衝，從技術上來說，這是一個線上操作，但是它會阻止所有訪問該緩衝的動作，直到緩衝沖洗完成。

table_cache_size

設定這個變數是不會立即生效，要等到下一個線程開啟表的時候才會生效。當它生效的時候，MYSQL會檢查變數的值。如果值大於緩衝中表的數量，線程就可以把新開啟的表插入到緩衝中。如果值小於緩衝中表的數量，MySQL就會從緩衝中刪除掉沒有使用的表。

thread_cache_size

設定這個變數不會立即生效，生效被延時到了下一次線程關閉的時候。在那時，MySQL檢查緩衝中是否有空間儲存線程。如果是，它會把線程緩衝起來，供另外一個串連使用。如果不是，它會直接結束掉線程。在這種情況下，緩衝中線程的數量，以及線程緩衝使用的記憶體數量不會立即就下降。只有當新串連為了使用線程把它從緩衝中移走的時候才會看到下降。（MySQL只有在串連關閉的時候才會把線程加入緩衝，也只有在建立新串連的時候 才從緩衝中移除線程。）

query_cache_size

在伺服器啟用的時候，MySQL會為查詢快取一次性分配變數所定義數量的記憶體。如果更新了變數（即使把值設定為當前值），MySQL會立即刪除掉所有緩衝的查詢，重新把緩衝設定為定義的大小，並且重新初始化緩衝的記憶體。

read_buffer_size

MySQL只有在需要的時候才會為該緩衝區分配記憶體，但是它會一次性地把指定的大小分配給該緩衝。

read_rnd_buffer_size

MySQL只會在查詢的時候才給這個緩衝區分配記憶體，並且只會分配所需要的記憶體。

sort_buffer_size

MySQL只有在查詢需要排序的時候才會為這個緩衝區分配記憶體。但是只要發生了排序，MySQL會立即分配變數定義的所有記憶體，不管是否需要這麼大的空間。

調整MySQL運行參數，修改/etc/my.cnf檔案調整mysql運行參數重啟MySQL後生效，在MySQL4版本以後，一部分內部變數可以在MySQL運行時設定，不過重啟MySQL就失效了。
mysqld程式--目錄和檔案
basedir      = path          # 使用給定目錄作為根目錄(安裝目錄)。
datadir      = path          # 從給定目錄讀取資料庫檔案。
pid-file     = filename      # 為mysqld程式指定一個存放進程ID的檔案(僅適用於UNIX/Linux系統);
 
[mysqld]                       
socket = /tmp/mysql.sock     # 為MySQL客戶程式與伺服器之間的本地通訊指定一個通訊端檔案(Linux下預設是/var/lib/mysql/mysql.sock檔案)
port             = 3306      # 指定MsSQL偵聽的連接埠
key_buffer       = 384M      # key_buffer是用於索引塊的緩衝區大小，增加它可得到更好處理的索引(對所有讀和多重寫)。
                               索引塊是緩衝的並且被所有的線程共用，key_buffer的大小視記憶體大小而定。
table_cache      = 512       # 為所有線程開啟表的數量。增加該值能增加mysqld要求的檔案描述符的數量。可以避免頻繁的開啟資料表產生的開銷
sort_buffer_size = 2M        # 每個需要進行排序的線程分配該大小的一個緩衝區。增加這值加速ORDER BY或GROUP BY操作。
                               注意：該參數對應的分配記憶體是每串連獨佔！如果有100個串連，那麼實際分配的總共排序緩衝區大小為100×6=600MB
read_buffer_size = 2M        # 讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享。
query_cache_size = 32M       # 指定MySQL查詢結果緩衝區的大小
read_rnd_buffer_size    = 8M # 改參數在使用行指標排序之後，隨機讀用的。
myisam_sort_buffer_size =64M # MyISAM表發生變化時重新排序所需的緩衝
thread_concurrency      = 8  # 最大並發線程數，取值為伺服器邏輯CPU數量×2，如果CPU支援H.T超執行緒，再×2
thread_cache            = 8  # #緩衝可重用的線程數
skip-locking                 # 避免MySQL的外部鎖定，減少出錯幾率增強穩定性。
[mysqldump]
max_allowed_packet      =16M # 伺服器和用戶端之間最大能發送的可能資訊包
 
[myisamchk]
key_buffer   = 256M
sort_buffer  = 256M
read_buffer  = 2M
write_buffer = 2M
 
其他選擇性參數：
back_log = 384
    指定MySQL可能的串連數量。
    當MySQL主線程在很短時間內接收到非常多的串連請求，該參數生效，主線程花費很短時間檢查串連並且啟動一個新線程。
    back_log參數的值指出在MySQL暫時停止回應新請求之前的短時間內多少個請求可以被存在堆棧中。
    如果系統在一個短時間內有很多串連，則需要增大該參數的值，該參數值指定到來的TCP/IP串連的偵聽隊列的大小。
    試圖設定back_log高於你的作業系統的限制將是無效的。預設值為50。對於Linux系統推薦設定為小於512的整數。
max_connections = n
    MySQL伺服器同時處理的資料庫連接的最大數量(預設設定是100)。超過限制後會報 Too many connections 錯誤
key_buffer_size = n
    用來存放索引區塊的RMA值(預設設定是8M)，增加它可得到更好處理的索引(對所有讀和多重寫)
record_buffer：
    每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩衝區。
    如果你做很多順序掃描，你可能想要增加該值。預設數值是131072(128K)
wait_timeout：
    伺服器在關閉它之前在一個串連上等待行動的秒數。
interactive_timeout：
    伺服器在關閉它前在一個互動串連上等待行動的秒數。
    一個互動的客戶被定義為對 mysql_real_connect()使用 CLIENT_INTERACTIVE 選項的客戶。
    預設數值是28800，可以把它改為3600。
skip-name-resolve           
    禁止MySQL對外部串連進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。
    但需要注意，如果開啟該選項，則所有遠程主機串連授權都要使用IP地址方式，否則MySQL將無法正常處理串連請求！
log-slow-queries = slow.log 
    記錄慢查詢,然後對慢查詢一一最佳化
skip-innodb
skip-bdb
    關閉不需要的表類型,如果你需要,就不要加上這個
         
         
# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況；
如果Qcache_hits的值非常大，則表明查詢緩衝使用非常頻繁，如果該值較小反而會影響效率，那麼可以考慮不用查詢緩衝；
如果Qcache_free_blocks的值非常大，則表明緩衝區中片段很多。
 
 
##########################################
######      max_allowed_packet      ######
##########################################
通訊資訊包是發送至MySQL伺服器的單個SQL語句，或發送至用戶端的單一行。
在MySQL 5.1伺服器和用戶端之間最大能發送的可能資訊包為1GB。
當MySQL用戶端或mysqld伺服器收到大於max_allowed_packet位元組的資訊包時，將發出“資訊包過大”錯誤，並關閉串連。對於某些用戶端，如果通訊資訊包過大，在執行查詢期間，了能回遇到“丟失與MySQL伺服器的串連”錯誤。
用戶端和伺服器均有自己的max_allowed_packet變數，因此，如你打算處理大的資訊包，必須增加用戶端和伺服器上的該變數。
如果你正在使用mysql用戶端程式，其max_allowed_packet變數的預設值為16MB。要想設定較大的值，可用下述方式啟動mysql：
mysql> mysql --max_allowed_packet=32M
它將資訊包的大小設定為32MB。
伺服器的預設max_allowed_packet值為1MB。如果伺服器需要處理大的查詢，可增加該值（例如，如果準備處理大的BLOB列）。例如，要想將該設定為16MB，可採用下述方式啟動伺服器：
mysql> mysqld --max_allowed_packet=16M
 
也能使用選項檔案來設定max_allowed_packet。要想將伺服器的該變數設定為16MB，可在選項檔案中增加下行內容：
[mysqld]
max_allowed_packet=16M
增加該變數的值十分安全，這是因為僅當需要時才會分配額外記憶體。例如，僅當你發出長查詢或mysqld必須返回大的結果行時mysqld才會分配更多記憶體。該變數之所以取較小預設值是一種預防措施，以捕獲用戶端和伺服器之間的錯誤資訊包，並確保不會因偶然使用大的資訊包而導致記憶體溢出。
如果你正是用大的BLOB值，而且未為mysqld授予為處理查詢而訪問足夠記憶體的許可權，也會遇到與大資訊包有關的奇怪問題。如果懷疑出現了該情況，請嘗試在mysqld_safe指令碼開始增加ulimit -d 256000，並重啟mysqld。
##########################################
#####   MySQL怎樣開啟和關閉資料庫表  #####
##########################################
table_cache, max_connections和max_tmp_tables影響伺服器保持開啟的檔案的最大數量。如果你增加這些值的一個或兩個，你可以遇到你的作業系統每個進程開啟檔案描述符的數量上強加的限制。然而，你可以能在許多系統上增加該限制。請教你的OS文檔找出如何做這些，因為改變限制的方法各系統有很大的不同。
table_cache與max_connections有關。例如，對於200個開啟的串連，你應該讓一張表的緩衝至少有200 * n，這裡n是一個連接(join)中表的最大數量。

 

show variables like '%slow%'

Max_used_connections/max_connections =0.85 ---Max_connections

Key_cache_miss_rate=key_reads/key_read_requests 0.1%--Key_buffer_size 對myisam表有效

key_blocks_used/(key_blocks_used+key_blocks_used)--Key_buffer_size 對myisam表有效

Created_tmp_disk_tables / Created_tmp_tables <=0.25 --對應的變數：tmp_table_size,max_heap_table_size

Open_tables 量比較大，可以調整參數table_cache

thread_created 過大，請配置 thread_cache_size=

查詢快取(query cache)

　　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_limit | 2097152 | 超過此大小的查詢將不緩衝

　　| query_cache_min_res_unit | 4096 | 緩衝塊的最小大小 4K，過大，容易造成片段和浪費

　　| query_cache_size | 203423744 | 查詢快取大小

　　| query_cache_type | ON | 緩衝類型，決定緩衝什麼樣的查詢，樣本中表示不緩衝 select sql_no_cache 查詢

| query_cache_wlock_invalidate | OFF |當有其他用戶端正在對MyISAM表進行寫操作時，如果查詢在query cache中，是否返回cache結果還是等寫操作完成再讀表擷取結果。

查詢快取片段率 = 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%

 

排序使用方式：

Sort_merge_passes | 29 |

　　| Sort_range | 37432840 |

　　| Sort_rows | 9178691532 |

　　| Sort_scan | 1860569 |

調整的參數：Sort_buffer_size

檔案開啟數（Open_files）：對應參數open_files_limit
比較合適的設定：Open_files / open_files_limit * 100% <= 75%

表鎖情況：
Table_locks_immediate表示立即釋放表鎖數
Table_locks_waited表示需要等待的表鎖數
Table_locks_immediate / Table_locks_waited > 5000，最好採用InnoDB引擎