什么是自適應(yīng)參數(shù)

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

自適應(yīng)參數(shù)是如何設(shè)置和適應(yīng)變化的

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

innodb_buffer_pool_size:

• <1G: 128M(innodb_dedicated_server=為OFF時的默認(rèn)取值)

• <=4G: 探測到的物理內(nèi)存 * 0.5

• >4G: 探測到的物理內(nèi)存 * 0.75

innodb_log_file_size:

• <1G: 48M(innodb_dedicated_server=為OFF時的默認(rèn)取值)

• <=4G: 128M

• <=8G: 512M

• <=16G: 1024M

• >16G: 2G

innodb_flush_method:

如果系統(tǒng)允許設(shè)置為O_DIRECT_NO_FSYNC。如果系統(tǒng)不允許，則設(shè)置為InnoDB默認(rèn)的Flush method。 

上述這些參數(shù)在MySQL每次啟動時自動探測服務(wù)器（包括虛擬機和容器的內(nèi)存）配置并自動生效。

自適應(yīng)參數(shù)使用注意

• innodb_dedicated_server默認(rèn)設(shè)置為OFF，不會自適應(yīng)調(diào)整3個參數(shù)值。該參數(shù)也不是動態(tài)參數(shù)，無法動態(tài)調(diào)整，也就是說MySQL啟動后無法修改這個參數(shù)

• innodb_dedicated_server=ON 設(shè)置以后它其實只探測了服務(wù)器內(nèi)存，所以目前只能自適應(yīng)調(diào)整內(nèi)存相關(guān)的三個參數(shù)

• innodb_dedicated_server=ON的情況下，如果還顯式設(shè)置了 innodb_buffer_pool_size / innodb_log_file_size / innodb_flush_method 參數(shù)，顯示設(shè)置的這些參數(shù)會優(yōu)先生效，并且在MySQL的錯誤日志中會打印如下內(nèi)容：

"[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參數(shù)。 

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

• 顯示指定某一個值，并不會影響其他變量的自適應(yīng)參數(shù)值設(shè)置。例如顯式設(shè)置了innodb_buffer_pool_size，那么buffer pool會按照你顯示設(shè)置的值初始化，而不是 innodb_dedicated_server參數(shù)對應(yīng)的值。但是innodb_log_file_size 和 innodb_flush_method 并不會受影響，它們還是會按照innodb_dedicated_server的自適應(yīng)值按照服務(wù)器的內(nèi)存大小來設(shè)置。

• innodb_dedicated_server=ON的情況下，mysqld服務(wù)進(jìn)程每次重啟后都會自動調(diào)整上述三個參數(shù)值。在任何時候MySQL都不會將自適應(yīng)值保存在持久配置中。

• 如果系統(tǒng)不支持O_DIRECT_NO_FSYNC，MySQL會沿用之前的默認(rèn)值。MySQL仍然必須保證在所有平臺上能正常啟動，不需要任何其他更改。

• 如果自適應(yīng)導(dǎo)致innodb_log_file_size對應(yīng)的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服務(wù)拒絕啟動。

• innodb_dedicated_server=ON并不見得是最優(yōu)的配置。例如，你用了MyISAM，MyRocks等其他存儲引擎時，建議手工調(diào)整，而不是設(shè)置innodb_dedicated_server=ON

• XFS系統(tǒng)請手工設(shè)置inndob_flush_method=O_DIRECT。在inndob_flush_method=O_DIRECT_NO_FSYNC下，InnoDB使用O_DIRECT來刷新IO，但是跳過fsync()步驟。對某些文件系統(tǒng)有效，但是對XFS文件系統(tǒng)并不適用。為了保證文件的metadata刷新到磁盤中，XFS必須使用O_DIRECT。

自適應(yīng)之前是怎么樣的

在5.7上，innodb_buffer_pool_size默認(rèn)為134217728即128MB，如果采用默認(rèn)設(shè)置，MySQL 5.7大致只能消耗系統(tǒng)的512M內(nèi)存。 

而innodb_log_file_size=50331648 即48M，對于大并發(fā)下的請求并不適用。 

這也導(dǎo)致大量文章建議采用相應(yīng)的方法優(yōu)化設(shè)置這些參數(shù)，例如： 

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這個參數(shù)可以很大程度解決這方面的問題。

為什么調(diào)整這幾個參數(shù)而不是其他參數(shù)

這個參數(shù)在InnoDB上對性能的影響相對較大，并且也是最急迫需要自適應(yīng)調(diào)整的幾個參數(shù)。(個人覺得innodb_buffer_pool_instances也應(yīng)該在自適應(yīng)調(diào)整的范圍內(nèi)) 

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

要解決其他問題，例如sort_buffer_size，read_rnd_buffer_size等連接內(nèi)存自適應(yīng)調(diào)整，需要對內(nèi)存的精細(xì)控制，并且各種應(yīng)用訪問方式并不一樣，并不是那么容易自適應(yīng)；而innodb_read_io_threads，innodb_write_io_threads等需要根據(jù)CPU核數(shù)調(diào)整，也跟應(yīng)用訪問模式有一定關(guān)系；對于innodb_io_capacity而言，要探測底層存儲設(shè)備具體的IO能力，并相應(yīng)設(shè)置，也不是一個簡單的工作。 

到底其他影響性能的自適應(yīng)參數(shù)什么時候調(diào)，只能敬請期待了。

適應(yīng)場景

• 運行MySQL的服務(wù)器上是專門給MySQL提供服務(wù)的。innodb_dedicated_server的默認(rèn)設(shè)置都是假設(shè)這個服務(wù)器的資源，MySQL都能用起來。

不適應(yīng)場景

• 單機多實例情況下不適應(yīng)。

• 其他有特殊場景要求的不適用。比如：不是主要以InnoDB為存儲引擎的；服務(wù)器上還有其他應(yīng)用程序的等等。

重大意義

各位云廠商的同志們有福了，利用這個參數(shù)就可以保證服務(wù)器（虛擬機或者容器）擴展以后，MySQL能“自適應(yīng)”以盡量消耗更多的服務(wù)器資源，而不用自己設(shè)計一個自動擴展MySQL服務(wù)器資源配置的腳本。既避免了服務(wù)器擴展以后MySQLbuffer pool不變等，使用不了那么多資源；也避免了服務(wù)器縮減了以后MySQLbuffer pool過大等，導(dǎo)致MySQL服務(wù)進(jìn)程啟動不起來。

這個參數(shù)的改變，也意味著：

• 后續(xù)MySQL的類似參數(shù)會越來越優(yōu)化，DBA排查問題時對MySQL參數(shù)的考慮會越來越少

• MySQL的運維DBA的工作越來越簡單了，MySQL也會越來越智能

作者簡介

李春，沃趣科技聯(lián)合創(chuàng)始人，高級MySQL數(shù)據(jù)庫專家，浙江大學(xué)數(shù)據(jù)庫碩士

從事MySQL相關(guān)工作10年+。曾作為阿里巴巴MySQL數(shù)據(jù)庫leader，負(fù)責(zé)產(chǎn)品，應(yīng)用架構(gòu)的優(yōu)化和部署；實現(xiàn)了阿里巴巴3億產(chǎn)品從Oracle小型機到MySQL SSD的平滑遷移。技術(shù)上專注于MySQL復(fù)制、數(shù)據(jù)一致性、數(shù)據(jù)安全性、擴展性和運維自動化的領(lǐng)域。

原文地址https://blog.csdn.net/woqutechteam/article/details/80856108