管理 mariaDB 中的數十億行感測器數據

我是一家管理行業生態足跡減少的小公司的新“IT 人員”，出於契約原因，他們需要將每個感測器的每個數據更改（有些每秒鐘更改一次，有些每分鐘更改一次）保存 5 年年。他們正在對這些數據進行一些計算，以證明每年減少的生態足跡，並檢測安裝中的一些錯誤/不合邏輯的事情。

目前我們有一個項目正在進行中，另外兩個項目將在下週推出。

之前的 IT 人員在 VPS 上設置了 MariaDB 伺服器，結構如下：

CREATE TABLE `machine` (
 `Name` varchar(100) CHARACTER SET utf8 NOT NULL,
 `Site` int(11) DEFAULT NULL,
 `Emplacement` varchar(200) DEFAULT NULL,
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`Name`),
 UNIQUE KEY `ID` (`ID`),
 KEY `FK_machine_site` (`Site`),
 CONSTRAINT `FK_machine_site` FOREIGN KEY (`Site`) REFERENCES `site` (`ID`)
) ENGINE=InnoDB;

CREATE TABLE `mesure` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `Machine` varchar(100) NOT NULL,
 `Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `Valeur` decimal(18,5) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `machine_timestamp` (`Machine`,`Date`) USING BTREE,
 KEY `Date` (`Date`),
 CONSTRAINT `FK_valeur_machine` FOREIGN KEY (`machine`) REFERENCES `machine` (`Name`)
) ENGINE=InnoDB;

CREATE TABLE `site` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `Nom` varchar(50) NOT NULL,
 `Ville` varchar(100) DEFAULT NULL,
 `Code_Postal` varchar(100) DEFAULT NULL,
 `Rue` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB;

在大約 7 個月的時間裡，我們在“mesure”中獲得了大約 6 億行，在“machine”中獲得了 80 行，在“site”表中只有一個，數據庫大約 40GB 目前執行良好，可以訪問數據現在是每月數據提取的好時機（我做了一個腳本，每週恢復並生成一個 excel，所有計算都在每秒內完成）。

我們決定暫時在每個項目的新 VM 上設置一個新伺服器。

總結上下文

• 每個項目一個數據庫和一個伺服器，目前 MariaDB
• 每個項目大約 100 個感測器，可變更新，目前項目 7 個月內大約 6 億條記錄，40GB
• 數據無法縮減、標準化，我們需要將這些數據保留 5 年
• 與數據庫的低互動，僅在應用程序讀取感測器數據的情況下寫入內部。並每月提取一次以恢復每月數據。
• IT 預算有限，無法負擔大型伺服器。我正在開發 4 核、8GB RAM、SSD VPS，僅用於數據恢復應用程序（資源使用率低）和數據庫

我的問題

• 看來我們要快速達到測量 ID PK 的最大 int 值，我需要將它設置為 bigint 嗎？正在考慮將 PK 更改為 (Machine,Date) 夫婦這是個好主意嗎？
• 未來我們會面臨這種做法的限制嗎？
• 留在 MariaDB 上是個好主意，還是我需要查看其他數據庫？正在尋找 TimeScaleDB，對此有何評論、正面/負面評論？
• 例如，我可以做哪些優化來減小數據庫的大小？正在考慮在具有大硬碟驅動器的獨特低成本伺服器中為每個項目每年“存檔”壓縮數據庫轉儲（這些轉儲將在本地和雲驅動器上備份），以減少我們在 mariaDB 伺服器上進行的備份的大小，並且僅如果在極少數情況下需要檢索數據，則在本地數據庫上恢復轉儲。對此有何評論？

由於您可能在這個表中有十億行，因此我將重點關注它：

CREATE TABLE `mesure` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `Machine` varchar(100) NOT NULL,
 `Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `Valeur` decimal(18,5) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `machine_timestamp` (`Machine`,`Date`) USING BTREE,
 KEY `Date` (`Date`),
 CONSTRAINT `FK_valeur_machine` FOREIGN KEY (`machine`) REFERENCES `machine` (`Name`)
) ENGINE=InnoDB;

每行佔用大約 70 個字節（包括成本）；在 5 年內將其乘以數十億行，然後… 明白了嗎？我們需要縮小這個表。

decimal(18,5)——這似乎太大了。它需要 9 個字節。考慮使用FLOAT（不，不是 FLOAT(18,5)）。這需要 4 個字節並且有大約 7 個有效數字。這對於我所知道的任何感測器來說都綽綽有餘。

Machine varchar(100)- 將其縮小為連接到machine表格的“id”。您最終可能擁有多少台機器？大概在 25​​6 到 64K 之間？SMALLINT UNSIGNED在此處和表中都使用 2 字節machine。

為了允許不太可能的重複， doINSERT IGNORE而不是 simple INSERT。

你能在同一秒內獲得同一台機器的 2 個讀數嗎？可能不是。或者即使你這樣做了，你可以（應該？）扔掉多餘的閱讀？所以，我推薦 PRIMARY KEY(machine_id, date)（並消除UNIQUE具有相同列的現有）。

對 PK 的更改消除了ID您目前擁有的 4 個字節。 INT是一場將在不到一年的時間內發生的災難——當它達到大約 20 億的極限時。

修改後的架構

CREATE TABLE `measure` (
 `MachineId` SMALLINT UNSIGNED NOT NULL,
 `Date` datetime NOT NULL,
 `Valeur` FLOAT NOT NULL,
 PRIMARY KEY(`Machine`,`Date`),
 KEY `Date` (`Date`),
 CONSTRAINT `FK_valeur_machine`
       FOREIGN KEY (`machineId`) REFERENCES `machine` (`ID`)
) ENGINE=InnoDB;

這些更改可能會將表的磁碟佔用空間減少一半。您說查詢現在足夠快，但隨著時間的推移它們會變慢。這種模式將有助於保持它們“足夠快”。

匯總表

您可能需要“報告”或圖表或其他查詢，這些查詢對SELECT ... WHERE date between... GROUP BY machineId. 你會發現這比你喜歡的要慢。見http://mysql.rjweb.org/doc.php/summarytables；我們可以在另一個問題中討論。

如果處理得當，您可以考慮不將原始數據保存 5 年，而將匯總數據“永久”保存。摘要的大小應該是原來的十分之一，查詢速度應該是原來的 10 倍。（YMMV - 您的里程可能會有所不同。）

刪除舊數據

如果您只保留數據 5 年，您將如何刪除“舊”數據？你會發現一個大DELETE的效率非常低。現在提前計劃PARTITION BY RANGE(TO_DAYS(date))。有每月分區。更多：http: //mysql.rjweb.org/doc.php/partitionmaint。 DROP PARTITION快得多。

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