🥇Korištenje particioniranja u MySQL za Zabbix s velikim brojem objekata praćenja

Za praćenje servera i usluga već dugo koristimo kombinirano rješenje bazirano na Nagiosu i Muninu, i to s uspjehom. No, ova kombinacija ima određene nedostatke, stoga mi, poput mnogih, aktivno koristimo Zabbix,. U ovom članku ćemo objasniti kako minimalnim naporima možete riješiti problem s performansama prilikom povećanja broja prikupljenih metrike i rasta volumena MySQL baze podataka.

Problemi korištenja MySQL baze podataka zajedno sa Zabbixom

Dok je baza podataka bila mala i broj metrika koje su se pohranjivale bio je mali, sve je bilo savršeno. Standardni proces housekeeper, koji pokreće sam Zabbix Server, uspješno je brisao zastarjele zapise iz baze podataka, sprječavajući njezin rast. Međutim, čim je broj prikupljenih metrika porastao i volumen baze podataka dosegla određenu veličinu, situacija se pogoršala. Housekeeper više nije stizao brisati podatke unutar zadanog vremenskog intervala, a u bazi podataka su ostajali stari podaci. Tijekom rada housekeepera dolazilo je do povećanog opterećenja na Zabbix Serveru, koje je moglo trajati dugo vremena. Postalo je jasno da se situacija mora riješiti.

Ovo je poznat problem, gotovo svaki tko je radio s velikim volumenima monitoringa na Zabbixu susreo se s istim. Rješenja su također bila nekoliko: na primjer, zamjena MySQL-a s PostgreSQL-om ili čak Elasticsearch-om, no najjednostavnije i već isprobano rješenje bilo je prebacivanje na particioniranje tablica koje pohranjuju podatke metrika u MySQL bazi podataka. Odlučili smo ići upravo tim putem.

Prijelaz s običnih MySQL tablica na particionirane

Zabbix je dobro dokumentiran i tablice u kojima pohranjuje metrike su poznate. To su tablice: history, gdje se pohranjuju float vrijednosti, history_str, gdje se pohranjuju kratke string vrijednosti, history_text, gdje se pohranjuju dugački tekstualni podaci i history_uint, gdje se pohranjuju cijele brojčane vrijednosti. Tu je još i tablica trends, koja pohranjuje dinamiku promjena, no odlučili smo da je ne diramo jer je njezina veličina mala, a malo kasnije ćemo se opet vratiti na nju.

U osnovi, bilo je jasno koje tablice treba obraditi. Odlučili smo napraviti particije za svaki tjedan, osim za posljednji, na temelju brojeva u mjesecu, tj. četiri particije po mjesecu: od 1. do 7., od 8. do 14., od 15. do 21. i od 22. do 1. (sljedećeg mjeseca). Teškoća je bila u tome što smo morali potrebne tablice pretvoriti u particionirane 'u hodu', ne prekidajući rad Zabbix poslužitelja i prikupljanje metrike.

Kako god čudno zvučalo, u tome nam je pomogla sama struktura podataka tablica. Na primjer, tablica history ima sljedeću strukturu:

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

pri tome

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

Kao što vidimo, svaka metrike na kraju se unosi u tablicu s dva vrlo važna i korisna polja itemid i clock. Na taj način, možemo stvoriti privremenu tablicu, na primjer, imenom history_tmp, postaviti za nju particioniranje i zatim prenijeti sve podatke iz tablice history, a zatim preimenovati tablicu history u history_old, a tablicu history_tmp u history, nakon čega ćemo dodati one podatke koje nismo prenijeli iz history_old u history i ukloniti history_old. To se može raditi potpuno sigurno, nećemo ništa izgubiti, jer navedena polja itemid i clock osiguravaju vezanje konkretne metrike za određeno vrijeme, a ne za neki redoslijedni broj.

Postupak prijelaza

Pažnja! Vrlo je poželjno, prije početka bilo kakvih radnji, napraviti potpunu sigurnosnu kopiju iz baze podataka. Svi smo mi živi ljudi i možemo napraviti grešku u unosu komandnog niza, što može dovesti do gubitka podataka. Da, sigurnosna kopija neće osigurati maksimalnu ažurnost, ali bolje imati takvu nego nikakvu.

Dakle, ništa ne isključujemo i ne zaustavljamo. Važno je da na samom MySQL poslužitelju postoji dovoljno slobodnog mjesta na disku, tj. da za svaku od gore navedenih tablica history, history_text, history_str, history_uint, minimalno, bude dovoljno mjesta za stvaranje tablice s sufiksom '_tmp', imajući na umu da će imati istu zapreminu kao i izvornja tablica.

Nećemo opisivati sve nekoliko puta za svaku od gore navedenih tablica i razmotrit ćemo sve na primjeru samo jedne od njih — tablice history.

Dakle, stvaramo praznu tablicu history_tmp na temelju strukture tablice history.

CREATE TABLE `history_tmp` LIKE `history`;

Stvaramo potrebne particije. Kao primjer, učinit ćemo to na mjesec. Svaka particija se stvara na osnovu pravila particioniranja temeljenog na vrijednosti polja clock, koje uspoređujemo s vremenskom oznakom:

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

Ova naredba dodaje particioniranje za našu stvorenu tablicu history_tmp. Da pojasnimo, podaci čija vrijednost polja clock manja je od '2019-02-01 00:00:00' doći će u particiju p20190201, zatim podaci čija vrijednost polja clock veća je od '2019-02-01 00:00:00' ali manja od '2019-02-07 00:00:00' doći će u particiju p20190207 i tako dalje.

Važna napomena: Što će se dogoditi ako u našoj particioniranoj tablici budu podaci čija vrijednost polja clock bude veća ili jednaka '2019-03-01 00:00:00'? Budući da za te podatke ne postoji odgovarajuća particija, oni neće biti pohranjeni u tablicu i bit će izgubljeni. Stoga, morate se sjetiti pravovremeno stvarati dodatne particije kako biste izbjegli gubitak podataka (o čemu govori dalje).

Dakle, privremena tablica je pripremljena. Učitavamo podatke. Proces može trajati prilično dugo, ali srećom ne blokira druge upite, tako da samo trebate imati strpljenja:

INSERT IGNORE INTO `history_tmp` SELECT * FROM history;

Ključna riječ IGNORE prilikom inicijalnog učitavanja nije obavezna, budući da u tablici ionako nema podataka, no bit će vam potrebna prilikom dodatnog učitavanja podataka. Osim toga, može biti korisna ako ste morali prekinuti ovaj proces i započeti ispočetka.

Dakle, nakon nekog vremena (možda čak nekoliko sati), prvo učitavanje podataka je prošlo. Kao što razumijete, sada tablica history_tmp sadrži ne sve podatke iz tablice history, već samo one koji su bili u njoj u trenutku kada je započeto izvršenje upita. Ovdje zapravo imate izbor: ili napravimo još jedan prolaz (ako je proces učitavanja trajao dugo), ili odmah prelazimo na preimenovanje tablica, o čemu je bilo riječi ranije. Prvo razgovarajmo o drugom prolazu. Za početak trebamo saznati vrijeme posljednje unesene stavke u history_tmp:

SELECT max(clock) FROM history_tmp;

Pretpostavimo da ste dobili: 1551045645. Sada koristimo dobivenu vrijednost u drugom prolazu unošenja podataka:

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

Ovaj prolaz trebao bi završiti znatno brže. No, ako je prvi prolaz trajao satima, a drugi također dugo, možda bi bilo ispravno napraviti i treći prolaz, koji se izvršava potpuno analogno drugome.

Na kraju ponovno izvršavamo operaciju dobivanja vremena posljednjeg umetanja zapisa u history_tmp, izvršivši:

SELECT max(clock) FROM history_tmp;

Pretpostavimo da ste dobili 1551085645. Spremite ovu vrijednost — trebat će nam za dodatno unošenje.

A sada zapravo, kada je primarno unošenje podataka u history_tmp završeno, prelazimo na preimenovanje tablica:

POČNI;
PREIMENUJ TABLICU history TO history_old;
PREIMENUJ TABLICU history_tmp TO history;
KRAJ;

Ovaj blok smo oblikovali kao jednu transakciju kako bismo izbjegli trenutak umetanja podataka u nepostojeću tablicu, jer nakon prvog PREIMENUJ do trenutka izvršenja drugog PREIMENUJ, tablica history neće postojati. Ali čak i ako tijekom operacija PREIMENUJ u tablicu history dođu neki podaci, a same tablice još neće biti (zbog preimenovanja), dobit ćemo malo pogrešaka umetanja na koje se može zanemariti (mi imamo nadzor, a ne banku).

Sada imamo novu tablicu history s particioniranjem, ali nedostaju joj podaci koji su dobiveni tijekom posljednjeg prolaza umetanja podataka u tablicu history_tmp. No, te podatke imamo u tablici history_old i sada ćemo ih dodati odatle. Za to nam je potrebna ranije spremljena vrijednost 1551085645. Zašto smo spremili tu vrijednost, a ne koristili maksimalno vrijeme umetanja već iz trenutne tablice history? Потому что новые данные уже в неё поступают и мы получим неверное время. Итак, дозаливаем данные:

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

Nakon završetka ove operacije, imamo u novoj, particioniranoj tablici history sve podatke koji su bili u staroj, plus one koji su već došli nakon preimenovanja tablice. Tablica history_old više nam nije potrebna. Možemo je odmah obrisati, ili možemo napraviti sigurnosnu kopiju prije brisanja (ako ste pomalo paranoidni).

Cijeli gore opisan proces potrebno je ponoviti za tablice history_str, history_text i history_uint.

Što treba ispraviti u postavkama Zabbix Server

Sada je održavanje baze podataka u vezi s poviješću podataka prebačeno na nas. To znači da Zabbix više ne bi trebao brisati stare podatke — time ćemo se baviti sami. Da Zabbix Server ne pokuša sam očistiti podatke, morate ući u web sučelje Zabbixa, odabrati u izborniku 'Administracija', zatim podizbornik 'Opće', a zatim u desnom padajućem izborniku odabrati 'Čišćenje povijesti'. Na pojavnoj stranici potrebno je ukloniti sve oznake za grupu 'Povijest' i pritisnuti gumb 'Ažurirati'. Ovo će spriječiti nepotrebno čišćenje tablica. povijest* putem housekeepera.

Obratite pažnju na istoj stranici na grupu 'Dinamika promjena'. Ovo je upravo tablica trends, kojoj smo se obavezali vratiti. Ako je i ona postala prevelika i treba joj particioniranje, uklonite oznake i u ovoj grupi, a zatim obradite tu tablicu na sličan način kao što se radilo za tablice povijest*.

Daljnje održavanje baze podataka

Kao što je ranije napisano, za normalan rad na particioniranim tablicama, potrebno je pravovremeno kreirati particije. To se može napraviti ovako:

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

Osim toga, budući da smo stvorili particionirane tablice i zabranili Zabbix Serveru da ih čisti, uklanjanje starih podataka sada je naša briga. Srećom, ovdje nema nikakvih problema. To se jednostavno radi brisanjem one particije čiji nam podaci više nisu potrebni.

Na primjer:

ALTER TABLE history DROP PARTITION p20190201;

Za razliku od naredbi DELETE FROM s navedenim rasponom datuma, DROP PARTITION se izvršava za nekoliko sekundi, potpuno ne opterećuje server i radi jednako besprijekorno u slučaju korištenja MySQL replikacije.