針對 5-10K 小型數據庫優化 MariaDB

執行基於 MariaDB LTS (1:10.1.44-0ubuntu0.18.04.1) 的 SaaS，具有近 5000 個數據庫（每個租戶 1 個）。每個租戶的平均值：8MB 數據（在磁碟上），約 50 個表。CPU/Mem 負載可以忽略不計。幾年來一直執行良好，每天增加約 5-20 個租戶。

然而。當 dbs 的 nr 增長到大約 5150 時，MariaDB 伺服器程序崩潰。不幸的是，到目前為止，我還無法提取有用的日誌消息。此外，MariaDB 啟動大約需要 10 分鐘，當我想嘗試新設置時，這相當長。

您會推薦哪些設置來降低崩潰風險並加快啟動時間？

還有一些特點：

• 顯示全球狀態
• 顯示變數
• 伺服器有 2CPU 和 8GB 記憶體，其中 25% 在高峰時段可用。
• 每個 CPU 核心的負載在高峰期從不超過 0.20
• 連接是短暫的，我希望不會有超過 20 個並發數據庫連接。
• 所有表都是 InnoDB
• 每個數據庫的架構可能不同（但對於 98% 的租戶來說可能是相同的）
• 我使用庫存配置，除了：

innodb_file_per_table
open_files_limit        = 65536
log_warnings            = 3
innodb_buffer_pool_size = 1G

FWIW，我願意升級到較新版本的 MariaDB，但考慮到新的不確定性風險，我寧願做出明智的決定，也不願信誓旦旦。

感謝您的任何建議！

觀察：

• 版本：10.1.44-MariaDB-0ubuntu0.18.04.1
• 8 GB 記憶體
• 正常執行時間 = 4d 23:42:13
• 您沒有在 Windows 上執行。
• 執行 64 位版本
• 您似乎完全（或大部分）執行 InnoDB。

更重要的問題：

除非你的伺服器上有很多其他的應用程序，innodb_buffer_pool_size否則應該增加，也許到 5G。

“table_open_cache”有壓力，目前設置為2000；雙倍。

從 400增加到table_definition_cache1000

innodb_log_file_size比較小。但是，將它變大對您的舊版本來說是一種痛苦，所以我目前不推薦。

你在用 ARIA 嗎？

很多複雜的查詢。看到這個調查他們：http: //mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

你有一堆儲存過程？他們中的一些人做準備和執行？其中一些似乎無法關閉（DEALLOCATE）它們。這可能會導致過多的記憶體使用。（我不知道這是否會導致崩潰，但您應該清理它們。）並考慮subquery_cache=off（參見optimizer_switch）

細節和其他觀察：

( Opened_tables ) = 2,098,690 / 430933 = 4.9 /sec– 打開表格的頻率 – 增加 table_open_cache (現在 2000)

( Opened_table_definitions ) = 1,397,838 / 430933 = 3.2 /sec– 打開 .frm 文件的頻率 – 增加 table_definition_cache（現在是 400）和/或 table_open_cache（現在是 2000）。

( 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_pages_written / Innodb_buffer_pool_write_requests ) = 1,753,906 / 7374538 = 23.8%– 必須命中磁碟的寫入請求 – 檢查 innodb_buffer_pool_size（現在為 1073741824）

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 737,214,976 / (430933 / 3600) / 2 / 48M = 0.0612– 比率 – (見分鐘)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 430,933 / 60 * 48M / 737214976 = 490– InnoDB 日誌輪換之間的分鐘數從 5.6.8 開始，可以動態更改；請務必同時更改 my.cnf。– （輪換間隔 60 分鐘的建議有些武斷。）調整 innodb_log_file_size（現在為 50331648）。（不能在 AWS 中更改。）

( innodb_flush_method ) = innodb_flush_method =– InnoDB 應該如何要求作業系統寫入塊。建議使用 O_DIRECT 或 O_ALL_DIRECT (Percona) 以避免雙重緩衝。（至少對於 Unix。）有關 O_ALL_DIRECT 的警告，請參閱 chrischandler

( default_tmp_storage_engine ) = default_tmp_storage_engine =

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

( innodb_io_capacity ) = 200- 磁碟上每秒的 I/O 操作數。100 用於慢速驅動器；200 用於旋轉驅動器；SSD 1000-2000；乘以 RAID 係數。

( Innodb_deadlocks ) = 2 / 430933 = 0.017 /HR– 死鎖 – SHOW ENGINE INNODB STATUS；查看最新的一對死鎖的查詢。

( sync_binlog ) = 0– 使用 1 來增加安全性，以 I/O 為代價 =1 可能會導致大量“查詢結束”；=0 可能會導致“binlog 在不可能的位置”並在崩潰中失去事務，但速度更快。

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

( innodb_buffer_pool_populate ) = OFF = 0– NUMA 控制

( log_warnings ) = log_warnings = 3

( (Com_show_create_table + Com_show_fields) / Questions ) = (622768 + 622768) / 21607187 = 5.8%– 頑皮的框架 – 花費大量精力重新發現模式。– 向第 3 方供應商投訴。

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

( Qcache_lowmem_prunes/Qcache_inserts ) = 2,656,624/4159715 = 63.9%– Removal Ratio（由於記憶體不足而需要修剪的頻率）

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 4531192) / 4235 / 16384 = 0.176– query_alloc_block_size vs formula – 調整 query_alloc_block_size (現在 16384)

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

( Created_tmp_disk_tables / Questions ) = 1,309,758 / 21607187 = 6.1%– 需要磁碟 tmp 表的查詢的百分比。– 更好的索引/沒有斑點/等等。

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

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (27132 + 306536 + 20780 + 1296) / 372094 = 0.956– 每個送出的語句（假設所有 InnoDB） – 低：可能有助於在事務中將查詢分組；高：長期交易使各種事情緊張。

( Select_scan ) = 3,511,698 / 430933 = 8.1 /sec– 全表掃描 – 添加索引/優化查詢（除非它們是小表）

( Select_scan / Com_select ) = 3,511,698 / 14426841 = 24.3%– % 的選擇進行全表掃描。（可能被儲存常式愚弄。）——添加索引/優化查詢

( Com_stmt_prepare - Com_stmt_close ) = 12,457,121 - 12445287 = 11,834– 有多少準備好的語句尚未關閉。– 關閉準備好的語句

( binlog_format ) = binlog_format = STATEMENT– 聲明/行/混合。– ROW 是 5.7 (10.3) 的首選

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

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

( Subquery_cache_hit / ( Subquery_cache_hit + Subquery_cache_miss ) ) = 356,216 / ( 356216 + 10183006 ) = 3.4%– 子查詢記憶體命中率

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

異常小：

Com_show_status = 0.0084 /HR
Handler_write = 0.11 /sec

異常大：

Acl_database_grants = 4,656
Acl_users = 4,658
Aria_pagecache_reads = 2.9 /sec
Com_create_db = 0.9 /HR
Com_drop_db = 0.14 /HR
Com_drop_user = 0.14 /HR
Com_grant = 0.9 /HR
Com_release_savepoint = 0.067 /HR
Com_rollback_to_savepoint = 1.9 /HR
Com_savepoint = 0.067 /HR
Com_show_create_table = 1.4 /sec
Com_show_fields = 1.4 /sec
Com_stmt_close = 29 /sec
Com_stmt_prepare = 29 /sec
Com_unlock_tables = 0.06 /sec
Feature_fulltext = 0.067 /sec
Feature_subquery = 34 /sec
Feature_timezone = 0.054 /sec
Handler_savepoint = 0.067 /HR
Handler_savepoint_rollback = 1.9 /HR
Innodb_pages0_read = 223,449
Tc_log_page_size = 4,096

異常字元串：

innodb_default_row_format = compact
innodb_fast_shutdown = 1

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