Databáze - 300 GB tabulka

[PanVP]

@Krčmář: Mohl bys to rozbité fórum spravit. Člověk si nedá pozor a odhlásí ho to...

Pokud jsou tam indexy, zkoušel jste je před nalitím dat vypnout a po nalití opět zapnout?

Tak jsem si to myslel správně:

Note: When you insert into an empty table with INSERT or LOAD DATA, MariaDB automatically does an DISABLE KEYS before and an ENABLE KEYS afterwards.

A třeba to dělám úplně špatně:

Nejprve provedu:
SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;
SET @@global.innodb_autoinc_lock_mode = 2;

A samozřejmě import obalím:
SET autocommit=0;
... SQL import statements ...
COMMIT;

A pak:
LOAD DATA LOCAL INFILE 'file_name' INTO TABLE table_name;

Čímž jsem skončil, protože zhruba ve chvíli, kdy se tabulka nevejde do paměti, tak to lehne...

Nejsem odborník na databáze, takže netvrdím, že to "JE" chyba na straně MáňaDB.
Když se někdo jebne kladivem do ruky, není to chyba kladiva.
Ale ...co udělat líp??

[Reklama]

[Peter Humaj]

Zdravim, pouzivame PostgreSQL na archivaciu hodnot procesnych dat (cize v podstate nieco ako casove rady).
Nastavenych je iba par parametrov tykajucich sa pamate.
Jedna z velkych aplikacii: data su archivovane s definovanou hlbkou archivacie (per objekt), ale zaroven sa ukladaju aj data, ktore sa nemazu - mesacne "casove rezy", v ramci mesiaca vznika u tohto zakaznika nie 1 ale 5 tabuliek v 5 databazach (ale to je technikalita). V maji 2018 vzniklo v tych 5 tabulkach 135 GB dat, vid blog
https://www.ipesoft.com/sk/blog/archiv-a-postgresql-trezory

Tabulky maju par stlpcov (ID objektu, hodnota, nejake priznaky, casova znacka).
Momentalne tam je cca 14 TB dat (od r. 2006). Citanie je vzdy s vyuzitim indexu (ID konkr. procesnej veliciny + cas). Standardne disky, ziadne SSD (aj ked aj to sa chysta pri najblizsej migracii na nove zelezo).
Tieto data boli povodne v Oracle databaze, v 2017-2018 premigrovane do PostgreSQL, odvtedy to bezi tam.
(https://www.ipesoft.com/sk/blog/migracia-trezorov-na-postgresql-v-praxi)
V podstate bez problemov a bezudrzbovo.

Takze: zapis prebieha stale 24/7/365. Citanie na ziadost uzivatelov (obcas si stazuju, ale v zasade je to interaktivne..).

[PanVP]

Děkuju!
Přesně to jsem potřeboval!

[Filip Jirsák]

Problém je, že import obsahuje až 60-90% už existujících dat.
Tj. bulk import v podstatě vytváří pro každý insert nejprve select, jestli je možné řádek vložit.
Do nějakých větších akcí s optimalizacemi jsem se nepouštěl, protože mi to zdechalo už okolo desítek GB importu.
Ale třeba to dělám vážně špatně, osobně se rozhodně nepovažuji za specialistu na databáze.

Možná bych měl nejprve menší tabulku naimportovat do nějaké "pracovní tabulky", možná memorytable (?), pak vyhodit všechny duplicity a pak provést insert do hlavní tabulky...hm(?)....?

Přesně tak. Neimportujte všechna data do pomocné tabulky a teprve pak odstraňujte duplicity. 300 GB asi do memory tabulky nedostanete, ale to nevadí.

Nejsem odborník na databáze, takže netvrdím, že to "JE" chyba na straně MáňaDB.
Když se někdo jebne kladivem do ruky, není to chyba kladiva.
Ale ...co udělat líp??

Myslel jsem, že se bavíme o PostgreSQL. Do MariaDB/MySQL bych se takový objem dat nepokoušel nacpat. Asi to jde, používá se i na velkých projektech, ale pak to asi znamená, že to spravuje někdo, kdo se v MariaDB/MySQL fakt vyzná.

[Exceptions]

problém bych neřekl, že je v použité databázi, ale ve způsobu jak s ní pracuješ, jak s mariadb, tak i pgsql je možné mít tabulky i k TB, ale už to chce odejít z výchozího nastavení.

Pg umí od 9.x insert on conflict do nothing, stejně tak mariadb umí insert on duplicate key. V kombinaci s prepared statements a bulkem pro třeba 5000 values, zvedneš rychlost ukládání dat do db o dva řády (tj. cca 20000 insertů za vteřinu na jediné instanci při 10 paralelních dotazech a ve výchozím nastavení). Pg umí i dobře komprinovat data, takže ti ušetří místo na disku. Na webu najdeš řadu kalkulaček pro nastavení db podle množství dat a druhu práce.

Zkus ClickHouse, tenhle úkol by mohl zvládat ve výchozím nastavení a poměrně rychle, má pokročilé metody komprese dat a můžeš ušetřit i 80 % původní velikosti.

Pokud nepotřebuješ přímo databázi, mrkni třeba na formát dat Apache Parquet, dostupný je z řady programovacích jazyků, data umí velice efektivně deduplikovat, komprimovat. Rychle s tím lze pracovat třeba přes spark (python, scala, r), za pár desítek minut na středně silném stroji to máš uložené a zpracované.

Další možnost je jít do cloudu, pronajmout si na pár desítek minut nějakou databázi, tam data zpracovat a pak si je stáhnout zpracovaný a deduplikovaný, pokud teda to chceš jednorázově.

[Reklama]

[alex6bbc]

proc se asi btrfs takto jmenuje, protoze ma b-tree index.
takze proc by neslo pouzit fs a metadata v db.
vidim chybu v navrhu.

[PanVP]

Myslel jsem, že se bavíme o PostgreSQL. Do MariaDB/MySQL bych se takový objem dat nepokoušel nacpat.

Tak to jsem pochopil opačně, jako že mám dát MariaDB ještě šanci
Totiž, mně by MariaDB vlastně vyhovovala, skutečně jí znám.

Ale už jsem na ní provařil dost času, tak to zkusím s PGSQL.

Budu na to myslet, díky.

[PanVP]

Takže co s tím, ať se to trochu hejbe?

A)Projít optimalizace PGSQL pro bulk importy
https://www.2ndquadrant.com/en/blog/7-best-practice-tips-for-postgresql-bulk-data-loading/

B)Základní "mega" tabulku naprdět do PGSQL přes COPY nebo dbcrossbar
http://www.dbcrossbar.org/

C) Vytvořit RAMDISK (30 GB pro začátek)
https://www.linuxbabe.com/command-line/create-ramdisk-linux
https://www.techrepublic.com/article/how-to-use-a-ramdisk-on-linux/

(S tím jsem se už kdysi drbal a musel jsem kvůli tomu sestavovat jádro, protože tam byl nějaký limit na 4MB nebo co. Snad už to teď nebude potřeba.)

D) Vytvořit TABLESPACE přes nově vytvořený RAMDISK
https://www.postgresql.org/docs/9.1/manage-ag-tablespaces.html

E) Založit uvnitř tabulku s parametrem UNLOGGED
https://www.postgresql.org/docs/9.1/sql-createtable.html#AEN67445

F) Do UNLOGGED tabulky nacpat import (jeho část rozšiřuje hlavní tabulku).

G) Promazat tuhle pracovní tabulku, což zmenší počet záznamů na 10 až 40% původní velikosti

H) Exportovat jí nebo se jí pokusit rovnou dostat do hlavní tabulky.
Fakticky bych asi dokázal data spojit i na disku.

I) Dropnout pracovní tabulku a pokračovat další dávkou.

Zapomněl jsem na něco, co by mohlo mít zásadní vliv na rychlost zpracování?

BTW...vyčlenil jsem si na to 4H...jako že "to dám"...chudák já...

[Filip Jirsák]

Já bych se pro začátek vykašlal na ten RAM disk. Připadá mi to jako předčasná optimalizace. Teprve kdybyste viděl, že to pořád drhne, dává smysl se jím zabývat.

[PanVP]

@Fórum je hrozně rozjebaný....zase mi to smázlo post

vykašlal na ten RAM disk

Teda ne že bys neměl asi pravdu, ale když už mám teda řídit Černobyl, tak si chci zmáčknout všechna tlačítka ne 

Jinak právě kvůli těmto čarodějným praktikám přibyl "TEMPORARY TABLESPACE".
Jeho použití vypadá celkem bezpečně:
https://blog.dbi-services.com/about-temp_tablespaces-in-postgresql/

https://blog.dbi-services.com/can-i-put-my-temporary-tablespaces-on-a-ram-disk-with-postgresql/

A líbí se mi na tom i to, že by se nemusel vytvářet žádný historický hnůj...

[Filip Jirsák]

@Fórum je hrozně rozjebaný....zase mi to smázlo post

Zdejší fórum běží na nějaké staré verzi SMF. Nemyslím si, že by fórum někdo opravil, takže zbývá jen možnost naučit se s tím žít a nepsat příspěvky jako nepřihlášený uživatel.

Teda ne že bys neměl asi pravdu, ale když už mám teda řídit Černobyl, tak si chci zmáčknout všechna tlačítka ne 

OK :-)

Jinak právě kvůli těmto čarodějným praktikám přibyl "TEMPORARY TABLESPACE".
Jeho použití vypadá celkem bezpečně:
https://blog.dbi-services.com/about-temp_tablespaces-in-postgresql/

https://blog.dbi-services.com/can-i-put-my-temporary-tablespaces-on-a-ram-disk-with-postgresql/

A líbí se mi na tom i to, že by se nemusel vytvářet žádný historický hnůj...

PostgreSQL je dost schopná databáze, akorát člověk občas musí prolézat různá zákoutí, aby našel, co potřebuje. Ten objem dat, co potřebujete zpracovat, není nijak závratný – takže by mi přišlo divné, kdyby to PostgreSQL nezvládl.

[Idris]

Z relačních databází mám zkušenosti jen s Postgresem, ten by to dát měl (jak už psali jiní výše). Ale nestačila by nějaká KV databáze? Ty bývají rychlé a bez zádrhelů, když nejsou potřeba indexy. Chtělo by jich to ale vyzkoušet víc, třeba s Tokyem mám špatné zkušenosti ("korupce"), jiní hlásí to samé s LevelDB. KV databáze by také efektivně vyřešila problém s duplicitami.

[Logik]

K fóru: napsanou odpověď, když Vás to odlásí, jde dostat z konzole browseru.(Otevřete ji, jdete na záložku network, dáte reload...)Akorát pak chce ve výslednejch datech requestu vyházet HTML značky....

[redustin]

Nebo něco jako Form History addon Firefoxu.

[Standa Blábol]

Mno, uplne sice nechapu zadani, lec pokud je potreba pracovat s time-series daty, doporucil bych time-series databazi.
Konkretne v postgresu velice schopna extenze TimescaleDB, kdera podporuje i komprimaci

Jinak co se tyce mych zkusenosti s Postgresem, je nutno mit na mysli, ze je to databaze transakcni.
Mnoho podivnych brzd v Postgresu uz vyresilo nastrkani commitu, kde je vsude mozno logicky strcit.