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、線程獨享內(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
)

線程獨享內(nèi)存

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

線程共享內(nèi)存

1、query_cache_size：查詢緩存

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

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

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

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

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

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

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

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

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

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

為了提高系統(tǒng)的性能，MySQL 并不是每次都是將二進制日志直接寫入 Log File，而是先將信息寫入 Binlog Buffer 中，當滿足某些特定的條件（如 sync_binlog參數(shù)設(shè)置）之后再一次寫入 Log File 中。我們可以通過 binlog_cache_size 來設(shè)置其可以使用的內(nèi)存大小，同時通過 max_binlog_cache_size 限制其最大大小（當單個事務(wù)過大的時候 MySQL 會申請更多的內(nèi)存）。當所需內(nèi)存大于 max_binlog_cache_size 參數(shù)設(shè)置的時候，MySQL 會報錯：“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ù)日志的時候，為了提高性能，也是先將信息寫入 Innofb Log Buffer 中，當滿足 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中，且同時會進行文件系統(tǒng)到磁盤的同步操作，但是每個事務(wù)的commit并不會觸發(fā)任何log buffer 到log file的刷新或者文件系統(tǒng)到磁盤的刷新操作；

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

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

具體的展示

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

1（實時寫，實時刷）： log_buffer —實時—> log_file —實時—> disk

2（實時寫，延遲刷）： log_buffer —實時—> 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次 二進制日志binary log時，會使用fdatasync()函數(shù)將它的寫二進制日志binary log同步到磁盤中去

此外，MySQL文檔中還提到，這幾種設(shè)置中的每秒同步一次的機制，可能并不會完全確保非常準確的每秒就一定會發(fā)生同步，還取決于進程調(diào)度 的問題。實際上，InnoDB 能否真正滿足此參數(shù)所設(shè)置值代表的意義正常 Recovery 還是受到了不同 OS 下文件系統(tǒng)以及磁盤本身的限制，可能有些時候在并沒有真正完成磁盤同步的情況下也會告訴 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)信息來緩存，這一點和 Oracle SGA 中的 database buffer cache 非常類似。所以，InnoDB Buffer Pool 對 InnoDB 存儲引擎的性能影響之大就可想而知了。可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算得到 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)存，是我個人認為對 MySQL 性能有較大影響的集中主要的共享內(nèi)存。實際上，除了這些共享內(nèi)存之外，MySQL 還存在很多其他的共享內(nèi)存信息，如當同時請求連接過多的時候用來存放連接請求信息的back_log隊列等。

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