為 zabbix 調整大型 mysql 數據庫的性能
我一直在為 zabbix 調整 mysql 數據庫已經有一段時間了。我們實際上並沒有 DBA 來協助解決這個問題。
我們有一個每秒執行 16k 個值的 Zabbix 伺服器。我們的數據庫是 MariaDB 5.5.38，執行 galera 複製，只有兩個節點。我很樂意提供幫助調整所需的任何資訊。
我們看到的問題是我們的 zabbix 圖表被延遲並且我們的 dbsyncer 被最大化。根據 Zabbix 性能調整手冊和 zabbix 支持人員，我們的數據庫無法跟上發送的數據量。
我們在伺服器上有 512GB 的 RAM，在帶有 SSD 的 RAID 10 中，以及 2.59GHz 的 32 個核心。
mariadb、zabbix 和 apache 服務都在這台伺服器上執行。
我能做些什麼來提高這台伺服器的性能。基於 iostat、vmstat 和其他作業系統性能檢查工具，作業系統似乎沒有被過度使用。
提前致謝。
“每秒執行 16k 值”——您的意思是“每秒插入的行數”嗎？
SELECTs每秒？還有什麼？MySQL 最重要的設置是
innodb_buffer_pool_size，您的設置可能應該是 300G。innodb_buffer_pool_instances=16.
innodb_flush_log_at_trx_commit預設為 1。1更安全；2 導致更少的 I/O。如果您
INSERTs每秒執行 16K，則有幾種技術可以減少 I/O（您暗示這“太高了”）。批處理（在單個 中執行多行INSERT），將它們放在單個事務中，使用LOAD DATA或“暫存”。請多描述一些事情。變數/狀態分析
有一些有趣的事情。以下是重點：
( innodb_max_dirty_pages_pct ) = 30– 當 buffer_pool 開始刷新到磁碟時 – 為什麼這麼低？（可能沒關係）
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((23406029 + 990332255) ) / 769924 = 1316 /sec– InnoDB I/O – 增加 innodb_buffer_pool_size？增加innodb_max_dirty_pages_pct？
( Created_tmp_tables ) = 300,386,596 / 769924 = 390 /sec– 創建“臨時”表作為複雜 SELECT 的一部分的頻率。
( Created_tmp_disk_tables ) = 16,281,268 / 769924 = 21 /sec– 創建磁碟“臨時”表作為複雜 SELECT 的一部分的頻率 – 增加 tmp_table_size 和 max_heap_table_size。檢查何時使用 MEMORY 而不是 MyISAM 的臨時表規則。也許較小的模式或查詢更改可以避免 MyISAM。更好的索引和查詢的重新制定更有可能有所幫助。
( Select_scan ) = 22,715,682 / 769924 = 30 /sec– 全表掃描 – 添加索引/優化查詢（除非它們是小表）
( Sort_merge_passes ) = 412,662 / 769924 = 0.54 /sec– 大量排序 – 增加 sort_buffer_size 和/或優化複雜查詢。
( long_query_time ) = 10.000000 = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 1（並打開慢日誌） – 將有助於定位頑皮的查詢。
Threads_running = 45– 可能表明查詢相互之間存在衝突
Innodb_buffer_pool_pages_flushed / Questions = 0.49– 也許表明 buffer_pool 不是一個有效的記憶體。
Select_range / Com_select = 22%——高，但不一定是壞的。(Select_range = 360/sec)
Opened_files = 86/sec. 我擔心table_open_cache，但沒有指標表明它是否“足夠大”。您可以將寫入分組到更大的事務中嗎？有很多 I/O；這會減少它。
Com_begin目前Com_commit為 257/秒。很多寫入都是
UPDATEs. 請詳細說明他們在做什麼。如果它們是遞增計數器，那麼以某種方式收集它們可能是值得的。
innodb_io_capacity = 6000– 我不知道這是否最適合您的系統。
Innodb_x_lock_spin_waits = 5500/sec- 一個巨大的數字，但我不知道該怎麼做。
large_page_size——我沒有經歷過；你怎麼樣了？
slave_skip_errors = 1062- 也許你需要使用INSERT IGNOREorINSERT .. ON DUPLICATE KEY UPDATE..？慢日誌
許多查詢都有
from items t,hosts r where t.hostid=r.hostid and r.proxy_hostid=13242 and r.status in (0,1) and t.type in (0,7,1,4,6,12,2,3,9,10,11,13,14,16,17,5) order by t.itemid這需要這些複合索引：
items: INDEX(hostid, type) -- in this order hosts: INDEX(proxy_host_id, status, hostid) -- this order; helps WHERE; 'covering'我也扔掉 KEY hosts_3，因為它是多餘的。
我看到了冗餘索引的情況，例如
KEY `c_items_4` (`interfaceid`), KEY `items_6` (`interfaceid`),推薦
DROPping其中之一。其他許多表都存在問題，但我不會討論它們；Zabbix 應該修復它們。
更多的
UPDATE item_discovery ... WHERE itemid BETWEEN ... AND ... OR itemId IN (...);這可能是非常低效的，因為
OR. 最好的解決方案（對於 MySQL；不了解 Zabbix）是將它分成兩部分UPDATEs：UPDATE item_discovery ... WHERE itemid BETWEEN ... AND ...; UPDATE item_discovery ... WHERE itemId IN (...);更多 2
將 1000(?) 行插入到表的一個分區中不應花費 6-10 秒。如果您一次將這些插入限制為 100 行，可能會有所幫助。
更多 3
如上所述，加快查詢速度將有所幫助。但我現在看到了一個不同的問題。那個 30 分鐘的慢日誌片段？那是某種通常不會發生的尖峰？當你到達時這是什麼
Max_used_connections = 523？在這樣的情況下會發生什麼：523 個執行緒，都獲得了 32 個核心的一小部分，等等，互相絆倒。我看到一些查詢需要超過 3 秒，但它們不應該超過大約 3毫秒。在這種情況下，我建議弄清楚如何限制上游的連接數，即在 Zabbix 客戶端中。同時，你可以減少
max_connections到，比如說，200。這很可能會絆倒客戶；但這可能比似乎什麼都沒有完成的很長一段時間要好。此外，5.5 已經過時了；開始製定升級計劃。
討厭的刪除
@12:37（也許每 xx:37）有一個 123 秒的查詢完成：
DELETE FROM events WHERE source = 0 AND object = 0 AND clock < ...;雖然這是此類的最佳指標
KEY `events_2` (`source`,`object`,`clock`)顯然還不夠好。123 秒的磁碟密集型刪除，包括建構回滾內容，可以減慢其他事情——我認為這就是發生的事情。
治愈？
計劃 A：與其
DELETE每小時做一次，不如說每 5 分鐘做一次（每次都有不同的clock截止時間）。這應該將其加速到 10 秒而不是 123 秒，可能更快。計劃 B：將其設為
PARTITIONed表格並使用DROP PARTITION，這在系統上的密集程度要低得多。（但這增加了很多複雜性，Zabbix 可能會阻礙。）計劃 C：“不斷地”刪除這些東西，使用另一個程序。可能
Loop: DELETE FROM events WHERE source = 0 AND object = 0 AND clock < ... -- update this cutoff as needed LIMIT 1000; sleep 1 second repeat計劃 D：請參閱我的大刪除部落格了解其他技術（以及計劃 A、B、C 的一些詳細資訊。）
繼續追求其他建議；您可能已接近臨界點——也就是說，如果數據集的大小翻倍，則沒有什麼能讓系統恢復活力。我的各種建議會延遲，但不會消除世界末日。