緩沖區不能為空。 參數名: buffer_MySQL8.0記憶體相關參數介紹

本文來自讀者投稿

作者：姚遠

首先我們給出MySQL記憶體使用的計算公式：

MySQL理論上使用的記憶體 = 全局共享記憶體 + max_connections×線程獨享記憶體。

也就是：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)

下面我們按照全局記憶體參數與線程獨享參數分類，簡單介紹下相關參數的作用。

全局共享記憶體

innodb_buffer_pool_size

innodb_buffer_pool_size這個參數是對Mysql資料庫最重要的參數之一，它對 InnoDB 存儲引擎的作用類似于 Key Buffer Cache 對 MyISAM 存儲引擎的影響，主要差別是 InnoDB Buffer Pool 不僅僅緩存索引資料，會緩存表的資料，而且完全按照資料檔案中的資料快結構資訊來緩存，這一點和 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)時的資料時，索引塊在buffter pool中不存在，修改資訊就會被cache在change buffer中，當通過索引掃描把需要的索引塊讀取到buffer pool時，會和change buffer中修改資訊合并，再擇機寫回disk。

目的還是為了減少随機IO帶來性能損耗，說明白了：把随機IO盡量變成順序IO。現在SSD盛行，在SSD上随機通路和順序通路性能幾乎差不多的情況下，change buffering特性不會帶來多大的性能提升，但對于廉價的機械硬碟，這個參數還是能幫助提高性能的。

change buffering由參數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.

注意這個記憶體是在Innodb的buffer pool中配置設定的，計算總記憶體的時候不用算它。

innodb_change_buffer_max_size

表示change buffer在buffer pool中的最大占比，預設25%，最大50%。如果系統中有嚴重的insert、update并且還有活躍的delete時，就增大max_size；針對不更改資料的純報表系統，可以減小該參數值。

innodb_log_buffer_size

這是 InnoDB 存儲引擎的事務日志所使用的緩沖區。為了提高性能，也是先将資訊寫入 Innofb Log Buffer 中，當滿足 innodb_flush_log_trx_commit 參數所設定的相應條件(或者日志緩沖區寫滿)之後，才會将日志寫到檔案(或者同步到磁盤)中。innodb_flush_log_trx_commit 參數可以設定為0，1，2，解釋如下：

• 0：log buffer中的資料将以每秒一次的頻率寫入到logfile中，且同時會進行檔案系統到磁盤的同步操作，但是每個事務的commit并不會觸發任何log buffer 到log file的重新整理或者檔案系統到磁盤的重新整理操作，該模式速度最快，但不太安全，mysqld程序的崩潰會導緻上一秒鐘所有事務資料的丢失；
• 1：在每次事務送出的時候将log buffer 中的資料都會寫入到logfile，同時也會觸發檔案系統到磁盤的同步，該模式是最安全的，但也是最慢的一種方式；
• 2：事務送出會觸發log buffer 到logfile的重新整理，但并不會觸發磁盤檔案系統到磁盤的同步，該模式速度較快，也比0安全，隻有在作業系統崩潰或者系統斷電的情況下，上一秒鐘所有事務資料才可能丢失。

thread_cache_size

線程池緩存大小，當用戶端斷開連接配接後将目前線程緩存起來，當在接到新的連接配接請求時快速響應無需建立新的線程 。這尤其對那些使用短連接配接的應用程式來說可以極大的提高建立連接配接的效率。可以通過(Connections - Threads_created) / Connections * 100% 計算出連接配接線程緩存的命中率。也可以通過如下幾個MySQL狀态值來适當調整線程池的大小：

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 持續升高，可适當增加 thread_cache_size 的大小。

table_open_cache

table_open_cache指定表高速緩存的大小，用來緩存表檔案的檔案句柄資訊。當我們的用戶端程式送出Query給MySQL的時候，MySQL需要對Query所涉及到的每一個表都取得一個表檔案句柄資訊，如果沒有Table Cache，那麼MySQL就不得不頻繁的進行打開關閉檔案操作，無疑會對系統性能産生一定的影響，每當MySQL通路一個表時，如果在表緩沖區中還有空間，該表就被打開并放入其中，這樣可以更快地通路表内容。注意，這裡設定的是可以緩存的表檔案句柄資訊的數目，而不是記憶體空間的大小。

通過檢查峰值時間的狀态值Open_tables和Opened_tables，可以決定是否需要增加table_open_cache的值。其中Open_tables是目前正在打開表的數量，Opened_tables是所有已經打開表的數量。注意，不能盲目地把table_open_cache設定成很大的值，設定太大超過了shell的檔案描述符(通過ulimit -n檢視)，造成檔案描述符不足，進而造成性能不穩定或者連接配接失敗。如果發現open_tables等于table_open_cache，并且opened_tables在不斷增長，那麼你就需要增加table_open_cache的值了(上述狀态值可通過SHOW GLOBAL STATUS LIKE 'Open%tables'獲得)。如果Open_tables的值已經接近table_cache的值，且Opened_tables還在不斷變大，則說明mysql正在将緩存的表釋放以容納新的表，此時可能需要加大table_cache的值。對于大多數情況，比較适合的值：

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

建議把MySQL資料庫放在生産環境中試運作一段時間，然後把參數的值調整得比Opened_tables的數值大一些，并且保證在比較高負載的極端條件下依然比Opened_tables略大。

table_definition_cache

table_definition_cache和table_open_cache類似，前者緩存frm檔案，關于後者，文檔中并沒有說明，應該是ibd/MYI/MYD；

狀态值：

Open_table_definitions：表定義檔案.frm被緩存的數量

Opened_table_definitions：曆史上總共被緩存過的,frm檔案數量

key_buffer_size

key_buffer_size指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀态值Key_read_requests和Key_reads，可以知道key_buffer_size設定是否合理。比例key_reads /key_read_requests應該盡可能的低，至少是1:100，1:1000更好(上述狀态值可以使用SHOW STATUS LIKE ‘key_read%'獲得)。key_buffer_size隻對MyISAM表起作用。即使你不使用MyISAM表，但是内部的臨時磁盤表是MyISAM表，也要使用該值。可以使用檢查狀态值created_tmp_disk_tables得知詳情。

max_connections

MySQL的最大連接配接數，增加該值增加mysqld 要求的檔案描述符的數量。如果伺服器的并發連接配接請求量比較大，建議調高此值，以增加并行連接配接數量，當然這建立在機器能支撐的情況下，因為如果連接配接數越多，介于MySQL會為每個連接配接提供連接配接緩沖區，就會開銷越多的記憶體，是以要适當調整該值，不能盲目提高設值。數值過小會經常出現ERROR 1040: Too many connections錯誤，可以過’conn%’通配符檢視目前狀态的連接配接數量，以定奪該值的大小。max_used_connections / max_connections * 100% (理想值≈ 85%) 如果max_used_connections跟max_connections相同 那麼就是max_connections設定過低或者超過伺服器負載上限了，低于10%則設定過大。

線程/會話/連接配接獨享記憶體

binlog_cache_size

為每個session 配置設定的記憶體，在事務過程中用來存儲二進制日志的緩存，可以提高記錄bin-log的效率，預設32K，沒有大事務，dml也不是很頻繁的情況下可以設定小一點，如果事務大而且多，dml操作也頻繁，則可以适當的調大一點。

資料庫binlog_cache_size的使用情況，可以檢視：Binlog_cache_disk_use表示因為我們binlog_cache_size設計的記憶體不足導緻緩存二進制日志用到了臨時檔案的次數，Binlog_cache_use  表示 用binlog_cache_size緩存的次數

tmp_table_size和max_heap_table_size

tmp_table_size規定了内部記憶體臨時表的最大值，每個線程都要配置設定。(實際起限制作用的是tmp_table_size和max_heap_table_size的最小值。)如果記憶體臨時表超出了限制，MySQL就會自動地把它轉化為基于磁盤的MyISAM表，存儲在指定的tmpdir目錄下，預設:

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

優化查詢語句的時候，要避免使用臨時表，如果實在避免不了的話，要保證這些臨時表是存在記憶體中的。如果需要的話并且你有很多group by語句，并且你有很多記憶體，增大tmp_table_size(和max_heap_table_size)的值。這個變量不适用與使用者建立的記憶體表(memory table)。

可以比較内部基于磁盤的臨時表的總數和建立在記憶體中的臨時表的總數(Created_tmp_disk_tables和Created_tmp_tables)，一般的比例關系是:

Created_tmp_disk_tables/Created_tmp_tables<5%

max_heap_table_size定義了使用者可以建立的記憶體表(memory table)的大小.這個值用來計算記憶體表的最大行數值。這個變量支援動态改變，即set @max_heap_table_size = xxx。

推薦閱讀

(點選标題可跳轉閱讀)

MySQL關于日期為零值的處理

關于日期及時間字段的查詢

MySQL分區表最佳實踐

— END —

如果你喜歡我的文章

請在文末右下角點一下在看❤