如何為特定的數據庫負載指定機器？

我們的主要身份驗證數據庫用於 100k+ 使用者，並且正在增長是共享機器上的虛擬機，我們希望將其提取到自己的隔離硬體中。這將使我們能夠隔離資源，並幫助優化優化。但是，我不清楚什麼是我們正在做的理想機器，或者如何確定它。所以我向你尋求一些建議。

• 我們在 Debian Stretch 64 位 VM 上使用 MariaDB 10.1.26（以前稱為 mysql），記憶體為 10GB，分配有 6 個核心。CPU 大部分時間處於空閒狀態。
• 三個數據庫大約有 320 個表，沒有一個表很大，每個都在 100mb 以下。
• 磁碟上的數據大小為 24GB，ibdata1 為 2.2GB。InnoDB 表中的數據：1019.8M（表：37）。
• 讀/寫：98% / 2%
• 總緩衝區：1.9G 全域 + 2.9M 每個執行緒（最大 2500 個執行緒）
• 達到的最大記憶體使用量：4.6G（已安裝 RAM 的 47.52%）

這台機器被複製到輔助伺服器（然後從輔助伺服器複製到第三伺服器），因此中繼日誌佔用了大量空間。此伺服器用於帳戶身份驗證，它通常執行 SELECT 並讀取 InnoDB 行，因此它的數據非常少，但它需要執行良好且速度快。平均而言，有 230 個連接的執行緒，但在某些時期，連接峰值會導致我們達到 max_connections，我們一直在增加它，並且一直在調整各種參數，但我們的可用記憶體已達到極限我們可以分配。在某些情況下，某些查詢花費的時間比應有的時間長，這可能是網路問題，或者當我們達到最大連接數時，慢查詢日誌中沒有任何內容，因為它們是典型的查詢，通常相當快速地，

• 我們的慢查詢為零（在 28M 查詢中）
• 極少中止的連接
• 沒有需要臨時表的排序
• 並且沒有沒有索引的連接。

我們確實在磁碟上創建了臨時表：42%（磁碟上 1K / 總共 3K），這有點高，我不確定為什麼會這樣。

• 我們的執行緒記憶體命中率：99%（735 創建 / 913K 連接）
• 表記憶體命中率：97%（253 打開 / 259 打開）。
• 使用的打開文件限制：0% (50/16K)
• 我們 99% 的表鎖是立即獲得的（總共 29M）。

我們對所有表都使用 InnoDB。一些 InnoDB 統計數據：

• InnoDB 執行緒並發：0
• 每個表的 InnoDB 文件已啟動
• InnoDB 緩衝池/數據大小：1.2G/1019.8M
• InnoDB 緩衝池實例：1
• InnoDB 讀取緩衝區效率：99.98%（240354196 次點擊/總共 240398682）
• InnoDB Write Log 效率：75.01%（302451 hits/403237 total）
• InnoDB 日誌等待：0.00%（0 次等待 / 100786 次寫入）

我有以下最後一年的 munin 圖，我很樂意應要求提供。它們太多了，無法單獨提供：二進制/中繼日誌使用；不同的命令/秒；執行緒和連接數；表記憶體、打開文件、打開表；處理程序活動（寫入、更新、刪除等）；InnoDB 緩衝池大小、頁數和修改頁數；InnoDB 緩衝池活動（頁面讀取、創建和寫入）；InnoDB 檢查點年齡；InnoDB 歷史列表長度；InnoDB 插入緩衝區的數量和大小；InnoDB IO（文件讀取/寫入、日誌寫入和文件同步）；InnoDB IO 掛起；InnoDB 日誌緩衝區大小，KB 刷新和寫入；InnoDB 行操作；InnoDB 信號量；InnoDB 事務。我也有各種各樣的類型；表鎖的數量；臨時磁碟表；和執行緒數。

• 時間 = 3.342
• qps = 270.123

基於以下幾點：

use information_schema;
select VARIABLE_VALUE into @num_queries from GLOBAL_STATUS where VARIABLE_NAME = 'QUESTIONS';
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME = 'UPTIME';
select VARIABLE_VALUE into @num_com from GLOBAL_STATUS where VARIABLE_NAME = 'COM_COMMIT';
select VARIABLE_VALUE into @num_roll from GLOBAL_STATUS where VARIABLE_NAME = 'COM_ROLLBACK';
select (@num_com + @num_roll) / @uptime as tps, @num_queries / @uptime as qps;

• 顯示變數
• 顯示全域變數
• 顯示全球狀態
• iostat -x 輸出
• ulimit -a： core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 39926 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 39926 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
• df -h： Filesystem Size Used Avail Use% Mounted on udev 4.9G 0 4.9G 0% /dev tmpfs 1001M 452K 1000M 1% /run /dev/vda1 45G 28G 15G 65% / tmpfs 4.9G 0 4.9G 0% /dev/shm tmpfs 5.0M 0 5.0M 0% /run/lock tmpfs 4.9G 0 4.9G 0% /sys/fs/cgroup tmpfs 1001M 0 1001M 0% /run/user/0
• 免費-h： total used free shared buff/cache available Mem: 9.8G 2.8G 4.1G 452K 2.8G 6.7G Swap: 0B 0B 0B

大多數表都是MyISAM？在性能方面，這可能會阻礙您。衝突資訊：“320 個表”；“InnoDB 表中的數據：1019.8M（表：37）。”；“我們對所有表都使用 InnoDB”

“最大達到的記憶體使用量：4.6G”，“10G RAM”——你有其他應用程序在同一個虛擬機中執行嗎？如果不是，您可能沒有充分利用 RAM。也可能不是：“InnoDB 緩衝池/數據大小：1.2G/1019.8M”

“從中學到大學”——為什麼？這樣做是有正當理由的，但是如果第二級死了，第三級就毫無用處了。

“連接峰值導致我們達到 max_connections”——這可能是一個嚴重的問題。它是由 MyISAM 中的表鎖引起的嗎？查詢慢？DDL 操作（ALTER等）？在某些情況下，限制客戶端（例如 Web 伺服器）的連接比增加max_connections. 我需要更好地了解正在發生的事情。

在峰值期間，Threads_running會上漲嗎？當它STATUS超過 20 個時，MySQL 很可能會絆倒自己。延遲受到影響，吞吐量停滯不前，甚至下降。

“我們的慢查詢為零”。我敢打賭long_query_time仍然是無用的預設值 10 秒。將其更改為 1。

“在磁碟上創建的臨時表：42%”——不好。但是讓我們通過慢日誌找到“最差”的查詢來解決這個問題。

請遵循http://mysql.rjweb.org/doc.php/mysql_analysis中的慢日誌建議

圖表：大多數會很無聊。但是尖峰可能很有趣。尤其是排列顯示異常活動的圖表，同時顯示您的“連接高峰”。（列出指標而不是實際提供圖表可能就足夠了。）

270 qps 和 3 tps——適中。（一項調查顯示 100 qps 大約是中位數；1900 是第 90 個百分位數。）

顯然，您的桌子很少，因為您還沒有撞到 open_file_limit = 1024。

“您無法通過調整來擺脫性能問題”（並且大多數可調參數都可以）。所以，我認為慢日誌（見上面的連結）是最好的下一步。

全球狀態和變數分析

觀察：

• 版本：10.1.26-MariaDB-0+deb9u1
• 10 GB 記憶體
• 正常執行時間 = 1d 05:53:01
• 您沒有在 Windows 上執行。
• 執行 64 位版本
• 您似乎完全（或大部分）執行 InnoDB。

更重要的問題：

這裡的值表示您主要執行 InnoDB。

key_buffer_size = 50M
innodb_buffer_pool_size = 4G

Innodb 刪除的行與插入的行驚人地接近——你在做什麼處理？

虛假的COMMITs？ (Com_insert + Com_update + Com_delete + Com_replace) < Com_commit

更改慢日誌參數。

每秒 20 條管理命令——這是怎麼回事？

細節和其他觀察：

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (512M - 1.2 * 1449 * 1024) / 10240M = 5.0%– key_buffer 中浪費的 RAM 百分比。– 減小 key_buffer_size。

( Key_blocks_used * 1024 / key_buffer_size ) = 1,449 * 1024 / 512M = 0.28%– 使用的 key_buffer 的百分比。高水位線。– 降低 key_buffer_size 以避免不必要的記憶體使用。

( innodb_buffer_pool_size / _ram ) = 1250M / 10240M = 12.2%– 用於 InnoDB buffer_pool 的 RAM 百分比

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (512M / 0.20 + 1250M / 0.70) / 10240M = 42.4%– 大部分可用的 ram 應可用於記憶體。– http://mysql.rjweb.org/doc.php/memory

( innodb_buffer_pool_size ) = 1250M– InnoDB 數據 + 索引記憶體 – 128M（舊的預設值）小得可憐。

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 21,062 / 79999 = 26.3%– buffer_pool 的 pct 目前未使用 – innodb_buffer_pool_size 比需要的大嗎？

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 855,522 / 3597243 = 23.8%– 寫入必須命中磁碟的請求 – 檢查 innodb_buffer_pool_size

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 252,449,792 / (107581 / 3600) / 2 / 156M = 0.0258– 比率 – (見分鐘)

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

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( Innodb_rows_deleted / Innodb_rows_inserted ) = 50,443 / 50359 = 1——流失——“不要排隊，就去做。” （如果 MySQL 被用作隊列。）

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

( max_connections ) = 2,500– 最大連接數（執行緒）。影響各種分配。– 如果 max_connections 太高並且各種記憶體設置很高，您可能會用完 RAM。

( innodb_buffer_pool_populate ) = OFF = 0– NUMA 控制

( query_alloc_block_size / _ram ) = 16,384 / 10240M = 0.00%– 用於解析。佔記憶體的百分比

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

( Created_tmp_disk_tables / Created_tmp_tables ) = 1,400 / 3347 = 41.8%– 溢出到磁碟的臨時表的百分比 – 可能增加 tmp_table_size 和 max_heap_table_size；改進指標；避免斑點等

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

( binlog_format ) = binlog_format = STATEMENT– 聲明/行/混合。ROW 是首選；它可能成為預設值。

( expire_logs_days ) = 0– 多久自動清除 binlog（經過這麼多天） – 太大（或為零）= 消耗磁碟空間；太小 = 需要快速響應網路/機器崩潰。（如果 log_bin = OFF，則不相關）

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

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

( Connections ) = 922,332 / 107581 = 8.6 /sec– Connections – 增加wait_timeout；使用池化？

( thread_cache_size ) = 256– 要保留多少額外程序（使用執行緒池時不相關）（自 5.6.8 起自動調整大小；基於 max_connections） 0 對於非 Windows 效率低；10應該沒問題。超過 100 可能會導致 OOM。

異常小：

Handler_read_next / Handler_read_key = 0.557
Handler_read_rnd_deleted = 0
Handler_tmp_write = 6 /sec
Innodb_buffer_pool_pages_made_young = 0.57 /HR
Innodb_secondary_index_triggered_cluster_reads = 10.2MB
Rows_tmp_read = 0.8M
interactive_timeout = 300
max_heap_table_size = 1MB
max_tmp_tables * tmp_table_size / _ram = 0.31%
min(max_heap_table_size, tmp_table_size) = 1MB

異常大：

Acl_column_grants = 27
Acl_function_grants = 2
Acl_procedure_grants = 5
Acl_table_grants = 37
Com_admin_commands = 20 /sec
Com_drop_db = 0.1 /HR
Com_show_binlogs = 12 /HR
Com_show_slave_hosts = 0.033 /HR
Handler_discover = 1.7 /HR
Innodb_read_views_memory = 23,864
Max_used_connections = 691
Slave_connections = 2
Slaves_connected = 0.033 /HR
Threads_cached = 150
back_log = 550
host_cache_size = 728
innodb_defragment_fill_factor = 0.9
innodb_lru_scan_depth / innodb_io_capacity = 5.12
max_relay_log_size = 1024MB

異常字元串：

Slave_heartbeat_period = 1800
innodb_fast_shutdown = 1
myisam_stats_method = NULLS_UNEQUAL
opt_s__engine_condition_pushdown = off

為您的 my.cnf 考慮的建議

$$ mysqld $$每秒速率 = RPS

# 08/18/2018 Suggestions by mysqlservertuning com
log_error=scaup-error.log  # you REALLY do NOT want to be flying BLIND.
max_connections=1000  # from 2500 since ~700 max_used_connections
max_heap_table_size=2M  # from 1M for additional RAM based results
tmp_table_size=2M  # from 1M 2 be = max_heap_table_size & reduce created_tmp_disk_tables
read_rnd_buffer_size=192K  # from 256K to reduce handler_read_rnd_next RPS
aria_pagecache_buffer_size=10M  # from 128M since 99% unused
aria_pagecache_division_limit=50  # for WARM cache & reduce aria_pagecache_reads RPS
key_buffer_size=20M  # from ~512M since 99% unused
key_cache_division_limit=50  # from 100 for WARM cache & reduce key_reads count
expire_logs_days=10  # from 0 for some historical logging
innodb_buffer_pool_size=4G  # from ~1.2G for INNODB data, ndx in RAM & GROWTH
thread_cache_size=100  # from 256 per refman CAP at 100 to avoid OOM

您在這一天有 165 次回滾，如果可能的話，由於回滾的成本，需要對其進行研究和糾正。

請記住每個工作日只更改 1 次，監控，如果更改似乎是有害的，請從 my.cnf 中刪除並讓我知道，拜託。

此外，my.cnf 中只有 1 行具有完全相同的 VARIABLE_NAME 以避免混淆。將使用 my.cnf 中最後一個相同的 VARIABLE_NAME。

有關其他建議，請查看我的個人資料，網路個人資料以獲取聯繫資訊，包括我的 Skype ID。

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