Postgres lassú lekérdezés

Adatbázis: SQL, XML DB

Kedves Fórumozók!

Adott a következő struktúrájú Postgres adatbázis táblám:

CREATE TABLE sensor_result
(
id serial NOT NULL,
sensor_id integer NOT NULL,
date integer NOT NULL DEFAULT (date_part('epoch'::text, now()))::integer,
status boolean NOT NULL,
interrupt boolean NOT NULL,
request_time real,
CONSTRAINT "PK_sensorresultid" PRIMARY KEY (id),
CONSTRAINT "FK_sensorid" FOREIGN KEY (sensor_id)
REFERENCES sensors (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);

Ebbe a táblába percenként kerül be közel 700 mérő műszer mérési eredménye, melyek méréseit 1 évig kell megőrizni.
Innen már csak egy gyors fejszámolás és kijön, hogy ~367.920.000 bejegyzést fog tartalmazni a tábla élesítéstől számított 1 éven belül.

A problémám az, hogy a mérési eredményekből különböző kimutatásokat kéne készítenem. A kimutatásokat sajnos van, hogy több különböző adatból kell összeállítanom, ami több különböző lekérdezést jelent. Összevonogatni az eredményeket nem lehet, mert a request_time adat külön-külön is fontos.

Tesztelés képpen feltöltöttem a táblát ~5 millió adattal. És itt ért a meglepetés.
Egy egyszerű: SELECT count(1) FROM sensor_result; lekérdezés is átlag 1600ms volt a lekérdezés ideje.
Ha egy WHERE záradékot is beleteszek pl: WHERE sensor_id=1, akkor további 200ms-el nőtt a lekérdezés ideje.Tekintettel arra, hogy esetenként 3-4 lekérdezést is le kell futtatnom (ami nem feltétlenül ilyen egyszerű) és nem 5millió adat lesz a táblában, hanem 300 millió, így ez elfogadhatatlan mértékű válaszidőt eredményezne.
Várható lekérdezések: date mezőben tárolt dátum (unix timestamp) szerint meghatározott intervallum esetén a request_time átlaga, szintén meghatározott intervallum esetén a mérések száma, abból azok száma, ahol a status=true, vagy éppen false, ugyan így az interrupt mezőre is.

Van valakinek valami ötlete arra, hogy mit tudnék tenni a lekérdezések gyorsítása érdekében? Akár az adatbázis átalakítása is szóba jöhet, mert még nem került élesítésre a rendszer. Sajnos egy dolog nem megoldható: adatbázis váltás, maximum postgreshez hasonló licenselésű alternatív adatbázis kezelőre.

Előre is köszönöm mindenkinek az építő jellegű hozzászólást.
Köszönettel:
NoMan

  • 6721 megtekintés

( mhmxs v | 2012. 10. 04., cs – 08:25 )

Amit első körben tennék, hogy partícionálnám a táblát mondjuk a date mező mentén. Szerencsére utólag is lehet új partíciót létrehozni (amennyiben még nincs olyan rekord a táblában, ami az új partícióba kéne, hogy kerüljön, tehát minden hónap utolsó napján létre tudod hozni a következő partíciót, vagy mondjuk 30M soronként). A SELECT count(1) FROM sensor_result;-ot a partícionálás sem fogja meggyorsítani, de ha úgy választod meg a partíció feltételét, ami aztán a lekérdezésekben is gyakran előfordul, a PG tudni fogja melyik gyermek táblából szedje az adatokat. Egyáltalán nem meglepő, hogy sokáig számol már 5M rekordnál, nem tudom ez a szám mennyit változott az elmúlt pár évben, de én annó azt olvastam, hogy 7M felett nem lehet "normálisan" kezelni az adatmennyiséget.

A másik amit lehet csinálni, hogy egy idő elteltével, ha lehet, archiválni kell bizonyos adatokat, de ez már az üzleti logikán múlik.

-
Barátkozás a Groovyval

( SzBlackY | 2012. 10. 04., cs – 08:25 )

Indexek hogy vannak? A kimutatásokat milyen sűrűn kell megcsinálni és akkor milyen válaszidő lehet rájuk? (pl. ha csak hetente egyszer mondjuk, akkor lehet megoldás, hogy lehúzod egy ideiglenes táblába az adathalmazt, azon megcsinálod a kereséshez szükséges indexeket és úgy keresel, akkor az indexelés a beíráskor nem fogja a rendszert). Ha minden lekérdezésben a date mező szerint szűrsz, akkor esetleg a dátum alapján partíciónálni a táblát?

Sajnos a kimutatásokat mindig biztosítani kell, mert van ehhez egy webes GUI, amit az illetékesek bármikor megtekinthetnek és meg is fognak tekinteni.

Hát a 2-3sec már viszonylag magas válaszidőnek számít.

Ennek a particionálásnak utána nézek. De ha mondjuk az alapvető problémámat nem oldja meg, akkor szinte mindegy.

Pedig kellene.

Másik: a COUNT(1) a COUNT(*) helyett nem minden esetben jobb megoldás, mert dönthet úgy is az adatbázis-kezelő, hogy először létrehoz 300M 1-est és utána azt COUNT()-olja, ami nyilvánvalóan lassú tud lenni.

Másrészt, postgres.conf-ba érdemes belenézni, legtöbbször alapból kb. egy 64 mega rammal szerelt P2-re van méretezve a cache mérete.

Harmadrészt: EXPLAIN?

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

( dii | 2012. 10. 04., cs – 09:23 )

Hát, ezt a problémát elég sok oldalról meg kell nézegetni tervezésnél. Én valószínű tartanék egy táblát csak az INSERT műveletekre, mivel gyaníthatólag nem lenne jó, ha a lekérdezések még a műszeradatok feldolgozását is nehezítenék. Majd ennek egy replikát a tetszőlegessen bonyolult lekérdezésekhez.

A lekérdezések gyorsítása elég szerteágazó kérdés. Az egyszerű kérdések, hogy például a lekérdezések mindig véletlenszerű tartományt érintenek-e, vagy várhatóan nagy részük rutinszerű (mondjuk mindig az utolsó 5 perc). Erre lehet már optimalizálni.

Persze ott van még a vas alálapátolás, a várható adatbázis mérete, a lehető legtöbb memória használatával összevetve. Illetve az adatbázis beállítások behangolása a CPU/RAM/DISK szentháromság kapcsán.

Na, a nagy általánosságok helyett: Replikáció megoldható-e? Milyen hardverre kerül majd ez a dolog? Célhardveren ment-e a tesztelés? PostgreSQL beállítások matatva voltak-e már? Ha igen, mik a nem default beállítások?

És még egy végső felvetés, valóban relációs adatbázisra van-e szükség? Az a megérzésem, hogy valamilyen NoSQL megoldással sokkal jobb eredményt érnél el erre...

Replikáció megoldható, ha itt most másik táblára gondoltál és nem másik szerverre.

A cél hardveren került tesztelésre, amin a megtöbb *_buffer *_mem változók már fel lettek húzva, de nem mondanám, hogy agyonra optimalizálgattam. Sajnos elég kévés tapasztalatom van ilyen téren. Nem tudom, hogy mivel teszek jót és mivel rosszat.

Nincs tapasztalatom a NoSQL terén. De ha azt mondjátok, hogy mindenképpen jobb választás, akkor utána nézek a témának.

A cél hardver még nem tudom milyen lesz. Amire kezdetbe ki kell telepítenem:
- Intel(R) Xeon(R) CPU X3220 @ 2.40GHz
- 4 Gb DDR2 ECC
- RAID EDITION SATA winyó
Aztán innen idővel elképzelhető, hogy átköltözik egy másik vasra, de kezdetben tuti, hogy ez ad majd neki otthont.

( n.balazs v | 2012. 10. 04., cs – 09:24 )

Kérdésem, hogy mekkora lekérdezési időt tartanál elfogadhatónak 400 millió bejegyzés esetén?

Tekintettel arra - ahogy fent is írtam - nem kérdezek le mást, csak számosságot.

Mennyi olyan sor van, ahol a sensor_id=1 és status='t'?
Mennyi olyan sor van, ahol a sensor_id=1 és status='t' és date > x és date < y?
Mennyi olyan sor van, ahol a sensor_id=1 és status='f'?
Mennyi olyan sor van, ahol a sensor_id=1 és status='f' és date > x és date < y?

Egyetlen számosságtól eltérő lekérdezés a request_time átlaga.

Tehát semmi bonyolult kérdésem nincs a tábla felé. Nem úgy, mint a Google-nak, akinek figyelembe kell venni relevanciát, elgépelési hibákat..stb. és mégis ms-ek alatt választ ad, és ha jól tudom, akkor mysql-t használnak (persze agyon hackelve, de az már más kérdés). És ott nem 400millió bejegyzése van.

Így számítanék a viszonylag gyors lekérdezésre.

Legoptimálisabb az lenne, ha aggregált adatokat külön táblában tartanál.

Csináltam egy 45M rekordos teszttáblát. Ami most nekem feltűnt eddig, az az, hogy hiába van index, explain alapján akkor is szekvenciális olvasást hajt végre, ami mindenképp lassú lesz. (most 2,2G az adattábla meg hozzá 2,9G index.) Később még ránézek, mert most érdekel, hogy miért nem használ indexet, de most lépnem kell.

Most durva becslés, de 14G adatot (ez kb. 350M rekord) végigolvasni szekvenciálisan... Nos, az mindenképp lassú lesz, akárhogy csűröd-csavarod.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

> nem kérdezek le mást, csak számosságot.

Ha ez ilyen egyszerű, akkor előre kiszámolhatod a lekérdezések eredményeit. Az adatok mehetnek egy szimpla log-ba, a lekérdezések eredményeit pedig folyamatosan aktualizálni lehet egy pár gigás fájlban. Adatbázis kezelőre nincs szükség.

> Mennyi olyan sor van, ahol a sensor_id=1 és status='f' és date > x és date < y?

Előre kiszámolható: (Mennyi olyan sor van, ahol a sensor_id=1 és status='f' és date <= y?) - (Mennyi olyan sor van, ahol a sensor_id=1 és status='f' és date <= x?)

Vagy valami ilyesmi.

Erre már én is gondoltam, csak a konzisztencia elkerülése végett szeretnék inkább élő adatokból dolgozni.

Persze egyelőre úgy áll a helyzet, hogy nincs más megoldás. De még várok, mert azért nagyon sok tapasztalt és rutinos ember van itt. Hátha tud egy tökéletes tippet adni.

Ha ez tenyleg igy van, akkor lehet erdemes nosql modszereket alkalmazni, vagyis elfelejteni a normalformat, es csak a countereket tarolni (es az atlaghoz az osszeget es a szamossagot, amibol az atlag kijon).

Igy megvan az egesz 5-6 soros tablazatbol. Persze eltarolhatod az adatokat egy masik tablaban is, hogyha hosszabb/melyebb elemzesre is neha szukseged van.

Nah már vártam az első hozzászólót, aki kioktat, hogy mennyire nyomi vagyok. Úgy látom nem kellett sokáig várnom.

Tudom, hogy mi az az átlag. De nem fogom bizonygatni, hogy igen is értelmes ember vagyok.
Ha nem írsz a topikba többet, akkor nem írsz. Bizonyára Te annyira zseni vagy, hogy még soha nem kellett mástól kérdezned és nem jártál úgy, hogy a kapott válaszok alapján további problémák merültek fel benned, de még nem tudtad teljes egészében megfogalmazni.

Lényegtelen... köszi az eddigi hozzászólásaid.

45M. De szerintem generálok még 300M-t.

Xeon E5-1620, 32G ram, mezei 1T-s SATA disk, semmi raid, W7, Pg 9.1x64

Szerk: ettől függetlenül, ha tényleg azonnali lekérdezések kellenek ekkora adatmennyiségre, akkor vagy szét kell dobnod több gépre vagy előre kiszámítod azt, amit lehet. Véleményem szerint utóbbi költséghatékonyabb az esetedben.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Valszeg kompozit index (sensor_id, true) még lejjebb viszi, mert akkor - megint csak ha tud index-onlyt a Pg - a főtábláig el se kell menni, mi által az IO drámaian lecsökken, pláne a kisebb indexfájloknak nagyobb esélyük van cache-ben maradni (ezen a ponton utánanéznék a Pg konténer és bufferpool definícióinak, ha értelmezhető ez ott).

( joco01 v | 2012. 10. 04., cs – 10:17 )

A teszt adataid hogy néztek ki, kb. a valóságnak megfelelő számú eltérő sensor_id és date érték volt?

Első körben (date) és (sensor_id, date) indexeket hoznék létre, meg persze a date szerinti partícionálás is jó ötlet, amit fentebb mondtak. Aztán explain-nel kellene nézegetni, hogy mit csinál.

--
joco voltam szevasz

( saxus | 2012. 10. 04., cs – 11:18 )

Jut eszembe:

A SELECT COUNT(*) FROM table PostgreSQL-ben a tervezéséből adódóoan nem lesz gyors, mivel a párhuzamosan futó tranzakciók miatt nem tudhatja, hogy pontosan mennyi rekord van a táblában, csak ha megnézi, hogy az adott tranzakció szerint mennyit lát. Pl. elindítasz egy delete-t egy tranzakción belül, akkor a SELECT COUNT() már a törölt rekordokkal kevesebbet kell visszaadnia, de ez még egy másik tranzakció számára nem látszik, ha még nem lett commitolva a módosítás.

http://wiki.postgresql.org/wiki/Slow_Counting

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Ha tudod, hogy milyen szinten érdekel a párhuzamosan futó tranzakciók befolyása, és ha a Pg úgy támogatja az izolációs szinteket, ahogy valószínűleg támogatja (?), akkor minden, vagy szinte minden műveletet ignorálhat select, csak közölni kell vele.

15 éve nem nyúltam Pg-hez, és addig sem mélyen, így a fenti állítás általánosságban igaz a modern motorokra.

( jailhouse v | 2012. 10. 04., cs – 11:22 )

Három szó: index,index,index

Amúgy milyen verziójú a PostgreSQL?

----------------------------
Előnevelt csirke kapható!

"Három szó: index,index,index"

A szentháromság mellé említést érdemel a tengelyek számának növelése - ha még nem történt meg.

Sőt, ha időszaki kimutatáskészítésről van szó, és más nem sígét - és a feltételek adottak - nem elvetendő gondolat az aktív db-t lekopizni, és a másolatot nyúzni OLAP szinten.

( Nickname | 2012. 10. 04., cs – 11:31 )

igen, http://www.postgresql.org/docs/9.2/static/sql-explain.html kimenetet kérnénk.

addig felesleges bármit is tanácsolni.

illetve talán azt, hogy ha nem valós real_time adatok kellenek, érdemes lenne nem a zizegő adatbázisos selectecet durrogtatni kukucskálás (olap, etc) célból, hanem egy async replikált adatbázison ami mondjuk hajnal 2 és 3 között frissül?

( NoMan | 2012. 10. 04., cs – 13:19 )

Nagyon úgy fest, hogy le kell rombolnom azt az illúziót, hogy meg tudom ezt oldani élő adatokból. Mindenképpen kompromisszumokat kell kötnöm.

Tehát marad az, hogy a számításokat megcsinálom előre és feltriggerelem úgy, hogy karbantartsa a számításokat a rendszer adat változás esetén.

Hát... valószínűleg az a szakma csúcsa, amikor azzal, ami van, pont azt csinálják, amit még éppen lehet, vagy ha minimum meg tudják hasalni, hogy min kéne változtatni ahhoz, hogy mást lehessen.
Piaci szempontból pedig mindkettő akkor a legszakmaibb, ha az ár (beleértve az időegyenértékest) is szerepel ezek mellett, lehetőleg érzelmek által ne befolyásolt ceruzával írva.

- o -

Tkp. már az is az önfejlődés elleni csalás, hogy kész oprendszeren, kész motorral kezeli az ember ezeket az adatokat, miközben megírhatná a sajátjait is... miután legyártotta a saját gépét, hogy tudjanak min futni.

Ha mindent Nekem / Nekünk kéne összerakni, akkor igazából mindenki a kőbalta korából indulva építhetné fel a maga kis világát és el se jutna oda, hogy számítógépet csináljon. És persze nem érthet mindenhez minden ember. Így aki inkább szofveres beállítottságú, valószínű, hogy nem tudna nyákokat tervezni és azokra különböző mikro cpukat gyártani. Legalábbis én pl nem tudom, hogy hogyan állnék neki. :)
De amúgy igazad van, csalás, de áldozatokra mindig szükség van :)

Csak az a probléma, hogy az ilyen kompromisszumuk nem segítik elő a szakmai fejlődést :(
Szerintem pont az a szakmai fejlődés lényege, hogy az adott problémára a megfelelő eszközt veti be az ember. Írtad, hogy célhardverről van szó:
* Adatbázisból kiolvasással egy lekérdezéshez végig kell menni a teljes adatbázison, ami sűrűn tárolva, indexek nélkül is kb 2Gb saccra az alapján, amit írtál. Ezt végigolvasni tipikus mai beágyazott eszközön eltart egy ideig.
* Ha egy SD kártyát fizikailag nullázol induláskor, majd fájlrendszer nélkül sorfolytonosan írod rá az adatot binárisan, akkor:
* a lehető legtovább fogja bírni a tárolód, nem lesz rajta extra wear a fájlrendszer miatt
* by design tranzakcionális
* a lekérdezésed átlagait, összegeit is mindig írod, akkor ezeknek a lekérdezése konstans idejű lesz milliszekundum alatt
* a hardvered akár még mikrovezérlő is lehet, töredék költség és fogyasztás mellett

( NoMan | 2012. 10. 04., cs – 15:10 )

Köszönök szépen minden segítő hozzászólást.

Igazán hálás vagyok a segítségemre siető fórumozóknak.

( gabrielakos v | 2012. 10. 04., cs – 15:14 )

pg verzió? (ja közben megtaláltam 9.1.5, ez jó)
postgresql.conf -ban adtál neki memóriát?
táblán analyze volt?

--
Gábriel Ákos
http://i-logic.hu

( gabrielakos v | 2012. 10. 04., cs – 15:15 )

még egy dolog on insert / on update triggerrel szerintem megoldható lenne a folyamatos aggregálás a "gyakori kérdésekre", és akkor pillanatok alatt kapsz választ.

--
Gábriel Ákos
http://i-logic.hu

( pe2hu | 2012. 10. 05., p – 10:49 )

Pár tipp.

Mi írja az adatokat? Központi szerver program, vagy minden kliens direktben írja az adatbázist?

Ha központi szerver program, akkor az miért nem számolja ki az aktuális időpontban érvényes átlagot vagy akármilyen statisztikát, és tárolja el azt is más táblá(k)ban.

Ha nincs központi szerver program, hanem minden egyes mérőeszköz közvetlenül az adatbázist frissíti, akkor az adatbázis szervert is használhatod ilyen célra. Miért nem egy ideiglenes táblába írják a műszerek az adatokat, és azokat egy szerver oldali programmal (akár pgsql függvény on insert) feldolgozod és olyan formában tárolod el, hogy optimálisan le tudjad kérdezni.

A select count(*)-hoz egy megjegyzés.

A. Veszel annyi RAM-ot a gépbe, hogy memóriában legyen az összes index és tábla, mert ez minimum szekvenciális olvasás egy egyedi index-en. (Ha nincs megfelelő index, akkor a teljes táblán)

B. Miért nem készítesz minden egyes műszer ID-hez egy adatbázis sequence-t is (külön futó sorszám minden műszerhez minden adatrögzítéséhez). Mondjuk minden insert-kor kéred a következő számot és azt is eltárolod az adatbázis tábládban.
Ha van index a műszer azonosítón és a mérés időpontján, akkor két időpont közötti mérések számának meghatározásához lekéred az időintervallum első és utolsó bejegyzését (ez elég gyors lesz az index miatt) és a mérés sorszámokat kivonod egymásból.