什么是自適應參數

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，對于大并發下的請求并不適用。

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

https://www.percona.com/blog/2015/06/02/80-ram-tune-innodb_buffer_pool_size/

https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

https://www.percona.com/blog/2017/10/18/chose-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復制、數據一致性、數據安全性、擴展性和運維自動化的領域。

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的mysql设置参数0和1_MySQL 8.0 首个自适应参数横空出世的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。