Mysql

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

  • June 24, 2019

我們的主要身份驗證數據庫用於 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