Vlákno názorů k článku PostgreSQL 14: nové funkce, nástroje a interní optimalizace od dustin - Ad uváděné historické "zkušenosti" s mysql. Kolem r....

Ad uváděné historické "zkušenosti" s mysql. Kolem r. 2002 jsme vybírali DB pro náš systém. Mysql (samozřejmě InnoDB, žádné myisam, referenční integrita přes všechny tabulky, držela ORM mapování) na joinech přes cca 10 tabulek (každý atribut objektů má vlastní tabulku + tabulky přístupových ACL k objektům, hromadná vyhledávací query objektů s kritérii na různé atributy ) nám vycházela v těch testech tehdy 5 - 10x rychlejší než PostgreSQL. V té době byla pro nás volba zcela jasná. Je docela možné, že dnes by to již vyšlo nastejno, už jsme testy neopakovali. Pro náš účel (veškerá byznys logika záměrně v aplikaci, aby se netříštily používané technologie a snadno se přidávaly nové funkce/úpravy) nám na mysql (dnes samozřejmě mariadb) chybí "jen" použitelný fulltext, který je opravdu zásadní nedostatek. Kdyby byl fulltext postgresu OK a rychlost srovnatelná, byl by to důvod k přechodu, než řešit synchronizaci fulltextu třeba v ES.

Tohle IMHO už dávno neplatí. Fulltext Postgresu mi přijde dobrý, včetně rychlosti a podpory pro češtinu (taky to už je nějaký čas, co jsem s tím pracoval).

Mam skusenost (prakticku) s tym ze ak to porovnanie DB robi clovek ktory tak tak zvlada mysql (pozna iba zaklady) tak postgres vzdy vyjde podstatne neefektivne proti mysql. Ak to porovnanie urobi niekto kto dokaze postgres vyuzit na rozumnej urovni, tak postgres vyjde vyrazne lepsie... je to ako porovnavat stihacku s praskovacim lietadlom, ak do stihacky posadis pilota praskovacieho lietadla tak sa vecsiny cudlikov a pacok ani nechyti a v porovnani stihacka vyjde horsie ako praskovacie lietadlo ;)

Mam skusenost (prakticku) s tym ze ak to porovnanie DB robi clovek ktory tak tak zvlada mysql (pozna iba zaklady) tak postgres vzdy vyjde podstatne neefektivne proti mysql. Ak to porovnanie urobi niekto kto dokaze postgres vyuzit na rozumnej urovni, tak postgres vyjde vyrazne lepsie

Je to přesně tak. Pokud byla aplikace napsaná pro mysql, nebo byla napsaná univerzálně (což asi byla, když šlo vyměnit DB na zkoušku), nemohl být Postgres rychlejší. Kdyby byla napsaná přímo pro Postgres a využívala pokročilé možnosti SQL, pak by to dost pravděpodobně PG vyhrál. Stejným neduhem trpí povětšinou cokoliv, co používá ORM.

Rozdíl ve výkonu je pak spíš daný tím, že Postgres má hodně robustní komunikační protokol, a režie na dotaz je o něco větší. To se běžně "navrátí" tím, že dotazy mohou být komplikovanější a kvalitní databáze dotaz zoptimalizuje, využije správně indexy a navrátí spoustu výsledků zpracovaných naráz. Pokud však aplikace sází desítky malých dotazů pro získání jednotlivých dat, které pak zpracovává aplikace, tak to mysql vyhraje. Ušetří se totiž ten čas na režii protokolu.

Vtip je prave v tom ze to volanie do postgresu, ak je spravne naprogramovane, vrati tych udajov daleko menej. Nemusi totiz prenasat udaje ktore potrebuje bussines logika napisana napriklad v hypertextovom preprocesori (bez hate, php k tomu comu je urcene pouzivam casto) ktory na hromadne spracovanie dat nie je moc dobre optimalizovany. Miesto toho medziudaje spracuje funkcia v pgplsql ktore je pre hromadne spracovanie udajov optimalizovane a vysledok su relevantne udaje. Toto v mysql nie je proste mozne.

....a v porovnani stihacka vyjde horsie ako praskovacie lietadlo ;) ...

jestlize jsou ale v beznem zivote potreba z 99% praskovaci letadla, tak pak jsou ty ficurky tech stihacek presto k niceemu ...

No od stihacky necakameze bude praskovat, rovnako ako od praskovacieho lietadla necakame ze bude niest rakety. Takze predmetom porovnania budu ine vlastnosti. Dolet, rychlost, manevrovatelnost...
I ked si viem ovela lepsiepredstavit stihacku v nebojovom nasadeni ako cmeliaka v bojovom nasadeni :D

nojo, ty priklady ...
Pripustme, ze ta pgsql je 10 x yykonejsi nez mysql. (pan stehule rika, ze to dnes vlastne uz ani tak neni, ale budiz).
Co jsem chtel rici bylo, ze kdyz se 99% vsech aplikaci, ktere potrebuji nejak ulozit data spokoji s 10% vykonosti mysql , tak pak je to uz jedno, ze existuje dalsi moznost, ktere je jeste 10 x vykonnejsi. Vpodstate takhle prehnanou vykonnost nikdo nepotrebuje.

jestlize jsou ale v beznem zivote potreba z 99% praskovaci letadla, tak pak jsou ty ficurky tech stihacek presto k niceemu ...

Ono to není úplně trefné přirovnání. Spíš bych to přirovnal k dálkové dopravě. Pokud budete posílat z Číny malé jednotlivé balíčky do Evropy poštou, tak si moc nepomůžete ani tím, když si pronajmete celý kontejner na lodi. Nepomůžete si ani tím, že přijmete do firmy tři další sekretářky, které budou balíčky evidovat a řešit poštou zpět do Číny reklamace.

Výkon získáte teprve tím, když se Vám podaří zajistit, že už ve fabrice zabalí zboží na palety v předem dohodnutém pořadí. Když palety nastrkají do kontejneru taky v dohodnutém pořadí. Když v přístavu bude čekat tahač s kontejnerovým návěsem a když na celnici dorazí deklarace ve stejnou chvíli, jako zboží. Když kamion pak zastaví ve čtyřech distribučních místech a vyloží z kontejneru vždy paletu nejblíž u dveří. Když v každém skladu budete brát z palety seshora. Když budete mít rovnou doručených X % zboží navíc, přesně podle vypozorované míry reklamací.

Oba přístupy (jednotlivé balíčky přímo z Číny do Evropy), nebo srovnané kontejnery, palety a zboží na paletách plní účel na 99 %. Rozdíl je v tom, že na dopravě individuálních balíčků už nemáte co zrychlit. Na tom druhém způsobu ano, můžete dávat jiné pokyny (např. velké kusy balit vespod, malé nahoru, měnit procenta kusů navíc pro reklamace, můžete lépe označovat zboží v rámci palety)...

To je právě ten rozdíl mezi SQL. Jedna databáze Vám poskytuje funkce pro to urychlení (transakce = kontejner, rychlé agregáty = správně balení palet, ...), zatímco druhá databáze tyto funkce neposkytuje. Ale ten výkon získáte až teprve se změnou přístupu k dopravě. Dokud nezměníte přístup a budete posílat individuální balíky zboží (tj. práci si odedřete až v cílovém místě), tak výměna RDBMS nijak zásadně nepomůže. Účelem SQL je, aby data šla utříděná, zpracovaná, transformovaná už od zdroje a tam leží klíč k výkonu.

pochopil jsem to spravne v tom Vasem priklade, ze ...'Když budete mít rovnou doručených X % zboží navíc, přesně podle vypozorované míry reklamací.' - tim myslite ty lepsi statistiky u pgsql oproti mysql?
Pan Stehule dole rika, ze se to dnes vpodstate vyrovnalo. Ale hlavne co rika (neustale) je, ze kdyz je dostatek vykonu, tak se ty databaze vlastne nelisi.

Moje poznamka (a viz take vyse) byla smerem k tomu, ze u 99% aplikaci jsou ty pozadavky tak male, ze ten vykon dostacuje vzdycky.
Nakonec i u Vas v tom priklade -> kdyz se bude z Ciny posilat 5 baliku za rok, tak se to zvladne rucne a to sofistikovane reseni nebudei potreba.

Ve Vasem priklade je jeden zajimavy aspekt.
' že už ve fabrice zabalí zboží na palety v předem dohodnutém pořadí. Když palety nastrkají do kontejneru taky v dohodnutém pořadí. '
To je takovy klasicky problem, ktery ukazuje hranice moznosti SQL. Nejen, ze se to do toho kontejneru ma dat v tom poradi, ale melo by se to tam take smysluplne vejit a vyplnit ten kontejner optimalne. A zde by byl zapotrebi nejaky takovy hypoteticky SQL statement jako:

select * from a,b,c where 'az bude kontejner plny' :-)

To zatim stavajici SQl systemu myslim neumi. Obecne u vsech takovych prikladuj jde o to, ze nelze nijak rozumne omezit tu vysledkovou mnozinu. A pak se musi sahnout k tomu imperativnimu pristupu a prohnat kus po kuse nejakym algoritmem - tedy presne to co kritizujete, kdyz hovorite o tom posilani individualnich baliku ...

Ale ten arguument sa da obratit.
Ak robi porovnaia clovek co sa vyzna v Postgrese, tak vyjde lepsie.

Podla vasho argumentu potom plati, ze sa neda povedat, ze by Postgre bolo lepsie.

Podle mě je to myšleno jinak.
Potřebujete splnit nějaký úkol - řekněme, naprogramovat web a CMS.

Ten samý úkol můžete řešit dvěma způsoby:
a) neumíte SQL a nerozumíte RDBMS a namastíte dotazy "nějak", případně použijete ORM
b) umíte SQL a rozumíte svému RDBMS a dotazy a transakce vytvoříte tak, aby vyhovovaly zvolené databázi

Porovnání dvou databází pak můžete provést různě. V případě ad a) nemusíte dělat žádné úpravy. Zde dost často vyhraje mysql nad postgresql, právě díky menší režii a datům rychle dohledatelným podle primárního klíče.

V případě ad b) už ale můžete najít obrovské rozdíly. Troufnu si tvrdit, že i kdybyste se rozkrájel, tak s mysql nedokážete práci s daty zoptimalizovat k podstatně vyššímu výkonu. V případě postgresql naopak ano.

Konkrétně využijete mnohonásobné joiny (10 joinů není nic, co by bylo považováno za "velký dotaz"), využijete agregáty, SQL funkce, window funkce, lateral joiny. Tedy vlastnosti, které mysql buďto umí pomalu, nebo nenabízí vůbec.

Typický příklad z praxe: tři pracovníci, prodávající u McDonald's. Každý prodej je zaznamenán formou účtenky - ví se kdo, kdy a jaké zboží prodal. Chcete zjistit, který pracovník prodává zboží s nejvyšší marží (na cheeseburgeru víte, že vyděláváte méně, než na zmrzlině a pracovníci mají pokyn nabízet zmrzku). Chcete tu statistiku znát za poslední směnu a za posledních 10 směn. Směny jsou nepravidelné (některý den někdo nepracuje, každému začíná pracovní doba jindy). Zajímá Vás jak to, jestli nabízejí úspěšně zmrzlinu, ale i to, jestli jsou celkové výkonní (jeden může dobře prodávat zmrzlinu, ale obsluhuje pomaleji, takže ani ta zvýšená marže nevynahradí jeho pomalost).

V mysql taková data nemáte šanci získat jinak, než že si oddělenými dotazy vytaháte jednotlivá data (kdy měl kdo posledních 10 směn - jeden je bude mít v předcházejících 10 dnech, jiný je bude mít rozložené na 20 dní). Zcela nezávisle spočítáte součty a marže pro poslední směnu a nezávisle pro 10 směn. Do databáze budete z aplikace posílat minimálně tři dotazy a mezi nimi bude aplikační logika. Na konci pomocí vnořených smyček vytisknete tabulku.

V postgresu na to použijete jeden jediný velký dotaz. Směny si získáte subselectem, který využijete pro join. Pro součty za poslední směnu a za 10 směn využijete window funkce a agregáty. Data nakonec naformátujete do kostky. Jeden jediný dotaz, který má databáze možnost zoptimalizovat a zpracovat rychle (nemusí třikrát číst ta samá data, když ví, že je má sečíst pro poslední směnu a pro posledních 10 směn). V aplikaci vypíše data z kostky, aniž byste použil vnořené smyčky.

[@Pavel Stěhule k článku: zrovna toto je šikovné užití grouping sets a na DISTINCT se docela teším, protože při těchto operacích fakt vznikají duplicity, které sice vznikají logicky, ale pro výstup dat nepřinášejí žádnou hodnotu]

Právě v tom, kde jsou limity schopností databáze tkví to porovnání, která je výkonnější při správném užití.

Ja neobhajujem MySQL (je to priserna a pomala databaza), len som poukazal na nevalidny argument.

Existují univerzální testy - TPC-B, TPC-H, které můžete pustit vůči libovolné SQL databázi. Předpokládám, že v TPC-B bude mít MySQL navrch, a v TPC-H zas bude mít navrch Postgres. V reálném světě asi minimum aplikací budou generovat zátěž podobnou záteži generované těmito syntetickými testy.

Když znám MySQL a znám PostgreSQL, tak samozřejmě dokáži vymyslet test, ve kterém bude MySQL 10x rychlejší než Postgres, a zrovna tak dokáži vymyslet test, kde bude Postgres 10x rychlejší než MySQL. Nicméně, když znám obě databáze, tak také dokážu se vyhnout slabým stránkám MySQL a dokáži se vyhnout slabým stránkám Postgresu. Případně dokáži v aplikaci použít obě databáze, abych využil silné stránky obou databází.

MySQL má jednodušší optimalizátor, stejně tak má relativně hloupé statistiky, a až do verze 8.0 i hloupý executor (interpret jazyka prováděcích plánů). Interpret uložených procedur také není extra rychlý (a až do verze 8.0 postrádal i relativně základní funkce). Tudíž komplexnější dotazy, které vrací větší počet řádků na větších datech budou na MySQL pomalé (aplikačně budou ještě pomalejší). Ale InnoDB tabulky jsou uspořádané podle primárního klíče. V podstatě InnoDB tabulka je indexem nad primárním klíčem. Díky tomu všechny operace nad primárním klíčem budou v InnoDB výrazně rychlejší než nad pg. Pokud potřebujete jen transakční storage, a neděláte reporting, tak MySQL poslouží špičkově.

Postgres je defacto pravý opak. Postgresové tabulky nejsou organizované podle primárního klíče. Tudíž operace nad primárním klíčem budou vždy pomalejší než v MySQL. Ale má chytřejší optimalizátor, chytřejší statistiky, uložené procedury jsou naprosto jiná liga, má chytřejší datové typy. V postgresu mohu dost výpočtů udělat na úrovni db, a nemusím data stěhovat po síti, provádět konverze, atd.

A když toto vím, tak tomu mohu přizpůsobit návrh aplikace nebo volbu databáze, nebo volbu testů. Pro některé aplikace se hodí víc Postgres, a pro jiné MySQL, a někdo preferuje styl a strategii, která vyhovuje MySQL, a někde jiný ho dost nemusí.

Pri povolenom innodb_file_per_ta­ble je pri primarnom kluci rychlejsia. Ale ak je ta moznost zakazana, co vo vedsine pripadov je, tak po naboptnani tabuliek uz nie je cesty spat, nepomoze ani optimize table. To je ten zlomovy okamih, ked ide vykon prudko dole.

@ oss

Nie je to vseobecny nazor ale reakcia. Ked opomeniete kontext, ktory urcuje to na co som reagoval, tak to vyznie ako blbina. Ako kazdy text vytrhnuty z kontextu.

Ked tak pointa, problem je vo vedsine pripadov medzi klavesnicou a stolickou. Nie v postgres alebo mysql...

21. 5. 2021, 09:55 editováno autorem komentáře