如何為特定的數據庫負載指定機器?
我們的主要身份驗證數據庫用於 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。