Dotaz: Jak v postgresql "zamknout pohled na resultset"

[pocestny05]

Ahoj. V php načítám data z tabulky v postgresu stylem:

select id, s1, s2 from ...

Výsledek dotazu předám do třídy php iterátoru, kterou jsem si napsal a pak postupně čtu podle potřeby řádek po řádku. Je to rychlé, šetří to paměť a mám jistotu, že mám konzistentní pohled na data. Jenže já bych si chtěl vybudovat cache a nenačítat znovu data, které jsem si už načetl. Tzn. chtěl bych jenom číst id řádků a podle toho bych se rozhodl zda to číst dál:

select id from ...

Jenže ve chvíli, kdy se rozhodnu provést "select s1, s2 from ... where id = ...", tak už ten daný řádek nemusí v tabulce existovat. Transakce na to použít nemohu, čtení všech výsledků dotazu není ihned po jeho provedení. Explicitní zamykání řádků zase znemožní, aby někdo jiný dané řádky smazal.

Potřebuji vlastně označit řádky a vytvořit tak konstantní pohled, ale umožnit někomu jinému, aby ty řádky reálně mohl mazat (ale já měl pořád ten svůj pohled). Jak to udělat?

Odpovědi

[Filip Jirsák]

Máte nějaký důvod, proč to takhle komplikovat a zpomalovat? Nerozumím větě "transakce na to použít nemohu", protože přesně k zobrazení konzistentního stavu dat transakce slouží.

[pocestny05]

Mám objektový návrh aplikace. Pro zjednodušení: třída DatabazovaVrstva vrací objekty Radek, kde Radek odpovídá jednomu řádku tabulky. S objekty Radek poté někde v aplikaci pracuji. Pokud by těch objektů Radek bylo hodně a DatabazovaVrstva všechny naráz načítala do paměti, pak by php skript mohl zabrat relativně dost paměti. Proto nevrací pole objektů Radek, ale RadekIterator, který se chová jako pole a například po každé foreach iteraci provede jeden DB fetch.

Většinu objektu to vrací během spuštění php skriptu 1x, ale s některými se pracuje v různých částech aplikace několikrát. Proto chci vytvořit cache, která si bude pamatovat N nejpoužívanějších (nejčtenějších) objektů.

[okbob]

To je špatně - když pracujete s relační databází, a provádíte hromadné operace, tak nejlepší, co můžete udělat, je zapomenout na OOP (a začít myslet v relacích a procedurálně). Navíc, čím víc udělá práce databáze, a čím méně klient, tím to bude rychlejší. PostgreSQL je databáze, která dobře zvládá komplexní SQL a pro složitější věci je možné jednoduše používat uložené procedury. Je tedy hloupé ji degradovat na storage nějaké cache.

[pocestny05]

To si nerozumíme. Databáze neslouží jako cache, spoustu věcí dělá za aplikaci právě postgresql. Jde mi o jinou věc: když převádím "řádek" či spíše relaci na objekt, tak provedu select z databáze. Když budu chtít s tím samým objektem pracovat znovu, tak bych chtěl použít cache, která by vlastně byla pole načtených objektů. Cílem je, abych místo 100 dotazů na ta samá data provedl jen jeden dotaz a vrácená data si cachoval na úrovni php.

Celkově aplikace hodně moc pracuje s databází a mezi aplikací a db se přenáší dost dat. Proto je DB a aplikace na stejném systému. Je otázka, zda cache vůbec něco řeší (i když asi jo, třeba PropelORM ji používá). Minimálně si ušetřím čas znovusestavováním objektů.

Dejme tomu, že v každém fetch vrátí databáze 20 sloupců, kdy "velikost dat řádku" je od 5 do 5000 KB. Co z následujícího je rychlejší? (tabulka je ve skutečnosti join několika tabulek)

• Vybrat všechno (SELECT s1 ... s20 FROM tabulka WHERE xy LIMIT 500) a pak postupně řádek po řádku s daty pracovat (převádět na objekty případně nedělat nic a použít cache)
• Vybrat primární klíče (SELECT s1 FROM tabulka WHERE xy LIMIT 500) a pak postupně řádek po řádku s daty pracovat (převádět na objekty). Pokud objekt není nacachován, pak SELECT s2 ... s20 FROM tabulka WHERE s1=PKHODNOTA

Jde o to, jak rychlé je postgresql ve vracení většího množství dat a zda se vůbec vyplatí drobit to na selecty primárních klíčů a podle potřeby načítat ostatní sloupce. Rovněž je otázka, zda vůbec cachovat kvůli rychlosti komunikace php-postgresql - jak rychlé je N krát opakovat stejný dotaz? Při dotazování používám prepared statements, takže by to mělo být rychlejší a bezpečnější.

Děkuji za jakékoliv postřehy k mým mnohým otázkám. Nedokážu to moc reálně posoudit, protože jak na straně php, tak postgresql se dá celkem čarovat s nastavením různých cachí a přidělené paměti.

[okbob]

Nakešovaná id Vám ale téměř vůbec nepomohou - pokud chcete použít cache, tak už byste vůbec neměl přistupovat do databáze.

Základním pravidlem je nedělat hromadné operace přes objekty - to je neskutečná brzda. Data posílám na klienta pouze tehdy, když je chci zobrazit nebo někam odeslat.

[okbob]

Cache id se používaly před 15 roky ve VB - výsledkem byly docela rychlé aplikace, které ale byly docela brutálně komplikované - na to co uměly.

[Filip Jirsák]

Co z následujícího je rychlejší?

Ty vaše pokusy s cache jsou evidentně předčasná optimalizace, když se na tohle ptáte. Normálně se preferuje první varianta, ke druhé se přistoupí až tehdy, pokud je jasně změřené, že v daném konkrétním případě je ta druhá varianta efektivnější.

Jinak to ale z vašeho popisu vypadá, že by té vaší aplikaci mnohem víc, než cache, prospělo, kdybyste databázi používal správně. Podle vašeho dotazu to totiž vypadá, že máte na jedné webové stránce sto různých výpisů, které vypisují stejná data, každý řádek může mít až 5 MB. Když bude jeden dotaz vracet jen 10 záznamů, vychází to na 5 GB na jednu webovou stránku. Opravdu? To vám nějaký webový prohlížeč zobrazí? A ještě navíc tam chcete mít různé transakce, proč? I kdybyste něco takového doopravdy potřeboval dělat, o čemž pochybuju, pořád pravděpodobně bude mnohem efektivnější z těch „100 dotazů na ta samá data“ udělat dotaz jeden.

[pocestny05]

Klientovi nikdy neposílám velké množství dat. Kdybych měl stručně shrnout co ta aplikace dělá tak:

1. Uživatel nahraje textový soubor
2. Aplikace soubor postupně parsuje a na základě jeho obsahu načítá nějaká transformační pravidla
3. Aplikace transformuje soubor a uloží ho lokálně
4. Aplikace odešle uživateli výsledek operace

Transformační pravidlo T je vlastně objekt. Načítá se z databáze. Každé pravidlo může mít libovolný počet vnořených pravidel. Jednoduše řečeno pravidlo obsahuje pokyny, případně nějaká data, která lze použít při transformaci vstupního souboru. Díky vnořování může být strom pravidel třeba:

T1
├── T8
└── T9
T2
└── T8
T3
└── T8
T4

Když načítám T1, pak aplikace musí načíst i T8 a T9. Když načítám T2, musí načíst T8 atp. Zde je výhodné držet si T8 v cache. Nemůžu si dopředu rozparsovat celý soubor a pak s tím pracovat. Potřebuji šetřit paměť a navíc aplikace nemá jen webové rozhraní a nemusí dopředu znát obsah souboru. Cache už nějak implementovanou mám, zajímalo by mě, zda by to šlo lépe. Takto to funguje:

1. Parser potřebuje T1, pak ho načtu z databáze
2. Parser potřebuje T1, pak ho načtu z databáze
3. Parser potřebuje T1, jenže už ho potřeboval 2x, proto si ho načtu z DB a nacachuji
4. Parser potřebuje T1, jenže už ho potřeboval 2x, provedu dotaz do DB a použiji nacachovaný
5. ...
6. Parser potřebuje Tn, pak ho načtu z databáze
7. Parser potřebuje Tn, pak ho načtu z databáze
8. Parser potřebuje Tn, jenže už ho potřeboval 2x, proto si ho načtu z DB a nacachuji. Pokud už je načtených objektů moc, vyhodím ten nejméně používaný.
9. ...

Neřeším úpravy hodnot databáze další osobou. Data si cachuji jen N sekund. Strom načítám v transakci. Co hlavně řeším je to, že i u bodu 4 provádím dotaz do databáze typu "SELECT t_name, s1, s2, ... FROM t WHERE x = y AND i LIKE ..." a pokud t_name znám, pak si ušetřím konstrukci stromu T1->T8,T9. V tom případě mi ale databáze vrací i nepotřebné sloupce s1, s2, ..., které teď nepotřebuji (strom už mám). Tak je otázkou, zda nenačítat jen t_name a podle potřeby další dotaz na zbylé sloupce a jestli potenciálně vrácená nepotřebná data mají jen zanedbatelný vliv na výkon (výhodu mají určitě v možnosti transakčního zpracování).

[pocestny05]

Jo a parsování, načítání pravidel a transformace probíhá postupně, ne parsování, pak načtení pravidel a pak transformace.

[Ivan]

Ony ty databaze zase nejsou tak blby. Ony taky umi cachovat. Opravdu to cachovani tolik pomaha? Na dotaz pres primarni klic by mela DB odpovedet v radech milisekund. Spis bych se zameril na to jak snizit pocet round-tripu do te databaze.

Co je lepsi cache v PHP anebo cache Postgresu? Jak dlouho trva ta transformace v porovnani s nactenim dat z DB?

[pocestny05]

Nedokážu určit, co je rychlejší. V PHP záleží na nastavení přidělené paměti, na rozšíření PHP, OS apod., u postgresql na nastavení shared buffers apod. Určitě vím, že současná cache, kdy vlastně eliminuji pouze vytváření objektů (a taky dotazování, pokud provádím Transformace::findById, tzn. hledám jen jeden záznam podle ID a nemusím se dotazovat postgresql - 1 nevýhoda: findById může najít to, co findAllNěco ne) dost urychlila aplikaci.

[Filip Jirsák]

Ty nepotřebné sloupce by měly vliv na výkon jedině tehdy, pokud by kvůli nim databáze musela přečíst nový blok z disku - tj. pokud by bez nich dokázala odpovědět jen z indexu a nemusela stejně načítat příslušný blok třeba kvůli sousednímu řádku. Když už byste chtěl implementovat to vaše řešení, pořád nevidím jediný důvod proč nepoužít transakce, které jsou právě na tohle určené.

Jinak mi pořád připadá, že jste si vybral velmi nevhodné nástroje, a pak řešíte nějakou velmi pochybnou optimalizaci. Potřebujete šetřit paměť, a přitom chcete kešovat velký objem dat. Optimalizujete zpracování transformačních pravidel, místo abyste je měl uložená ve tvaru, v jakém je můžete rovnou použít.

[pocestny05]

Jinak mi pořád připadá, že jste si vybral velmi nevhodné nástroje, a pak řešíte nějakou velmi pochybnou optimalizaci. Potřebujete šetřit paměť, a přitom chcete kešovat velký objem dat. Optimalizujete zpracování transformačních pravidel, místo abyste je měl uložená ve tvaru, v jakém je můžete rovnou použít.

Je to o kompromisu. Data (i pravidla) se ukládají v takovém tvaru, aby se dala zajistit konzistence na úrovni DB a aby DB mohla provádět potřebné operace.

[Delaunay]

Pokud neřešíte případné úpravy hodnot v databázi další osobou, tak bych postupoval následujícím způsobem.

Načetl bych si jediným rekurzivním SQL dotazem celý strom pravidel.

Následně bych jako cache pro objekty vytvořil haldu s omezeným počtem prvků řazených primárně podle počtu přístupů k danému objektu z aplikace a sekundárně podle času vložení do haldy.

Pokud by během plnění cache nastal případ, že by již nebyla data v databázi k dispozici, zahodil bych všechny dosavadní výsledky transformace, poté z databáze znovunačetl strom pravidel, vyresetoval počítadla přístupů u všech již nacachovaných objektů na haldě a začal s transformací od začátku.

[Michal Kubeček]

Transakce na to použít nemohu, čtení všech výsledků dotazu není ihned po jeho provedení.

Proč byste nemohl? Pokud je mi známo, PostgreSQL používá MGA (MVCC), takže read-only snapshot transakce by neměla nijak zvlášť překážet. Aspoň u Firebirdu to tak je.

[pedro]

create temp table mojetabule as select id, s1, s2 from ...

a ta temp tabulka se už nezmění a zcela jistě nebude nikomu překážet, jedině bude zabírat místo.

[aubi]

Bude zabirat misto a cas na vytvoreni. Taky musite resit nazev tabulky, protoze uvedeny zpusob ma problem s dvojity pristupem na tuhle stranku.

Proc nestaci jedna transakce? Hledejte transaction isolation - dostanete presne totez chovani bez pomocne tabulky.

aubi

[Michal Kubeček]

To jsem navrhoval už minulé pondělí, ale holt to asi vyžaduje překonat nějaký myšlenkový blok a dočasná tabulka připadá lidem z nějakého důvodu jednodušší.

[OldFrog {Ondra Nemecek}]

Tím blokem je nutnost pochopit koncept transakcí včetně isolation levels. Což přiznejme si, je v reálu o dost těžší než vzít data, která tudle vidím a strčit je do nějaký dočasný tabulky.

BTW ono je toho víc, co vypadá jednoduše, ale dá to zabrat. Před rokem mě dostal například upsert v Postgres (v poslední Postgres to je snad implementováno už nativně).

[xkucf03]

Nechci říct, že mezipaměť je zbytečná¹, ale pro začátek bych se snažil využít možností relační databáze. Přiděl jí víc paměti a uvidíš, jak se ti odvděčí. Ono je totiž celkem jedno, jestli si data v RAM drží tenhle proces nebo tamten. A databáze (nebo třeba OS, souborové systémy atd.) bývají typicky lépe napsané než běžné zakázkové aplikace. Mezipaměť v aplikaci bych použil spíš pro data po nějakém náročnějším zpracování (třeba vygenerované PDF, XHTML stránka, obrázek, archiv atd.), ne je tam udržovat v surové formě, v jaké jsou i v databázi.

_{[1] zvlášť když je to po síti – na jiném stroji – nebo těch aplikačních serverů máš víc a databázi jen jednu}

[Delaunay]

Za omezujících podmínek, např. schopnostech programátora, nevhodně zvolených technologiích, termínu dokončení... je opravdu často výhodnější nehledat hned na začátku technicky optimální řešení, ale vydat se zprvu cestou využití brutální síly.