如何為 mariaDB 獲得更多連接或如何減少每個執行緒的 RAM 使用量?
我們有一個 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 記憶體計算器(1、2),結果不同。我還閱讀了里克斯的“經驗法則”。我認為 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_connections
RAM 的情況下增加。但是——讓我們討論一下 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。
觀察,
- max_connections 的目前限制為 262,而您的 max_used_connections 為 164,正常執行時間為 116 天。直到 max_used_connections 達到 90% 的可用性,保持它在原處。
- 在 query_cache_type=OFF 和 query_cache_size=0 的情況下禁用查詢記憶體將消除 qcache_lowmem_prunes 在 116 天正常執行時間中每秒鐘執行 9 次。
- com_rollback 平均每 6 秒 1 次。想像一下成本並嘗試消除原因。我們的一般日誌分析將有助於發現問題。
- com_stmt_prepare 報告為每 13 秒執行一次。為什麼沒有報告 com_stmt_execute 操作?通常我們會看到匹配的數字。
- com_stmt_close 每 13 秒執行一次以釋放資源,這很好。
- select_scan 報告的 116 天正常執行時間的表掃描 RPS 為 25。指示需要在某些表中建立索引的列,這將減少所需的查詢執行時間,更不用說掃描表的成本了。
請查看我們的個人資料以獲取聯繫資訊,並聯繫以獲取其他可行的建議。還有其他配置變數需要調整。