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

標籤：智能   改進   red   裝置   虛擬   ecif   reads   程式   不同的   

MySQL 8.0 首個自適應參數橫空出世
MySQL 8.0推出一個號稱可以自適應伺服器的參數，保證在各種不同的伺服器、虛擬機器、容器下自動適配伺服器資源。本文將為大家詳細介紹該參數。

作者 李春·沃趣科技聯合創始人＆進階MySQL資料庫專家
出品 沃趣科技

｜什麼是自適應參數
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"

```sql* 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 sizeHow to Choose the MySQL innodb_log_file_sizeMySQL 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 8.0 首個自適應參數橫空出世](http://blog.woqutech.com/2018/07/10/mysql-8-0-%E9%A6%96%E4%B8%AA%E8%87%AA%E9%80%82%E5%BA%94%E5%8F%82%E6%95%B0%E6%A8%AA%E7%A9%BA%E5%87%BA%E4%B8%96/)

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