Azure Database for MySQL - 記憶體不斷增加然後崩潰

我知道有一些和我類似的文章，但沒有一個能幫助我。

我有一個 Azure Database for MySQL。平均記憶體使用率在 90% 以上，有時伺服器會崩潰並重新啟動。 重啟後記憶體使用量不斷增加，慢慢達到90%的使用率。在下圖中查看此行為。

平均而言，我們有大約 300 個與數據庫的活動連接。在崩潰時，連接沒有任何峰值。

我們的工作負載在多個雲平台上執行，我們在其他託管數據庫服務上沒有這種問題。基於此，我可以說我們的查詢、函式等都寫得很好。所以問題來自其他地方。

伺服器參數：

• MySQL版本： 5.7
• 通用 v1 儲存 400GB 和 4vCPU -> 20GB RAM（來自Azure 定價層模型）
• 儲存引擎： InnoDB

我收集了一些關於我的伺服器的配置和指標。這些是我檢查過的值。

InnoDB

innodb_buffer_pool_size 約為 8GB，剩下 20-8=12GB 記憶體可供使用。

• innodb_buffer_pool_chunk_size 67108864
• innodb_buffer_pool_instances 8
• innodb_buffer_pool_size 8053063680
• innodb_dirty_page_fflush 10
• innodb_flush_log_at_trx_commit 1
• innodb_flush_method async_unbuffered
• innodb_log_buffer_size 16777216
• innodb_log_file_size 268435456

登錄

• log_bin 關閉

讀取、排序、加入緩衝區

• 讀取緩衝區大小 262144
• 加入緩衝區大小 262144
• 排序緩衝區大小524288

查詢記憶體

• have_query_cache 是
• query_cache_limit 1048576
• 查詢記憶體大小 0
• 查詢記憶體類型 關閉

其他有用的配置

• 最大連接數 1260
• 執行緒記憶體大小20
• thread_handling 每個連接一個執行緒
• 執行緒堆棧 262144
• table_open_cache 2500
• table_open_cache_instances 1

性能模式記憶體使用情況：

select event_name, current_alloc, high_alloc
from sys.memory_global_by_current_bytes 
where current_count > 0;

結果。（比1MiB較小記錄未示出）

儲存器/ performance_schema / table_handles 27.19
MIB 27.19 MIB 儲存器/ performance_schema / events_statements_summary_by_thread_by_event_name 17.61 MIB 17.61 MIB

儲存器/ performance_schema / events_statements_history_long 13.66 MIB 13.66

MIB儲存器/ performance_schema / memory_summary_by_thread_by_event_name 11.25
MIB 11.25 MIB 儲存器/ performance_schema / events_statements_summary_by_digest。令牌 10.27 MiB 10.27 MiB

記憶體/performance_schema/events_statements_history_long.tokens 9.77 MiB 9.77 MiB

儲存器/ performance_schema / events_statements_history_long.sqltext 9.77 MIB 9.77 MIB

儲存器/ performance_schema / events_statements_history 6.99 MIB 6.99 MIB

儲存器/ performance_schema / events_statements_current 6.99 MIB 6.99

MIB儲存器/ performance_schema / events_waits_summary_by_thread_by_event_name 6.47
MIB 6.47 MIB 儲存器/ performance_schema / file_instances 5.50
MIB 5.50 MIB 儲存器/ performance_schema /table_io_waits_summary_by_index_usage 5.50 MiB 5.50 MiB

儲存器/ performance_schema / events_statements_summary_by_digest 5.20 MIB 5.20 MIB

儲存器/ performance_schema / events_statements_history.tokens 5.00 MIB 5.00 MIB

儲存器/ performance_schema / events_statements_history.sqltext 5.00 MIB 5.00 MIB

儲存器/ performance_schema / events_statements_current.tokens 5.00 MIB 5.00 MIB

儲存器/ performance_schema / events_statements_current.sqltext 5.00 MiB 5.00 MiB

記憶體/performance_schema/events_statements_summary_by_account_by_event_name 4.40 MiB 4.40 MiB

儲存器/ performance_schema / events_statements_summary_by_host_by_event_name 4.40 MIB 4.40 MIB

儲存器/ performance_schema / events_statements_summary_by_user_by_event_name 4.40 MIB 4.40

MIB儲存器/ performance_schema / table_shares 4.00
MIB 4.00 MIB 儲存器/ performance_schema / events_transactions_history_long 3.13
MIB 3.13 MIB 儲存器/ performance_schema / memory_summary_by_account_by_event_name 2.81
MIB 2.81 MIB 儲存器/ performance_schema / memory_summary_by_host_by_event_name 2.81 兆字節 2.81 兆字節

儲存器/ performance_schema / memory_summary_by_user_by_event_name 2.81
MIB 2.81 MIB 儲存器/ performance_schema / events_stages_summary_by_thread_by_event_name 2.34 MIB 2.34

MIB儲存器/ performance_schema / mutex_instances 2.12 MIB 2.12 MIB儲存器/ performance_schema / rwlock_instances 2.12
MIB 2.12 MIB 儲存器/ performance_schema /執行緒 1.78 MIB 1.78 MIB儲存器/ performance_schema / events_waits_summary_by_user_by_event_name 1.62 兆字節 1.62 兆字節

儲存器/ performance_schema / events_waits_summary_by_account_by_event_name 1.62 MIB 1.62 MIB儲存器/ performance_schema / events_waits_summary_by_host_by_event_name 1.62

MIB 1.62 MIB 儲存器/ performance_schema / events_waits_history_long 1.60 MIB 1.60 MIB儲存器/ performance_schema / events_transactions_history 1.60 MIB 1.60 MIB 儲存器/ performance_schema / events_statements_summary_by_digest.sqltext 1.50 MIB 1.50 MIB 儲存器/ performance_schema /table_lock_waits_summary_by_table 1.34 MiB 1.34 MiB

SHOW ENGINE INNODB STATUS命令的相關輸出：

緩衝池和記憶體

分配的總大記憶體8237875200
分配的 字典記憶體28248522

緩衝池大小 491520
可用 緩衝區 8195

數據庫頁面 478536

舊數據庫頁面176484

修改的數據庫頁面 1637

等待讀取 0

等待寫入：LRU 0，刷新列表 0，單頁 0

頁面年輕 531503，非年輕78203347

0.00 youngs/s, 0.00 non-youngs/s

頁面讀取 2821560, 創建 701494, 寫入 3187860

0.00 reads/s, 0.00 created/s, 0.00 writes/s

緩衝池命中率 1000 / 1000, young-making 率 0 / 1000不是 0 / 1000

頁面預讀 0.00/s，無訪問權限 0.00/s，隨機預讀 0.00/s

LRU len: 478536, unzip_LRU len: 0

I/O sum

$$ 2280 $$: 電流$$ 0 $$, 解壓總和$$ 0 $$: 電流$$ 0 $$

歡迎任何想法，如果需要，我很樂意提供更多資訊。

=====================編輯=========================

的整個輸出顯示變數：

https ://pastebin.com/VHpyJ44j

SHOW GLOBAL STATUS 的全部輸出：

https ://pastebin.com/cVMyF9EJ

快速解決方法是降低innodb_buffer_pool_size到，比如說，7G。

但是使用 20G 的 RAM，您不應該用完 RAM。您在同一台伺服器上是否有其他應用程序？他們是導致崩潰的人嗎？

如需進一步分析 MySQL，請提供所有的VARIABLES和：http STATUS: //mysql.rjweb.org/doc.php/mysql_analysis#tuning

在我對變數和狀態的分析中沒有顯示太多：

全球狀態和變數分析：

觀察：

• 版本：5.7.32
• 20 GB 記憶體
• 正常執行時間 = 1d 10:43:41
• 您正在 Windows 上執行。
• 847 次每秒

更重要的問題：

Max_used_connections = 453 – 這是相當高的。如果實際上有這麼多程序正在執行（不僅僅是睡眠），那麼連接可能會相互絆倒。

max_connections = 1260 – 將其降至 500 以避免執行記憶體不足的風險。（但是，您可能會用完“連接”，然後應該對其進行處理。）

innodb_io_capacity_max = 100,000——太高了；將其降至 2000。類似的設置同上。

有一些緩慢查詢的跡象可能是可優化的。找到繁忙的查詢，讓我們討論其中的幾個。

每秒 170 個“設置選項”——這是怎麼回事？

細節和其他觀察：

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((16M / 0.20 + 7680M / 0.70)) / 20480M = 54.0%– 大部分可用的 ram 應可用於記憶體。– http://mysql.rjweb.org/doc.php/memory

( table_open_cache ) = 2,500– 要記憶體的表描述符的數量 – 幾百通常是好的。

( binlog_cache_size * max_connections ) = (1M * 1260) / 20480M = 6.2%– RAM 用於記憶體傳輸到 binlog 的事務。– 減少 binlog_cache_size（現在 1048576）和/或 max_connections（現在 1260）

( innodb_buffer_pool_size ) = 7,680 / 20480M = 37.5%– InnoDB buffer_pool 使用的 RAM 百分比 – 設置為可用 RAM 的 70% 左右。（太低效率低；太高風險交換。）

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096– 頁面清理器每秒的工作量。– “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復：考慮 1000 / innodb_page_cleaners（現在是 4）。還要檢查交換。

( innodb_lru_scan_depth ) = 1,024 – “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復

( innodb_io_capacity ) = 100,000– 沖洗時，使用這麼多的 IOP。– 讀取可能緩慢或尖刺。

( innodb_io_capacity_max ) = 100,000– 緊急沖洗時，使用這麼多的 IOP。– 讀取可能緩慢或尖刺。

( innodb_doublewrite ) = innodb_doublewrite = OFF– 額外的 I/O，但在崩潰時額外的安全性。– FusionIO、Galera、Replicas、ZFS 可以關閉。

( Innodb_os_log_written ) = 38,144,144,384 / 125021 = 305101 /sec– 這是 InnoDB 繁忙程度的指標。– 非常繁忙的 InnoDB。

( Innodb_log_writes ) = 5,626,176 / 125021 = 45 /sec

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 125,021 / 60 * 256M / 38144144384 = 14.7– InnoDB 日誌輪換之間的分鐘數從 5.6.8 開始，可以動態更改；請務必同時更改 my.cnf。– （輪換間隔 60 分鐘的建議有些隨意。）調整 innodb_log_file_size（現在為 268435456）。（不能在 AWS 中更改。）

( innodb_file_per_table ) = innodb_file_per_table = OFF– 將每個文件放在自己的表空間中 – （輕度推薦，尤其是大表）

( Innodb_row_lock_time_max ) = 23,963– 鎖定行的最長時間（毫秒） – 可能發生衝突的查詢；可能是表掃描。

( Innodb_row_lock_waits ) = 344,886 / 125021 = 2.8 /sec– 獲取行鎖延遲的頻率。– 可能是由可以優化的複雜查詢引起的。

( innodb_flush_neighbors ) = 1– 將塊寫入磁碟時的小優化。– 使用 0 表示 SSD 驅動器；1 用於硬碟。

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON– 通常應該是 ON。– 在某些情況下，OFF 更好。另請參見 innodb_adaptive_hash_index_parts（現在為 8）（5.7.9 之後）和 innodb_adaptive_hash_index_partitions（MariaDB 和 Percona）。ON 涉及罕見的崩潰（錯誤 73890）。10.5.0 決定預設關閉。

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF– 是否記錄所有死鎖。– 如果你被死鎖困擾，打開它。注意：如果你有很多死鎖，這可能會寫入很多磁碟。

( max_allowed_packet ) = 1,024 / 20480M = 5.0% – 如果您沒有要載入的大 blob（等），則減小該值。否則減小 innodb_buffer_pool_size（現在為 8053063680）以騰出空間。交換對性能來說很糟糕。

( local_infile ) = local_infile = ON – local_infile (now ON) = ON 是一個潛在的安全問題

( Created_tmp_disk_tables ) = 721,828 / 125021 = 5.8 /sec– 作為複雜 SELECT 的一部分創建磁碟“臨時”表的頻率 – 增加 tmp_table_size（現在為 16777216）和 max_heap_table_size（現在為 16777216）。檢查何時使用 MEMORY 而不是 MyISAM 的臨時表規則。也許較小的模式或查詢更改可以避免 MyISAM。更好的索引和查詢的重新制定更有可能有所幫助。

( Created_tmp_disk_tables / Created_tmp_tables ) = 721,828 / 1400391 = 51.5%– 溢出到磁碟的臨時表的百分比 – 可能增加 tmp_table_size（現在為 16777216）和 max_heap_table_size（現在為 16777216）；改進指標；避免斑點等

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (22984286 + 12815127 + 1192460 + 0 + 9893254 + 32725) / 125021 = 375 /sec– writes/sec – 50 writes/sec + 日誌刷新可能會最大化 HDD 的 I/O 寫入容量

( relay_log_space_limit ) = 1024M– 副本上中繼日誌的最大總大小。（0=無限）——讓我們討論一下限制的理由。

( slow_query_log ) = slow_query_log = OFF– 是否記錄慢查詢。(5.1.12)

( long_query_time ) = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 2

( log_slow_slave_statements ) = log_slow_slave_statements = OFF– (5.6.11, 5.7.1) 預設情況下，複製的語句不會出現在慢日誌中；這導致他們顯示。– 在慢日誌中查看可能干擾副本讀取的寫入會很有幫助。

異常小：

(Com_select) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.555
Innodb_dblwr_pages_written = 0
Innodb_num_open_files = 21
innodb_buffer_pool_chunk_size = 64MB
innodb_lru_scan_depth / innodb_io_capacity = 0.0102
net_buffer_length / max_allowed_packet = 0.00%

異常大：

Com_alter_table + Com_flush = 0.037 /sec
Com_commit = 96 /sec
Com_create_db = 0.12 /HR
Com_create_function = 3.5 /HR
Com_create_index = 23 /HR
Com_create_procedure = 0.23 /HR
Com_create_table = 76 /HR
Com_create_user = 0.12 /HR
Com_create_view = 1.3 /HR
Com_delete = 102 /sec
Com_delete_multi = 9.5 /sec
Com_drop_db = 0.26 /HR
Com_drop_table = 90 /HR
Com_drop_user = 0.26 /HR
Com_empty_query = 1.6 /HR
Com_flush = 0.033 /sec
Com_grant = 0.12 /HR
Com_insert = 183 /sec
Com_insert_select = 1.7 /sec
Com_insert_select + Com_replace_select = 1.7 /sec
Com_release_savepoint = 1.4 /HR
Com_rollback_to_savepoint = 0.05 /sec
Com_savepoint = 1.6 /HR
Com_set_option = 170 /sec
Com_show_create_func = 42 /HR
Com_show_create_proc = 7.9 /HR
Com_show_function_status = 1.9 /HR
Com_show_keys = 0.045 /sec
Com_show_master_status = 0.033 /sec
Com_show_plugins = 36 /HR
Com_show_procedure_status = 7.3 /HR
Com_show_slave_status = 0.033 /sec
Com_show_triggers = 0.056 /sec
Com_show_warnings = 0.073 /sec
Com_truncate = 60 /HR
Com_update_multi = 0.26 /sec
Handler_read_last = 2 /sec
Handler_savepoint = 1.6 /HR
Handler_savepoint_rollback = 0.05 /sec
Open_table_definitions = 2,499
Performance_schema_file_instances_lost = 1,242
binlog_cache_size = 1.05e+6
binlog_group_commit_sync_delay = 1,000
net_read_timeout = 120
net_write_timeout = 240
optimizer_trace_offset = --1
port = 20,019
report_port = 20,019

異常字元串：

binlog_row_image = MINIMAL
delay_key_write = OFF
flush = ON
have_crypt = NO
innodb_fast_shutdown = 1
innodb_temp_data_file_path = C:  emp\ibtmp1:12M:autoextend
innodb_tmpdir = C:\temp
log_bin_trust_function_creators = ON
lower_case_file_system = ON
lower_case_table_names = 1
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
relay_log_recovery = ON
slave_parallel_type = LOGICAL_CLOCK
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
sql_safe_updates = ON

引用自：https://dba.stackexchange.com/questions/300457