Tabulka v MySQL (MariaDB)

Tabulka v MySQL (MariaDB)
« kdy: 03. 08. 2020, 16:23:23 »
Ahoj, kratce a strucne. Popis situace :
Tabulka MyISAM v MariaDB, jednoducha struktura, asi 10 sloupcu, zadne relace, nic jen ta tabulka.
Pocet zaznamu zhruba 9 milionu, velikost tabulky 12.5 GiB.
Do tabulky se insertuje prubezne v case, zhruba nekolik stovek tisic denne, v case plynule rozprostreno.
Obcas se delaji Selecty, hledani podle jednoho string sloupce, je zaindexovan.
Jednou denne probiha vymaz starych zaznamu, zaznamy starsi nez x dnu jsou deletovany.
Tabulka slouzi vlastne jako cache jistych dat, sbiranych v realnem case.
Funguje to v pohode, nicmene vymazem se to samozrejme fragmentuje, atd.
Zajimaji me nejake zajimave napady, jako by to kdo resil :-) Vic hlav vice vi....
« Poslední změna: 03. 08. 2020, 16:28:05 od honzouch »


Re:Tabulka v MySQL (MariaDB)
« Odpověď #1 kdy: 03. 08. 2020, 17:20:10 »
Ahoj, kratce a strucne. Popis situace :
Tabulka MyISAM v MariaDB, jednoducha struktura, asi 10 sloupcu, zadne relace, nic jen ta tabulka.
Pocet zaznamu zhruba 9 milionu, velikost tabulky 12.5 GiB.
Do tabulky se insertuje prubezne v case, zhruba nekolik stovek tisic denne, v case plynule rozprostreno.
Obcas se delaji Selecty, hledani podle jednoho string sloupce, je zaindexovan.
Jednou denne probiha vymaz starych zaznamu, zaznamy starsi nez x dnu jsou deletovany.
Tabulka slouzi vlastne jako cache jistych dat, sbiranych v realnem case.
Funguje to v pohode, nicmene vymazem se to samozrejme fragmentuje, atd.
Zajimaji me nejake zajimave napady, jako by to kdo resil :-) Vic hlav vice vi....

Přesně na tohle je super partitioning

https://mariadb.com/kb/en/partitioning-overview/

Re:Tabulka v MySQL (MariaDB)
« Odpověď #2 kdy: 03. 08. 2020, 17:50:57 »
Pouzit sloupcovou databazi, ty jsou primo stavene pro tento use case. Selekty nad miliardami zaznamu v realnem case nejsou problem. Vetsinou maji mnohem dokonalejsi komprimaci, nahravani byva mnohem rychlejsi, mazani po dnech doporuceny zpusob. Nektere maji i rozhrani z/do mysql.
« Poslední změna: 03. 08. 2020, 17:52:38 od A.P.Hacker »

Re:Tabulka v MySQL (MariaDB)
« Odpověď #3 kdy: 03. 08. 2020, 21:38:00 »
Pouzit sloupcovou databazi, ty jsou primo stavene pro tento use case. Selekty nad miliardami zaznamu v realnem case nejsou problem. Vetsinou maji mnohem dokonalejsi komprimaci, nahravani byva mnohem rychlejsi, mazani po dnech doporuceny zpusob. Nektere maji i rozhrani z/do mysql.

Jsou různé sloupcové databáze - nicméně mezi jejich silné stránky patří agregace nad větším balíkem dat, ale mohou mít pomalejší vyhledávání - právě díky komprimaci, která zlevňuje sekvenční načítání při hromadném zpracování, a naopak prodražuje přístup k nějakému konkrétnímu řádku. Pokud se nepoužije partitioning tak mazání, update záznamů je naopak velice pomalý - výrazně pomalejší než u řádkových databází. Takže hodně záleží na tom, co tazatel potřebuje a dělá, a také na konkrétní databázi - mezi sloupcovými databázemi jsou hodně velké rozdíly.

Re:Tabulka v MySQL (MariaDB)
« Odpověď #4 kdy: 03. 08. 2020, 21:54:33 »
dekuji panove za nazory...
prace s tabulkou je nasledujici. inserty probihajici prubezne v case. jednou za den delete starych zaznamu. obcasny select jednotlivych radku hledanych podle textoveho indexovaneho sloupce tridene dle casu. jedna se o jednotlive zaznamy, zadne operace nad tim se rozhodne neprovadeji. jeste doplneneni. vyhledava se vlastne podle jisteho identifikatoru “zarizeni”. tech je ted asi 15000. jednotlive radky jsou vlastne zaznamy o jejich vysilani...
« Poslední změna: 03. 08. 2020, 21:56:58 od honzouch »


Re:Tabulka v MySQL (MariaDB)
« Odpověď #5 kdy: 03. 08. 2020, 21:58:21 »
Pouzit sloupcovou databazi, ty jsou primo stavene pro tento use case. Selekty nad miliardami zaznamu v realnem case nejsou problem. Vetsinou maji mnohem dokonalejsi komprimaci, nahravani byva mnohem rychlejsi, mazani po dnech doporuceny zpusob. Nektere maji i rozhrani z/do mysql.

Jsou různé sloupcové databáze - nicméně mezi jejich silné stránky patří agregace nad větším balíkem dat, ale mohou mít pomalejší vyhledávání - právě díky komprimaci, která zlevňuje sekvenční načítání při hromadném zpracování, a naopak prodražuje přístup k nějakému konkrétnímu řádku. Pokud se nepoužije partitioning tak mazání, update záznamů je naopak velice pomalý - výrazně pomalejší než u řádkových databází. Takže hodně záleží na tom, co tazatel potřebuje a dělá, a také na konkrétní databázi - mezi sloupcovými databázemi jsou hodně velké rozdíly.

pochopil jsem dotaz tak, ze update zaznamu tazatel nepotrebuje, jen odmazavani starych zaznamu.

Re:Tabulka v MySQL (MariaDB)
« Odpověď #6 kdy: 04. 08. 2020, 06:58:16 »
Pouzit sloupcovou databazi, ty jsou primo stavene pro tento use case. Selekty nad miliardami zaznamu v realnem case nejsou problem. Vetsinou maji mnohem dokonalejsi komprimaci, nahravani byva mnohem rychlejsi, mazani po dnech doporuceny zpusob. Nektere maji i rozhrani z/do mysql.

Jsou různé sloupcové databáze - nicméně mezi jejich silné stránky patří agregace nad větším balíkem dat, ale mohou mít pomalejší vyhledávání - právě díky komprimaci, která zlevňuje sekvenční načítání při hromadném zpracování, a naopak prodražuje přístup k nějakému konkrétnímu řádku. Pokud se nepoužije partitioning tak mazání, update záznamů je naopak velice pomalý - výrazně pomalejší než u řádkových databází. Takže hodně záleží na tom, co tazatel potřebuje a dělá, a také na konkrétní databázi - mezi sloupcovými databázemi jsou hodně velké rozdíly.

pochopil jsem dotaz tak, ze update zaznamu tazatel nepotrebuje, jen odmazavani starych zaznamu.

Jednak tam byl nástin seeku, který sloupcovým databázím nemusí být úplně po chuti (díky větší velikosti bloku), a i delete bývá na sloupcových databázích problém (pokud se neřeší partitioningem). Možná jste nemyslel sloupcové databáze, ale time series databáze - https://en.wikipedia.org/wiki/Time_series_database

Re:Tabulka v MySQL (MariaDB)
« Odpověď #7 kdy: 04. 08. 2020, 14:34:48 »
Pouzit sloupcovou databazi, ty jsou primo stavene pro tento use case. Selekty nad miliardami zaznamu v realnem case nejsou problem. Vetsinou maji mnohem dokonalejsi komprimaci, nahravani byva mnohem rychlejsi, mazani po dnech doporuceny zpusob. Nektere maji i rozhrani z/do mysql.

Jsou různé sloupcové databáze - nicméně mezi jejich silné stránky patří agregace nad větším balíkem dat, ale mohou mít pomalejší vyhledávání - právě díky komprimaci, která zlevňuje sekvenční načítání při hromadném zpracování, a naopak prodražuje přístup k nějakému konkrétnímu řádku. Pokud se nepoužije partitioning tak mazání, update záznamů je naopak velice pomalý - výrazně pomalejší než u řádkových databází. Takže hodně záleží na tom, co tazatel potřebuje a dělá, a také na konkrétní databázi - mezi sloupcovými databázemi jsou hodně velké rozdíly.

pochopil jsem dotaz tak, ze update zaznamu tazatel nepotrebuje, jen odmazavani starych zaznamu.

Jednak tam byl nástin seeku, který sloupcovým databázím nemusí být úplně po chuti (díky větší velikosti bloku), a i delete bývá na sloupcových databázích problém (pokud se neřeší partitioningem). Možná jste nemyslel sloupcové databáze, ale time series databáze - https://en.wikipedia.org/wiki/Time_series_database

prave mazani po celych dnech partioningem resit lze, ale diky mnohem mensim narokum na misto muzete mazat i po delsich intervalech.

zajimal by me konkretni selekt nad jednou tabulkou, ktery je v libovolne radkove databazi rychlejsi nez napr. v clickhouse, podle me budete mit problem jen vytvorit tak velkou tabulku v radkove databazi na beznem HW, na ktere by se sloupcova databaze zapotila.

Re:Tabulka v MySQL (MariaDB)
« Odpověď #8 kdy: 04. 08. 2020, 15:51:27 »


prave mazani po celych dnech partioningem resit lze, ale diky mnohem mensim narokum na misto muzete mazat i po delsich intervalech.

zajimal by me konkretni selekt nad jednou tabulkou, ktery je v libovolne radkove databazi rychlejsi nez napr. v clickhouse, podle me budete mit problem jen vytvorit tak velkou tabulku v radkove databazi na beznem HW, na ktere by se sloupcova databaze zapotila.

SELECT * FROM tab WHERE id = 29292;

a id bude nad oindexovanym sloupcem s mene nez 1% vyskytu, a tabulka bude mit 20-30 sloupcu.

Jakmile byste zacal vic joinovat, a stale by se vyplatil nested loop, tak by se to sloupcovym databazim moc nelibilo. Mezi sloupcovymi databazemi jsou dost velke rozdily - ale pokud bych vzal ty, ktere maji podporu SQL - Vertika, Vectorwise, Monet, .. tak jsou optimalizovane pro OLAP ulohy nad denormalizovanym star schema, snowflake schema modelem. Tam funguji spickove, pro OLTP, a OLTP obvykle dotazy mohou byt pomalejsi - resp. to co je vyhodou pro OLAP je nevyhodou pro OLTP.

Re:Tabulka v MySQL (MariaDB)
« Odpověď #9 kdy: 04. 08. 2020, 16:11:15 »


prave mazani po celych dnech partioningem resit lze, ale diky mnohem mensim narokum na misto muzete mazat i po delsich intervalech.

zajimal by me konkretni selekt nad jednou tabulkou, ktery je v libovolne radkove databazi rychlejsi nez napr. v clickhouse, podle me budete mit problem jen vytvorit tak velkou tabulku v radkove databazi na beznem HW, na ktere by se sloupcova databaze zapotila.

SELECT * FROM tab WHERE id = 29292;

a id bude nad oindexovanym sloupcem s mene nez 1% vyskytu, a tabulka bude mit 20-30 sloupcu.

Jakmile byste zacal vic joinovat, a stale by se vyplatil nested loop, tak by se to sloupcovym databazim moc nelibilo. Mezi sloupcovymi databazemi jsou dost velke rozdily - ale pokud bych vzal ty, ktere maji podporu SQL - Vertika, Vectorwise, Monet, .. tak jsou optimalizovane pro OLAP ulohy nad denormalizovanym star schema, snowflake schema modelem. Tam funguji spickove, pro OLTP, a OLTP obvykle dotazy mohou byt pomalejsi - resp. to co je vyhodou pro OLAP je nevyhodou pro OLTP.

selekt vsech sloupcu podle primarniho klice je rychly. Primarni klice jdou vyselektovat poddotazem nad materializovanym pohledem s tim neprimarnim indexem id. Asi se to lisi v ruznych DB. Neni to uplne primocare, ale podle me stale rychlejsi a uspornejsi na prostor nez v radkovych DB.

Re:Tabulka v MySQL (MariaDB)
« Odpověď #10 kdy: 04. 08. 2020, 16:30:33 »

selekt vsech sloupcu podle primarniho klice je rychly. Primarni klice jdou vyselektovat poddotazem nad materializovanym pohledem s tim neprimarnim indexem id. Asi se to lisi v ruznych DB. Neni to uplne primocare, ale podle me stale rychlejsi a uspornejsi na prostor nez v radkovych DB.

To nemůže být z principu - u tohoto typu dotazu prostě sloupcová databáze musí udělat víc operací než řádková databáze - bez ohledu na skutečnost, že sloupcové databáze dost často mají větší velikost stránky. A pokud uděláte materializovaný pohled (projekci ve Vertice), tak jste si defacto vyrobil řádkovou verzi vašich dat, za kterou samozřejmě zaplatíte při aktualizacích (prostor na disku, IO operace). Neexistuje formát (způsob) uložení dat, který by byl efektivní pro hromadné operace i pro operace nad jedním řádkem. Stonebraker (jeden z autorů Postgresu a Cstore (prototyp Vertiky)) dokazoval, že takový formát existovat nemůže.

Re:Tabulka v MySQL (MariaDB)
« Odpověď #11 kdy: 04. 08. 2020, 16:53:53 »

selekt vsech sloupcu podle primarniho klice je rychly. Primarni klice jdou vyselektovat poddotazem nad materializovanym pohledem s tim neprimarnim indexem id. Asi se to lisi v ruznych DB. Neni to uplne primocare, ale podle me stale rychlejsi a uspornejsi na prostor nez v radkovych DB.

To nemůže být z principu - u tohoto typu dotazu prostě sloupcová databáze musí udělat víc operací než řádková databáze - bez ohledu na skutečnost, že sloupcové databáze dost často mají větší velikost stránky. A pokud uděláte materializovaný pohled (projekci ve Vertice), tak jste si defacto vyrobil řádkovou verzi vašich dat, za kterou samozřejmě zaplatíte při aktualizacích (prostor na disku, IO operace). Neexistuje formát (způsob) uložení dat, který by byl efektivní pro hromadné operace i pro operace nad jedním řádkem. Stonebraker (jeden z autorů Postgresu a Cstore (prototyp Vertiky)) dokazoval, že takový formát existovat nemůže.

i tech nekolik operaci byva v souctu rychlejsi, prave diky dokonalejsi komprimaci vetsinou pracujete s mensim mnozstvim dat.

Vertika neni uplne nejlepsi priklad state of the art sloupcove databaze https://clickhouse.tech/benchmark/dbms/

prezentace, ktera vyvraci to tvrzeni Michaela Stonebrakera https://www.percona.com/sites/default/files/ple19-slides/day1-pm/clickhouse-for-timeseries.pdf
« Poslední změna: 04. 08. 2020, 16:55:27 od A.P.Hacker »

Re:Tabulka v MySQL (MariaDB)
« Odpověď #12 kdy: 04. 08. 2020, 18:28:03 »

i tech nekolik operaci byva v souctu rychlejsi, prave diky dokonalejsi komprimaci vetsinou pracujete s mensim mnozstvim dat.

Vertika neni uplne nejlepsi priklad state of the art sloupcove databaze https://clickhouse.tech/benchmark/dbms/

prezentace, ktera vyvraci to tvrzeni Michaela Stonebrakera https://www.percona.com/sites/default/files/ple19-slides/day1-pm/clickhouse-for-timeseries.pdf

V tom dotazu, který jsem tady uvedl, načítáte jednu datovou stránku a několik málo stránek indexu. Tam Vám komprimace nemůže pomoct. Tu prezentaci jsem si prohlédl, a nikde tam nevidím nic, co by vyvracelo moje předchozí tvrzení. V těch benchmarkách se mluví o time series dotazech, a když jsou uvedeny, tak jsou tam agregace nebo window funkce. Jakmile začnete agregovat, tak je to něco jiného. Tam mají sloupcové databáze neskutečnou výhodu - a to nejen v komprimaci - i kdybyste měl data v paměti, tak agregace nad sloupcemi se dá udělat výrazně rychlejší. Ale pokud tam nemáte agregaci, tak tam není důvod, proč by to mělo být rychlejší - když načítám pár záznamů z indexu.