MySQL 8.0 首個自適應參數橫空出世

｜什麼是自適應參數

MySQL8.0推出一個號稱可以自適應伺服器的參數，保證在各種不同的伺服器、虛擬機、容器下自動適配伺服器資源，讓我們一起來看看到底它能做到什麼地步。

｜自適應參數是如何設置和適應變化的

可以設置參數 innodb_dedicated_server=ON來讓MySQL自動探測伺服器的內存資源，確定innodb_buffer_pool_size, innodb_log_file_size 和 innodb_flush_method 三個參數的取值。具體取值策略如下。

innodb_buffer_pool_size:

<1G: 128M(innodb_dedicated_server=為OFF時的默認取值)
<=4G: 探測到的物理內存 * 0.5
>4G: 探測到的物理內存 * 0.75

innodb_log_file_size:

<1G: 48M(innodb_dedicated_server=為OFF時的默認取值)
<=4G: 128M
<=8G: 512M
<=16G: 1024M
>16G: 2G

innodb_flush_method:

如果系統允許設置為O_DIRECT_NO_FSYNC。如果系統不允許，則設置為InnoDB默認的Flush method。

上述這些參數在MySQL每次啟動時自動探測伺服器（包括虛擬機和容器的內存）配置並自動生效。

｜自適應參數使用注意

innodb_dedicated_server默認設置為OFF，不會自適應調整3個參數值。該參數也不是動態參數，無法動態調整，也就是說MySQL啟動後無法修改這個參數
innodb_dedicated_server=ON 設置以後它其實只探測了伺服器內存，所以目前只能自適應調整內存相關的三個參數
innodb_dedicated_server=ON的情況下，如果還顯式設置了 innodb_buffer_pool_size / innodb_log_file_size / innodb_flush_method 參數，顯示設置的這些參數會優先生效，並且在MySQL的錯誤日誌中會列印如下內容：

"[Warning] InnoDB: Option innodb_dedicated_server is ignored for because variable name=? is specified explicitly."

variable name 指的就是 innodb_buffer_pool_size/innodb_log_file_size/inndob_flush_method參數。

注意：你不管是在配置文件、命令行、還是MySQL新引入的固化配置中設定上述三個參數都被認為是顯式指定了參數值，都會優先生效。

顯示指定某一個值，並不會影響其他變數的自適應參數值設置。例如顯式設置了innodb_buffer_pool_size，那麼buffer pool會按照你顯示設置的值初始化，而不是 innodb_dedicated_server參數對應的值。但是innodb_log_file_size 和 innodb_flush_method 並不會受影響，它們還是會按照innodb_dedicated_server的自適應值按照伺服器的內存大小來設置。
innodb_dedicated_server=ON的情況下，mysqld服務進程每次重啟後都會自動調整上述三個參數值。在任何時候MySQL都不會將自適應值保存在持久配置中。
如果系統不支持O_DIRECT_NO_FSYNC，MySQL會沿用之前的默認值。MySQL仍然必須保證在所有平臺上能正常啟動，不需要任何其他更改。
如果自適應導致innodb_log_file_size對應的redo log file超過了磁碟空間限制（這個空間得有多小！），將會採取以下措施：

* 新生成的日誌文件redo log將被刪除

* 錯誤日誌顯示如下

"[ERROR] InnoDB: Error number 28 means No space left on device [ERROR] InnoDB: Cannot set log file to size MB"

| * mysqld服務拒絕啟動。

innodb_dedicated_server=ON並不見得是最優的配置。例如，你用了MyISAM，MyRocks等其他存儲引擎時，建議手工調整，而不是設置innodb_dedicated_server=ON
XFS系統請手工設置inndob_flush_method=O_DIRECT。在inndob_flush_method=O_DIRECT_NO_FSYNC下，InnoDB使用O_DIRECT來刷新IO，但是跳過fsync()步驟。對某些文件系統有效，但是對XFS文件系統並不適用。為了保證文件的metadata刷新到磁碟中，XFS必須使用O_DIRECT。

｜自適應之前是怎麼樣的

在5.7上，innodb_buffer_pool_size默認為134217728即128MB，如果採用默認設置，MySQL 5.7大致只能消耗系統的512M內存。

而innodb_log_file_size=50331648 即48M，對於大並發下的請求並不適用。

這也導致大量文章建議採用相應的方法優化設置這些參數，例如：

Is 80% of RAM how you should tune your innodb_buffer_pool_size?

How to calculate a good InnoDB log file size

How to Choose the MySQL innodb_log_file_size

MySQL 8.0提供了innodb_dedicated_server=ON這個參數可以很大程度解決這方面的問題。

｜為什麼調整這幾個參數而不是其他參數

這個參數在InnoDB上對性能的影響相對較大，並且也是最急迫需要自適應調整的幾個參數。(個人覺得innodb_buffer_pool_instances也應該在自適應調整的範圍內)

目前它也只是探測了系統內存，實現起來比較簡單，並且對性能改進非常有效，基本能解決絕大部分入門DBA安裝的性能問題。就像一個在班級成績排名倒數的同學，先幫他解決了60分及格的問題再考慮提高到班級前10名。

要解決其他問題，例如sort_buffer_size，read_rnd_buffer_size等連接內存自適應調整，需要對內存的精細控制，並且各種應用訪問方式並不一樣，並不是那麼容易自適應；而innodb_read_io_threads，innodb_write_io_threads等需要根據CPU核數調整，也跟應用訪問模式有一定關係；對於innodb_io_capacity而言，要探測底層存儲設備具體的IO能力，並相應設置，也不是一個簡單的工作。

到底其他影響性能的自適應參數什麼時候調，只能敬請期待了。

｜適應場景

運行MySQL的伺服器上是專門給MySQL提供服務的。innodb_dedicated_server的默認設置都是假設這個伺服器的資源，MySQL都能用起來。

｜不適應場景

單機多實例情況下不適應。
其他有特殊場景要求的不適用。比如：不是主要以InnoDB為存儲引擎的；伺服器上還有其他應用程序的等等。

｜重大意義

各位雲廠商的同志們有福了，利用這個參數就可以保證伺服器（虛擬機或者容器）擴展以後，MySQL能「自適應」以盡量消耗更多的伺服器資源，而不用自己設計一個自動擴展MySQL伺服器資源配置的腳本。既避免了伺服器擴展以後MySQLbuffer pool不變等，使用不了那麼多資源；也避免了伺服器縮減了以後MySQLbuffer pool過大等，導致MySQL服務進程啟動不起來。

這個參數的改變，也意味著：

後續MySQL的類似參數會越來越優化，DBA排查問題時對MySQL參數的考慮會越來越少
MySQL的運維DBA的工作越來越簡單了，MySQL也會越來越智能

｜作者簡介

李春，沃趣科技聯合創始人，高級MySQL資料庫專家，浙江大學資料庫碩士

從事MySQL相關工作10年+。曾作為阿里巴巴MySQL資料庫leader，負責產品，應用架構的優化和部署；實現了阿里巴巴3億產品從Oracle小型機到MySQL SSD的平滑遷移。技術上專註於MySQL複製、數據一致性、數據安全性、擴展性和運維自動化的領域。