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的錯誤日志中會列印如下内容：

'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将被删除

 ●  錯誤日志顯示如下

1. "[ERROR] InnoDB: Error number 28 means 'No space left on device'

2. [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也會越來越智能

原文釋出時間為：2018-10-10

本文作者：李春·沃趣科技

本文來自雲栖社群合作夥伴“

老葉茶館

”，了解相關資訊可以關注“

”。