To, že provozuji jednu obzvlášť rozlezlou instanci Zabbixu, pravidelní čtenáři mého blogu asi již vědí. V současnosti je na ní navěšených přes 130 zařízení, většinou MikroTiků, ze kterých se po SNMP vehementně tahají nejrůznější data. Dohromady se hlídá cca 4300 hodnot, z nichž je dobrých 80% aktualizovaných po jedné minutě. To je docela hodně dat, takže je záhodno popřemýšlet, zda na ně předpřipravená udělátka dostačují.
Datový náfuka
„Hodně dat“ je v tomto případě nějakých 240 MB denně. Připočteme-li indexy, write-ahead logy a další zvířátka, objem zapisovaných dat se zdvojnásobí. Jasně, půl giga dat za den, to není nic světoborného, ale na malý podpultový servřík, jehož pořizovací cena odpovídá dvěma lahvím kořalky, už to není úplně nezanedbatelné. Problém však není samotný objem dat nebo rychlost jejich přibývání. Právě naopak. Problém tkví v druhé straně procesu uchování dat, tedy v jejich mazání.
Zabbix má vlastní housekeeper, který si jednou za čas projede databázi, najde staré záznamy a vygumuje je. Protože používám SNMPv3 a protože MikroTik se s nějakým hloubkovým testováním svých produktů moc nepáře (i když posledních několik verzí vypadá, že se konečně někdo probral), existuje v RouterOS bug, vinou kterého hodnoty snmpEngineBoots, resp. snmpEngineTime občas vracejí nesmyslné údaje a Zabbix pak takového SNMP agenta označí jako nedostupného. Jelikož Zabbix hromadu dat cachuje, místo aby klienta úplně zahodil, snaží se spojení znovu navázat s hodnotami z cache, což se mu samozřejmě nedaří. Takže si tak vesele monitorujete a najednou vám odpadne MikroTik. A pak další. A další a další, až po čtyřech nebo pěti dnech nemonitorujete vůbec nic. Všichni jsou mrtví, Dejve. Na dotaz skrze snmpget nebo jiný jednorázový tool RouterOS požadovaná data vrátí, ale Zabbix se s rozbitými agenty bavit odmítá. Problém se dá opravit jak na straně MikroTiku, tak na straně Zabbixu, kde je pro velice podobnou záležitost otevřen ticket ZBX-8385 a stejně tak by se dal vyřešit downgradem na SNMPv2 z mé strany, ale zatím ani k jednomu nedošlo. Zatím se problém jen odrbává tak, že se Zabbix jednou za 4 hodiny restartuje. Až při psaní tohoto článku a důkladné analýze všech spojení a dat jsem zjistil, jak idiotský nápad to je.
Vysavač a prachovka
Zabbixův housekeeper je napevno nastaven tak, že první úklid proběhne hodinu po startu, a pak v intervalech nastavených v konfiguraci. Takže hodinu po startu se housekeeper zvedne a jde se SELECTem do databáze podívat, jaká že data je možno odeslat na věčnost. Smlouva mezi mnou a mým zákazníkem stanovuje, že historická data jsou uchovávána po dobu 90 dní. Takže se zapnutým logováním pomalých databázových dotazů pak první úklid po restartu vypadá asi takto:
22415:20151029:132904.504 executing housekeeper
22415:20151029:132933.584 slow query: 29.073004 sec, "select itemid,min(clock) from history group by itemid"
22415:20151029:133707.892 slow query: 454.243674 sec, "select itemid,min(clock) from history_uint group by itemid"
22415:20151029:134218.675 slow query: 3.020152 sec, "delete from history_uint where itemid=233 and clock<1438345744"
22415:20151029:134250.697 slow query: 3.100935 sec, "delete from history_uint where itemid=391 and clock<1438345744"
22415:20151029:140530.020 slow query: 5.081831 sec, "delete from history_uint where itemid=5581 and clock<1445516944"
22415:20151029:140534.010 slow query: 3.989776 sec, "delete from history_uint where itemid=5582 and clock<1445516944"
22415:20151029:140645.755 housekeeper [deleted 267091 hist/trends, 0 items, 174 events, 0 sessions, 0 alarms, 0 audit items in 2261.243907 sec, idle 1 hour(s)]Ano, to myslím vážně. Sedm a půl minuty na jednom zavšiveným SELECTu. A celý úklid se vším všudy pak trval víc jak půl hodiny, protože mimo těch pár DELETE co běžely déle jak 3 vteřiny, jich tam bylo ještě dalších několik stovek, které spadly těsně pod hranici tří vteřin a zalogovány jako pomalé nebyly. I laikovi je jasné, že tohle bude asi úplně špatně.
Následující dva úklidy jsou o něco rychlejší a většinou doběhnou do pěti minut, protože většina důležitých věcí je už v paměti, případně keších na stranách Zabbixu i PostgreSQL, ale časový rozptyl je naprosto nepředvídatelný a výkon je i tak tristní. Pro úplnost doplním, že index, který obsahuje údaj o čase v mé tabulce s uloženými historickými číselnými hodnotami (history_uint), má obludných 22 GB, takže ani jeho použití nic moc neřeší. No a za čtyři hoďky jede celé kolečko znova. Nebo spíš stojí.
Jde to i líp
Hmm, ale já už dopředu vím, která data mají mít jakou platnost, takže jednak nepotřebuju SELECT na to abych zjistil, co chci mazat, a jednak by se mi hodila nějaká databázová featura, která by mi třeba seskupovala data už při vkládání, abych pak jen ukázal palcem dolů a Postgres by přiskočil a jedním precizním pohybem by data za daný časový interval zlikvidoval. Třeba nějaký dílčí index. No jasně, nebo třeba celou dílčí tabulku, protože můj databázový engine umí table partitioning. Pak budu jednoduše moci stará data ve zlomku vteřiny DROPnout.
Table partitioning je technika často používaná v enterprise-grade databázích. V současné době jej do jisté míry umí všechny databázové systémy, včetně toho blekotajícího idiota s delfínkem, který se nacpe všude. V principu jde o to, že se vytvoří rodičovská tabulka, úplně stejném způsobem jako jakákoliv jiná. A pak se řekne databázovému enginu, ať na jejím základě vytváří dceřiné tabulky s úplně stejnou strukturou a když se náhodou někdo zeptá na data z oné rodičovské, vrátí mu data i z dceřiných. V podstatě si to můžete představit jako jeden zUNIONovaný pohled na hromadu tabulek se stejnou strukturou.
Partitioning
ANSI SQL zápis a chování oddílů nijak moc neřeší, takže různé databázové enginy implementují partitioning různými způsoby. Většina databází například umí definovat oddíly a jejich omezení už při vytváření samotné tabulky. Oracle navíc umí i composite partitioning, takže je možno vytvářet subpartitions a gigantické tabulky rozmělnit na malé koherentní oddíly. Zacházení s oddíly je pak poměrně blbuvzdorné a konzistentní, na druhou stranu poměrně rigidní, protože třeba nedovoluje vkládání dat přímo do rodičovské tabulky. Vymysleli jste si partitioning, takže všechno musí byt v partitionách. DB2 pak umí auto-partitioning, takže mu můžete říct, že mezi 1. 1. 2000 a 31. 12. 2070 chcete mít oddíl pro každý měsíc a voilà, máte ho mít. Podobnou věc, i když v omezené míře umí dokonce i MySQL. O tom, na co všechno musí engine myslet a vůbec o celé problematice table partitioningu by se dal napsat celý samostatný článek, ale protože já už mám backend i strukturu tabulek danou, budu pokračovat pouze s PostgreSQL.
Vývojáři PostgreSQL na implementaci plnotučného partitioningu stále pracují, takže v současné chvíli se dá partitioning vyrobit pouze za pomoci triggerů. To je na jednu stranu fajn, protože přístup typu urob si sám umožňuje páchat nejrůznější zvěrstva dle potřeb aplikace. Na druhou stranu bohužel přináší i ošklivé množství problémů. Tak předně je potřeba samotný trigger vůbec napsat, což představuje spoustu práce pro databázového admina a v případě, že je potřeba nějaká vyšší logika, i spoustu práce pro databázový engine, který musí zpracovávat nezanedbatelné množství instrukcí navíc pro každý vkládaný řádek. Další problém je, že pokud aplikace provádí INSERT na rodičovskou tabulku a vy jí onen vkládaný řádek v triggeru seberete a vložíte do dceřiné tabulky, pgSQL aplikaci oznámí, že do požadované tabulky bylo vloženo 0 řádků, což je sice pravda, ale zas ne tak docela. Pokud pro daný rozsah však oddíl nemáte, do rodičovské tabulky se normálně vloží. Vyrobíte-li odpovídající oddíl až poté, data se samozřejmě z rodičovské tabulky sama nepřesunou a konzistence je v řiti. Dceřiné tabulky mají navíc vlastní indexy, na což je také při dynamickém vytváření potřeba myslet a nezapomenout je vytvořit. A pokud byste náhodou chtěli nebohý záznam v oddílu updatovat tak, že by se ocitl mimo rozmezí oddílu, ve kterém zrovna je, musíte celou tuhle anabázi absolvovat ještě jednou pro UPDATE triggery. A ano, tím chci jako nenápandě říci, že PostgreSQL má v současné chvíli něco řešeného hůře než MySQL. Neuvěřitelné.
Proof of concept
Dost bylo teorie a keců, takže teď pár ukázek. Základní věc, která je k definici oddílu v PostgreSQL potřeba, je klíčové slůvko INHERITS, které databázovému enginu říká, že tabulka nejen že dědí strukturu rodičovské tabulky, ale má se chovat jako její součást. Dále je potřeba ještě klíčovým slovem CHECK nastavit omezení, které určuje, jaké řádky do daného oddílu spadnou. Podmínka by měla samozřejmě být jednoznačná, aby se jednotlivé oddíly nepřekrývaly, jinak nebude možno využít constraint_exclusion optimalizaci pro planner, která je v tomto případě pro zvýšení výkonu životně důležitá.
Máme-li tedy tabulku
CREATE TABLE history (
itemid bigint not null
clock integer not null default 0
value numeric(16,4) not null default 0.0000
ns integer not null default 0
);Můžeme na ní například vytvořit oddíl pouze pro data z října 2015
CREATE TABLE history201510 (
CHECK ( clock >= extract(epoch from timestamp '2015-10-01') AND clock < extract(epoch from timestamp '2015-11-01') )
) INHERITS (history);INSERT se vkládanými daty pak musíme provést nad tabulkou s příslušným oddílem, nikoliv nad rodičovskou tabulkou. Do té sice stále normálně vkládat můžeme, ale databázový engine za nás špinavou práci dělat nebude a do oddílu příslušná data automaticky nepřehodí. To je samozřejmě trochu problém, protože zdrojové kódy vaší aplikace, ve kterých je jméno tabulky natvrdo zahákované, měnit nemůžete. Nicméně není to problém neřešitelný. Vypusťte na to BEFORE INSERT tygra, který při insertu po datech skočí a data zažene je do správné tabulky.
CREATE OR REPLACE FUNCTION zbx_history_partition() RETURNS TRIGGER AS $$
DECLARE
parttable TEXT;
BEGIN
parttable := TG_TABLE_NAME || to_char(to_timestamp(NEW.clock), 'YYYYMM');
EXECUTE 'INSERT INTO partitions.' || parttable || ' SELECT ($1).*' USING NEW;
RETURN NULL;
END
$$ LANGUAGE plpgsql;CREATE TRIGGER tg_history_before_insert BEFORE INSERT ON history FOR EACH ROW EXECUTE PROCEDURE zbx_history_partition();V téhle fázi bude jakýkoliv INSERT nad tabulkou history zachycen triggerem a každý řádek bude šoupnut do příslušného měsíčního oddílu. Funkce ovšem počítá s tím, že oddíl již existuje a pokud ne, vyhodí výjimku. Takže bychom museli ručně povytvářet hromadu oddílů (a jejich indexů) až za hranici očekávané životnosti databáze. Je to sice jedna z možností, ale s přibývajícím počtem tabulek se nám taková akce komplikuje, takže efektivnější bude nechat oddíly vytvářet dynamicky.
Automatická třídička
Zabbix dělí historická data do tabulek podle typu ukládaných dat. Budu teda potřebovat rozkouskovat tabulky history, history_uint, history_str, history_text a history_log. Problém s výkonem mám sice pouze u tabulky history_uint, ale ve chvíli, kdy v Zabbixu vypnu housekeeping, musím se postarat o všechny tabulky s historií sám, takže je stejně tak dobře můžu všechny uklízet stejným způsobem. S přibývajícím počtem zařízení by se mi pak mohlo stát, že budu mít stejný problém i u tabulek s trendy, ale momentálně jej nemám, takže se budu věnovat pouze historii. Oddíly budu vytvářet pro každý den a cron jobem budu každý den mazat stará data.
Nejprve si usnadním budoucí práci tím, že si pro tabulky oddílů vytvořím vlastní schéma. Nebudu je tedy rvát ke zbytku do public, ale vytvořím si schéma partitions, abych věděl, že všechno v tomto schématu můžu obhospodařovat stejným způsobem a nemusel vytvářet další vrstvu logiky na to, abych poznal, co je partition a co ne.
CREATE SCHEMA IF NOT EXISTS partitions AUTHORIZATION zabbix;Pak upravím výše zmíněnou funkci zbx_history_partition() tak, aby zachytávala výjimku vyhozenou při pokusu o vložení řádku do neexistujícího oddílu, tento oddíl vytvořila i se všemi indexy a dalšími náležitostmi a řádek se pokusila vložit znovu.
CREATE OR REPLACE FUNCTION zbx_history_partition() RETURNS TRIGGER AS $$
DECLARE
parttable TEXT;
startdate INT;
enddate INT;
BEGIN
-- Figure out the partition table name
parttable := TG_TABLE_NAME || to_char(to_timestamp(NEW.clock), 'YYMMDD');
-- Try inserting data to the partition
EXECUTE 'INSERT INTO partitions.' || parttable || ' SELECT ($1).*' USING NEW;
RETURN NULL;
-- If the partition does not exist
EXCEPTION WHEN undefined_table THEN
-- Calculate the start and end dates for the respective day
startdate := EXTRACT(epoch FROM date_trunc('day', to_timestamp(NEW.clock)));
enddate := EXTRACT(epoch FROM date_trunc('day', to_timestamp(NEW.clock) + '1 day'::INTERVAL));
-- Create new partition with indexes, constraints and all the stuff from parent table and add also the clock check
EXECUTE 'CREATE TABLE partitions.' || parttable || ' (LIKE ' || TG_TABLE_NAME || ' INCLUDING ALL, CHECK (clock >= ' || startdate || ' AND clock < ' || enddate || ')) INHERITS (' || TG_TABLE_NAME || ')';
-- Retry inserting to the newly created partition
EXECUTE 'INSERT INTO partitions.' || parttable || ' SELECT ($1).*' USING NEW;
RETURN NULL;
END
$$ LANGUAGE plpgsql;No a nakonec funkci nacpu do triggerů všech tabulek s historií.
CREATE TRIGGER tg_history_before_insert BEFORE INSERT ON history FOR EACH ROW EXECUTE PROCEDURE zbx_history_partition();
CREATE TRIGGER tg_history_uint_before_insert BEFORE INSERT ON history_uint FOR EACH ROW EXECUTE PROCEDURE zbx_history_partition();
CREATE TRIGGER tg_history_str_before_insert BEFORE INSERT ON history_str FOR EACH ROW EXECUTE PROCEDURE zbx_history_partition();
CREATE TRIGGER tg_history_text_before_insert BEFORE INSERT ON history_text FOR EACH ROW EXECUTE PROCEDURE zbx_history_partition();
CREATE TRIGGER tg_history_log_before_insert BEFORE INSERT ON history_log FOR EACH ROW EXECUTE PROCEDURE zbx_history_partition();PostgreSQL pak okamžitě začne vyrábět oddíly nic netušícímu Zabbixu rovnou pod zadkem. Po pár dnech běhu tedy místo děsivého
zabbix=# \dt+ history_uint
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------------+-------+--------+-------+-------------
public | history_uint | table | zabbix | 27 GB |
(1 row)zabbix=# \dt+ partitions.history_uint*
List of relations
Schema | Name | Type | Owner | Size | Description
------------+--------------------+-------+--------+--------+-------------
partitions | history_uint151022 | table | zabbix | 64 MB |
partitions | history_uint151023 | table | zabbix | 210 MB |
partitions | history_uint151024 | table | zabbix | 209 MB |
partitions | history_uint151025 | table | zabbix | 218 MB |
partitions | history_uint151026 | table | zabbix | 207 MB |
partitions | history_uint151027 | table | zabbix | 211 MB |
partitions | history_uint151028 | table | zabbix | 209 MB |
partitions | history_uint151029 | table | zabbix | 208 MB |
partitions | history_uint151030 | table | zabbix | 207 MB |
partitions | history_uint151031 | table | zabbix | 171 MB |
(10 rows)Harmonogram úklidu
Dalším krokem je odstavení integrovaného housekeeperu. Jednoduše v administraci Zabbixu odškrtněte checkbox u historie a hotovo. Housekeeper se přestane zajímat o tabulky s historií a nechá je napospas svému osudu.
Dále potřebujete nějaký skript, který oddíly ve schématu partitions proběhne, porovná data a staré tabulky zahodí. To opět řeším procedurou uloženou přímo v databázi.
CREATE OR REPLACE FUNCTION zbx_history_cleanup() RETURNS VOID AS $$
DECLARE
parttable RECORD;
threshold TIMESTAMP;
BEGIN
-- Calculate the last day of 90 days retention period
threshold := date_trunc('day', now() - '90 days'::INTERVAL);
-- Iterate for all tables in partitions schema
FOR parttable IN SELECT * FROM pg_tables WHERE schemaname = 'partitions' LOOP
-- If the table is for a day prior to the threshold
IF threshold > to_timestamp(substring(parttable.tablename FROM '[0-9]*$'), 'YYMMDD') THEN
-- Call Skrillex and drop the bass
EXECUTE 'DROP TABLE partitions.' || parttable.tablename;
END IF;
END LOOP;
END
$$ LANGUAGE plpgsql;No a na závěr si jen vyberete nějaký vhodný okamžik během dne a šoupnete uklízečku do cronu
0 0 * * * postgres psql -c 'SELECT zbx_history_cleanup()' zabbix >/dev/nullJako zázrakem pak bude log housekeeperu vypadat nějak takto
72729:20151031:222906.789 executing housekeeper
72729:20151031:222907.125 housekeeper [deleted 23 hist/trends, 0 items, 90 events, 0 sessions, 0 alarms, 0 audit items in 0.327628 sec, idle 1 hour(s)]Z původních ~2200 sekund na 0.3 je celkem uspokojivé zlepšení, co říkáte?
Odkazy a zdroje
Pokud byste si rádi nastudovali něco více o table partitioningu v PostgreSQL pokračujte tudy:
- https://wiki.postgresql.org/wiki/Table_partitioning
- http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html
Při vytváření SQL funkcí a procedur jsem se nechal inspirovat zde:
Tamější skripty řeší i tabulky trendů a přidávají možnost výběru délky intervalu, ale přijdou mi místy až nepotřebně složité. Navíc u tabulek oddílů vytváří pouze jediný index, i přesto, že původní tabulka má několik dalších, jejichž nepřítomnost má opět znatelný negativní dopad na výkon. Odkaz je vhodný spíše jako výchozí bod k vaší vlastní implementaci, určitě nedoporučuju bezhlavě copypastovat. Což ostatně nedoporučuju za žádných okolností.