Nevýhody InnoDB oproti MyISAM v MariaDB

[Wasper]

Já ACID nepotřebuji. Proto jsem tak dlouho zůstal u MyIsam. A nyní se ukazuje, že InnoDB umí velkou část těch dobrých  věcí jen za cenu podstatné snížení rychlosti zápisů. Ale naštěstí to jde vypnout a tím tuto  špatně dokumentovanou nevýhodu innodB  (pomalost zápisů) eliminovat. Všichni ti co píší o výhodách InnoDB zapomínají zmínit, že je to  za cenu pomalejších zápisů. Ale lze najít vhodný kompromis nastavením.

Uprimne, tohle plne plyne z teorie.

Z vlastností innoDB potřebuji jen to, aby zápisy nečekaly na dokončení předchozího čtení a následující čtení zase na dokončení předchozích zápisů. Zápisy jsou obecně rychlé, ale v myIsam čekají na dokončení předchozího čtení. Tedy jeden náročnější select dovede zamčít celou tabulku pro všechny operace, pokud se v jeho průběhu vyskytne nějaký zápis.

Pak kouknete sem, tohle Vam muze dost pomoct.
https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html

[Reklama]

[Exceptions]

Je rychlost zápisu velmi podobná jako u myISAM.  Tedy další špatně dokumentovaná nevýhoda innoDB je, že defaultní nastavení je mnohonásobně pomalejší pro zápis, což je ale možné vyladit vhodným nastavením.

Nezlobte se, ale to není ani nevýhoda, ani špatně dokumentovaná. Možná před pokusy byste si měl přečíst alespoň tohle https://dev.mysql.com/doc/refman/8.4/en/mysql-acid.html
To, co jste udělal je, že jste v podstatě vypnul integritu nad databází, kterou InnoDB nabízí (přesnější popis najdete v dokumentaci).

Asi něco (velmi volný příklad) podobného, jako kdybyste u diskového pole zapnul zápisovou cache bez baterky, a stěžoval si, že pole bez toho zapisuje pomalu.

Já ACID nepotřebuji. Proto jsem tak dlouho zůstal u MyIsam. A nyní se ukazuje, že InnoDB umí velkou část těch dobrých  věcí jen za cenu podstatné snížení rychlosti zápisů. Ale naštěstí to jde vypnout a tím tuto  špatně dokumentovanou nevýhodu innodB  (pomalost zápisů) eliminovat. Všichni ti co píší o výhodách InnoDB zapomínají zmínit, že je to  za cenu pomalejších zápisů. Ale lze najít vhodný kompromis nastavením.
Z vlastností innoDB potřebuji jen to, aby zápisy nečekaly na dokončení předchozího čtení a následující čtení zase na dokončení předchozích zápisů. Zápisy jsou obecně rychlé, ale v myIsam čekají na dokončení předchozího čtení. Tedy jeden náročnější select dovede zamčít celou tabulku pro všechny operace, pokud se v jeho průběhu vyskytne nějaký zápis.

nevím, proč máš opakovaný problém poukazovat na špatnou dokumentovanost, naopak je to dokumentované poměrně jasně.

Pokud používáš ext, mrkni ještě na O_DIRECT_NO_FSYNC, zrychlí ti to zápis.

Mysli na to, že všechny tyhle nastavení ti snižují spolehlivost ukládaných dat, pokud se něco rozbije, musíš tabulku opravovat, to může způsobit velké výpadky, ztráty dat.

Cest jak celý innodb engine udělat velmi rychlý je mnoho a nemusí se vypínat věci, které snižují spolehlivost.

[xsouku04]

Mám ZFS, takže některé ochranné prvky, které dělá innoDB jsou zbytečné, protože jsou dělány na úrovni souborového systému.
Zatím innoDB používám jen na replice hlavní databáze.  Replik je několik.   Tedy věci jako ACID a ochranu v případě výpadku elektřiny nepotřebuji, protože v nejhorším případě si repliku nahodím znovu.
Ale potřebuji maximální rychlost zápisu a malé opotřebení SSD disků. Rychlé čtení se také hodí.
A hlavně aby čtení neblokovalo zápis a zápis čtení jako je to u MyIsam. Žádné další "výhody" innoDB nepotřebuji, protože zdržují a opotřebovávají disky a pro můj use case nejsou potřeba.

Pro hlavní databázi, až budu dělat přechod na innoDB bude nejspíše to stejné jen s tím rozdílem, že v případě výpadku elektřiny nechci aby se databáze úplně rozbila, ale nevadí mi, že přijdu např. o posledních 10 sekund zápisů.
Ale potřebuji, aby tabulky byly v použitelném stavu po nastartování se mohlo pokračovat. A já nemusel databázi startovat z několik minut starého snapshotu nad ZFS, protože z dat hlavní databáze se stala neopravitelná fašírka.


Je spousta použití databází, kdy ACID není středobod. Ale dobrý výkon a rychlost, nízké opotřebení a možnost paralelního čtení a zápisů ano. Bohužel k takovému použití jsem moc dokumentace nenašel.  Možná nezbude než experimentovat. Vytvořit si repliku, které občas "vypnout proud" a zkoumat stav databáze.  Jestli se občas stane, že z dat zbude jen nepoužitelné fašírka nebo nikoli. Nebo tabulky budou vyžadovat opravy v řádech hodin nebo dokonce dnů. Ztrát některých zápisů v poslední minutě před vypnutím elektřiny mne netrápí.

[Exceptions]

tak, pokud data nepošleš na disk, těžko ti pomůže zfs .

Kde ty dokumentace vůbec hledáš? Vždyť za ty roky má innodb popsané snad všechny varianty.

Pokud chceš, aby ti zápis neblokoval čtení, tak hledej věci jako dirty ready (READ UNCOMMITTED v innodb).

Z tvých experimentů a nastavení mám špatný pocit, vypadá to, že prostě nevíš co děláš. Např. innodb_doublewrite=0 je skvělé, ale v takovém případě musíš na zfs nastavit innodb_log_write_ahead_size na ideálně dvojnásobek page_size, aby nedošlo k částečnému zápisu. Nastavit innodb_buffer_pool_size na 40G je šílenost.

[Wasper]

A já nemusel databázi startovat z několik minut starého snapshotu nad ZFS, protože z dat hlavní databáze se stala neopravitelná fašírka.

Nechci býti kverulant, ale to, co s daným nastavením bude na tom ZFS snapshotu patrně bude neopravitelná fašírka taky, jen o 10 minut starší.

Připojuji se k předřečníkovi - fakt tušíte, co děláte?

[Reklama]

[Standa Blábol]

A když už do toho hrabete, nechcete to předělat rovnou na postgresql?

O tom jsem přemýšlel. Ale pokud vím postgreSQL vyniká hlavně  v konzistenci a pokročilém použití. Já spíše potřebuji jen hrubou rychlost a funkčnost 24/7 na což postgreSQL zas tak dobrý není. Pokud by se mi ztratil náhodně jeden záznam z milionu, netrápí mne to. Stejně tak mne netrápí kdybych např. při výpadku elektřiny (počítám že by se mohlo přihodit jednou za 10 let) přišel třeba o 20 vteřin dat. Naopak kdybych musel někdy v noci opravovat či optimalizovat nějakou tabulku třeba půl hodiny a proto ji uzamčít, je to velký problém.
A taky se mi nechce učit nic nového, když vlastně nevím jestli bych si vůbec polepšil. Spíše nikoli.

Od MyISam odcházím z toho důvodu, že zápisy blokují následující čtení.    Tedy pokud bych měl dva delší selecty, mohou probíhat paralelně bez problémů, pokud mezi nimi nebyl žádný zápis. Ale pokud v okamžiku delšího selectu přijde zápis, tak ten čeká na dokončení selectu. A následující select zase čeká na dokončení toho zápisu, takže se to celé ucpe jen kvůli jednomu delšímu selectu.  To by mělo innodb vyřešit.  Pokud by ale existovala možnost nastavit u MyIsam, že má zápis menší prioritu než čtení a zápis by čekal až na okamžik, kdy není žádný požadavek na čtení, mohli bychom dále používat myIsam. Zápis je totiž  v našem případě vždy rychlý. Ale bohužel změnit tu prioritu se mi nepovedlo.

Mno, nebylo receno o jaka data jde, lec kdyz je rec o mozne ztrate 20 vterin dat, asi pujde o nejaky time-series.
A na to je v postgresu extenze TimescaleDB, ktera ma luxusni vlastnosti vcetne automatickeho komprimovani time-series dat, vysledek je ze zabere na disku cca 10% mista. Resi problematiku ruchleho zapisu.
K tomu patroni cluster a prohledavani dat a backup resit na online replice.

[xsouku04]

Mno, nebylo receno o jaka data jde, lec kdyz je rec o mozne ztrate 20 vterin dat, asi pujde o nejaky time-series.
A na to je v postgresu extenze TimescaleDB, ktera ma luxusni vlastnosti vcetne automatickeho komprimovani time-series dat, vysledek je ze zabere na disku cca 10% mista. Resi problematiku ruchleho zapisu.
K tomu patroni cluster a prohledavani dat a backup resit na online replice.

Jedná se o telefonní hovory. TimesclaDB vypadá, že by  mělo jít použít i když to není úplně typický případ a nenašel jsem žádný příklad, že by to někdo používal na ukládání hovorů.  Každý hovor má hodně atributů. Nejdůležitější je čas, linka, uživatel, volající, volané číslo, délka, délka zvonění,destinace, cena. A spousta dalších méně důležitých atributů.

Asi by to chtělo ze začátku ukládat tím starým způsobem a navíc i do TimescaleDB. Mohu tam také zkušebně nalít ty data zkusit jestli se operace výrazně zrychlí. V každém případě by to byl dost velký zásah v porovnání jen se změnou z MyIsam na InnoDB.

[Pavel Stěhule]

Mám ZFS, takže některé ochranné prvky, které dělá innoDB jsou zbytečné, protože jsou dělány na úrovni souborového systému.

ZFS je dost pomalý filesystém - a při velké zátěži jsou s ním s databázemi problémy. MyISAM neřeší konzistenci - zápisy končí ve filesystémové cache - a v případě havárie můžete příjít o data z filesystémové cache - což vzhledem k primitivnímu formátu nemusí být problém - můžete mít ale nakopnutý index, a to už problém být může. InnoDB má mnohem složitější formát a pokud by došlo ke ztrátě konzistence, tak to není jen o ztrátě pár záznamů. ZFS vám pomůže jen v tom, že si můžete udělat snapshot a vrátit se k nějakému snapshotu. Pokud by se ale rozbila konzistence v MySQL, tak je vám ZFS na dvě věci. InnoDB má na rozdíl od MyISAM vlastní write cache - o kterou v případě havárie můžete přijít, a tak musíte mít konzistentní transakční logy. Pokud byste je neměl, tak to, že přijdete o pár záznamů je to nejmenší - můžete mít nakopnutý index, což rozbije vyhledávání - unikátní index nemusí být unikátní, atd atd. MyISAM je do jisté míry "inmemory db", takže některé operace mohou být velice rychlé. Má velice primitivní formát - který je relativně odolný vůči chybám. InnoDB funguje už úplně jinak, a případné chyby konzistence v InnoDB mají mnohem horší dopad.

[Standa Blábol]

Mno, nebylo receno o jaka data jde, lec kdyz je rec o mozne ztrate 20 vterin dat, asi pujde o nejaky time-series.
A na to je v postgresu extenze TimescaleDB, ktera ma luxusni vlastnosti vcetne automatickeho komprimovani time-series dat, vysledek je ze zabere na disku cca 10% mista. Resi problematiku ruchleho zapisu.
K tomu patroni cluster a prohledavani dat a backup resit na online replice.

Jedná se o telefonní hovory. TimesclaDB vypadá, že by  mělo jít použít i když to není úplně typický případ a nenašel jsem žádný příklad, že by to někdo používal na ukládání hovorů.  Každý hovor má hodně atributů. Nejdůležitější je čas, linka, uživatel, volající, volané číslo, délka, délka zvonění,destinace, cena. A spousta dalších méně důležitých atributů.

Asi by to chtělo ze začátku ukládat tím starým způsobem a navíc i do TimescaleDB. Mohu tam také zkušebně nalít ty data zkusit jestli se operace výrazně zrychlí. V každém případě by to byl dost velký zásah v porovnání jen se změnou z MyIsam na InnoDB.

jednodusene receno, timescale je parttitioning na steroidech. Pokud mas atribut, podle ktereho to muze data rozsekat na chunky, typicky cas, a pokud tcoje dotazy typicky dotazuji pres casova okna, hodne ti to pomuze.

PoC mas hotovy za den, pgloaderem natahej mysql data vcetne komplet steuktury do postgresu, ty velke datove tabulky zkonvertujj na timescale hypertabulky s kompresi treba pro tyden stare chunky. Co zkomprimujes uz neupdatujes, to ale v tvym usecase nevadi.
Hylertabulka je z pohledu klienta pro CRUD transparentni.
Hlavni prinos je hlavne v tom chunkovani, postgres pracuje jenom s prave potrebnymi chunky a i mazani starych dat se dela zahazovanim chunku bez nutnosri transakcniho delete.
 A pri instalaci nezapomen na timescaledb-tune, tahle utilitka ti poladi konfigurak postgresu

[Standa Blábol]

Jo a postgresa instaluj z postgresql.org, anzto tuto verzi podporuji originalni timescale baliky
V distre h byva postgres i timescale, ale jenom v pure OSS variante, ktera je orezana a neumi komprimaci. Potrebujes community versi, to je mezistupen mezi pure OSS a komercni variantou

[Standa Blábol]

Jo a nezapomen pak i na to patroni, kde bude leader urcen ciste pro zapisy a cteni backup dat apod na replikach.
jako bonus k tomu dostanes HA setup.

Pomoci haproxy nebo pgbounceru s externim chckem leadera si vystavis port pro zapis na 6543, port pro ciste cteni z repliky na 7432 a tvoje aplikace nemusi zadne prepinani nijak resit, staci aby umely reconnect spadle konexe

[xsouku04]

tak, pokud data nepošleš na disk, těžko ti pomůže zfs .

Např. na ZFS mohu vypnout double write, protože zfs je copy on write a tak se nemůže stát, že něco se zapíše jen napůl.
Také mohu vypnout checksumy.

Kde ty dokumentace vůbec hledáš? Vždyť za ty roky má innodb popsané snad všechny varianty.
Pokud chceš, aby ti zápis neblokoval čtení, tak hledej věci jako dirty ready (READ UNCOMMITTED v innodb).

Mě je ale úplně jedno co mi vrátí select. Tedy jestliže nejnovější zápisy v poslední vteřině vynechá nebo nikoli, nebo je vynechá jen někdy. Důležité je jen to, aby to běželo rychle a efektivně a čtení se zápisy neblokovalo navzájem. Tedy nevím kterou variantu si mám vybrat.

Z tvých experimentů a nastavení mám špatný pocit, vypadá to, že prostě nevíš co děláš. Např. innodb_doublewrite=0 je skvělé, ale v takovém případě musíš na zfs nastavit innodb_log_write_ahead_size na ideálně dvojnásobek page_size, aby nedošlo k částečnému zápisu.

Na tohle jsem nikde nenarazil.  Pokud to někdo nastavuje tak na 16K, ale přitom nenastavuje page_size.

Nastavit innodb_buffer_pool_size na 40G je šílenost.

Na více místech radí, že to má být 70-80% paměti co je k dispozici. Stroj má 64 GB.

Že různí lidé na internetu radí různé věci na mne dělá dojem, že je to opravdu špatně dokumentované.

Podotýkám, že na replice vůbec nejde o bezpečnost dat, protože je to už kopie. A repliku mohu kdykoli obnovit z jiné repliky, co běží na jiném stroji.
Na hlavní databázi, až s ní přejdu také na innoDB, by byla dobrá vlastnost, aby to výpadek elektřiny/pád systému přežilo a po nastartování to mohlo ihned pokračovat. Možná ztráta dat za několik vteřin před pádem není vůbec podstatná.  Navíc se dá obnovit z replik a kdyby se nějaký záznam náhodou neobnovil, tak se celkem nic neděje.

[xsouku04]

A já nemusel databázi startovat z několik minut starého snapshotu nad ZFS, protože z dat hlavní databáze se stala neopravitelná fašírka.

Nechci býti kverulant, ale to, co s daným nastavením bude na tom ZFS snapshotu patrně bude neopravitelná fašírka taky, jen o 10 minut starší.

Připojuji se k předřečníkovi - fakt tušíte, co děláte?

Snapshot je velmi rychlá operace. Na tak krátkou dobu si mohu dovolit zamknout všechny tabulky a flushnout všechno na disk. Po tu velmi krátkou dobu by měly být blokovány další zápisy, ale nikoli čtení. Tedy pokud to innoDB podporuje. Pak snapshot nebude rozbitý.  S MyISam s tím problém nebyl.

[xsouku04]

Mám ZFS, takže některé ochranné prvky, které dělá innoDB jsou zbytečné, protože jsou dělány na úrovni souborového systému.

ZFS je dost pomalý filesystém - a při velké zátěži jsou s ním s databázemi problémy.

To by mohl být jeden z důvodů, proč pozoruji při přechodu na innoDB takové zpomalení zápisů.
Pod Linuxem ZFS nebylo použitelné ani s innoDB, vznikala tam write aplifikace a kromě špatného výkonu to ničilo disky. Tedy používal jsem ext4. Byl jsem pak překvapen, že na FreeBSD se stejným hardware najednou problémy nebyly. A ZFS fungovalo s MyIsam dobře.

MyISAM neřeší konzistenci - zápisy končí ve filesystémové cache - a v případě havárie můžete příjít o data z filesystémové cache - což vzhledem k primitivnímu formátu nemusí být problém - můžete mít ale nakopnutý index, a to už problém být může.

Potvrzuje to je pravda. Ale index menší tabulky lze celkem snadno a rychle jednou za 5 let případně opravit. Stejně tak oprava tabulky jednou za 5 let je relativně rychlá. Větší tabulka se skládá s partition. Mohu to rozbít a opravit jen tu poškozenou, což je také rychlé. Navíc také tu poškozenou mohu přejmenovat a nahradit ji  novou prázdnou.
Celý systém tak v době opravy může běžet bez problémů.

InnoDB má mnohem složitější formát a pokud by došlo ke ztrátě konzistence, tak to není jen o ztrátě pár záznamů.

Myslím že máte pravdu. Když se InnoDB pokazí je to horší a často je lepší začít obnovou ze zálohy.

ZFS vám pomůže jen v tom, že si můžete udělat snapshot a vrátit se k nějakému snapshotu.
Pokud by se ale rozbila konzistence v MySQL, tak je vám ZFS na dvě věci. InnoDB má na rozdíl od MyISAM vlastní write cache - o kterou v případě havárie můžete přijít, a tak musíte mít konzistentní transakční logy. Pokud byste je neměl, tak to, že přijdete o pár záznamů je to nejmenší - můžete mít nakopnutý index, což rozbije vyhledávání - unikátní index nemusí být unikátní, atd atd. MyISAM je do jisté míry "inmemory db", takže některé operace mohou být velice rychlé. Má velice primitivní formát - který je relativně odolný vůči chybám. InnoDB funguje už úplně jinak, a případné chyby konzistence v InnoDB mají mnohem horší dopad.

Ano souhlasím.   Řešením by mohlo být na hlavní databázi nepoužívat ZFS, aby se snížila režie zápisů. Nebo  prostě zvolit vhodný kompromis nastavení. Ale jak přesně jej vybrat?  Také by mohla být možnost,  na hlavní databázi zůstat na MyIsam a pro jakékoli náročnější čtení používat jen neblokující repliky s innoDB, které ale nebudou odolné vůči havárii. Ale může jich být více a jdou relativně snadno znovu nahodit, když se data poškodí. Např. se vrátím k poslednímu snapshotu před havárií a během pár vteřin replika všechno dožene do aktuálního stavu.

[xsouku04]

Jo a postgresa instaluj z postgresql.org, anzto tuto verzi podporuji originalni timescale baliky
V distre h byva postgres i timescale, ale jenom v pure OSS variante, ktera je orezana a neumi komprimaci. Potrebujes community versi, to je mezistupen mezi pure OSS a komercni variantou

Používám teď FreeBSD, ti na to mají automatizovanou kompilaci/balíček https://www.freshports.org/databases/timescaledb/
Je to ta verze s podporou komprimace? Předpokládám, že by to mohla být ta comunitty (TSL) protože freebsd nemá takové problémy s licencemi.  Mají tam napsáno licence Apache TSL. No mělo by se to poznat minimálně podle toho odkud a jaké zdrojáky to stahuje.