Amazon Aurora MySQL數(shù)據(jù)庫配置最佳實踐

在AWS Cloud當(dāng)中遷移或啟動新的Amazon Aurora MySQL實例之后，您是否考慮過以下幾個問題？

·“接下來該做什么？我該如何讓實例保持最佳運行狀態(tài)？”

·“是否需要對現(xiàn)有參數(shù)做出修改？”

·“具體該對哪些參數(shù)做出修改？”

如果這些問題確實困擾著您，希望本篇文章能給大家?guī)硪稽c有意義的指導(dǎo)與啟發(fā)。

在本文中，我們將探討、闡述并總結(jié)Amazon Aurora（兼容MySQL）當(dāng)中的參數(shù)配置建議。這些數(shù)據(jù)庫參數(shù)本身及其取值，將在AWS Cloud內(nèi)新創(chuàng)建或遷移實例的存留、調(diào)優(yōu)及重新配置當(dāng)中發(fā)揮重要作用。另外，我們還將討論應(yīng)該將Amazon RDS for MySQL中的哪些參數(shù)保留至Aurora實例當(dāng)中。最后，本文還將論述參數(shù)的默認(rèn)值、哪些參數(shù)對您實例的穩(wěn)定性及性能優(yōu)化至關(guān)重要等具體問題。

在執(zhí)行變更之前，首先需要明確的就是變更背后的需求與動機。盡管大多數(shù)參數(shù)設(shè)置已經(jīng)具有不錯的默認(rèn)值，但應(yīng)用程序工作負(fù)載本身的變化可能要求我們對參數(shù)做出具體調(diào)整。因此在著手修改之前，請先回答以下幾個問題：

·當(dāng)前是否存在穩(wěn)定性問題，例如重新啟動或者故障轉(zhuǎn)移？

·我能讓應(yīng)用程序的查詢運行速度更快一些嗎？

Aurora參數(shù)組快速入門

Aurora MySQL參數(shù)組分為兩種類型：數(shù)據(jù)庫參數(shù)組與數(shù)據(jù)庫集群參數(shù)組。其中某些參數(shù)會對整個數(shù)據(jù)庫集群的配置產(chǎn)生影響，例如二進制日志格式、時區(qū)與字集集默認(rèn)值等。其他參數(shù)的影響范圍則僅限于單一數(shù)據(jù)庫實例。

在本文中，我們將結(jié)合不同的上下文對各項參數(shù)進行分類，聊聊哪些參數(shù)會影響Aurora集群的行為、穩(wěn)定性及功能，而哪些參數(shù)會在變更之后影響性能表現(xiàn)。

需要強調(diào)的是，這兩種參數(shù)類型都具有預(yù)設(shè)默認(rèn)值，而且只有部分參數(shù)允許修改。

在對生產(chǎn)數(shù)據(jù)庫“下手”之前

參數(shù)變更往往會產(chǎn)生意想不到的結(jié)果，包括性能下降或者系統(tǒng)不穩(wěn)定等。在對任何數(shù)據(jù)庫配置參數(shù)做出變更之前，請首先思考以下最佳實踐：

·在測試環(huán)境中為生產(chǎn)實例創(chuàng)建克隆或還原快照（詳見說明文檔）。通過這種方式，您既獲得高度近似于生產(chǎn)環(huán)境的測試條件，又不致對實際業(yè)務(wù)造成影響。

·為測試實例生成可模擬生產(chǎn)工作負(fù)載的工作負(fù)載。

·根據(jù)各項關(guān)鍵性能指標(biāo)檢查系統(tǒng)性能，具體包括CPU利用率、數(shù)據(jù)庫連接數(shù)量、內(nèi)存利用率、緩存命中率、查詢吞吐量以及延遲等。請在執(zhí)行變更之前提取這些基準(zhǔn)數(shù)據(jù)，并在變更之后觀察結(jié)果變化。

·一次只變更一項參數(shù)，以避免發(fā)生歧義。

·如果變更未能對測試系統(tǒng)產(chǎn)生可以直接測量的影響，請考慮將參數(shù)恢復(fù)為默認(rèn)值。

·記錄哪些參數(shù)能夠產(chǎn)生積極影響，以及哪些性能指標(biāo)因變更而發(fā)生了切實改善。

默認(rèn)參數(shù)值及其重要性

某些數(shù)據(jù)庫實例參數(shù)當(dāng)中包含變量或者公式，其值則由常量確定——例如實例大小、內(nèi)存占用量、實例的網(wǎng)絡(luò)端口及其分配到的存儲容量等。大家最好不要變動這些設(shè)置，因為這些參數(shù)會隨著實例的規(guī)模伸縮而自動調(diào)整。

例如，Aurora數(shù)據(jù)庫參數(shù)innodb_buffer_pool_size的默認(rèn)值為：

{DBInstanceClassMemory*3/4}

DBInstanceClassMemory 

是一項變量，以GiB為單位設(shè)置為您實例的內(nèi)存大小。

例如：對于擁有30.5 GiB內(nèi)存的db.r4.xlarge實例來說，此值為20090716160 bytes，即18.71 GiB。

假如我們決定將此參數(shù)設(shè)置為一個固定值，例如18000000000 bytes，而后對db.r4.large實例執(zhí)行縮容操作，那么實例總內(nèi)存將降低至原本的一半（15.2 GiB）。在對數(shù)據(jù)庫引擎的修改完成之后，我們很可能遭遇內(nèi)存不足問題，并導(dǎo)致實例無法正確啟動。

要快速瀏覽由系統(tǒng)變量自動計算得出的參數(shù)，大家可以在參數(shù)組定義中搜索這些參數(shù)，具體方法為：搜索大括號字符“{”。

如果您打算查詢實例所使用的實際值，可以通過兩種方式在命令行上實現(xiàn)。具體方法為使用SHOW GLOBAL VARIABLES 或者 SELECT語句：

mysql> SHOW GLOBAL VARIABLES where Variable_Name='innodb_buffer_pool_size';

+-------------------------+------------+

| Variable_name           | Value      |

+-------------------------+------------+

| innodb_buffer_pool_size | 8001683456 |

+-------------------------+------------+

1 row in set (0.01 sec)

mysql> SELECT @@innodb_buffer_pool_size;

+---------------------------+

| @@innodb_buffer_pool_size |

+---------------------------+

|                8001683456 |

+---------------------------+

1 row in set (0.00 sec)

參數(shù)值設(shè)置錯誤的癥狀與診斷

當(dāng)某些參數(shù)發(fā)生設(shè)置錯誤時，有可能在MySQL錯誤日志中被記錄為內(nèi)存不足。在這種情況下，實例會進入滾動重啟狀態(tài)并生成類似于以下形式的事件日志，同時就需要調(diào)整的參數(shù)值提供相應(yīng)建議：

2018-12-29 19:05:16 UTC  [-]MySQL has been crashing due to incompatible parameters. Please check your memory parameters, in particular the max_connections, innodb_buffer_pool_size, key_buffer_size, query_cache_size, tmp_table_size, innodb_additional_mem_pool_size and innodb_log_buffer_size. Modify the memory parameter and reboot the instance.

參數(shù)分類

在本文的討論范圍之內(nèi)，我們將Aurora MySQL參數(shù)分為兩大類：

1.負(fù)責(zé)控制數(shù)據(jù)庫行為與功能，但對資源利用率及實例穩(wěn)定性沒有影響的參數(shù)。

2.通過管理實例中資源（例如緩存及基于內(nèi)存內(nèi)的緩沖區(qū)）的分配方式，對性能造成影響的參數(shù)。

下面，我們一起來看其中一部分具有代表性的參數(shù)、對應(yīng)默認(rèn)值，以及對它們做出修改后會給實例的行為或性能造成哪些影響。下表所示，為參數(shù)組中列出的參數(shù)名稱、Aurora與MySQL默認(rèn)值、以及受此參數(shù)影響的功能摘要。

建議與影響

下面我們對各項關(guān)鍵參數(shù)給數(shù)據(jù)庫帶來的具體影響做出簡要說明，同時通過具體用例介紹這些參數(shù)的調(diào)整方式：

autocommit

推薦設(shè)置：使用默認(rèn)值（1或ON）以保證每條SQL語句（除非屬于由用戶開啟的事務(wù)的組成部分）在運行時可自動提交。

影響：將值設(shè)置為OFF可能導(dǎo)致使用模式錯誤，例如未決事務(wù)的打開速度減慢、未被關(guān)閉甚至無法正確提交。這會影響到數(shù)據(jù)庫的性能與穩(wěn)定性。

max_connections

建議設(shè)置：使用默認(rèn)值（可變值）。在使用自定義值時，請保證只配置為應(yīng)用程序?qū)嶋H用于任務(wù)執(zhí)行的連接數(shù)量。

影響：將連接限制數(shù)量設(shè)置得過高，有可能占用更多內(nèi)存容量，且其中大部分資源被浪費在未使用連接上。此外，這種設(shè)置還可能引發(fā)數(shù)據(jù)庫連接峰值，進而影響數(shù)據(jù)庫的性能與穩(wěn)定性。

系統(tǒng)會根據(jù)您的實例內(nèi)存分配方式與大小，利用以下公式自動填充此變量參數(shù)，因此建議您優(yōu)先使用默認(rèn)值：

GREATEST({log(DBInstanceClassMemory/805306368,2)*45},{log(DBInstanceClassMemory/8187281408,2)*1000})

例如，對于擁有15.25 GiB內(nèi)存的Aurora MySQL db.r4.large實例而言，此項參數(shù)將被設(shè)置為1000：

DBInstanceClassMemory = 16374562816 bytes

max_connections = GREATEST({log(16374562816/805306368,2)*45},{log(16374562816/8187281408,2)*1000})

max_connections = GREATEST(195.56,1000) = 1000

如果遇到連接錯誤，且連接錯誤日志中顯示大量Too many connections，可將此項參數(shù)設(shè)置為固定值（而非變量值）。

如果您的應(yīng)用程序需要更多連接，因此有必要將max_connections設(shè)置為固定值時，請考慮在應(yīng)用程序與數(shù)據(jù)庫之間建立連接池或代理。這種方式同樣適用于難以可靠預(yù)測或控制連接數(shù)量的場景。

當(dāng)大家將這項參數(shù)手動配置為超過建議連接數(shù)的值時，Amazon CloudWatch中的數(shù)據(jù)庫連接指標(biāo)會在超出閾值的部分顯示紅線。CloudWatch的判斷標(biāo)準(zhǔn)源自以下公式：

Threshold value for max_connections = {DBInstanceClassMemory/12582880}

例如，對于具有15.25 GiB內(nèi)存（ 15.25 x 1024 x 1024 x 1024 = 16374562816 bytes）的db.r4.large實例，警戒閾值大約為1300條連接。當(dāng)然，如果實例資源充足，您仍然可以使用配置中指定的最大連接數(shù)。

max_allowed_packet

建議設(shè)置：使用默認(rèn)值（4194304 bytes）。僅在數(shù)據(jù)庫工作負(fù)載有明確需求時，才使用自定義值。在處理會返回大元素（例如長字符串或BLOB）的查詢時，請調(diào)整此項參數(shù)。

影響：在此處設(shè)置較大的值，并不會影響到消息緩沖區(qū)的初始大小。相反，此參數(shù)只是在查詢負(fù)載增加時，允許系統(tǒng)將消息緩沖區(qū)擴容至預(yù)先定義的上限。如果設(shè)定較大的參數(shù)值，一旦出現(xiàn)大量合法的并發(fā)查詢，則可能引發(fā)內(nèi)存不足問題。

如果將此項參數(shù)設(shè)置得太小，則會顯示以下錯誤：

ERROR 1153 (08S01) at line 3: Got a packet bigger than 'max_allowed_packet' bytes

group_concat_max_len

建議設(shè)置：使用默認(rèn)值（1024 bytes）。僅在工作負(fù)載有明確需求時使用自定義值。具體而言，只在您希望變更GROUP_CONCAT()語句的返回值并允許引擎返回更長的列值時，才對此項參數(shù)做出調(diào)整。此值應(yīng)與max_allowed_packet并行使用，后者負(fù)責(zé)確定響應(yīng)的最大大小。

影響：將這項參數(shù)設(shè)置得過高，可能導(dǎo)致內(nèi)存占用量過高以及內(nèi)存不足等問題。而設(shè)置得太低則可能導(dǎo)致查詢失敗。

innodb_ft_result_cache_limit

建議設(shè)置：使用默認(rèn)值（2000000000 bytes）。在工作負(fù)載有明確需求時使用自定義值。

影響：由于該值已經(jīng)接近1.9 GiB，進一步增大該值有可能導(dǎo)致內(nèi)存不足。

max_heap_table_size

建議設(shè)置：使用默認(rèn)值（16777216 bytes）。限制用戶在內(nèi)存內(nèi)創(chuàng)建表時指定的最大大小。變更此值只會影響新創(chuàng)建的表，原有表不受影響。

影響：將此參數(shù)設(shè)置過高會導(dǎo)致內(nèi)存利用率提升，并在內(nèi)存內(nèi)表量激增時導(dǎo)致內(nèi)存不足問題。

performance_schema

建議設(shè)置：由于會大量占用內(nèi)存，請在t2實例上禁用此參數(shù)。

影響：在Aurora MySQL 5.6當(dāng)中，系統(tǒng)已經(jīng)通過啟發(fā)式方式對Performance Schema內(nèi)存進行了預(yù)分配。這項預(yù)分配以其他配置參數(shù)為基礎(chǔ)，具體包括max_connections, table_open_cache及table_definition_cache等。在Aurora MySQL 5.7中，Perofmrance Schema內(nèi)存采用按需分配模式。Performance Schema通常會消耗1到3 GB內(nèi)存。如果數(shù)據(jù)庫實例的內(nèi)存不足，則啟用Performance Schema有可能引發(fā)內(nèi)存不足問題。

binlog_cache_size

建議設(shè)置：使用默認(rèn)值（32768 bytes）。這項參數(shù)負(fù)責(zé)控制二進制日志緩存所能使用的內(nèi)存量。調(diào)高這項參數(shù)可利用緩沖區(qū)避免大量磁盤寫入，從而提升系統(tǒng)對大型事務(wù)的處理性能。此緩存按連接進行分配。

影響：對于數(shù)據(jù)庫連接數(shù)量較大的環(huán)境，請控制此項參數(shù)以避免導(dǎo)致內(nèi)存不足問題。

bulk_insert_buffer_size

建議設(shè)置：使用默認(rèn)值，此參數(shù)并不適用于Aurora MySQL。

innodb_buffer_pool_size

建議設(shè)置：使用默認(rèn)值（可變值），此參數(shù)在Aurora中被預(yù)配置為實例內(nèi)存總量的75%。您可以在SHOW ENGINE INNODB STATUS的輸出結(jié)果中查看緩沖池的使用情況。

影響：較大的緩沖池可在系統(tǒng)重復(fù)訪問同一表內(nèi)數(shù)據(jù)時減少磁盤I/O操作，進而提高整體性能。由于需要容納InnoDB引擎本體，因此實際分配的內(nèi)存量可能略高于實際配置值。

innodb_sort_buffer_size

建議設(shè)置：使用默認(rèn)值（1048576 bytes）。

影響：高于默認(rèn)值可能會給具有大量并發(fā)查詢的系統(tǒng)帶來整體內(nèi)存壓力。

join_buffer_size

建議設(shè)置：使用默認(rèn)值（262144 bytes）。各種類型的操作（包括JOIN）中已經(jīng)預(yù)先分配有該值，且單一查詢可對緩沖區(qū)內(nèi)的多個實例進行分配。如果需要提高聯(lián)接性能，建議大家向相應(yīng)表中添加索引。

影響：在具有大量并發(fā)查詢的環(huán)境中，更改此參數(shù)可能帶來嚴(yán)重的內(nèi)存壓力。即使增加索引，調(diào)高此值也無法實現(xiàn)更好的JOIN查詢性能。

key_buffer_size

建議設(shè)置：使用默認(rèn)值（16777216 bytes），因為此項參數(shù)與Aurora無關(guān)，且僅影響MyISAM表的性能。

影響：不會對Aurora性能造成影響。

myisam_sort_buffer_size

建議設(shè)置：使用默認(rèn)值（8388608 bytes）。由于不會影響到InnoDB，因此這項參數(shù)不適用于Aurora。

影響：不會對Aurora性能造成影響。

query_cache_size

建議設(shè)置：使用默認(rèn)值（可變值）。此項參數(shù)在Aurora中已經(jīng)預(yù)先調(diào)整，且實際值遠(yuǎn)大于MySQL默認(rèn)值。Aurora的查詢緩存不會出現(xiàn)可擴展性問題（MySQL中的查詢緩存同樣不會出現(xiàn)此類問題）。但您也可以根據(jù)實際需求，修改此項參數(shù)以適應(yīng)高吞吐量、高需求工作負(fù)載。

影響：通過此緩存訪問查詢時，會對查詢性能造成影響。您可以在“QCache”部分下SHOW STATUS命令的輸出結(jié)果中查看查詢緩存的使用情況。

query_cache_type

建議設(shè)置：啟用。在默認(rèn)情況下，查詢緩存在Aurora中處于啟用狀態(tài)，我們建議您保留這種啟用狀態(tài)，從而提高性能并降低運營成本。但如果您確定當(dāng)前工作負(fù)載無法因此受益，則可禁用查詢緩存。此類用例之一為高強度寫入型工作負(fù)載，其中不涉及任何讀取查詢。

影響：如果您的工作負(fù)載會復(fù)用查詢內(nèi)容（例如可重復(fù)的SQL語句），那么在Aurora中禁用查詢緩存可能會影響數(shù)據(jù)庫性能。您可以在“QCache”部分SHOW STTUS命令的輸出結(jié)果中查看查詢緩存的使用情況。

read_buffer_size

建議設(shè)置：使用默認(rèn)值（262144 bytes）。

影響：設(shè)置較大的值可提升整體內(nèi)存壓力，并可能導(dǎo)致內(nèi)存不足問題。除非您確定能夠在不損害系統(tǒng)穩(wěn)定性的前提下改善性能，否則請不要調(diào)高此值。

read_rnd_buffer_size

建議設(shè)置：使用默認(rèn)值（524288 bytes）。由于底層存儲集群的性能特點，這里我們無需改動Aurora的默認(rèn)設(shè)置。

影響：設(shè)置較大的值可能導(dǎo)致內(nèi)存不足問題。

table_open_cache

建議設(shè)置：除非您的工作負(fù)載需要同時訪問大量表，否則請不要改動此項參數(shù)。表緩存會占用大量內(nèi)存，而Aurora中的默認(rèn)值已經(jīng)遠(yuǎn)高于MySQL默認(rèn)值。此項參數(shù)會根據(jù)實例大小自動調(diào)整。

影響：對于包含大量表（數(shù)十萬級別）的數(shù)據(jù)庫，可能需要調(diào)高此項參數(shù)，這是因為某些表可能不適合保存在內(nèi)存中。但將此值設(shè)置得過高可能導(dǎo)致內(nèi)存不足。如果您啟用了Performance Schema，此項設(shè)置也會間接影響到Performance Schema的可用內(nèi)存量。

table_definition_cache

建議設(shè)置：使用默認(rèn)值。此項參數(shù)在Aurora中的默認(rèn)值已經(jīng)遠(yuǎn)高于MySQL默認(rèn)值，并會根據(jù)實例大小與類型進行自動調(diào)整。如果您的工作負(fù)載要求使用此參數(shù)，且數(shù)據(jù)庫需要同時打開大量表，則調(diào)高參數(shù)可能會加快表開啟操作的速度。此參數(shù)需要與table_open_cache配合使用。

影響：如果您啟用了Performance Schema，此項設(shè)置也會間接影響到Performance Schema的可用內(nèi)存量。如果使用高于默認(rèn)值的設(shè)置量，可能會導(dǎo)致內(nèi)存不足問題。

tmp_table_size

建議設(shè)置：使用默認(rèn)值（16777216 bytes）。與max_heap_table_size配合，此項參數(shù)會限制用于查詢處理的內(nèi)存內(nèi)表大小。當(dāng)超出臨時表容量上限時，表將被交換至磁盤。

影響：如果值設(shè)置得過高（數(shù)百MB以上）可能導(dǎo)致內(nèi)存問題甚至內(nèi)存不足。此項參數(shù)不會影響由MEMORY引擎創(chuàng)建的表。

結(jié)論與要點

在部署新的Aurora MySQL實例時，大部分參數(shù)已經(jīng)完成預(yù)優(yōu)化，足以作為后續(xù)參數(shù)變更前的良好基準(zhǔn)。不同參數(shù)值的具體組合主要取決于系統(tǒng)實際情況、應(yīng)用程序工作負(fù)載以及所需要的吞吐量等因素。此外，在變化率高、增長速度快、數(shù)據(jù)攝取量大且工作負(fù)載動態(tài)程度較強的數(shù)據(jù)庫系統(tǒng)上，大家也需要對這些參數(shù)進行持續(xù)監(jiān)控與評估。我們建議您每隔幾個月（或者幾周）進行一輪監(jiān)控與評估，確保數(shù)據(jù)庫始終適應(yīng)應(yīng)用程序與業(yè)務(wù)的實際需求。

要將參數(shù)調(diào)整成功轉(zhuǎn)化為可量化的性能提升，我們建議您不斷試驗、建立基準(zhǔn)并認(rèn)真比較每變更后的性能結(jié)果。此外，我們還建議您在向?qū)崟r生產(chǎn)系統(tǒng)提交變更之前，做好測試與性能比較工作。

如果您希望了解關(guān)于特定參數(shù)的更多詳細(xì)信息，請參閱AWS支持文檔或聯(lián)系A(chǔ)WS技術(shù)客戶團隊。如果你喜歡今天的文章，請多多點贊評論和我們互動哦～

本篇作者

Fabio Higa 是一名數(shù)據(jù)庫專家技術(shù)賬戶經(jīng)理，AWS 的專精方向為 RDS Aurora/MySQL 引擎。他與全球的諸多企業(yè)級客戶已有逾 3 年的合作經(jīng)驗。在閑暇時間，他喜歡擺弄自己的汽車，并開著它們參加當(dāng)?shù)氐谋荣悺?/span>