Dotaz: 3700 tabuliek

[elon]

Dobry den,

rad by som poznal vas nazor na nasledujuci usecase, popripade rady ako dalej.

 

Mame prisne strukturovane vedecke data. Logicky su rozdelene do tabuliek podla prefixu. Kazda tabulka zodpoveda inemu prefixu. V podstate su to key-value tabulky (80% citanie, 20% zapis) s tym, ze hodnota ma 7 atributov a je rozdelena do stlpcov. Kluc stlpec VARCHAR(38) s unique btree indexom. Ostatne stlpce su VARCHAR(64).

 

Historicky sme ukladali data do jednej Postgresovej super tabulky.

Potom sme super tabulku rozdelili na particie (podla prefixu)

Zitili sme, ze Postgres ma velky overhead - vyexportovane CSV malo 5GB, tabulka na disku zaberala 7GB.

Ked sa velkost databazy dostala na velkost cca 10TB uz sa s Postgresom nedalo rozumne pracovat.

Importovanie dat z 10GB CSV (prikaz COPY) trval viac ako 24hodin.

Rozhodli sme sa opustit Postgres a tabulky vyexportovat do 3700 SQLITE databaz podla prefixu na 10GBE NFS storage.

3-4 roky to bolo fajn. Data pribudali. Dostali sme sa na hranicu 30TB a kazda SQLITE databaza na priemerne cca 11GB.

 

SQLITE je super, velmi dobre sa s tym pracuje, majma Python, Pandas. No zaciname pomaly narazat na limity SQLITE aj pri pouzivani tuningu s .PRAGMA parametrami.

 

Rad by som sa opytal ci by ste mi vedeli odporucit nejaku technologiu na ukladanie tabuliek a import dat v rozumnom case. Hlavnu prioritu ma integrita dat, podpora pythonu. Davame prednost single serveru pred clustrom. Dakujem.

---

---

Odpovědi

[Heron]

Moc nerozumím, proč jste opustili postgresql. Jasně, jedna DB (tabulka) o velikosti 10TB už něco váží, zejména update indexů trvá, ale to se dá obejít tím, že se dropne index, nalijou se tam data a na konci index opět vytvoří. Rozdělit data do jednotlivých tables / databases můžete klidně i v postgresu. 11GB per database není vůbec nic.

Takže zkuste udělat totéž, co děláte se sqlite (tedy takto nasekaná data vrátit zpět do PG) a uvidíte.

Ale celkem fascinující řešení nad sqlite. Je to skvělá knihovna pro robustní ukládání dat a single user přístup.

[Kit]

SQLite na NFS není dobře.

[Heron]

Proč? Pozor je potřeba dát pouze pokud se má sdílet jeden soubor mezi více klientů (pro zápis), tam je NFS skutečně nevhodný (https://www.sqlite.org/faq.html#q5), proto jsem taky psal "single user přístup". Jinak je SQLite skvělá robustní knihovna pro ukládání dat, viz také odkazovaný článek níže.

[elon]

Ano, samozrejme chapeme vyhody a nevyhody NFS a single user access.

 

Vyhodou je akasi "kniznica" SQLite databaz, ked administrator ma povolenie prepisovat subory. Ostatni kolegovia len citanie. Dalsiu velku vyhodu, ktoru sme ziskali je portabilita. Staci si nakopirovat databazu/databazy ku sebe na pocitac. Ak chce clovek pracovat nemusi mat pripojenie k serveru.

[Filip Jirsák]

Nenapsal jste, co s těmi daty děláte. Ale také bych se přikláněl k použití PostgreSQL, nevidím důvod, proč by to neměla zvládnout.

[z_sk]

Zmeň ukladanie v tabuľkách indexovanie z typu z Btree na HASH tabuľky (v MariaDB je default BTree a dá sa v nej zmeniť). BTree ma limity.

Ak to más zdielané, tak neťahaj cez NFS, ale trebars sa dotazuj cez HTTP.

Hm. Také veci by ma bavili optimalozovať. Indexoval som pri štatistikách, ktoré som robil do 5M stránkoch, všetko sám programoval a prístup k dátam bol rýchly.

[Filip Jirsák]

O MariaDB nebyla v dotazu vůbec řeč. U databáze, na kterou jsou kladené trošku větší nároky, bych MySQL/MariaDB zvažoval jedině v případě, kdy bych odněkud věděl, že zrovna tenhle use case zvládá MySQL/MariaDB skvěle.

[z_sk]

Spominal typ indexovanie BTree a na take velke mnozstvo nie je hodne. Vyslo mi vypoctom, ze tam ma (max) 20-30 milion zaznamov na DB.

O MariaDB pisem, lebo ako referencie mojej skusenosti.

P.S.: Ako to robia profici vid., resp. este kukni orig. subory: https://www.root.cz/clanky/vymenit-200-milionu-certifikatu-za-den-let-s-encrypt-pripravuje-nejhorsi-scenar/

[Filip Jirsák]

Vhodnost indexu závisí hlavně na datech a na tom, co je potřeba s daty dělat. Na základě hash indexu např. záznamy neseřadíte.

[elon]

U nas je na prvom mieste integrita dat. Musim datam ulozenym v DB verit. Nesmu sa poskodit popripade inak degradovat.

Rychlost pristupu nie je moc dolezita. S SQLite databazou sa da super pracovat v Python, ci uz cez Pandas alebo SQL Alchemy.

HTTP je zbytocna vrstva. Potrebujeme priamy pristup k datam, neviem si predtstavit ako by sme v Pandas pracovali datami obalenymi v HTTP.

[Kit]

HTTP není úplně zbytečnou vrstvou, zejména pokud se nachází na stejném serveru jako data. Při vyhledávání je řádově rychlejší než přímý přístup k datům. V případě SQLite se velmi dobře uplatní cache souborového systému.

Data z HTTP serveru se dají přenášet jako plain, JSON, XML, HTML nebo cokoli dalšího, nejlépe rovnou s potřebnou agregací.

[z_sk]

Btree je náročný na memory (pre low cost maschiny), tak som si vytvoril iný spôsob, ktoré nebude naročne na RAM pamät, bude rychle a a dôkaze nekonečno spracovať dát ("key" => "value" typu) a pôjde to aj bežnom železe.

[samalama]

... a dôkaze nekonečno spracovať dát...

ja som vzdycky vedel, ze na to mas...

[z_sk]

…a dokáže nekonečno spracovať…

[AraxoN]

Na key-value údaje je SQL overkill. Neskúšali ste Berkeley DB?

[Filip Jirsák]

Co na tom NoSQL databáze bude moci udělat efektivněji? Zejména když jsou tam sekundární indexy.

[AraxoN]

Aké sekundárne indexy? OP píše len o primárnom indexe.

A nehovorím o NoSQL databázach, len o Berkeley DB. To je súbor na disku, ktorý obsahuje strom s údajmi. Nič viac.

Čo by na tom mohlo byť rýchlejšie? Nerobí sa komunikácia medzi procesmi, neparsuje sa SQL, nerieši sa konkurenčný prístup, copy-on-write, ACID ani ďalšie veci, ktoré plnohodná databáza má, ale v danom use-case možno nie sú potrebné.

Ak neveríte, vyskúšajte si to. Berkeley DB je na key-value databázy niekoľkonásobne rýchlejšia než SQL, a vo väčšine prípadov je dokonca rýchlejšia než memcached.

[Filip Jirsák]

Aha, přehlédl jsem se, viděl jsem „každý stlpec“ a ono je to „kluc stlpec“. Omlouvám se.

A nehovorím o NoSQL databázach, len o Berkeley DB. To je súbor na disku, ktorý obsahuje strom s údajmi. Nič viac.

Berkeley DB je jedna z NoSQL databází. Ano, vznikla dřív, než se jim tak začalo říkat, to ale na věci nic nemění.

[elon]

Berkley DB, bol horucim kandidatom ked sme odchadzali od Postgres.

 

Pamatam si, ze najvacsimi nevyhodami boli: lebo Oracle, lebo licencia, lebo Java, lebo SQLite ma lepsiu podporu v Python.

Takze viac politicke rozhodnutie ako logicke :).

[Filip Jirsák]

Asi si pletete Berkeley DB s Berkeley DB Java Edition.

[AraxoN]

Myslel som pôvodnú Berkeley DB, knižnicu napísanú v C, dostupnú pod BSD licenciou. Produkty, ktoré pod týmto menom do sveta uvádza Oracle, nepoznám.

[Heron]

Pokud je prioritou odolnost vůči poškození dat, doporučuju tento článek. Je sice již staršího data, ale ledacos napoví. Na straně 12 je porovnání odolnosti různých databází proti chybám v FS. SQLite z toho vyšel jako vítěz, na druhém místě PostgreSQL.

[okbobcz]

Tam děláte něco divného - import 10GB CSV by Postgres měl zvládnout do 10-20 minut - 24 hodin je úlet - tam muselo něco být hodně špatně.

Teoreticky si dovedu představit, že vám bloatnul index, ale ani to by vám nemělo prodloužit import na 24 hodin.

Jinak Postgres má docela velkou režii - je to multiuživatelská databáze - optimalizovaná na OLTP. Řádek v Postgresu má docela velkou hlavičku 21 bajtů.

Pokud máte indexy, tak je důležité, aby se podstatné části indexu udržely v RAM. Jakmile vám začnou vypadávat z cache, a musí se opakovaně načítat, tak se jakákoliv databáze brutálně zpomalí. Klasické doporučení říká, že RAM ku velikosti db by měla být 1/10 max 1/100. Takže na 30T db byste měli mít 3T RAM (v nejhorším případě 300GB)

[Xerces]

Hodně samozřejmě záleží na použitém HW, ale import 10GB za den to je málo i na sekretářku. Myslím, že když budete hledat, tak se vám to musí povést optimalizovat. Zkušenost z nedávné doby ... Nahrání cca 1,2TB z textových souborů na 8-mi jádru (SMT4) 2h včetně indexů. Ale nebyl to tedy PostgreSQL.

[elon]

Hlavny problem Postgresu bol v particiach.

 

Pouzivalili sme particie zalozene na LIST. Nieco ako

CREATE TABLE abcdefgh (id INTEGER, kluc VARCHAR(38), prefix VARCHAR(3), hodnota1 INTEGER, hodnota2 VARCHAR(64) ) PARTITION BY LIST(prefix);

CREATE TABLE abcdefgh_part_prefix PARTITION OF abcdefgh FOR VALUES IN ('prefix1');

 

Problem bol velky overhead na disku a potom pri importe CSV Postgres musel kazdy jeden riadok urobit:

- match na prefix (prefixov je cca 3700) aby zaznam zaradil do pozadovane particie

- prehladat zlozeny unique index stlpcov kluc+prefix aby sa vyhol duplicitam

 

Pri importe sme prepli tabulku do unlogged modu. Prinieslo to zrychlenie, avsak nerelevantne.

[Filip Jirsák]

Myslím, že je to jen otázka správného postupu při importu dat, případně vyladění databáze. Víc by určitě dokázal poradit Pavel Stěhule.

[okbobcz]

tam mohl dělat problém vyšší počet partitions - pro starší verze se doporučovaly tak nižší stovky partitions. a pak je potřeba asi mít dostatečně nadimenzovaný shared buffers - aby se gró toho indexu udrželo v shared_bufferu

[Xerces]

Doufám, že to nebude vypadat, jako že rýpu, ale můžete alespoň naznačit co je to za vědecká data, kde je primární klíč varchar(38)?

[a1bert]

hadam nejaka LGBT :D

[elon]

Velmi zjednodusene su to materialove tabulky s niekolkymi fyzikalno-chemickymi parametrami, ktore boli namerane alebo inak ziskane.

[Ovrscout]

Sqlite je obecně dost rychlé pokud neděláte nějaké fujky věci(což použití skrze NFS může být, s touto kombinací nemám zkušenost)

Ale než budete optimalizovat, nebo měnit technologii, tak by bylo dobré najít a změřit kde vlastně je problém. Aby jste nebil překvapen.

Pár bodíků pro začátek:

• Jaké jsou typy dotazů a jak dlouho trvají -> nejsou některé abnormálně pomalé? Kde vás trápí rychlost?
• Jaké jsou počty dotazů - dle typů dotazu - které se typicky posílá na jednu databázi (pro jednu úlohu, za minutu, atp).
• Jaký je souběh přístupu k všem databázím na NFS?
• Jaký je rozdíl v rychlosti při přístupu k databázi z NFS(za běžného vytížení ostatními i pokud běží jen jedna aplikace - pokud možno) a z lokálního disku?
• Na serveru, i clientu/ech zkontrolovat/doměřit vytížení sítě, CPU, Disku a RAM jestli někde nejste plně vytíženi.

Je to docela dost práce, ale mohlo by z toho vypadnout kde je problém. Případně to může vést k potřebě změřit/zkontrolovat další věci(indexy, parametrizované dotazy, velikost cache(včetně místa pro indexy ),sqlite temp soubory, ..., síť, atp.). Až pak bych zvažoval různá řešení od tuningu jednotlivých komponent až po změny v architektuře (třeba lokální cache?), a až nakonec případná změna DB.

[Ovrscout]

chjo, "Nebyl"

[elon]

Toto su pekne otazky na ktore by sme sa mali zamerat. Mate uplnu pravdu uzkym hrdlom je NFS a import data nez NFS. Dakujem za tipy.

 

Tieto otazky vychadzaju zo standardneho modelu databazovych systemov - Client/Server. Pouzivali sme tuto architekturu pri rieseni s Postgresql. No koli pomalosti sposobenynou velym mnozstvom particii zalozenych na PARTITION BY LIST(prefix) a UNIQUE contraint sme presli na SQLite. https://www.abclinuxu.cz/poradna/databaze/show/470578#19

V sucasnoti pouzivame SQLite databazy ako "kniznicu" pristupnu cez NFS s prisne strukturovanymi datami. Kolegovia vedia pracovat s SQLite databazami v read-only cez NFS share alebo si SQLite databazy nakopiruju k sebe na PC. Len administrator ma pravo zapisu/prepisu novych dat - prave koli tomu ze SQLite nie je vhodna na multi access.

 

Problem nastava, ked administrator dostane balik napr. 50-80GB surovych dat. Jedna sa o velky CSV subor. Musi data roztriedit podla prefixov (3700) a naimportovat data do jednotlivych SQLite databaz (3700).

Toto je asi najvacsi problem. Triedenie a importovanie podla prefixov.

 

Zatial to vyzera, ze najlepsim riesenim bude SSH pristup na server a import nebude robit cez NFS ale lokalne na servery. No nie som moc spokojny s tymto riesenim.

[Filip Jirsák]

Zatial to vyzera, ze najlepsim riesenim bude SSH pristup na server a import nebude robit cez NFS ale lokalne na servery. No nie som moc spokojny s tymto riesenim.

Já bych zkusil PostgreSQL. Odpadnou vám tím problémy se sdílením dat, protože je budete mít na jednom serveru, ke kterému se může připojit kdokoli.

[j]

Problem je, ze ty vic udaju neuvadis nez uvadis ...

+20TB za 4 roky = cca 5TB rocne => rekneme 25GB/den (pracovni). Pokud budu vychazet z tvyho udaje 20%, tak to znamena, ze se denne precte cca 100GB. Na to vazne netreba nijak uber HW ani SW. Ovsem tyhle cisla by sis mel realne zjistit a overit. Ve skutecnosti pak nesejde na GB, ale na mnoztvi dotazu.

Jak se s tema datama pracuje? Bezne v databazich byva 90+% dat, ke kterym se vubec nepristupuje a prave to mnoztvi dat se kteryma se realne pracuje ovlivnuje dimenzovani naprosto vseho.

Rekneme ze z kristalovy koule vyvestim, ze realne pracujes s desetinasobkem denni davky = 1TB dat => ramka serveru by mela byt aspon 1TB. To je ti ovsem nanic s sqlite.

U storage nehraje roli konektivita, ale IOps. To je limitujici faktor. NFS bych pak povazoval za ... hodne blbej napad, stejne jako jiny sitofilesystemy. Vkladas overhead tam, kde na to libovolna databaze je nejcitlivejsi = na store. Tam patri FC/iSCSI, pokud to neni lokalni uloziste.

---

Dete s tim guuglem dopice!

[elon]

Ano,

mate pravdu najvacsim problemom je NFS. Preto ideme vyskusat pristup cez SSH a triedenie/import/aktualizacia dat priamo na servery. s tym ze NFS zostane stale na citanie. Aj ked toto riesenie sa mi moc nepaci. Ale vyzera ze nateraz je to asi najpouzitelnejsie.

Stale hladame sposob/technologiu ako pracovat lepsie pracovat s tymito datami.

[EtDirloth]

Ako uz bolo napisane, tabulka per databazu, ci dokonca per cluster je pristup realizovatelny aj v postgrese. Potom cez FDW mozno vyriesit efektivny pristup k shardom.

• Vykon importu dat do pgsql sa da ladit roznymi sposobmi - od naznaceneho drop/create indexu pred a po importe, cez prvotny raw import do docasnej tabulky za vyuzitia server side COPY alebo psql \copy alebo cez file_fdw, nasledny import do cielovej tabulky vo forme zlozitejsieho insert selectu, a tiez kombinaciou vsetkych.
• Zo skusenosti s bottleneckmi pri importe - mohol byt nevhodne napisany UPSERT (on conflict...) namiesto selektivneho insertu iba novych dat a updatu iba existujucich, resp. pouzitie inych foriem pomalych constraintov; najhorsi pristup, co som kedy videl bol foreach v nejakej procke.
• HDD overhead cisto texty obsahujucej databazy oproti CSV je logicky: DB musi okrem hodnot ukladat aj dlzky hodnot, indexy (to si jeden proste nepomoze - ak je dovod mat index, tak to zerie disk) a samozrejme mrtve riadky kvoli MVCC a casom aj index bloat. Dalsim dovodom vsak mohla byt denormalizacia a nevhodne zvolene datove typy (varchar->int alebo ->jsonb).
• Za cenu vyssieho naroku na diskovu kapacitu, je mozne v zavislosti od dat a dotazov zvysit rychlost r/w pristupu cez vertikalny partitioning.
• S postgresom by sa vam lahsie vyuzili moznosti paralelneho spracovania dotazov.

To sqlite riesenie vsak vyzera elegantne a ak vam vyhovuje, tak nie je co riesit. Ak potrebujete dalej skalovat, potom zvazte dalsi level shardingu, kedy najpocetnejsie prefixy rozbijete na mensie tabulky, pricom datovu integritu zabezpecite na aplikacnej urovni pocas importu. Tiez sa da uplatnit (opat v zavislosti na povahe a pouziti dat) vertikalny partitioning - teda u vas skor vertikalny sharding => rozne stlpce toho isteho prefixu v roznych tabulkach - u vas sqlite databazach - spajanych cez datovo a vypoctovo nenarocne integer ID (na ktore si v dalsej tabulke/databaze prelozite ten hrozny VARCHAR(38)).

Ja by som import (t.j. write) riesil cez copy-on-write. Idealne cez spominane SSH. A to tak, ze by som importoval do kopie aktualnej verzie databazy a na NFS ju zverejnoval premenovanim (snapshot FS?). Nevyhodou by mohlo byt, ze pocas importu by sa priebezne zapisalo na disk viac nez raz tolko dat, ako tam uz bolo - takze by sa asi rychlejsie zodrali - ale tak disky su lacne :P

Ak je spracovanie (read) vzdy riesene cez download a lokalny pristup, tak by mozno stala za zvazenie p2p distribucia - cez lokalnu torrent siet.

Skoda, ze nevidime priklad dat a dotazov nad nimi. Mozno by sa ukazali cesty smerujuce k vyssim normalnym formam.

[Jеdna]

Kukni aj tohle.