Dotaz: Algoritmus synchronizace dvou databází pomocí timestamp

[/lKA0/]

Ahoj. Prosím vás o pomoc s následujícím problémem. Mám 2 relační databáze z nichž každá umí timestamp sloupce. Potřeboval bych synchronizovat zdrojovou a cílovou databázi (respektive tabulku). Ve zdrojové databázi se může mazat, přidávat a upravovat záznamy, cílová db je jenom pro čtení. Synchronizaci pro případ přidání a upravení záznamů ve zdroj. db jsem už vyřešil: podívám se na poslední timestamp ve zdroj. db a pokud je větší než timestamp v cílové nebo pokud je timestamp stejný a počet hodnot s timestampem rozdílný (pro případ že bych se zrovna trefil do doby, kdy se ze zdrojovou db pracuje), tak se synchronizují poslední záznamy. Problém mi dělá jak poznat, že se ve zdrojové db něco smazalo (aniž bych vytvářel pomocné tabulky, triggery apod., protože to v tomto případě nemůžu). Můžu sice kontrolovat počet záznamů menších než timestamp ale veliký problém je, když se trefím do bodu, kdy se s daty pracuje, tady nemám jak poznat, která data byla smazána a která ne. Nemá někdo nápad jak tohle vyřešit?

Odpovědi

[/lKA0/]

Spíš bych asi potřeboval poradit jiný mechanismus synchronizace, protože timestamp = systémový čas, systémový čas se mění, nejvíc při přechodu letní/zimní.

[Heron]

UTC?

[/lKA0/]

To by se muselo nastavit v systému, já mám přístup jen k databázi.

[cronin]

A prave preto je to krajne nespolahlivy sposob.

[Filip Jirsák]

Pokud je v systému nastavena rozumná synchronizace, čas se skokově nemění, ale srovnává se zrychlením nebo zpomalením hodin. Při přechodu mezi zimním a letním časem se také skokově nemění, protože se změní časové pásmo.

[/lKA0/]

To jsou předpoklady které jsou málokdy splněny. Je fakt, že čas se většinou skokově nemění (aškoliv se často v linuxu používá ntpdate), ale při změně zimního/letního času se změní skokově. Jak jsem zjistil, tak timestamp v databázi odpovídá času v systému nebo UTC času, pokud je tak databáze ovšem nastavena.

[/lKA0/]

Tak oprava, změna časové zóny by na to mít alespoň v mysql vliv neměla, časová zóna se dá nastavit pro jednotlivá spojení.

[Filip Jirsák]

Pokud se používá průběžné seřizování času (např. přes ntpd), což je na serveru jediné rozumné řešení, nemění se čas skokově, ale zrychlením či zpomalením hodin. Pokud tedy rozdíl není příliš velký, ale to už je pak stejně jedno.

Při změně zimního/letního času se čas skokově nemění. Součástí časového údaje je i časová zóna, a 3:00 CEST je to samé, jako 2:00 CET.

Ale to jsem obojí psal už v předchozím komentáři…

[/lKA0/]

V mysql je čas ukládán v utc a až při čtení je konvertován do správné časové zóny, ale to je vlastně jedno.

[mich]

Jaký používáš databázový systém? Každý má nějaký nástroj na master-slave replikaci dat.

[/lKA0/]

Různé: zdrojová je firebird a cílová mysql.

[mich]

Na tvém místě bych zkusil tohle.

[/lKA0/]

To beru jako poslední možnost. Celý teď už docela obsáhlý "program" mám ale napsaný v php i veškerou konfiguraci a tak bych rád implementoval synchronizaci přímo v něm. Na DBreplicator jsem bohužel nenašel jakým algoritmem to funguje.

[mich]

Nevím jaký program už máš napsaný, takže napíšu jak bys to mohl implementovat mimo něj, snad to pujde napasovat. Takže máš master db (ta se mění) a slave (pro čtení a chceš do ní propagovat změny z mastera).

• Na master db vytvoříš novou tabulku (pracovní název LOG), která bude mít 2 sloupce - automaticky se zvyšující ID a VARCHAR, který bude uchovávat sql dotaz.
• Na slave tabulka s jediným sloupcem - ID. Bude mít vždy jeden řádek, k využití se dostanu (pracovní název LAST_TS).
• Na tabulku, kterou chceš replikovat přidáš AFTER TRIGGERy po UPDATE, INSERT a DELETE. Tyhle triggery udělají jedině to, že zalogují, co se právě v DB stalo do tabulky LOG.
• Na serveru, kde běží master DB poběží skript, který bude číst tabulku LOG a přeposílat jednotlivé záznamy na podobný skript, který poběží na serveru, kde běží slave. Protokol musí obsahovat ID (z tabulky LOG) a samotný požadavek, co se má provést. To ID se používá k řazení těch požadavků, takže tento skript z DB vybírá záznam s nejmenším ID.
• Skript na serveru slave dělá pořád to samé - čeká na zprávu od skriptu na masterovi a pokaždé, když ji dostane změní data ve své db + do tabulky LAST_TS zapíše hodnotu ID toho požadavku.
• Když skript na slavu zapíše data, vrátí ACK ID masterovi a ten může požadavek s tím ID smazat z LOGU.
• Pokud dojde k výpadku, tak ti to nevadí, protože slave uchovává ID posledního požadavku, který provedl (tabulka LAST_TS).
• Samozřejmě musí být na slavu otevřený port po kterém to bude komunikovat, což je zdroj nebezpečí.

[/lKA0/]

Bohužel jak jsem psal nemohu používat triggery (mimo jiné ani nemám oprávnění je vytvářet). S tímto omezením jsem zatím dospěl k názoru, že jediný způsob synchronizace je pokaždé přečíst PK a sloupec timestamp z mastera i slave a vzájemně je porovnávat a u toho co nesedí provést update, delete či insert.

[/lKA0/]

Už jsem přišel na algoritmus který by mohl fungovat. Nějaký skript bude aktualizovat třeba každých 5 minut:

na slavu se založí pomocná tabulka se sloupci (název tabulky, timestamp, count)

- pokud bude poslední timestamp na masterovi větší než timestamp v pomocné tabulce na slavu, tak se provede synchronizace od uloženého timestampu do posledního a zapíše se poslední timestamp a count s hodnotou 1 do pomocné tabulky, pokud je ale timestamp stejný a count > 1 tak se opět znovu sesynchronizuje poslední timestamp a dekrementuje count
- mazání záznamů: po provedení synchronizace se zkontroluje počet řádků v obou tabulkách a pokud je počet řádků v tabulce na slavu větší než počet řádků na masterovi, tak se načtou všechna všechny timestampy z obou tabulek a bude se porovnávat

Kvůli latenci tam sice může vzniknout dočasný problém když se mezi oběma kroky přidá do master tabulky dostatečné množství záznamů, ale to by měla zpravit příští synchronizace. Logicky toto ale musí fungovat. Prosím opravte mé případné omyly

[Robo]

podla mna to zbytocne synchronizujes 2-krat v jednom kroku; proste zosynchronizuj vsetko s "max(timestamp) - 1"

tak se načtou všechna všechny timestampy z obou tabulek a bude se porovnávat

lepsie by to bolo porovnavat na zaklade PK, aby si sa vyhol full scanu, kedze timestamp asi indexovany nebude

stale tam vsak vidim problemy so zistovanim max(timestamp) a zistovanim poctu zaznamov v oboch tabulkach -> to su vsetko (fast) full scany na dane tabulky, takze si myslim ze skopirovat celu tabulku zakazdym nanovo by bolo asi rychlejsie

[Tomáš]

Já bych si tam dal nějaký unikátní PK, který nejde updatovat. Potom bych si udělal tři pomocný tabulky, v jedný bych evidoval PK řádků, který jsem od poslední replikace updatoval, v druhý přidaný řádky (jejich PK) a ve třetí PK smazaných řádků. Každá replikace by tohle prozkoumala, udělala potřebné změny v té druhé databázi a potom tyhle pomocný tabulky smazala/označila záznamy za zreplikované. Pomocné tabulky bych plnil pomocí řádkových triggerů. Základ je v tom nemodifikovatelném primárním klíči.

Na timestamp bych moc nespoléhal, podle mne tam není záruka unikátnosti.

[/lKA0/]

Já na něj spoléhám jen tak, že se bude po sekundě inkrementovat, na nic jiného nesázím. PK je username, takže se pak porovnávají samozřejmě username + příp. timestamp. Docela mě ale zajímá zda ten můj popsaný mechanismus bude fungovat nebo jsem v tom udělal nějakou logickou chybu? Totiž ten můj mechanismus nemusí fungovat jen s timestampem, ale s jakoukoliv číselnou hodnotou a také není vyžadována žádná změna master tabulky, což je při větším počtu různých serverových replikací žádoucí.

[Tomáš]

Ohledně timestamp. Začnu replikační dávku a v tu stejnou chvilku přidám do DB tisíc řádek. Všechny nové řádky budou mít stejný timestamp a moje značka, kdy běžela replikační dávka bude mít také stejný timestamp. Takže nevím, které řádky jsem přenesl a které ne. Problém je skutečně s těmi smazanými řádky. To se bez pomocné tabulku udělá fakt blbě. Tedy kromě divokého selektu s joinem přes ty obě dvě databáze, což v tomto případě asi nebude úplně možné.

[/lKA0/]

Včera jsem to naprogramoval a vyzkoušel a vypadá to že to běží.

Ohledně timestamp. Začnu replikační dávku a v tu stejnou chvilku přidám do DB tisíc řádek. Všechny nové řádky budou mít stejný timestamp a moje značka, kdy běžela replikační dávka bude mít také stejný timestamp. Takže nevím, které řádky jsem přenesl a které ne.

Právě proto provádím synchronizaci daného timestampu vždy 2x v odstupech několika sekund.

Problém je skutečně s těmi smazanými řádky. To se bez pomocné tabulku udělá fakt blbě. Tedy kromě divokého selektu s joinem přes ty obě dvě databáze, což v tomto případě asi nebude úplně možné.

Pokud je počet řádků v lokální tabulce větší než ve vzdálené tak se projde řádek po řádku lokální a hledá se odpovídající záznam ve vzdálené. Toto je funkční a myslím si že jediné možné řešení.

[Robo]

ak mas PK username, tak to synchronizuj cez ten username; timestamp neni unikatny a pri synchronizacii cez timestamp tam budes mat navyse full scan celej zdrojovej tabulky

[FooBar]

(jestli to chapu vsechno spravne)

Kazdy radek bude mit dodatecny sloupec s logickym casem zmeny. Logicky cas se zvysuje o 1 zcela globalne (v Postgres bys to implementoval proste pres sekvence). Pri kazdem UPDATE na radku zvysis globalni hodnotu hodin o 1 a zmenis hodnotu na radku na tuto hodnotu. Pri kazdem INSERT zvysis globalni hodnotu o 1 a nastavis tuto hodnotu.

Budes mit jednu globalni hodnotu ktera bude vzdy MAX(logicke_hodiny) z tvoji tabulky (v pripade postgres sekvenci to uz mas). Slave DB ma tuto hodnotu take, a kdyz se synchronizuje, tak se podiva zda-li lokalni maximum logickych hodin na slave je stejne jako to na masterovi. Pokud je na masterovi vyssi hodnota, pak slave stahne vsechny sloupce ktere maji hodnotu logickych hodin vyssi nez to co byla slave hodnota maxima.

Co se tyka mazani, muzes si udrzovat whiteout table s IDckama ktery byly smazany. Pokud tuhle informaci potrebujes v slave db zjistit jen jednou, tak tu informaci pak muzes rovnou mazat. Tudiz tvuj delete bude ve skutecnosti:

BEGIN
DELETE FROM datatable WHERE id=[smazany id];
INSERT INTO whiteouts VALUES ( [smazany id];
COMMIT

Musi to byt v transakci s rozumnou izolaci a doporucuju to mit jako server-side funkci. Obdobne, kazdy pridany whiteout zvysi hodnotu globalnich logickych hodin; muzes zvazit mit pro whiteouts separatni logicke hodiny.

Slave pak zjisti zmenu na danem radku pomoci zmeny hodnoty logickych hodin, smazani radku se pak realizuje pomerne snadno:

BEGIN
DELETE FROM datatable WHERE id IN (SELECT id FROM remote_database.whiteouts)
DELETE remote_database.whiteouts
COMMIT

That said, delas hromadu prace kterou bys ROZHODNE nemel delat. Celou tuhle vrstvu by ti mel realizovat databazovej stroj, proc to nedelas?

[FooBar]

Jo a jeste abych to zduraznil, logicky hodiny, pouzivat systemovej cas na tohle je peknej vomrd i kdyz pouzivas UTC a mikrosekundy. Mej se rad a nedelej to, nemas to zapotrebi, pokud mas aspon jen trochu pricetnou DB.

[/lKA0/]

No synchronizuju to paradoxně z toho důvodu, že chci veškerou práci převést na databázový stroj. Nad těmi daty jsou složité selecty a hodí se mít to všechno v jedné db. Navíc počítám že do budoucna se bude sychronizovat i z něčeho jiného než je relační databáze. A k těm hodinám - jasně že tam kde si nemůžu být jistý systémovými hodinami (což si nemůžu být prakticky nikdy) použiju nějakou hodnotu která se bude inkrementovat (pro ten můj algoritmus je úplně jedno co tam bude za hodnotu - jen to musí jít porovnávat velikostně).

[FooBar]

Tim chces rict, ze slave DB se bude synchronizovat z vice zdroju (= vice master DB, ci jedna master DB a jiny dodatecny zdroj)? To se mas na co tesit, vicemene pak resis problem souteze o viteznou hodnotu pri eventual consistency.

Co se tyka "hodi se mit to vsechno v jedne db", absolutne nevidim proc by to melo byt v protikladu s tim, ze to bude DB a ne ty, kdo bude zajistovat synchronizaci. DB s tim ma zkusenost, ty ne:)

Neber si to osobne, nechci te urazit, ale... nejsem presvedcenej, ze vis co delas. Mam z toho vseho spis dojem, ze moc netusis, bastlis reseni na koleni a tim se zenes do silenyho pruseru s nekonzistentnima datama (ne dnes, ne zitra, ale dost brzo). Nechces dat nejakej big picture co to sakra vlastne resis za problem?

[/lKA0/]

Ubastlené na koleni to rozhodně není. Ale i tak očekávám neočekávané problémy ... Konkrétně řeším problém spojování dat z více zdrojů. Vemte si například že máte tři separátní databáze zaměstnanců. Jedna obsahuje příchody/odchody zaměstnanců (řešeno ve firebirdu), jedna řeší výdeje obědů zaměstnanců (mssql), další ..... Co bych měl asi tak dělat když nad každou databází sedí jiná aplikace od jiného dodavatele a já pak chci třeba zjistit zaměstnance, kteří přišli v 10.00 a oběd si dali do 2 hodin od jejich příchodu. V tom případě mi nezbývá nic jiného než vytvořit centrální databázi s několika tabulkami a do nich synchronizovat všechno co mě zajímá z ostatních zdrojů.

[dustin]

Tipnul bych si, že u tohoto typu údajů nepůjde o milióny řádků. Kdyby stačila aktualizace párkrát do hodiny, co se nepárat s inkrementálními aktualizacemi a prostě ty datové tabulky naplnit pokaždé znovu? MySQL zvládne desítky tisíc multiinsertů za sekundu. Např. každý den nalíváme do mysql pro vývojáře aktuální ostrá data, celkem přes 300 mil. řádků

[/lKA0/]

To ne, jsou to jen stovky řádků, ale i tak je to potřeba aktualizovat zatím cca 1krát za minutu.

[dustin]

Takový rozsah DB bude znovu nalít otázkou max. pár sekund i s konexemi do zdrojových a cílové DB :) Než se patlat s inkrementálním updatem, prodloužil bych dobu "neaktuálnosti" na pár minut a lil to tam pokaždé znovu.

[Robo]

ak ta zdrojova baza ma iba niekolko stoviek riakov, tak to fakt nema zmysel synchronizovat inkrementalne; synchronizacia zbehne za par sekund (aj bez toho, ze by si to paralelizoval)