🥇Utilizzo del partizionamento in MySQL per Zabbix con un gran numero di oggetti di monitoraggio

Per il monitoraggio di server e servizi utilizziamo da tempo e con successo una soluzione combinata basata su Nagios e Munin. Tuttavia, questa combinazione presenta alcuni svantaggi, quindi, come molti, stiamo sfruttando attivamente Zabbix. In questo articolo parleremo di come risolvere il problema delle prestazioni con minimi sforzi, quando aumenta il numero delle metriche raccolte e cresce il volume del database MySQL

Problemi nell'uso del database MySQL insieme a Zabbix

Fino a quando il database era piccolo e il numero delle metriche memorizzate era ridotto, tutto funzionava alla perfezione. Il processo housekeeper, che viene avviato dal server Zabbix, eliminava con successo le registrazioni obsolete dal database, impedendone la crescita. Tuttavia, non appena il numero delle metriche raccolte è aumentato e il volume del database ha raggiunto una certa dimensione, la situazione è peggiorata. L'housekeeper non riusciva a eliminare i dati nel tempo previsto, lasciando nel database vecchi dati. Durante il funzionamento dell'housekeeper, si verificava un carico eccessivo sul server Zabbix, che poteva durare a lungo. È diventato chiaro che era necessario affrontare questa situazione.

Questo è un problema noto; praticamente chiunque abbia lavorato con grandi volumi di monitoraggio su Zabbix ha incontrato la stessa difficoltà. Anche le soluzioni erano diverse: ad esempio, sostituire MySQL con PostgreSQL o addirittura Elasticsearch, ma la soluzione più semplice e testata era quella di passare alla partizionatura delle tabelle che memorizzano i dati delle metriche nel database MySQL. Decidemmo di seguire proprio questa strada.

Transizione da tabelle MySQL standard a tabelle partizionate

Zabbix è ben documentato e le tabelle in cui memorizza le metriche sono conosciute. Queste tabelle sono: history, dove vengono memorizzati valori float, history_str, dove vengono memorizzati valori stringa brevi, history_text, dove vengono memorizzati valori di testo lunghi e history_uint, dove vengono memorizzati valori interi. C'è anche una tabella trends, che memorizza l'andamento delle variazioni, ma abbiamo deciso di non toccarla, poiché la sua dimensione è ridotta e più avanti ci torneremo.

In generale, era chiaro quali tabelle dovevano essere trattate. Decidemmo di creare partizioni settimanali, ad eccezione dell'ultima, basate sui numeri del mese, ossia quattro partizioni al mese: dal 1 al 7, dall'8 al 14, dal 15 al 21 e dal 22 al 1 del mese successivo. La difficoltà stava nel dover trasformare le tabelle necessarie in partizionate "al volo", senza interrompere il funzionamento del server Zabbix e la raccolta delle metriche.

Stranamente, la struttura dei dati delle tabelle ci è stata d'aiuto. Ad esempio, la tabella history ha la seguente struttura:

`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',

con

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

Come possiamo vedere, ogni metrica viene infatti inserita nella tabella con due campi molto importanti e utili per noi itemid e clock. In questo modo, possiamo tranquillamente creare una tabella temporanea, per esempio chiamata history_tmp, impostare la partizione per essa e poi trasferire tutti i dati dalla tabella history, e successivamente rinominare la tabella history in history_old, e la tabella history_tmp in history, dopodiché aggiungere nuovamente i dati che ci mancano da history_old in history e cancellare history_old. È possibile farlo in modo assolutamente sicuro; non perderemo nulla, poiché i campi sopra indicati itemid e clock garantiscono l'associazione di una specifica metrica a un preciso momento, e non a un qualche numero di ordine.

Procedura di transizione

Attenzione! È vivamente consigliato, prima di iniziare qualsiasi azione, fare un backup completo del database. Siamo tutti esseri umani e possiamo commettere errori nel set di comandi, il che potrebbe portare alla perdita di dati. Sì, un backup non garantirà la massima attualità, ma è meglio averne uno che niente.

Quindi, non spegniamo né arrestiamo nulla. L'importante è che sul server MySQL ci sia un sufficiente spazio libero su disco, cioè che per ciascuna delle tabelle elencate sopra history, history_text, history_str, history_uint, ci sia almeno spazio sufficiente per creare una tabella con il suffisso "_tmp", considerando che avrà lo stesso volume della tabella originale.

Non descriveremo tutto più volte per ciascuna delle tabelle menzionate e considereremo il tutto con un esempio solo di una di esse — la tabella history.

Quindi, creiamo una tabella vuota history_tmp basata sulla struttura della tabella history.

CREATE TABLE `history_tmp` LIKE `history`;

Creiamo le partizioni necessarie. Per esempio, facciamolo su base mensile. Ogni partizione viene creata in base a una regola di partizione, basata sul valore del campo clock, che confrontiamo con il timestamp:

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"))
);

Questo operatore aggiunge la partizione per la tabella che abbiamo creato. history_tmpChiariremo che i dati in cui il valore del campo clock è inferiore a «2019-02-01 00:00:00» andranno nella partizione p20190201e poi i dati in cui il valore del campo clock è superiore a «2019-02-01 00:00:00» ma inferiore a «2019-02-07 00:00:00» andranno nella partizione p20190207 e così via.

Nota importante: Cosa succede se nella nostra tabella partizionata compaiono dati in cui il valore del campo clock è maggiore o uguale a «2019-03-01 00:00:00»? Poiché non esiste una partizione adeguata per questi dati, non verranno inclusi nella tabella e andranno persi. Pertanto, è necessario ricordarsi di creare partizioni aggiuntive in tempo per evitare tali perdite di dati (di cui parleremo più avanti).

Ora la tabella temporanea è pronta. Carichiamo i dati. Il processo può richiedere molto tempo, ma per fortuna non blocca altre query, quindi basta avere pazienza:

INSERT IGNORE INTO `history_tmp` SELECT * FROM history;

La parola chiave IGNORE non è obbligatoria durante il caricamento iniziale, poiché non ci sono dati nella tabella, ma sarà necessaria per il caricamento successivo. Inoltre, può essere utile se il processo di caricamento è stato interrotto e deve essere ripreso da capo.

Quindi, dopo un po' di tempo (potrebbero anche essere diverse ore), il primo caricamento dei dati è concluso. Come puoi capire, ora la tabella history_tmp contiene solo alcuni dati dalla tabella history, e solo quelli che erano presenti al momento dell'esecuzione della query. Qui hai sostanzialmente due opzioni: puoi effettuare un ulteriore passaggio (se il processo di caricamento è durato a lungo) o procedere immediatamente con la rinominazione delle tabelle, come accennato prima. Parliamo prima del secondo passaggio. Per prima cosa, dobbiamo determinare l'orario dell'ultima registrazione inserita in history_tmp:

SELECT max(clock) FROM history_tmp;

Supponiamo tu abbia ottenuto: 1551045645. Ora utilizziamo il valore ottenuto per il secondo passaggio di caricamento dei dati:

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

Questo passaggio dovrebbe concludersi notevolmente più rapidamente. Ma se il primo passaggio ha richiesto ore e il secondo richiede ancora molto tempo, potrebbe essere corretto eseguire anche un terzo passaggio, eseguito esattamente come il secondo.

Alla fine, eseguiamo di nuovo l'operazione per ottenere l'orario dell'ultima registrazione inserita in history_tmp, eseguendo:

SELECT max(clock) FROM history_tmp;

Supponiamo tu abbia ottenuto 1551085645. Salva questo valore, ci servirà per il caricamento successivo.

E ora, dopo che il caricamento iniziale dei dati in history_tmp è terminato, procediamo alla rinominazione delle tabelle:

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

Abbiamo organizzato questo blocco come una singola transazione per evitare l'inserimento di dati in una tabella inesistente, poiché dopo il primo RENAME fino a quando non viene eseguito il secondo RENAME, la tabella history non esisterà. Ma anche se durante le operazioni di RENAME arrivano dei dati nella tabella, e la tabella non esisterà ancora (a causa della rinominazione), avremo solo un numero ridotto di errori di inserimento, che possono essere trascurati (stiamo monitorando, non facendo banca). history Ora abbiamo una nuova tabella

Ora abbiamo una nuova tabella history con la partizione, ma mancano i dati che sono stati acquisiti durante l'ultimo passaggio di inserimento dei dati nella tabella history_tmp. Ma questi dati li abbiamo nella tabella history_old e li caricheremo da lì. Per questo, avremo bisogno del valore precedentemente salvato, 1551085645. Perché abbiamo salvato questo valore invece di utilizzare il tempo massimo di caricamento già dalla tabella attuale? history? Потому что новые данные уже в неё поступают и мы получим неверное время. Итак, дозаливаем данные:

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

Dopo aver completato questa operazione, nella nuova tabella partizionata history abbiamo tutti i dati che erano nella precedente, più quelli che sono già arrivati dopo la rinominazione della tabella. La tabella history_old non è più necessaria. Puoi eliminarla subito o, se sei preoccupato, fare un backup prima di eliminarla.

L'intero processo descritto sopra deve essere ripetuto per le tabelle history_str, history_text e history_uint.

Cosa bisogna modificare nelle impostazioni del Zabbix Server

Ora la manutenzione del database riguardante la storicità dei dati ricade su di noi. Ciò significa che Zabbix non deve più eliminare dati obsoleti — ci occuperemo noi di questo. Affinché Zabbix Server non tenti di pulire i dati autonomamente, è necessario accedere all'interfaccia web di Zabbix, selezionare nel menu "Amministrazione", quindi il sottomenu "Generale", e infine nel menu a discesa a destra selezionare "Cancellazione della storia". Nella pagina risultante, deseleziona tutte le caselle per il gruppo "Storia" e fai clic sul pulsante "Aggiorna". Questo eviterà la pulizia non necessaria delle tabelle history* tramite il housekeeper.

Nota anche in questa pagina il gruppo "Dinamiche di cambiamento". Questa è proprio la tabella trends, a cui abbiamo promesso di tornare. Se è diventata troppo grande e ha bisogno di partizionamento, deseleziona le caselle in questo gruppo e poi gestisci questa tabella esattamente come hai fatto per le tabelle precedenti. history*.

Ulteriore manutenzione del database

Come già accennato, per un corretto funzionamento delle tabelle partizionate, è necessario creare partizioni in tempo. Questo può essere fatto nel seguente modo:

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

Inoltre, poiché abbiamo creato tabelle partizionate e vietato a Zabbix Server di pulirle, ora siamo noi a occuparci della rimozione dei dati obsoleti. Fortunatamente, qui non ci sono problemi. Si fa semplicemente eliminando la partizione i cui dati non ci servono più.

Ad esempio:

ALTER TABLE history DROP PARTITION p20190201;

A differenza delle istruzioni DELETE FROM con intervallo di date, il comando DROP PARTITION viene eseguito in pochi secondi e non genera alcun carico. un server Funziona altrettanto bene anche in caso di utilizzo della replica MySQL.