MySQL 在 AWS 中達到 IOPS 壁壘，無法利用全卷 IOPS 級別
我們正在擴展現有的基於 MySQL 5.6.41（使用 Galera）的系統，該系統在 EC2 上的 AWS 中執行，並且在性能方面遇到了瓶頸，我們認為這將縮小到儲存 IOPS。
請注意，為了測試基線 MySQL 性能，Galera 集群由單個節點組成——沒有發生節點-節點複製。
設置
數據庫由幾張表組成，其中一張有大約 10M 行並處理大量讀寫量。讀取和寫入都發生在不同的行中，沒有行爭用（除了可能發生的任何索引範圍鎖定）。
該節點在 AWS R5.2xlarge（8 核，64GB 記憶體）上針對具有預置 IOPS 的 EBS 卷執行。
我們的負載測試包括同一可用區和私有子網中的兩個 8 核心盒，啟動 150 多個客戶端連接，並使用在實時執行系統中看到的相同訪問模式（按鍵讀取單行、修改和寫入更新）錘擊伺服器排）。
MySQL 配置為允許 500 個連接（緩衝池也是 48GB）。
症狀
最初，卷 IOPS 設置為 1000，在此期間，我們能夠執行負載測試並在 Cloud Watch 中看到最大 (1000) 的捲 IOPS 統計數據。
將捲 IOPS 設置為 2000 時，我們能夠將捲 IOPS 固定在最大值（2000），並看到每秒事務的等效增長。
然而，通過 MySQL 進行 3000 和 5000 的負載測試都不會超過 2000 IOPS，並且正如預期的那樣，我們也沒有看到每秒事務的任何增加。同樣，針對 MySQL 的 sysbench 也不能超過 2000 IOPS。
請注意，VM 和卷似乎配置正確，因為 fio 中的 io 測試確實將 IOPS 一直推到了極限（分別為 3000 和 5000）。
另請注意，MySQL 也沒有達到卷吞吐量限制，VM cpu 幾乎沒有達到 10% 的使用率。MySQL 在負載測試期間似乎很空閒。
問題是 IOPS 以外的問題嗎？
我們不這麼認為，原因如下——我們最初關心的是表爭用、連接數、請求數或數據頻寬。
因此，這是我們為確認這些可能相關所做的工作：
- 表爭用
一種。我們複製了負載測試中使用的主讀/寫表，並從兩個複製表之一中拆分測試客戶端的每個讀/寫——結果：使用的事務/秒或 IOPS 沒有變化
灣。將負載測試中使用的主讀/寫表切換為使用分區 - 結果：事務/秒或使用的 IOPS 沒有變化 2. 連接數、請求數或數據頻寬
一種。將負載測試設置為在不進行任何更改的情況下讀取然後寫入記錄數據，而這消除了最終的 io 寫入 MySQL 仍然為請求提供服務，並且（我們相信）執行與請求相關的正常獲取/鎖定 - 結果：每秒事務數顯著增加，但IOPS 仍限制在 2000
我們傾向於相信上面的 noop 更新測試（#2）可能會產生更高的性能，而 IOPS 再次成為限制因素。
我們嘗試過什麼來解決 MySQL 節點中的 IOPS 限制？
我們已經嘗試根據針對高事務系統、SSD 儲存和一般 MySQL 建議的調整文章修改以下配置項。
這些都是單獨和一起添加和調整的，但沒有看到任何改進。在許多情況下，無論好壞，性能都沒有變化：
innodb_buffer_pool_instances=48 innodb_flush_method=O_DIRECT innodb_io_capacity=3000 innodb_io_capacity_max=5000 innodb_read_io_threads=16 innodb_write_io_threads=16 innodb_log_file_size=6GB innodb_log_files_in_group=2 innodb_checksum_algorithm=crc32 innodb_flush_neighbors=0 innodb_page_size=64KB innodb_flush_log_at_trx_commit=0 query_cache_size=0 query_cache_type=0 thread_cache_size=32 thread_concurrency=20 transaction_isolation=READ-COMMITTED否則我們的基本配置是相當標準的：
[mysqld] innodb_buffer_pool_size=48G max_connections=500 skip_name_resolve=ON wsrep_slave_threads=32 wsrep_provider_options="evs.send_window=1024; evs.user_send_window=512"我確定問題出在我們配置錯誤的地方。
有什麼建議或想法嗎？
有許多原因可能導致無法使用所有可用的 IOP。這是一個無序列表的想法。
- 基準測試中呼叫了多少執行緒？
- 超過 40 個執行緒可能會進入“雷鳴般的羊群”場景，其中沒有一個執行緒有效執行，因為它們相互踩踏。我最喜歡的類比是雜貨店裡的人太多，而推車被木頭卡住了。
- 10M 行？聽起來您不需要 48GB 的記憶體用於 buffer_pool？
- 雙寫、flush_log_at_trx_commit 等的設置是什麼。可以更改這些設置以在更低的 IOP 下獲得更好的性能。（好吧，這與您的基準測試目標相反。）如果您使用多節點 Galera，這些設置可以放寬。
- 5.7 和 8.0 有進一步的改進。
- 是不是每個連接都做的很少，因此大部分時間都花在了建立和拆除連接上？威爾遜要求的東西將幫助我們分析這一點。
- 您的負載測試是否測試您的實際查詢？否則，基準往往會提供可疑的結果。
- 我很少看到 MySQL 伺服器會耗盡 CPU。當我這樣做時，它幾乎總是可以通過添加索引或重新編寫查詢來修復。也就是說，不要擔心測試中的低 CPU 使用率。
PARTITIONing幾乎從來都不是性能優勢。UPDATEs可能很昂貴。保留該行的副本以備不時之需ROLLBACK。如果兩個連接命中同一行，一個可能會延遲或終止（“死鎖”）。確保您的基准在更新行的隨機性方面模仿真實係統。嘗試一下：以 40 個連接的限制執行您的基準測試。可能是“每秒事務數”會增加。注意：此指標可能比 IOP 使用率更重要。
也許您的測試表明系統比您需要的要大？
由於您已將 MySQL 配置為 48GB 記憶體，因此基準測試的寫入都將在記憶體中，但是 MySQL 仍將寫入磁碟以獲取二進制日誌。但是您已將它們配置為每秒僅刷新一次，因此這幾乎不是您的問題。
我還在您的配置中看到您正在使用 Galera。請注意，Galera 中的每次寫入都必須通過網路傳輸到集群中的其他節點。每個事務都需要在本地送出之前得到其他節點的確認，因此 Galera 確實受到網路延遲的影響。我最近自己在 Google Cloud 中執行了一些基準測試，發現由於網路延遲，我無法超過每秒 7200 個事務，但我不記得我會獲得的最大 IOPS。但即使是預置的 IOPS 也無法提高我的基準，所以這只是網路級別的限制，您在使用雲時無法控制。
您可以做的是設置具有類似配置的單個 MySQL 節點，並將您的結果與 Galera 結果進行比較。