Mysql 8.0.21 long living Prepared statements 有記憶體洩漏？還是我們在做一些奇怪的事情
– 更新了問題，因為最初很容易假設我是在詢問 mysql 調整技巧，儘管一些技巧確實幫助我縮小了問題的範圍 –
——進一步更新。在對某些伺服器進行了部分升級之後，我們發現問題也出現在 MySQL 5.7 上，因此猜測這是 5.6 和 5.7 之間的某種行為變化。它在 MySQL 5.6 中沒有引起問題對我們來說仍然有點奇怪——
我們最近在幾台伺服器上從 MySQL 5.6 升級到 MySQL 8.0，其中一台伺服器很好，並且沒有任何問題，但它的負載明顯低於我們其他一台記憶體不足的伺服器。
我們的伺服器啟動，然後獲取 300 個連接，並通過 C3P0 池保持連接到 mysql 伺服器。
在 4 天裡，這個特定的伺服器累積了 3500 多個打開的準備好的語句，其中一些佔用了超過 300 Mb 在
memory/sql/Prepared_statement::main_mem_root我們的伺服器配置文件打開連接/準備好的語句是正常的，並且在 mysql 5.6 或 5.7 上沒有問題我可以看到它在某種程度上與準備好的語句有關，但是“分配解析的樹元素（Item、SELECT_LEX 和其他類的實例）。” 沒有告訴我太多，是記憶體結果嗎？它是這樣成長的嗎？
我們正在使用 connector/j 8.0.18 我查看了 8.0.18 -> 8.0.23 的發行說明，沒有任何明顯的記憶體洩漏修復
我們的連接參數包括
cachePrepStmts", "true"); useServerPrepStmts", "true");我們在 MySQL 5.6 上的 AWS 上執行這些伺服器，在 8GB RAM 上使用相同的覆蓋參數，當我們升級到 MySQL 8.0.21 時，我們在大約 1 天后就開始耗盡 RAM。我們將伺服器增加到 32Gb，但沒有更改參數。它已經使用了超過 15 GB 並且還在增加。
我們很確定它與每個連接執行緒的記憶體有關，但不知道為什麼。
查看 memory_by_thread_by_current_bytes 我們有超過 200Mb 的連接
伺服器在 AWS RDS m4.large 上執行 8.0.21
我已經審核了程式碼並且所有結果集都被關閉了。準備好的語句也在程式碼中，但上面的記憶體行為使它們保持打開狀態。
通常，innodb 緩衝池是 4GB，但是當我們在 8GB 機器上時，我們將它降低到 3GB 以獲得更多空間。
顯示全域狀態/memory_summary_by_thread_by_event_name/MySQL 調諧器執行/顯示引擎 innodb 狀態/顯示變數/sys.memory_by_thread_by_current_bytes/sys.memory_global_by_current_bytes https://gist.github.com/DaveB93/138f6bac254fee5bbbbb7ce2af7c2fef
- 更新 -
我們通過 JMX 連接到我們的應用程序並將 C3P0 連接池設置從“maxIdleTimeExcessConnections”更改為 300（從 0 開始）來“修復”這個問題
這清理了所有長期存在的連接，並釋放了 10GB 的記憶體，但這似乎不是我想要的長期解決方案。
RAM消耗的可能原因可能在這裡：
[!!] Temporary tables created on disk: 85% (249K on disk / 290K total)創建自動臨時表時，它總是
ENGINE=MEMORY首先創建。如果它不符合max_heap_table_size = 134217728限制，它將被轉移到磁碟上以供進一步處理。臨時表與會話/連接相關聯，因此每個執行緒可以在 RAM 中創建許多 128MB 大的臨時表 - 除了為讀取/連接/排序分配的緩衝區。我建議減少
max_heap_table_size到像 16M 這樣的合理值，因為在 85% 的情況下，temptables 無論如何都不適合 128M。另一個建議是優化執行時間最長的查詢（由於磁碟上的臨時表操作緩慢）。慢查詢日誌是一個很好的起點。
MySQLTuner 在“每執行緒記憶體”方面是悲觀的。如果你不交換，那麼你就沒有麻煩。
無論如何，這是對設置的另一種攻擊：
觀察：
- 版本：8.0.21
- 8 GB 記憶體
- 正常執行時間 = 3d 00:45:32
- 您沒有在 Windows 上執行。
- 執行 64 位版本
- 您似乎完全（或大部分）執行 InnoDB。
更重要的問題：
8.0減少
key_buffer_size到 10M。如前所述，請查看一些查詢。
如果磁碟是 SSD，則
io_capacity可以增加一些等設置。
table_open_cache大於必要的；減少到 3000。（沒有指標可以說明什麼值是最優的。）min(tmp_table_size, max_heap_table_size) 只有2M；這並不像 Tuner 抱怨的那麼糟糕。
細節和其他觀察：
( (key_buffer_size - 1.2 * Key_blocks_used * 1024) ) = ((256M - 1.2 * 0 * 1024)) / 8192M = 3.1%– key_buffer 中浪費的 RAM 百分比。– 減小 key_buffer_size（現在是 268435456）。
( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 256M = 0– 使用的 key_buffer 的百分比。高水位線。– 降低 key_buffer_size（現在為 268435456）以避免不必要的記憶體使用。
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((256M / 0.20 + 3072M / 0.70)) / 8192M = 69.2%– 大部分可用的 ram 應可用於記憶體。– http://mysql.rjweb.org/doc.php/memory
( table_open_cache ) = 4,096– 要記憶體的表描述符的數量 – 幾百通常是好的。
( 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_max / innodb_io_capacity ) = 2,000 / 200 = 10– 容量：max/plain – 推薦 2. Max 應該大約等於您的 I/O 子系統可以處理的 IOP。（如果驅動器類型未知，2000/200 可能是合理的一對。）
( Innodb_log_writes ) = 25,846,932 / 261932 = 99 /sec
( innodb_io_capacity ) = 200- 磁碟上每秒的 I/O 操作數。100 用於慢速驅動器；200 用於旋轉驅動器；SSD 1000-2000；乘以 RAID 係數。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF– 是否記錄所有死鎖。– 如果你被死鎖困擾，打開它。注意：如果你有很多死鎖，這可能會寫入很多磁碟。
( local_infile ) = local_infile = ON– local_infile (now ON) = ON 是一個潛在的安全問題
( Created_tmp_disk_tables ) = 269,432 / 261932 = 1 /sec– 作為複雜 SELECT 的一部分創建磁碟“臨時”表的頻率 – 增加 tmp_table_size（現在為 2097152）和 max_heap_table_size（現在為 134217728）。檢查何時使用 MEMORY 而不是 MyISAM 的臨時表規則。也許較小的模式或查詢更改可以避免 MyISAM。更好的索引和查詢的重新制定更有可能有所幫助。
( Created_tmp_disk_tables / Created_tmp_tables ) = 269,432 / 313876 = 85.8%– 溢出到磁碟的臨時表的百分比 – 可能增加 tmp_table_size（現在 2097152）和 max_heap_table_size（現在 134217728）；改進指標；避免斑點等
( Select_scan ) = 636,670 / 261932 = 2.4 /sec– 全表掃描 – 添加索引/優化查詢（除非它們是小表）
( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 3135 - 0 ) / ( 3135 + 0 ) = 100.0%——你正在結束你準備好的陳述嗎？- 添加關閉。
( Com_stmt_close / Com_stmt_prepare ) = 0 / 3135 = 0– 準備好的報表應該是關閉的。– 檢查是否所有Prepared 語句都“關閉”。
( Com_admin_commands / Queries ) = 1,968,489 / 33689117 = 5.8%– “管理員”命令的查詢百分比。 - 這是怎麼回事？
( long_query_time ) = 5– 用於定義“慢”查詢的截止時間（秒）。– 建議 2
( log_slow_slave_statements ) = log_slow_slave_statements = OFF– (5.6.11, 5.7.1) 預設情況下，複製的語句不會出現在慢日誌中；這導致他們顯示。– 在慢日誌中查看可能干擾副本讀取的寫入會很有幫助。異常小：
Com_show_fields = 0 min(max_heap_table_size, tmp_table_size) = 2MB異常大：
Com_alter_user = 0.014 /HR Com_flush = 12 /HR Com_purge_before_date = 12 /HR Com_show_create_func = 0.3 /HR Com_show_open_tables = 0.014 /HR Com_show_slave_hosts = 0.055 /HR Com_stmt_reset = 5.6 /sec Innodb_data_pending_fsyncs = 6,688 Innodb_system_rows_deleted = 0.069 /HR Innodb_system_rows_inserted = 0.069 /HR Innodb_system_rows_updated = 62 /HR Prepared_stmt_count = 3,131 Ssl_accepts = 300 Ssl_finished_accepts = 300 Ssl_session_cache_overflows = 172 Ssl_used_session_cache_entries = 128 Threads_connected = 304 back_log / max_connections = 100.0% innodb_thread_concurrency = 32 max_error_count = 1,024 max_length_for_sort_data = 4,096 optimizer_trace_offset = --1 performance_schema_max_cond_classes = 100 performance_schema_max_mutex_classes = 300 performance_schema_max_rwlock_classes = 60 performance_schema_max_stage_classes = 175 performance_schema_max_statement_classes = 218 performance_schema_max_thread_classes = 100異常字元串：
event_scheduler = ON gtid_mode = OFF_PERMISSIVE innodb_fast_shutdown = 1 log_output = TABLE log_statements_unsafe_for_binlog = OFF 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_exec_mode = IDEMPOTENT slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN time_zone = UTC version_compile_machine = aarch64