Mysql

如何為 mariaDB 獲得更多連接或如何減少每個執行緒的 RAM 使用量?

  • March 4, 2022

我們有一個 MariaDB 10.3 數據庫伺服器,在 8 核和 64GB RAM 機器上執行。數據庫引擎是 innoDB。我們目前max_connections = 175和我們的數據庫管理員告訴我,不可能進一步增加連接數(至少在不增加物理 RAM 等的情況下是不可能的)。

我們有一個高負載場景,需要將數字從 175 增加到 350。這可以通過“調整”以下數字來實現嗎?

我的直覺是,通過優化數據庫設置,應該可以實現更多。從評論來看,我們的管理員似乎確實執行了 MySQL Tuner。在與他交談時,我想提出一些改進建議,因此我會對 SO 社區對我們設置的意見感興趣。

# Memory-Sizing
open-files-limit                = 16384
table_cache                     = 8192
thread_cache_size               = 32
max_allowed_packet              = 128M
myisam_sort_buffer_size         = 32M
key_buffer_size                 = 128M
tmp_table_size                  = 1G
max_heap_table_size             = 1G
query_cache_type                = 1
query_cache_size                = 16M
query_cache_limit               = 16M
sort_buffer_size                = 8M
read_buffer_size                = 1M
read_rnd_buffer_size            = 512K

# InnoDB
innodb_open_files               = 16384
innodb_strict_mode              = 1
innodb_log_file_size            = 6G
innodb_log_buffer_size          = 128M
innodb_lock_wait_timeout        = 1200
innodb_large_prefix             = 1
innodb_buffer_pool_size         = 30G
innodb_buffer_pool_instances    = 30

# Tuning-Results
# @see https://github.com/major/MySQLTuner-perl
skip-name-resolve               = 1
join_buffer_size                = 3M
performance_schema              = ON

# replication
sync_binlog                     = 5
max_binlog_size                 = 512M

我使用了兩個不同的 DB 記憶體計算器(12),結果不同。我還閱讀了里克斯的“經驗法則”。我認為 tmp_table_size (和堆)可能太大 - 至少一個記憶體計算器表明每個連接都需要這個大小。我還計算了Created disk tmp tables ratio這裡建議的結果,結果是0.21% ——這似乎很低。

Tmp_disk_tables=((created_tmp_disk_tables*100/(created_tmp_tables+created_tmp_disk_tables))
= ((11597*100/(5453174 + 11597))
= 0,2122%

還有一些人建議關閉查詢記憶體。

(太多)變數很多,所以我感謝您的時間和幫助。

更新:謝謝大家的評論和回答。需要明確的是:我確實信任我們的管理員 - 這不是問題 :-) 我們的設置:我們有一個負載均衡器,它將負載平均分配到目前的 12 個應用程序伺服器節點。每台伺服器都配置了最多 14 個連接(最少 2 個)的連接池。還有一些額外的連接用於管理、維護以及能夠直接連接到數據庫。是的,我們已經擊中了No managed connections available within configured blocking timeout- 不是在所有伺服器節點上,而是在一些伺服器節點上。通常此設置執行良好,但在高峰情況下會發生這種情況(並行使用者/使用量更高)。所以問題實際上是如何能夠增加連接數。

現在回答你的問題:

  • 如果連接主要來自 Web 伺服器,則它可能配置得太高。 **答:**連接來自執行 Web 應用程序的應用程序伺服器
  • 如果它來自應用程序,它們是否無法關閉連接?**答:**根據我所做的分析,他們沒有。在高峰情況下,我們確實有這麼多並行使用者使用系統
  • 是否有某種形式的“連接池”?如果是這樣,它有什麼限制?**答:**是的,我們有:每台伺服器最少 2 個連接,最多 14 個連接。
  • “高負載場景”**答:**指系統上有很多並行使用者。有趣的是,我們的應用程序伺服器節點上的負載(CPU 方面)相對較低,因此它們可以為更多使用者提供服務,但是我們會受到最大數據庫連接的限制(因此我的問題是增加這些連接)
  • 慢查詢**回答:**我們確實寫了一個慢查詢日誌(記錄所有查詢>2s)並且99%的數據庫查詢在1s以下完成

可以在不增加max_connectionsRAM 的情況下增加。但是——讓我們討論一下 175 是否真的太大了

如果Max_used_connections沒有達到175,那就max_connections不是真正的問題。

如果你已經達到了這個限制,那麼讓我們從調查客戶是什麼開始。

  • 如果連接主要來自 Web 伺服器,則它可能配置得太高。
  • 如果它來自應用程序,它們是否無法關閉連接?
  • 是否有某種形式的“連接池”?如果是這樣,它有什麼限制?

關閉 query_cache;它(通常)是負擔而不是好處。

將這些設置為 RAM 的 1% 以下: tmp_table_size, max_heap_table_size. 它們不僅是每個查詢,還可能是每個子查詢。無論如何,1G非常適合“收益遞減”。基於磁碟的臨時表的出現有多種原因;更改這兩個設置無法擺脫所有磁碟臨時表。

根據 的設置innodb_buffer_pool_size,我建議大量 RAM 未使用。

我同意 0.21% 相當低。如果您想要更多類似的指標,請參閱http://mysql.rjweb.org/doc.php/mysql_analysis#tuning。它還將提供指標來說明查詢記憶體是否有用。

“高負載場景”——這是什麼意思?如果是“high ‘Load Average’”,那相當於“high CPU”。這個問題最好通過尋找最慢和/或最常見的查詢並嘗試加快它們來解決。使用SlowLog來幫助解決這個問題。

每秒速率 = RPS

為您的 my.cnf 考慮的建議

$$ mysqld $$加快查詢完成的部分

innodb_lru_scan_depth=100  # from 1024 to conserve 90% CPU cycles used for function every SECOND
innodb_buffer_pool_instances=16  # from 30 to reduce overhead of managing 14 instances
read_rnd_buffer_size=96K  # from 512K to reduce handler_read_rnd_next RPS of 150,967 
join_buffer_size=1M  # from 3M for your join row pointer management
sort_buffer_size=2M  # from 8M and could cause non-lethal increase in sort_merge_passes

最後 2 個是每個連接減少 6M 的 RAM。

觀察,

  1. max_connections 的目前限制為 262,而您的 max_used_connections 為 164,正常執行時間為 116 天。直到 max_used_connections 達到 90% 的可用性,保持它在原處。
  2. 在 query_cache_type=OFF 和 query_cache_size=0 的情況下禁用查詢記憶體將消除 qcache_lowmem_prunes 在 116 天正常執行時間中每秒鐘執行 9 次。
  3. com_rollback 平均每 6 秒 1 次。想像一下成本並嘗試消除原因。我們的一般日誌分析將有助於發現問題。
  4. com_stmt_prepare 報告為每 13 秒執行一次。為什麼沒有報告 com_stmt_execute 操作?通常我們會看到匹配的數字。
  5. com_stmt_close 每 13 秒執行一次以釋放資源,這很好。
  6. select_scan 報告的 116 天正常執行時間的表掃描 RPS 為 25。指示需要在某些表中建立索引的列,這將減少所需的查詢執行時間,更不用說掃描表的成本了。

請查看我們的個人資料以獲取聯繫資訊,並聯繫以獲取其他可行的建議。還有其他配置變數需要調整。

引用自:https://dba.stackexchange.com/questions/308128