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

MySQL理論上使用的內(nèi)存 = 全局共享內(nèi)存 + max_connections×線程獨(dú)享內(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ù)與線程獨(dú)享參數(shù)分類，簡(jiǎn)單介紹下相關(guān)參數(shù)的作用。

全局共享內(nèi)存

innodb_buffer_pool_size

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

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

innodb_change_buffering

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

目的還是為了減少隨機(jī)IO帶來(lái)性能損耗，說(shuō)明白了：把隨機(jī)IO盡量變成順序IO?，F(xiàn)在SSD盛行，在SSD上隨機(jī)訪問(wèn)和順序訪問(wèn)性能幾乎差不多的情況下，change buffering特性不會(huì)帶來(lái)多大的性能提升，但對(duì)于廉價(jià)的機(jī)械硬盤，這個(gè)參數(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.
  

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

innodb_change_buffer_max_size

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

innodb_log_buffer_size

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

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

thread_cache_size

線程池緩存大小，當(dāng)客戶端斷開連接后將當(dāng)前線程緩存起來(lái)，當(dāng)在接到新的連接請(qǐng)求時(shí)快速響應(yīng)無(wú)需創(chuàng)建新的線程 。這尤其對(duì)那些使用短連接的應(yīng)用程序來(lái)說(shuō)可以極大的提高創(chuàng)建連接的效率?？梢酝ㄟ^(guò)(Connections - Threads_created) / Connections * 100% 計(jì)算出連接線程緩存的命中率。也可以通過(guò)如下幾個(gè)MySQL狀態(tài)值來(lái)適當(dāng)調(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)

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

table_open_cache

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

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

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

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

table_definition_cache

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

狀態(tài)值：

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

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

key_buffer_size

key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過(guò)檢查狀態(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只對(duì)MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時(shí)磁盤表是MyISAM表，也要使用該值。可以使用檢查狀態(tài)值created_tmp_disk_tables得知詳情。

max_connections

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

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

binlog_cache_size

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

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

tmp_table_size和max_heap_table_size

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

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

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

可以比較內(nèi)部基于磁盤的臨時(shí)表的總數(shù)和創(chuàng)建在內(nèi)存中的臨時(shí)表的總數(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)的大小.這個(gè)值用來(lái)計(jì)算內(nèi)存表的最大行數(shù)值。這個(gè)變量支持動(dòng)態(tài)改變，即set @max_heap_table_size = xxx。

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