Arcsynchro a PostgreSQL trezory

Databázová platforma PostgreSQL je aj u našich zákazníkov čoraz obľubenejšia. Dosvedčujú to nielen nové projekty využívajúce PostgreSQL ako archívnu či MES databázu, ale aj migrácie v rámci upgradov existujúcich aplikácií.

Pokiaľ ide o archívne databázy, na migráciu dát sa používa utilita arcsynchro. Táto sa prostredníctvom ODBC pripojí k zdrojovej a cieľovej archívnej databáze a kopíruje dáta podľa zadaných parametrov (časové rozpätie, maska objektov alebo rozmedzie ich ID-čiek a pod).

V praxi trvá migrácia dát dni až týždne. Tvrdším orieškom sú trezorové databázy - kvôli objemu dát. Je pochopiteľné, že stovky gigabajtov dát zbierané celé roky sa budú kopírovať pomerne dlho.

Obr 1: NAS disk s trezormi pre dve D2000 aplikácie s cca. 50 a 100 mesiacmi dát.
Na disku je zapnutá NTFS kompresia, vďaka ktorej cca 5.82 TB trezorov zaberá 1.75 TB miesta.

Konverzia trezorových databáz u konkrétneho zákazníka (z Oracle na PostgreSQL) trvala niekoľko mesiacov. V súčasnosti prebieha ďalšia, ešte väčšia konverzia a preto sme sa zaoberali možnosťou, ako toto kopírovanie dát zrýchliť.

Parametrizované dotazy a dávkové vkladanie údajov

ODBC rozhranie ponúka možnosť parametrizovať SQL dotaz (napr. INSERT) - namiesto konkrétnych hodnôt sa použijú parametre - napr. INSERT INTO Data ( cas, hodnota) VALUES (?, ?) - a následne sa SQL dotaz volá mnohokrát po sebe s tým, že parametre sa naplnia rôznymi hodnotami. Výhoda parametrizovaných dotazov je aj v tom, že sa niektoré činnosti vykonávajú iba raz (parsovanie dotazu, vyhodnocovanie prístupu užívateľa k tabuľke, vytvorenie exekučného plánu optimalizátorom atď).

Parametrizované SQL dotazy sú v D2000-ke bežne používané - v dbmanageri, archíve aj v utilite arcsynchro. ODBC ale umožňuje naplniť celé pole parametrov a v rámci jedného volania vykonať napr. 1000 dotazov - tj. vložiť 1000 riadkov naraz - tzv. multirow inserty. Čo často vkladanie dát ešte zrýchli.

Preto bolo nedávno v utilite arcsynchro implementované ukladanie údajov do trezorových databáz PostgreSQL týmto spôsobom. V očakávaní zrýchlenia sme spustili testy a výsledok bol sklamaním - zrýchlenie sa nekonalo.

Zapnutie logovania v ODBC ovládači PostgreSQL a neskôr aj prezeranie zdrojových kódov ODBC odhalilo príčinu - vnútorne ODBC ovládač vkladá riadky do PostgreSQL databázy po jednom.

Vyskúšané boli ovládače PostgreSQL, EnterpriseDB aj nezávislého výrobcu ODBC ovládačov pre PostgreSQL - firmy Devart. Multirow inserty neboli rýchlejšie v žiadnom z ovládačov - aj keď na fóre firmy Devart vývojári napísali, že sa na to pozrú a dúfajú, že budú vedieť dávkové vkladanie zrýchliť.

Použitie textových súborov

Čo teraz? Namiesto čakania sme sa poobzerali po alternatívnych spôsoboch. Existuje napríklad vkladanie dát z textového alebo csv súboru (tzv. COPY TABLE). Problémom je, že pokiaľ v tabuľke už existujú duplicitné dáta, COPY TABLE skončí neúspešne - na rozdiel od INSERT príkazu, ktorý v PostgreSQL od verzie 9.5 podporuje klauzulu ON CONFLICT UPDATE a umožňuje tak dáta prepísať (tzv. UPSERT).

Pri pátraní sme ale narazili na fórum popisujúce fungovanie rozšírenia (extension) file_fdw. Toto rozšírenie (ktoré je ale súčasťou štandardnej inštalácie PostgreSQL) umožňuje definovať textový alebo databázový súbor ako externú tabuľku.

Asi najlepšie to objasní príklad:

Najskôr vytvoríme v databáze príslušné rozšírenie:

CREATE EXTENSION file_fdw;

Následne vytvoríme ‘vzdialený server’, ktorý je obsluhovaný rozšírením:

CREATE SERVER import_t FOREIGN DATA WRAPPER file_fdw;

A nakoniec zadefinujeme vzdialenú tabuľku patriaca pod vzdialený server:

CREATE FOREIGN TABLE mytbl (id INTEGER, cas TIMESTAMP, value DOUBLE PRECISION)
SERVER import_t OPTIONS ( filename 'D:\data.txt', format 'text' );

Pri definícii vzdialenej tabuľky sa povie, kde sa nachádza súbor a aký je jeho formát (text, csv, binary). A ďalej môžme už použiť SQL príkaz INSERT na vloženie dát z tabuľky mytbl do štandardnej tabuľky (napr. nachádzajúcej sa v trezore) s využitím UPSERT syntaxe:

INSERT INTO data(id, cas, value) SELECT * FROM mytbl
ON CONFLICT (id, cas) DO UPDATE SET value=EXCLUDED.value;

Následne môžme textový súbor zmazať, prípadne vytvoriť nanovo s inými dátami a vloženie zopakovať (rozšírenie tomu nebráni, pokiaľ sa SQL príkaz už skončil).

Implementácia v arcsynchro

Popisovaná funkcionalita bola podporená v utilite arcsynchro. Pokiaľ sú napĺňané PostgreSQL trezory, je možné použiť nový štartovací parameter /FM <path>.

FM znamená ‘File Mode’ a <path> je názov adresára, v ktorom sa budú textové súbory vytvárať.

Pozor - tento adresár musí byť prístupný pre PostgreSQL na čítanie (štandardne beží na Windows pod užívateľom Network Service). Znamená to zároveň, že arcsynchro musí byť spúšťané na počítači, kde je cieľová databáza s trezormi (čo v praxi nie je problém). Použitie UPSERT-u navyše znamená, že vlastnosť bude fungovať iba v PostgreSQL 9.5 a vyššom (čo v praxi tiež nie je problém, keďže spravidla inštalujeme najnovšie verzie PostgreSQL a staršie vieme upgradovať).

Arcsynchro do textového súboru vloží najviac toľko hodnôt, koľko špecifikuje parameter /CM (commit) a následne vykoná vloženie zo vzdialenej tabuľky. V praxi je prednastavená hodnota /CM parametra (1000) pre ‘File Mode’ malá a preto sme použili podstatne väčšiu - /CM 500000).

Aké sú reálne výsledky?

Obr 2: porovnanie výsledkov arcsynchro pracujúceho klasicky (vkladanie cez ODBC) a vo ‘File Mode’.

Klasicky bežiace arcsynchro je schopné vkladať dáta do trezorov priemernou rýchlosťou okolo 2400 hodnôt za sekundu. Vylepšené arcsynchro s použitím parametra /FM priemerne takmer 8300 hodnôt za sekundu, t.j. viac ako trikrát rýchlejšie. Zaujímavé je, že čím ďalej, tým je rýchlosť vyššia - uvidíme, ako dlho tento trend bude pokračovať :)

Záver

Trojnásobné zrýchlenie vkladania dát do trezorov určite nie je na zahodenie a umožní nám skrátiť dobu potrebnú na migráciu trezorov na platformu PostgreSQL v rámci prebiehajúceho projektu aj tých budúcich.

Upravené arcsynchro bude k dispozícii vo forme patchov pre verziu 11.0.53 a bude sa dať použiť pre verzie 9.1.31 a vyššie (keďže štruktúra archívnej a trezorových databáz sa od tejto verzie nemenila).

27.11.2017, Ing. Peter Humaj, www.ipesoft.com