MySQL8.0內(nèi)存相關(guān)參數(shù)總結(jié)

MySQL理論上使用的內(nèi)存 = 全局共享內(nèi)存 + max_connections×線程獨享內(nèi)存。

也就是：innodb_buffer_pool_size + innodb_log_buffer_size + thread_cache_size +table_open_cache + table_definition_cache +key_buffer_size + max_connections *（ thread_stack+ sort_buffer_size+join_buffer_size + read_buffer_size+read_rnd_buffer_size+ binlog_cache_size+tmp_table_size）

下面我們按照全局內(nèi)存參數(shù)與線程獨享參數(shù)分類，簡單介紹下相關(guān)參數(shù)的作用。

全局共享內(nèi)存

innodb_buffer_pool_size

innodb_buffer_pool_size這個參數(shù)是對Mysql數(shù)據(jù)庫最重要的參數(shù)之一，它對 InnoDB 存儲引擎的作用類似于 Key Buffer Cache 對 MyISAM 存儲引擎的影響，主要區(qū)別是 InnoDB Buffer Pool 不僅僅緩存索引數(shù)據(jù)，會緩存表的數(shù)據(jù)，而且完全按照數(shù)據(jù)文件中的數(shù)據(jù)快結(jié)構(gòu)信息來緩存，這一點和 Oracle SGA 中的 database buffer cache 類似，因此在SHOW ENGINE innodb status中查到的Buffer pool size要乘以16K。

可以通過 (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算得到 InnoDB Buffer Pool 的命中率。

innodb_change_buffering

change buffering是MySQL5.5加入的新特性，change buffering是insert buffer的加強，insert buffer只針對insert有效，change buffering對insert、delete、update(delete+insert)、purge都有效。當修改一個索引塊(secondary index)時的數(shù)據(jù)時，索引塊在buffter pool中不存在，修改信息就會被cache在change buffer中，當通過索引掃描把需要的索引塊讀取到buffer pool時，會和change buffer中修改信息合并，再擇機寫回disk。

目的還是為了減少隨機IO帶來性能損耗，說明白了：把隨機IO盡量變成順序IO?，F(xiàn)在SSD盛行，在SSD上隨機訪問和順序訪問性能幾乎差不多的情況下，change buffering特性不會帶來多大的性能提升，但對于廉價的機械硬盤，這個參數(shù)還是能幫助提高性能的。

change buffering由參數(shù)innodb_change_buffering控制:

• all:  buffer inserts, delete-marking operations, and purges.
• none:  Do not buffer any operations.
• inserts:  Buffer insert operations.
• deletes:  Buffer delete-marking operations.
• changes:  Buffer both inserts and delete-marking.
• purges:  Buffer the physical deletion operations that happen in the background.
  

注意這個內(nèi)存是在Innodb的buffer pool中分配的，計算總內(nèi)存的時候不用算它。

innodb_change_buffer_max_size

表示change buffer在buffer pool中的最大占比，默認25%，最大50%。如果系統(tǒng)中有嚴重的insert、update并且還有活躍的delete時，就增大max_size；針對不更改數(shù)據(jù)的純報表系統(tǒng)，可以減小該參數(shù)值。

innodb_log_buffer_size

這是 InnoDB 存儲引擎的事務(wù)日志所使用的緩沖區(qū)。為了提高性能，也是先將信息寫入 Innofb Log Buffer 中，當滿足 innodb_flush_log_trx_commit 參數(shù)所設(shè)置的相應(yīng)條件（或者日志緩沖區(qū)寫滿）之后，才會將日志寫到文件（或者同步到磁盤）中。innodb_flush_log_trx_commit 參數(shù)可以設(shè)置為0，1，2，解釋如下：

• 0：log buffer中的數(shù)據(jù)將以每秒一次的頻率寫入到logfile中，且同時會進行文件系統(tǒng)到磁盤的同步操作，但是每個事務(wù)的commit并不會觸發(fā)任何log buffer 到log file的刷新或者文件系統(tǒng)到磁盤的刷新操作，該模式速度最快，但不太安全，mysqld進程的崩潰會導(dǎo)致上一秒鐘所有事務(wù)數(shù)據(jù)的丟失；
• 1：在每次事務(wù)提交的時候?qū)og buffer 中的數(shù)據(jù)都會寫入到logfile，同時也會觸發(fā)文件系統(tǒng)到磁盤的同步，該模式是最安全的，但也是最慢的一種方式；
• 2：事務(wù)提交會觸發(fā)log buffer 到logfile的刷新，但并不會觸發(fā)磁盤文件系統(tǒng)到磁盤的同步，該模式速度較快，也比0安全，只有在操作系統(tǒng)崩潰或者系統(tǒng)斷電的情況下，上一秒鐘所有事務(wù)數(shù)據(jù)才可能丟失。
  

thread_cache_size

線程池緩存大小，當客戶端斷開連接后將當前線程緩存起來，當在接到新的連接請求時快速響應(yīng)無需創(chuàng)建新的線程 。這尤其對那些使用短連接的應(yīng)用程序來說可以極大的提高創(chuàng)建連接的效率。可以通過(Connections - Threads_created) / Connections * 100% 計算出連接線程緩存的命中率。也可以通過如下幾個MySQL狀態(tài)值來適當調(diào)整線程池的大?。?/p>

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| Threads_cached  | 2   |
| Threads_connected | 1   |
| Threads_created  | 3   |
| Threads_running  | 2   |
+-------------------+-------+
4 rows in set (0.01 sec)

當 Threads_cached 越來越少 但 Threads_connected 始終不降，且 Threads_created 持續(xù)升高，可適當增加 thread_cache_size 的大小。

table_open_cache

table_open_cache指定表高速緩存的大小，用來緩存表文件的文件句柄信息。當我們的客戶端程序提交Query給MySQL的時候，MySQL需要對Query所涉及到的每一個表都取得一個表文件句柄信息，如果沒有Table Cache，那么MySQL就不得不頻繁的進行打開關(guān)閉文件操作，無疑會對系統(tǒng)性能產(chǎn)生一定的影響，每當MySQL訪問一個表時，如果在表緩沖區(qū)中還有空間，該表就被打開并放入其中，這樣可以更快地訪問表內(nèi)容。注意，這里設(shè)置的是可以緩存的表文件句柄信息的數(shù)目，而不是內(nèi)存空間的大小。

通過檢查峰值時間的狀態(tài)值Open_tables和Opened_tables，可以決定是否需要增加table_open_cache的值。其中Open_tables是當前正在打開表的數(shù)量，Opened_tables是所有已經(jīng)打開表的數(shù)量。注意，不能盲目地把table_open_cache設(shè)置成很大的值，設(shè)置太大超過了shell的文件描述符(通過ulimit -n查看)，造成文件描述符不足，從而造成性能不穩(wěn)定或者連接失敗。如果發(fā)現(xiàn)open_tables等于table_open_cache，并且opened_tables在不斷增長，那么你就需要增加table_open_cache的值了(上述狀態(tài)值可通過SHOW GLOBAL STATUS LIKE 'Open%tables'獲得)。如果Open_tables的值已經(jīng)接近table_cache的值，且Opened_tables還在不斷變大，則說明mysql正在將緩存的表釋放以容納新的表，此時可能需要加大table_cache的值。對于大多數(shù)情況，比較適合的值：

• Open_tables / Opened_tables >= 0.85
• Open_tables / table_cache = 0.95
  

建議把MySQL數(shù)據(jù)庫放在生產(chǎn)環(huán)境中試運行一段時間，然后把參數(shù)的值調(diào)整得比Opened_tables的數(shù)值大一些，并且保證在比較高負載的極端條件下依然比Opened_tables略大。

table_definition_cache

table_definition_cache和table_open_cache類似，前者緩存frm文件，關(guān)于后者，文檔中并沒有說明，應(yīng)該是ibd/MYI/MYD；

狀態(tài)值：

Open_table_definitions：表定義文件.frm被緩存的數(shù)量

Opened_table_definitions：歷史上總共被緩存過的,frm文件數(shù)量

key_buffer_size

key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態(tài)值Key_read_requests和Key_reads，可以知道key_buffer_size設(shè)置是否合理。比例key_reads /key_read_requests應(yīng)該盡可能的低，至少是1:100，1:1000更好（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘key_read%'獲得）。key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時磁盤表是MyISAM表，也要使用該值?？梢允褂脵z查狀態(tài)值created_tmp_disk_tables得知詳情。

max_connections

MySQL的最大連接數(shù)，增加該值增加mysqld 要求的文件描述符的數(shù)量。如果服務(wù)器的并發(fā)連接請求量比較大，建議調(diào)高此值，以增加并行連接數(shù)量，當然這建立在機器能支撐的情況下，因為如果連接數(shù)越多，介于MySQL會為每個連接提供連接緩沖區(qū)，就會開銷越多的內(nèi)存，所以要適當調(diào)整該值，不能盲目提高設(shè)值。數(shù)值過小會經(jīng)常出現(xiàn)ERROR 1040: Too many connections錯誤，可以過'conn%'通配符查看當前狀態(tài)的連接數(shù)量，以定奪該值的大小。max_used_connections / max_connections * 100% （理想值≈ 85%） 如果max_used_connections跟max_connections相同 那么就是max_connections設(shè)置過低或者超過服務(wù)器負載上限了，低于10%則設(shè)置過大。

線程/會話/連接獨享內(nèi)存

binlog_cache_size

為每個session 分配的內(nèi)存，在事務(wù)過程中用來存儲二進制日志的緩存，可以提高記錄bin-log的效率，默認32K，沒有大事務(wù)，dml也不是很頻繁的情況下可以設(shè)置小一點，如果事務(wù)大而且多，dml操作也頻繁，則可以適當?shù)恼{(diào)大一點。

數(shù)據(jù)庫binlog_cache_size的使用情況，可以查看：Binlog_cache_disk_use表示因為我們binlog_cache_size設(shè)計的內(nèi)存不足導(dǎo)致緩存二進制日志用到了臨時文件的次數(shù)，Binlog_cache_use  表示 用binlog_cache_size緩存的次數(shù)

tmp_table_size和max_heap_table_size

tmp_table_size規(guī)定了內(nèi)部內(nèi)存臨時表的最大值，每個線程都要分配。（實際起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果內(nèi)存臨時表超出了限制，MySQL就會自動地把它轉(zhuǎn)化為基于磁盤的MyISAM表，存儲在指定的tmpdir目錄下，默認:

mysql> show variables like "tmpdir";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir    | /tmp/ |
+---------------+-------+

優(yōu)化查詢語句的時候，要避免使用臨時表，如果實在避免不了的話，要保證這些臨時表是存在內(nèi)存中的。如果需要的話并且你有很多group by語句，并且你有很多內(nèi)存，增大tmp_table_size(和max_heap_table_size)的值。這個變量不適用與用戶創(chuàng)建的內(nèi)存表(memory table)。

可以比較內(nèi)部基于磁盤的臨時表的總數(shù)和創(chuàng)建在內(nèi)存中的臨時表的總數(shù)（Created_tmp_disk_tables和Created_tmp_tables），一般的比例關(guān)系是:

Created_tmp_disk_tables/Created_tmp_tables5%

max_heap_table_size定義了用戶可以創(chuàng)建的內(nèi)存表(memory table)的大小.這個值用來計算內(nèi)存表的最大行數(shù)值。這個變量支持動態(tài)改變，即set @max_heap_table_size = xxx。

以上就是MySQL8.0內(nèi)存相關(guān)參數(shù)總結(jié)的詳細內(nèi)容，更多關(guān)于mysql8.0 內(nèi)存參數(shù)的資料請關(guān)注腳本之家其它相關(guān)文章！