在MySQL for Zabbix中使用分區並具有大量監控對象

為了監控伺服器和服務，我們長期以來一直在使用基於 Nagios 和 Munin 的組合解決方案，並且仍然很成功。 然而，這種組合有許多缺點，因此我們像許多人一樣積極利用 ZABBIX。 在本文中，我們將討論如何以最小的努力解決在增加採用的指標數量和增加 MySQL 資料庫磁碟區時的效能問題

在 Zabbix 中使用 MySQL 資料庫的問題

雖然資料庫很小並且儲存在其中的指標數量也很少，但一切都很棒。 Zabbix Server 本身啟動的標準管家程序成功地從資料庫中刪除了過時的記錄，防止其成長。 然而，一旦採用的指標數量增加並且資料庫大小達到一定大小，情況就會變得更糟。 Houserkeeper 無法在分配的時間間隔內刪除數據，舊數據開始保留在資料庫中。 當管家運作時，Zabbix Server 上的負載會增加，並且可能會持續很長時間。 很明顯，我們需要以某種方式解決目前的情況。

這是一個眾所周知的問題，幾乎每個在 Zabbix 上進行過大量監控的人都遇到過相同的事情。 還有幾種解決方案：例如，用 PostgreSQL 甚至 Elasticsearch 取代 MySQL，但最簡單且最經過驗證的解決方案是過渡到在 MySQL 資料庫中儲存指標資料的分區表。 我們決定就這樣走。

從常規 MySQL 表過渡到分區表

Zabbix 有詳細的文件記錄，並且它儲存指標的表是已知的。 這些是表格： history，儲存浮點值的位置， history_str，儲存短字串值的位置， history_text，其中存儲長文本值並且 history_uint，其中儲存整數值。 還有一張桌子 trends，它儲存變化的動態，但我們決定不碰它，因為它的大小很小，我們稍後會再回到它。

一般來說，哪些表需要處理是很清楚的。 我們決定根據月份的數字對每週進行分區，最後一周除外，即每月四批：1日至7日、8日至14日、15日至21日、22日至1日（下個月）。 困難在於我們需要「即時」將所需的表轉換為分區表，而不中斷 Zabbix Server 的操作和指標的收集。

奇怪的是，表本身的資料結構在這方面為我們提供了幫助。 例如表 history 具有以下結構：

`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int(11) NOT NULL DEFAULT '0',

其中

KEY `history_1` (`itemid`,`clock`)

正如您所看到的，每個指標最終都會輸入到一個表中，其中有兩個對我們來說非常重要且方便的字段 特美德 и 時鐘。 因此，我們可以輕鬆建立一個臨時表，例如名稱為 history_tmp，為其設定分區，然後將表中的所有資料傳輸到那裡 history然後重命名該表 history в history_old，和表 history_tmp в history，然後加入我們沒有填寫的數據 history_old в history 並刪除 history_old。 這可以完全安全地完成，我們不會丟失任何東西，因為上述字段 特美德 и 時鐘 提供將特定指標與特定時間而不是某個序號的綁定。

過渡程序本身

注意力！ 強烈建議在開始任何操作之前製作資料庫的完整備份副本。 我們都是活生生的人，可能會在命令集中犯錯，導致資料遺失。 是的。 備份並不能確保最大程度地保持最新狀態，但有總比沒有好。

所以，我們不會關閉任何東西或停止任何東西。 最主要的是MySQL伺服器本身有足夠的可用磁碟空間，即這樣對於上面列出的每個表 history, history_text, history_str, history_uint，至少有足夠的空間來創建後綴為“_tmp”的表，因為它與原始表的大小相同。

我們不會對上述每個表的所有內容進行多次描述，而是僅使用其中一個表的範例來考慮所有內容 - 該表 history.

那麼，讓我們建立一個空表 history_tmp 基於表結構 history.

CREATE TABLE `history_tmp` LIKE `history`;

我們創建我們需要的分區。 例如，讓我們這樣做一個月。 每個分區都是根據字段值的分區規則創建的 時鐘，我們將其與時間戳進行比較：

ALTER TABLE `history_tmp` PARTITION BY RANGE( clock ) (
PARTITION p20190201 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-01 00:00:00")),
PARTITION p20190207 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-07 00:00:00")),
PARTITION p20190214 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-14 00:00:00")),
PARTITION p20190221 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-21 00:00:00")),
PARTITION p20190301 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-01 00:00:00"))
);

該運算符為我們創建的表添加分區 history_tmp。 讓我們澄清一下該資料的欄位值 時鐘 小於「2019-02-01 00:00:00」的將包含在批次中 p20190201，則其欄位值的數據 時鐘 大於“2019-02-01 00:00:00”但小於“2019-02-07 00:00:00”的將包含在分區中 p20190207 等。

重要的提示： 如果分區表中的資料的時脈欄位值大於或等於“2019-03-01 00:00:00”，會發生什麼情況？ 由於該資料沒有合適的分區，因此它不會出現在表中並且會遺失。 因此，您需要記住及時建立額外的分割區，以避免此類資料遺失（如下所述）。

這樣，臨時表就準備好了。 填寫數據。 這個過程可能需要相當長的時間，但幸運的是它不會阻止任何其他請求，所以你只需要耐心等待：

INSERT IGNORE INTO `history_tmp` SELECT * FROM history;

在初始填充期間不需要 IGNORE 關鍵字，因為表中無論如何都沒有數據，但在添加數據時需要它。 此外，如果在上傳資料時必須中斷此過程並重新開始，這可能會很有用。

因此，過了一段時間（甚至可能是幾個小時），第一次資料上傳發生了。 如你所知，現在表格 history_tmp 不包含表中的所有數據 history，但僅限於請求開始時其中包含的內容。 在這裡，您實際上有一個選擇：要么我們再執行一次（如果填充過程需要很長時間），要么我們立即繼續重命名表，正如上面提到的。 我們先說第二遍。 首先我們需要了解最後插入記錄的時間 history_tmp:

SELECT max(clock) FROM history_tmp;

假設您收到： 1551045645。 現在我們在第二遍資料填充中使用結果值：

INSERT IGNORE INTO `history_tmp` SELECT * FROM history WHERE clock>=1551045645;

這段話應該結束得更快。 但是，如果第一遍需要幾個小時才能完成，而第二遍也花了很長時間，那麼進行第三遍可能是正確的，第三遍的執行方式與第二遍完全相同。

最後，我們再次執行取得最後一次插入記錄的時間的操作 history_tmp透過運行：

SELECT max(clock) FROM history_tmp;

假設您收到了 1551085645。 保存該值 - 我們將需要它來重新填充。

現在，實際上，當最初將資料填入 history_tmp 完成後，讓我們開始重命名表：

BEGIN;
RENAME TABLE history TO history_old;
RENAME TABLE history_tmp TO history;
COMMIT;

我們將此區塊設計為一個事務，以避免將資料插入到不存在的表中，因為在第一次 RENAME 執行後直到執行第二次 RENAME 期間，該表 history 將不存在。 但即使在對錶進行 RENAME 操作之間 history 一些資料將到達，但表本身尚不存在（由於重命名），我們將收到少量可以忽略的插入錯誤（我們有監控，而不是銀行）。

現在我們有一張新桌子 history 具有分區，但缺少最後一次向表中插入資料期間接收到的數據 history_tmp。 但是我們表格中有這個數據 history_old 現在我們將從那裡為它們充值。 為此，我們需要之前保存的值 1551085645。為什麼我們保存這個值而不使用當前表的最大填充時間 history？ 因為新資料已經進入其中，我們會得到錯誤的時間。 那麼，讓我們來添加數據：

INSERT IGNORE INTO `history` SELECT * FROM history_old WHERE clock>=1551045645;

這個操作完成後，在我們新建的分區表中 history 其中包含舊表中的所有數據，以及重命名表後已經到達的數據。 桌子 history_old 我們不再需要它了。 您可以立即刪除它，也可以在刪除之前對其進行備份（如果您偏執的話）。

對於表格需要重複以上整個過程 history_str, history_text и history_uint.

Zabbix Server 設定中需要修正的內容

現在，資料歷史方面的資料庫維護落在了我們的肩上。 這意味著 Zabbix 不再需要刪除舊資料——我們將自己做這件事。 為了防止Zabbix Server嘗試自行清除數據，您需要進入Zabbix Web介面，在選單中選擇“管理”，然後選擇“常規”子選單，然後在下拉清單中選擇“清除歷史記錄”正確的。 在出現的頁面上，您需要取消選取「歷史記錄」群組的所有複選框，然後按一下「更新」按鈕。 這將防止我們不必要地清理表格 history* 透過管家。

在同一頁上，請注意「變化動態」群組。 這只是一張桌子 trends，我們承諾會回來。 如果它也變得太大並且需要分區，請取消選中該組中的框，然後以與處理表相同的方式處理該表 history*.

進一步的資料庫維護

如前面所寫，為了對分割表進行正常操作，需要按時建立分割區。 你可以這樣做：

ALTER TABLE `history` ADD PARTITION (PARTITION p20190307 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-07 00:00:00")));

此外，由於我們建立了分割表並禁止 Zabbix Server 清理它們，因此刪除舊資料現在是我們關心的問題。 幸運的是，這裡完全沒有問題。 只需刪除我們不再需要的資料的分割區即可完成此操作。

例如：

ALTER TABLE history DROP PARTITION p20190201;

與指定日期範圍的 DELETE FROM 語句不同，DROP PARTITION 在幾秒鐘內執行，根本不載入伺服器，並且在 MySQL 中使用複製時工作同樣順利。

結論

所描述的解決方案已經過時間的考驗。 數據量不斷增長，但效能並沒有明顯下降。

來源： www.habr.com