MySQL記憶體引數及調整

dongyu2013發表於2014-03-08
MySql記憶體
MySQL記憶體引數及調整
本文主要介紹MySQL的記憶體引數的作用及其對效能的影響和調整，以及MySQL的記憶體分配

歡迎轉載，請註明作者、出處。
作者：張正
blog：http://space.itpub.net/26355921 
QQ：176036317
如有疑問，歡迎聯絡。

MySQL記憶體引數配置推薦：https://tools.percona.com/wizard

1.慢查詢日誌：
slow_launch_time=2  查詢大於某個時間的值(單位：s)
slow_query_log=on/off  開啟關閉慢查詢日誌
slow_query_log_file=/opt/data/host-slow.log  慢查詢日誌位置


2.連線數:
max_connections MySQL最大連線數
back_log   當連線數滿了後，設定一個值，允許多少個連線進入等待堆疊
max_connect_errors  賬號連線到伺服器允許的錯誤次數
connect_timeout  一個連線報文的最大時間(單位：s)
skip-name-resolve  加入my.cnf即可，MySQL在收到連線請求的時候，會根據請求包 
中獲得的ip來反向追查請求者的主機名。然後再根據返回
的主機名又一次去獲取ip。如果兩次獲得的ip相同，那麼連線就成功建立了。
加了次引數，即可省去這個步驟
NOTES:
查詢當前連線數:show global status like 'connections';


3.key_buffer_size   索引快取大小，是對MyISAM表效能影響最大的一個引數
32bit平臺上，此值不要超過2GB，64bit平臺不用做此限制，但也不要超過4GB
根據3點計算：
a.系統索引總大小 
b.系統實體記憶體 
c.系統當前keycache命中率
粗略計算公式：
Key_Size =key_number*(key_length+4)/0.67  
Max_key_buffer_size
Threads_Usage = max_connections * (sort_buffer_size + join_buffer_size + 
read_buffer_size+read_rnd_buffer_size+thread_stack)

key_cache_block_size ，是key_buffer快取塊的單位長度，以位元組為單位，預設值為1024。

key_cache_division_limit 控制著快取塊重用演算法。預設值為100，此值為key_buffer_size中暖鏈所佔的大小百分比(其中有暖鏈和熱鏈)，100意味著全是暖鏈。(類似於Oracle Data Buffer Cache中的default、keep、recycle)

key_cache_age_threshold 如果key_buffer裡的熱鏈裡的某個快取塊在這個變數所設定的時間裡沒有被訪問過，MySQL伺服器就會把它調整到暖鏈裡去。這個引數值越大，快取塊在熱鏈裡停留的時間就越長。
這個引數預設值為 300，最小值為100。

Myisam索引預設是快取在原始key_buffer中的，我們可以手動建立新的key_buffer，如在my.cnf中加入引數new_cache.key_buffer_size=20M。指定將table1和table2的索引快取到new_cache的key_buffer中：
cache index table1,table2 in new_cache;
(之前預設的key_buffer為default，現在手動建立的為new_cache)
手動將table1和table2的索引載入到key_buffer中：
load index into cache table1,table2;



系統中記錄的與Key Cache相關的效能狀態引數變數： global status 
◆Key_blocks_not_flushed，已經更改但還未重新整理到磁碟的DirtyCacheBlock； 
◆Key_blocks_unused，目前未被使用的CacheBlock數目； 
◆Key_blocks_used，已經使用了的CacheBlock數目； 
◆Key_read_requests，CacheBlock被請求讀取的總次數；  
◆Key_reads，在CacheBlock中找不到需要讀取的Key資訊後到“.MYI”檔案中(磁碟)讀取的次數；  
◆Key_write_requests，CacheBlock被請求修改的總次數；  
◆Key_writes，在CacheBlock中找不到需要修改的Key資訊後到“.MYI”檔案中讀入再修改的次數；
索引命中快取率：
key_buffer_read_hits=(1-Key_reads/Key_read_requests)*100% 
key_buffer_write_hits=(1-Key_writes/Key_write_requests)*100%
該命中率就代表了MyISAM型別表的索引的cache


4.臨時表   tmp_table_size (用於排序)
show global status like ‘created_tmp%’; 
| Variable_name            | Value   |  
| Created_tmp_disk_tables  | 21197   |  #在磁碟上建立臨時表的次數 
| Created_tmp_files        | 58      |  #在磁碟上建立臨時檔案的次數
| Created_tmp_tables       | 1771587 |  #使用臨時表的總次數
TmpTable的狀況主要是用於監控MySQL使用臨時表的量是否過多，
是否有臨時表過大而不得不從記憶體中換出到磁碟檔案上。 
a.如果：
Created_tmp_disk_tables/Created_tmp_tables>10%，則需調大tmp_table_size
比較理想的配置是：
Created_tmp_disk_tables/Created_tmp_tables<=25%
b.如果：
Created_tmp_tables非常大 ，則可能是系統中排序操作過多，或者是表連線方式不是很優化。

相關引數：
tmp_table_size  記憶體中，臨時表區域總大小
max_heap_table_size  記憶體中，單個臨時表的最大值，超過的部分會放到硬碟上。



5.table cache相關優化 ：
引數table_open_cache，將表的檔案描述符開啟，cache在記憶體中
global status：
open_tables   當前系統中開啟的檔案描述符的數量
opened_tables 系統開啟過的檔案描述符的數量

如果：
Opened_tables數量過大，說明配置中table_open_cache值可能太小

比較合適的值為：  
Open_tables / Opened_tables * 100% >= 85% 
Open_tables / table_open_cache * 100% <= 95%  



6.程式的使用情況
在MySQL中，為了儘可能提高客戶端請求建立連線這個過程的效能，實現了一個ThreadCache池，
將空閒的連線執行緒存放在其中，而不是完成請求後就銷燬。這樣，當有新的連線請求的時候，
MySQL首先會檢查ThreadCache池中是否存在空閒連線執行緒，如果存在則取出來直接使用，
如果沒有空閒連線執行緒，才建立新的連線執行緒。

引數：thread_cache_size
thread cache 池中存放的最大連線數
調整參考：
在短連線的資料庫應用中，資料庫連線的建立和銷燬是非常頻繁的，
如果每次都需要讓MySQL新建和銷燬相應的連線執行緒，那麼這個資源消耗實際上是非常大的，因此
thread_cache_size的值應該設定的相對大一些，不應該小於應用系統對資料庫的實際併發請求數。

引數：thread_stack  - 每個連線執行緒被建立的時候，MySQL給他分配的記憶體大小，
                      類似PGA中存放資料的記憶體部分(不包括排序的空間)

show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 80    |   #接受到的來自客戶端的總連線數，包括以前和現在的連線。
+---------------+-------+
show status like 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     | #當前系統中，快取的連線數
| Threads_connected | 1     | #當前系統中正連線的執行緒數
| Threads_created   | 77    | #建立過的匯流排程數
| Threads_running   | 1     | 
+-------------------+-------+
a.如果：
Threads_created 值過大，說明MySQL一直在建立執行緒，這是比較消耗資源的，應該適當增大
thread_cache_size的值
b.如果：
Threads_cached的值比引數thread_cache_size小太多，則可以適當減小thread_cache_size的值

ThreadCache命中率：
Threads_Cache_Hit=(Connections-Threads_created)/Connections*100%
一般來說，當系統穩定執行一段時間之後，我們的ThreadCache命中率應該保持在90%
左右甚至更高的比率才算正常。




7.查詢快取(Query Cache)   -- optional
將客戶端的SQL語句(僅限select語句)通過hash計算，放在hash連結串列中，同時將該SQL的結果集
放在記憶體中cache。該hash連結串列中，存放了結果集的記憶體地址以及所涉及到的所有Table等資訊。
如果與該結果集相關的任何一個表的相關資訊發生變化後(包擴：資料、索引、表結構等)，
就會導致結果集失效，釋放與該結果集相關的所有資源，以便後面其他SQL能夠使用。
當客戶端有select SQL進入，先計算hash值，如果有相同的，就會直接將結果集返回。

Query Cache的負面影響：
a.使用了Query Cache後，每條select SQL都要進行hash計算，然後查詢結果集。對於大量SQL
訪問，會消耗過多額外的CPU。
b.如果表變更比較頻繁，則會造成結果集失效率非常高。
c.結果集中儲存的是整個結果，可能存在一條記錄被多次cache的情況，這樣會造成記憶體資源的
過度消耗。

Query Cache的正確使用：
a.根據表的變更情況來選擇是否使用Query Cache，可使用SQL Hint：SQL_NO_CACHE和SQL_CACHE
b.對於 變更比較少 或 資料基本處於靜態 的表，使用SQL_CACHE
c.對於結果集比較大的，使用Query Cache可能造成記憶體不足，或擠佔記憶體。
可使用1.SQL_NO_CACHE 2.query_cache_limit控制Query Cache的最大結果集(系統預設1M)

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     | #是否支援Query Cache
| query_cache_limit            | 1048576 | #單個結果集的最大值，預設1M
| query_cache_min_res_unit     | 4096    | #每個結果集存放的最小記憶體，預設4K
| query_cache_size             | 0       | #Query Cache總記憶體大小，必須是1024的整數倍
| query_cache_type             | ON      | #ON,OFF,DEMAND(包含SQL_CACHE的查詢中才開啟)
| query_cache_wlock_invalidate | OFF     | 
+------------------------------+---------+
#query_cache_wlock_invalidate：
針對於MyISAM儲存引擎，設定當有WRITELOCK在某個Table上面的時候，
讀請求是要等待WRITE LOCK釋放資源之後再查詢還是允許直接從QueryCache中讀取結果，
預設為FALSE（可以直接從QueryCache中取得結果）


mysql> show status like 'qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     | 
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
# Qcache_free_blocks
QueryCache中目前還有多少剩餘的blocks
a.如果Qcache_free_blocks值較大，說明Query Cache中記憶體碎片比較多
b.如果Qcache_free_blocks約等於Qcache_total_blocks/2，說明記憶體碎片非常嚴重

移除碎片：
flush query cache;
這個命令會把所有的儲存塊向上移動，並把自由塊移到底部。

查詢快取碎片率：
查詢快取碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% 

c.如果：
查詢快取碎片率超過20%， 可以用flush query cache整理碎片，或者減小
query_cache_min_res_unit(如果該系統的查詢都是小資料量的話)

# Qcache_free_memory
QueryCache中目前剩餘的記憶體大小

查詢快取利用率：
查詢快取利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100% 

a.如果：
查詢快取利用率在25%以下，說明query_cache_size設定過大，可適當減小。
b.如果：
查詢快取利用率>80%，且Qcache_lowmem_prunes>50，說明query_cache_size可能有點小，或者
有太多的碎片

# Qcache_hits 
Query Cache的命中次數，可以看到QueryCache的基本效果；

# Qcache_inserts
Query Cache未命中然後插入的次數

Query Cache的命中率：
=Qcache_hits/(Qcache_hits+Qcache_inserts)

# Qcache_lowmem_prunes    
因為記憶體不足而被清除出Query Cache的SQL數量。

如果：
Qcache_lowmem_prunes的值正在增加，並且有大量的Qcache_free_blocks，
這意味著碎片導致查詢正在被從快取中永久刪除。

# Qcache_not_cached
因為query_cache_type的設定或者不能被cache的select SQL數量

# Qcache_queries_in_cache
Query Cache中cache的select SQL數量

# Qcache_total_blocks
當前Query Cache中block的總數量

Query Cache限制：
a) 5.1.17之前的版本不能Cache幫定變數的Query，但是從5.1.17版本開始，QueryCache已經開
始支援幫定變數的Query了；  
b) 所有子查詢中的外部查詢SQL不能被Cache；  
c) 在Procedure，Function以及Trigger中的Query不能被Cache；  
d) 包含其他很多每次執行可能得到不一樣結果的函式的Query不能被Cache。



8.排序使用情況：
引數 ：sort_buffer_size - 單個thread能用來排序的記憶體空間大小，系統預設2M

mysql> show variables like 'sort%';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 2097144 |
+------------------+---------+

mysql> show global status like 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |#在記憶體中無法完成排序，而在磁碟上建立臨時檔案的次數(兩倍)
| Sort_range        | 0     |#在範圍內執行的排序的數量
| Sort_rows         | 0     |#已經排序的行數
| Sort_scan         | 0     |#通過掃描表完成的排序的數量
+-------------------+-------+


9.檔案開啟數 open_files_limit

mysql> show variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  | #mysql總共能夠開啟的檔案的數量
+------------------+-------+

mysql> show global status like 'open%file%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 79    | # 系統當前開啟的檔案數
| Opened_files  | 278   | # 系統開啟過的檔案總數
+---------------+-------+
比較合適的設定：Open_files / open_files_limit * 100% <= 75%




10.表鎖情況
mysql> show global status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 96    | # 表示立即釋放的表鎖數
| Table_locks_waited    | 0     | # 表示需要等待的表鎖數
+-----------------------+-------+
如果 Table_locks_immediate / Table_locks_waited > 5000，最好採用InnoDB引擎。
因為InnoDB是行鎖而MyISAM是表鎖，對於高併發寫入的應用InnoDB效果會好些。



11.表掃描情況
mysql> show global status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 60    |
| Handler_read_key      | 2442  |
| Handler_read_next     | 286   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 28    |
| Handler_read_rnd_next | 3191  |
+-----------------------+-------+
mysql> show global status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 23    |
+---------------+-------+

計算表掃描率：  
表掃描率 = Handler_read_rnd_next / Com_select  
如果：
表掃描率超過4000，說明進行了太多表掃描，很有可能索引沒有建好，
增加read_buffer_size值會有一些好處，但最好不要超過8MB。   

# Handler_read_first
此選項表明SQL是在做一個全索引掃描(注意是全部，而不是部分)，所以說如果存在WHERE語句，
這個值是不會變的。如果這個值的數值很大，既是好事 也是壞事。
說它好是因為畢竟查詢是在索引裡完成的，而不是資料檔案裡，說它壞是因為大資料量時，
即便是索引檔案，做一次完整的掃描也是很費時的。

# Handler_read_key
此選項數值如果很高，說明系統高效的使用了索引，一切運轉良好

# Handler_read_next
此選項表明在進行索引掃描時，按照索引從資料檔案裡取資料的次數

# Handler_read_prev
此選項表明在進行索引掃描時，按照索引倒序從資料檔案裡取資料的次數，
一般就是ORDER BY ... DESC。

# Handler_read_rnd
簡單的說，就是查詢直接操作了資料檔案，很多時候表現為沒有使用索引或者檔案排序
可能是有大量的全表掃描或連線時沒恰當使用keys。
The number of requests to read a row based on a fixed position. This value is 
high if you are doing a lot of queries that require sorting of the result. You 
probably have a lot of queries that require MySQL to scan entire tables or you 
have joins that do not use keys properly.

# Handler_read_rnd_next 
此選項表明在進行資料檔案掃描時，從資料檔案裡取資料的次數。(物理IO次數)


12.dalayed_queue_size
在被插入到實際的資料表裡之前，來自insert delayed語句的資料航將在每個佇列裡等待
MySQL來處理他們。delayed_queue_size就是這個佇列所能容納的資料航的最大個數。當
這個佇列滿是，後續的insert delayed語句將被阻塞，直到這個佇列裡有容納他們的空間
為止。
如果有很多客戶在發出insert delayed語句以避免受阻塞，但你發現這些語句有阻塞的跡象，
加大這個變數的值將使更多的insert delayed語句更快地得到處理。


13.max_allowed_packet(最大值1G，預設值1M)
MySQL伺服器在於客戶端程式之間進行通訊時使用的緩衝區的最大長度。
如果你的客戶端經常批量傳輸一些非常長的語句，就需要在伺服器端和客戶端同時加大這個變數的值。
一般推薦，最少32M。

14.MySQL記憶體分配
mysql伺服器為每個連線上的客戶端執行緒，分配的記憶體空間：
read_buffer_size + read_rnd_buffer_size + sort_buffer_size + 
thread_stack + join_buffer_size

從記憶體的使用方式MySQL 資料庫的記憶體使用主要分為以下兩類
· 執行緒獨享記憶體
· 全域性共享記憶體
先分析 MySQL 中主要的 “執行緒獨享記憶體” 的。
在 MySQL 中，執行緒獨享記憶體主要用於各客戶端連線執行緒儲存各種操作的獨享資料，如執行緒棧資訊，分組排序操作，資料讀寫緩衝，結果集暫存等等，而且大多數可以通過相關引數來控制記憶體的使用量。

執行緒棧資訊使用記憶體(thread_stack)：
主要用來存放每一個執行緒自身的標識資訊，如執行緒id，執行緒執行時基本資訊等等，我們可以通過 thread_stack 引數來設定為每一個執行緒棧分配多大的記憶體。

排序使用記憶體(sort_buffer_size)：
MySQL 用此記憶體區域進行排序操作（filesort），完成客戶端的排序請求。當我們設定的排序區快取大小無法滿足排序實際所需記憶體的時候，MySQL 會將資料寫入磁碟檔案來完成排序。由於磁碟和記憶體的讀寫效能完全不在一個數量級，所以sort_buffer_size引數對排序操作的效能影響絕對不可小視。排序操作的實現原理請參考：MySQL Order By 的實現分析(http://www.kuqin.com/database/20081206/29716.html)。

Join操作使用記憶體(join_buffer_size)：
應用程式經常會出現一些兩表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的時候（all/index join），為了減少參與Join的“被驅動表”的讀取次數以提高效能，需要使用到 Join Buffer 來協助完成 Join操作（具體 Join 實現演算法請參考：
MySQL 中的 Join 基本實現原理(http://www.kuqin.com/database/20081206/29717.html)）。當 Join Buffer 太小，MySQL 不會將該 Buffer 存入磁碟檔案，而是先將Join Buffer中的結果集與需要 Join 的表進行 Join 操作，然後清空 Join Buffer 中的資料，繼續將剩餘的結果集寫入此 Buffer 中，如此往復。這勢必會造成被驅動表需要被多次讀取，成倍增加 IO 訪問，降低效率。

順序讀取資料緩衝區使用記憶體(read_buffer_size)：
這部分記憶體主要用於當需要順序讀取資料的時候，如無法使用索引的情況下的全表掃描，全索引掃描等。在這種時候，MySQL 按照資料的儲存順序依次讀取資料塊，每次讀取的資料快首先會暫存在read_buffer_size中，當 buffer 空間被寫滿或者全部資料讀取結束後，再將buffer中的資料返回給上層呼叫者，以提高效率。

隨機讀取資料緩衝區使用記憶體(read_rnd_buffer_size)：
和順序讀取相對應，當 MySQL 進行非順序讀取（隨機讀取）資料塊的時候，會利用這個緩衝區暫存讀取的資料。如根據索引資訊讀取表資料，根據排序後的結果集與表進行Join等等。總的來說，就是當資料塊的讀取需要滿足一定的順序的情況下，MySQL 就需要產生隨機讀取，進而使用到 read_rnd_buffer_size 引數所設定的記憶體緩衝區。

連線資訊及返回客戶端前結果集暫存使用記憶體(net_buffer_size)：
這部分用來存放客戶端連線執行緒的連線資訊和返回客戶端的結果集。當 MySQL 開始產生可以返回的結果集，會在通過網路返回給客戶端請求執行緒之前，會先暫存在通過 net_buffer_size 所設定的緩衝區中，等滿足一定大小的時候才開始向客戶端傳送，以提高網路傳輸效率。不過，net_buffer_size 引數所設定的僅僅只是該快取區的初始化大小，MySQL 會根據實際需要自行申請更多的記憶體以滿足需求，但最大不會超過 max_allowed_packet 引數大小。

批量插入暫存使用記憶體(bulk_insert_buffer_size)：
當我們使用如 insert … values(…),(…),(…)… 的方式進行批量插入的時候，MySQL 會先將提交的資料放如一個快取空間中，當該快取空間被寫滿或者提交完所有資料之後，MySQL 才會一次性將該快取空間中的資料寫入資料庫並清空快取。此外，當我們進行 LOAD DATA INFILE 操作來將文字檔案中的資料 Load 進資料庫的時候，同樣會使用到此緩衝區。

MySQL對硬體的"收益遞減點“為256G記憶體，32CPU。
(percona-server 5.1版本)






來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29012686/viewspace-1103804/，如需轉載，請註明出處，否則將追究法律責任。

相關文章