mysql中內(nèi)存的使用與分配_MySQL

mysql> show global variables like '%buffer%';
+-------------------------+------------+
| Variable_name  | Value |
+-------------------------+------------+
| bulk_insert_buffer_size | 4194304 |
| innodb_buffer_pool_size | 2013265920 |
| innodb_change_buffering | inserts |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 1048576 |
| key_buffer_size  | 16777216 |
| myisam_sort_buffer_size | 262144 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 1048576 |
| read_rnd_buffer_size | 1048576 |
| sort_buffer_size | 1048576 |
| sql_buffer_result | OFF |
+-------------------------+------------+
13 rows in set (0.01 sec)

mysql> show global variables like '%cache%';
+------------------------------+----------------------+
| Variable_name  | Value  |
+------------------------------+----------------------+
| binlog_cache_size  | 1048576  |
| have_query_cache  | YES   |
| key_cache_age_threshold | 300   |
| key_cache_block_size  | 1024   |
| key_cache_division_limit | 100   |
| max_binlog_cache_size | 18446744073709547520 |
| query_cache_limit  | 1048576  |
| query_cache_min_res_unit | 4096   |
| query_cache_size  | 0   |
| query_cache_type  | ON   |
| query_cache_wlock_invalidate | OFF   |
| table_definition_cache | 256   |
| table_open_cache  | 100   |
| thread_cache_size  | 100   |
+------------------------------+----------------------+
14 rows in set (0.00 sec)

可以看到部分配置信息

內(nèi)存的組成

1、線程共享內(nèi)存 2、線程獨(dú)享內(nèi)存

used_Mem =
+ key_buffer_size
+ query_cache_size
+ innodb_buffer_pool_size
+ innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_connections *(
 + read_buffer_size
 + read_rnd_buffer_size
 + sort_buffer_size
 + join_buffer_size
 + binlog_cache_size
 + thread_stack
 + tmp_table_size
 + bulk_insert_buffer_size
)

線程獨(dú)享內(nèi)存

1、read_buffer_size： 順序讀取數(shù)據(jù)緩沖區(qū)使用內(nèi)存

這部分內(nèi)存主要用于當(dāng)需要順序讀取數(shù)據(jù)的時(shí)候，如無發(fā)使用索引的情況下的全表掃描，全索引掃描等。在這種時(shí)候，MySQL 按照數(shù)據(jù)的存儲順序依次讀取數(shù)據(jù)塊，每次讀取的數(shù)據(jù)快首先會暫存在read_buffer_size中，當(dāng) buffer 空間被寫滿或者全部數(shù)據(jù)讀取結(jié)束后，再將buffer中的數(shù)據(jù)返回給上層調(diào)用者，以提高效率。

2、read_rnd_buffer_size：隨機(jī)讀取數(shù)據(jù)緩沖區(qū)使用內(nèi)存

和順序讀取相對應(yīng)，當(dāng) MySQL 進(jìn)行非順序讀取（隨機(jī)讀取）數(shù)據(jù)塊的時(shí)候，會利用這個(gè)緩沖區(qū)暫存讀取的數(shù)據(jù)。如根據(jù)索引信息讀取表數(shù)據(jù)，根據(jù)排序后的結(jié)果集與表進(jìn)行Join等等。總的來說，就是當(dāng)數(shù)據(jù)塊的讀取需要滿足一定的順序的情況下，MySQL 就需要產(chǎn)生隨機(jī)讀取，進(jìn)而使用到 read_rnd_buffer_size 參數(shù)所設(shè)置的內(nèi)存緩沖區(qū)。

3、sort_buffer_size：排序使用內(nèi)存

MySQL 用此內(nèi)存區(qū)域進(jìn)行排序操作（filesort），完成客戶端的排序請求。當(dāng)我們設(shè)置的排序區(qū)緩存大小無法滿足排序?qū)嶋H所需內(nèi)存的時(shí)候，MySQL 會將數(shù)據(jù)寫入磁盤文件來完成排序。由于磁盤和內(nèi)存的讀寫性能完全不在一個(gè)數(shù)量級，所以sort_buffer_size參數(shù)對排序操作的性能影響絕對不可 小視

4、join_buffer_size：連接使用內(nèi)存

應(yīng)用程序經(jīng)常會出現(xiàn)一些兩表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的時(shí)候（all/index join），為了減少參與Join的“被驅(qū)動表”的讀取次數(shù)以提高性能，需要使用到 Join Buffer 來協(xié)助完成 Join操作。 當(dāng) Join Buffer 太小，MySQL 不會將該 Buffer 存入磁盤文件，而是先將Join Buffer中的結(jié)果集與需要 Join 的表進(jìn)行 Join 操作，然后清空 Join Buffer 中的數(shù)據(jù)，繼續(xù)將剩余的結(jié)果集寫入此 Buffer 中，如此往復(fù)。這勢必會造成被驅(qū)動表需要被多次讀取，成倍增加 IO 訪問，降低效率。

5、thread_stack：線程棧信息使用內(nèi)存

主要用來存放每一個(gè)線程自身的標(biāo)識信息，如線程id，線程運(yùn)行時(shí)基本信息等等，我們可以通過 thread_stack 參數(shù)來設(shè)置為每一個(gè)線程棧分配多大的內(nèi)存

6、tmp_table_size：臨時(shí)表使用內(nèi)存

當(dāng)我們進(jìn)行一些特殊操作如需要使用臨時(shí)表才能完成的 Order By，Group By 等等，MySQL 可能需要使用到臨時(shí)表。當(dāng)我們的臨時(shí)表較?。ㄐ∮?tmp_table_size 參數(shù)所設(shè)置的大小）的時(shí)候，MySQL 會將臨時(shí)表創(chuàng)建成內(nèi)存臨時(shí)表，只有當(dāng) tmp_table_size 所設(shè)置的大小無法裝下整個(gè)臨時(shí)表的時(shí)候，MySQL 才會將該表創(chuàng)建成 MyISAM 存儲引擎的表存放在磁盤上。不過，當(dāng)另一個(gè)系統(tǒng)參數(shù) max_heap_table_size 的大小還小于 tmp_table_size 的時(shí)候，MySQL 將使用 max_heap_table_size 參數(shù)所設(shè)置大小作為最大的內(nèi)存臨時(shí)表大小，而忽略 tmp_table_size 所設(shè)置的值。而且 tmp_table_size 參數(shù)從 MySQL 5.1.2 才開始有，之前一直使用 max_heap_table_size。

7、bulk_insert_buffer_size：批量插入暫存使用內(nèi)存

當(dāng)我們使用如 insert … values(…),(…),(…)… 的方式進(jìn)行批量插入的時(shí)候，MySQL 會先將提交的數(shù)據(jù)放如一個(gè)緩存空間中，當(dāng)該緩存空間被寫滿或者提交完所有數(shù)據(jù)之后，MySQL 才會一次性將該緩存空間中的數(shù)據(jù)寫入數(shù)據(jù)庫并清空緩存。此外，當(dāng)我們進(jìn)行 LOAD DATA INFILE 操作來將文本文件中的數(shù)據(jù) Load 進(jìn)數(shù)據(jù)庫的時(shí)候，同樣會使用到此緩沖區(qū)

8、binlog_cache_size：二進(jìn)制日志緩沖使用內(nèi)存

我們知道InnoDB存儲引擎是支持事務(wù)的，實(shí)現(xiàn)事務(wù)需要依賴于日志技術(shù)，為了性能，日志編碼采用二進(jìn)制格式。那么，我們?nèi)绾斡浫罩灸?？有日志的時(shí)候，就 直接寫磁盤？可是磁盤的效率是很低的，如果你用過Nginx，一般Nginx輸出access log都是要緩沖輸出的。因此，記錄二進(jìn)制日志的時(shí)候，我們是否也需要考慮Cache呢？答案是肯定的，但是Cache不是直接持久化，于是面臨安全性的 問題——因?yàn)橄到y(tǒng)宕機(jī)時(shí)，Cache中可能有殘余的數(shù)據(jù)沒來得及寫入磁盤。因此，Cache要權(quán)衡，要恰到好處：既減少磁盤I/O，滿足性能要求；又保證 Cache無殘留，及時(shí)持久化，滿足安全要求

設(shè)置太大的話，會比較消耗內(nèi)存資源；設(shè)置太小的話，如果用戶提交一個(gè)“長事務(wù)（long_transaction）”，比如：批量導(dǎo)入數(shù)據(jù)。那么該事務(wù)必然會產(chǎn)生很多binlog，這樣 cache可能不夠用（默認(rèn)binlog_cache_size是32K），不夠用的時(shí)候mysql會把uncommitted的部分寫入臨時(shí)文件（臨時(shí) 文件cache的效率必然沒有內(nèi)存cache高），等到committed的時(shí)候才會寫入正式的持久化日志文件。

線程共享內(nèi)存

1、query_cache_size：查詢緩存

查詢緩存是 MySQL 比較獨(dú)特的一個(gè)緩存區(qū)域，用來緩存特定 Query 的結(jié)果集（Result Set）信息，共享給所有客戶端。

通過對 Query 語句進(jìn)行特定的 Hash 計(jì)算之后與結(jié)果集對應(yīng)存放在 Query Cache 中，以提高完全相同的 Query 語句的相應(yīng)速度。

當(dāng)我們打開 MySQL 的 Query Cache 之后，MySQL 接收到每一個(gè) SELECT 類型的 Query 之后都會首先通過固定的 Hash 算法得到該 Query 的 Hash 值，然后到 Query Cache 中查找是否有對應(yīng)的 Query Cache。如果有，則直接將 Cache 的結(jié)果集返回給客戶端。如果沒有，再進(jìn)行后續(xù)操作，得到對應(yīng)的結(jié)果集之后將該結(jié)果集緩存到 Query Cache 中，再返回給客戶端。

當(dāng)任何一個(gè)表的數(shù)據(jù)發(fā)生任何變化之后，與該表相關(guān)的所有 Query Cache 全部會失效，所以 Query Cache 對變更比較頻繁的表并不是非常適用，但對那些變更較少的表是非常合適的，可以極大程度的提高查詢效率，如那些靜態(tài)資源表，配置表等等。為了盡可能高效的利 用 Query Cache，MySQL 針對 Query Cache 設(shè)計(jì)了多個(gè) query_cache_type 值和兩個(gè) Query Hint：SQL_CACHE 和 SQL_NO_CACHE。

a、當(dāng)query_cache_type 設(shè)置為0（或者 OFF）的時(shí)候 不使用 Query Cache

b、當(dāng)query_cache_type設(shè)置為1（或者 ON）的時(shí)候，當(dāng)且僅當(dāng) Query 中使用了 SQL_NO_CACHE 的時(shí)候 MySQL 會忽略 Query Cache

c、query_cache_type 設(shè)置為2（或者DEMAND）的時(shí)候，當(dāng)且僅當(dāng)Query 中使用了 SQL_CACHE 提示之后，MySQL 才會針對該 Query 使用 Query Cache。

可以通過 query_cache_size 來設(shè)置可以使用的最大內(nèi)存空間

2、binlog_cache_size：二進(jìn)制日志緩沖區(qū)

二進(jìn)制日志緩沖區(qū)主要用來緩存由于各種數(shù)據(jù)變更操做所產(chǎn)生的 Binary Log 信息。

為了提高系統(tǒng)的性能，MySQL 并不是每次都是將二進(jìn)制日志直接寫入 Log File，而是先將信息寫入 Binlog Buffer 中，當(dāng)滿足某些特定的條件（如 sync_binlog參數(shù)設(shè)置）之后再一次寫入 Log File 中。我們可以通過 binlog_cache_size 來設(shè)置其可以使用的內(nèi)存大小，同時(shí)通過 max_binlog_cache_size 限制其最大大?。ó?dāng)單個(gè)事務(wù)過大的時(shí)候 MySQL 會申請更多的內(nèi)存）。當(dāng)所需內(nèi)存大于 max_binlog_cache_size 參數(shù)設(shè)置的時(shí)候，MySQL 會報(bào)錯(cuò)：“Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”。

3、key_buffer_size：MyISAM索引緩存

The key_buffer_size indicates the size of the key cache that MySQL uses to store indexes in memory. The cache stores index blocks in memory to avoid reading the disk repeatedly. The key_buffer_size is one of the most important variables to tune to improve MySQL database performance. The index blocks of MyISAM tables are stored in the key cache and are accessible to all processes which use MySQL globally.

The maximum size of the key_buffer_size variable is 4 GB on 32 bit machines, and larger for 64 bit machines. MySQL recommends that you keep the key_buffer_size less than or equal to 25% of the RAM on your machine. This also depends on the other processes that use memory on the machine and it is wise to check if you consistently have 25% of free memory using the Linux command free. More on this later.

4、innodb_log_buffer_size：InnoDB 日志緩沖區(qū)

這是 InnoDB 存儲引擎的事務(wù)日志所使用的緩沖區(qū)。類似于 Binlog Buffer，InnoDB 在寫事務(wù)日志的時(shí)候，為了提高性能，也是先將信息寫入 Innofb Log Buffer 中，當(dāng)滿足 innodb_flush_log_trx_commit 參數(shù)所設(shè)置的相應(yīng)條件（或者日志緩沖區(qū)寫滿）之后，才會將日志寫到文件（或者同步到磁盤）中?？梢酝ㄟ^ innodb_log_buffer_size 參數(shù)設(shè)置其可以使用的最大內(nèi)存空間。

注：innodb_flush_log_trx_commit 參數(shù)對 InnoDB Log 的寫入性能有非常關(guān)鍵的影響。該參數(shù)可以設(shè)置為0，1，2，解釋如下：

mysql日志操作步驟 ：log_buffer ---mysql寫 (write)---> log_file ---OS刷新 (flush)---> disk

0：log buffer中的數(shù)據(jù)將以每秒一次的頻率寫入到log file中，且同時(shí)會進(jìn)行文件系統(tǒng)到磁盤的同步操作，但是每個(gè)事務(wù)的commit并不會觸發(fā)任何log buffer 到log file的刷新或者文件系統(tǒng)到磁盤的刷新操作；

1：在每次事務(wù)提交的時(shí)候?qū)og buffer 中的數(shù)據(jù)都會寫入到log file，同時(shí)也會觸發(fā)文件系統(tǒng)到磁盤的同步；

2：事務(wù)提交會觸發(fā)log buffer 到log file的刷新，但并不會觸發(fā)磁盤文件系統(tǒng)到磁盤的同步。此外，每秒會有一次文件系統(tǒng)到磁盤同步操作。

具體的展示

0（延遲寫）： log_buffer --每隔1秒--> log_file —實(shí)時(shí)—> disk

1（實(shí)時(shí)寫，實(shí)時(shí)刷）： log_buffer —實(shí)時(shí)—> log_file —實(shí)時(shí)—> disk

2（實(shí)時(shí)寫，延遲刷）： log_buffer —實(shí)時(shí)—> log_file --每隔1秒--> disk

推薦的做法是 innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N為500 或1000) 且使用帶蓄電池后備電源的緩存cache，防止系統(tǒng)斷電異常

注：sync_binlog =N (N>0) ，MySQL 在每寫 N次 二進(jìn)制日志binary log時(shí)，會使用fdatasync()函數(shù)將它的寫二進(jìn)制日志binary log同步到磁盤中去

此外，MySQL文檔中還提到，這幾種設(shè)置中的每秒同步一次的機(jī)制，可能并不會完全確保非常準(zhǔn)確的每秒就一定會發(fā)生同步，還取決于進(jìn)程調(diào)度 的問題。實(shí)際上，InnoDB 能否真正滿足此參數(shù)所設(shè)置值代表的意義正常 Recovery 還是受到了不同 OS 下文件系統(tǒng)以及磁盤本身的限制，可能有些時(shí)候在并沒有真正完成磁盤同步的情況下也會告訴 mysqld 已經(jīng)完成了磁盤同步。

5、innodb_buffer_pool_size：InnoDB 數(shù)據(jù)和索引緩存

InnoDB Buffer Pool 對 InnoDB 存儲引擎的作用類似于 Key Buffer Cache 對 MyISAM 存儲引擎的影響，主要的不同在于 InnoDB Buffer Pool 不僅僅緩存索引數(shù)據(jù)，還會緩存表的數(shù)據(jù)，而且完全按照數(shù)據(jù)文件中的數(shù)據(jù)快結(jié)構(gòu)信息來緩存，這一點(diǎn)和 Oracle SGA 中的 database buffer cache 非常類似。所以，InnoDB Buffer Pool 對 InnoDB 存儲引擎的性能影響之大就可想而知了?？梢酝ㄟ^ (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計(jì)算得到 InnoDB Buffer Pool 的命中率。

6、innodb_additional_mem_pool_size：InnoDB 字典信息緩存

InnoDB 字典信息緩存主要用來存放 InnoDB 存儲引擎的字典信息以及一些 internal 的共享數(shù)據(jù)結(jié)構(gòu)信息。所以其大小也與系統(tǒng)中所使用的 InnoDB 存儲引擎表的數(shù)量有較大關(guān)系。不過，如果我們通過 innodb_additional_mem_pool_size 參數(shù)所設(shè)置的內(nèi)存大小不夠，InnoDB 會自動申請更多的內(nèi)存，并在 MySQL 的 Error Log 中記錄警告信息。

這里所列舉的各種共享內(nèi)存，是我個(gè)人認(rèn)為對 MySQL 性能有較大影響的集中主要的共享內(nèi)存。實(shí)際上，除了這些共享內(nèi)存之外，MySQL 還存在很多其他的共享內(nèi)存信息，如當(dāng)同時(shí)請求連接過多的時(shí)候用來存放連接請求信息的back_log隊(duì)列等。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com